ramfs_based_table_space
Differences
This shows you the differences between two versions of the page.
|
ramfs_based_table_space [2012/03/23 19:41] k2patel [Setting up a tablespace] |
ramfs_based_table_space [2020/08/10 02:35] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== 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. \\ | ||
ramfs_based_table_space.txt · Last modified: 2020/08/10 02:35 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
