====== Tablespace in Memory ======


==== Setting up a tablespace ====


== From PostgreSQL documentation: ==


Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files \\
representing database objects can be stored. Once created, a tablespace can be referred to by name when creating \\
database objects.The location must be an existing, empty directory that is owned by the PostgreSQL system user. \\
All objects subsequently created within the tablespace will be stored in files underneath this directory. \\
Creation of the tablespace itself must be done as a database superuser, but after that you can allow ordinary \\
database users to make use of it. To do that, grant them the CREATE privilege on it. 

First create the directory where tablespace will be stored. It must reside on your ramfs filesystem.

<code bash>
    	mkdir /mnt/ramfs/pgdata
    	chown postgres:postgres /mnt/ramfs/pgdata
    	chmod go-rwx /mnt/ramfs/pgdata
</code>    	    

== To create the tablespace and grant permissions on it: ==

<code sql>
    	CREATE TABLESPACE $TABLESPACE_NAME LOCATION '/mnt/ramfs/pgdata';
	GRANT CREATE ON TABLESPACE $TABLESPACE_NAME TO $ROLE_NAME;
</code>

In addition to setting up entries in internal PostgreSQL tables, a file named “PG_VERSION” will be created under \\
the tablespace directory. Get familliar with it and all other possible files (if) created because we need to \\
restore this structure later.

We are defining the tablespace into `template1′ database. Normally all other databases inherit from `template1′. \\
This way we don’t need to redefine it every time and it can be used for all subsequently created objects in the database.

It is not good idea for an application to access the database as superuser. Create a new role to use with your application.

Warning: Depending on your configuration you may need to tune SELinux policy to allow user `postgres’ \\
access to the tablespace directory.

== Creating databases ==

To create a new database in the tablespace use the command:

<code sql>
       CREATE DATABASE $DATABASE_NAME WITH TABLESPACE = $TABLESPACE_NAME;
</code>

All other objects created within this database will be stored in the same tablespace. \\
Tablespaces can be used when creating tables as well.

== Dumping and restoring the database ==


We need to dump and restore the database structure every time the ramfs filesystem is unmounted. Applications using memory databases should be aware of data loss and responsible for its recreation.

== To dump the database schema: ==

<code sql>
     pg_dump --create --schema-only --file=$FILENAME --host=$HOST -U postgres $DATABASE_NAME
</code>

== To restore the database schema: ==


<code sql>
     psql --file $FILENAME -U $ROLE_NAME --dbname postgres --host $HOST
</code>

==== Automating the process ====


To make this setup work every time when the server is restarted, we need to edit `/etc/init.d/postgresql’. Make sure the items appear in the correct order.

=== When stopping the database server: ===


== Dump database schema: ==

<code bash>
  pg_dump --create --schema-only --file=/root/memdb.sql --host=localhost -U postgres memdb
</code>         

Stop postmaster (original init code)
== Unmount ramfs: ==
   
<code bash>
         umount /mnt/ramfs
</code>

=== When starting the database server: ===


== Mount ramfs: ==


<code bash>
    if [ ! -d /mnt/ramfs ]; then
        mkdir -p /mnt/ramfs
    fi
    mount -t ramfs none /mnt/ramfs
</code>    	

== Recreate PostgreSQL tablespace directory: ==


<code bash>
    	mkdir /mnt/ramfs/pgdata
    	echo $PGMAJORVERSION > /mnt/ramfs/pgdata/PG_VERSION
    	chown -R postgres:postgres /mnt/ramfs/pgdata
    	chmod -R go-rwx /mnt/ramfs/pgdata
</code>


== Start postmaster (original init code) ==

Drop the database because it exists in PostgreSQL system tables only.

<code bash>
         psql --quiet -U postgres --host localhost --command "DROP DATABASE memdb;" 2>/root/psql.log
</code>         
    
== Restore the database schema: ==

    
<code bash>
         psql --quiet --file /root/memdb.sql -U $ROLE_NAME -d postgres --host localhost 2>/root/psql.log
</code>

==== Summary ====


You have just learned how to set up memory storage for the PostgreSQL database server. \\

[[http://magazine.redhat.com/2007/12/12/tip-from-an-rhce-memory-storage-on-postgresql/ | Referance]]