PostgreSQL dump and restore

Using Jiglu scripts

Jiglu comes with scripts to manage the backup of the database and Lucene text search indexes. They create and restore a zip file that contains a PostgreSQL dump file and the indexes. The scripts are located in the /opt/jiglu/db/postgresql directory.

Backup

The backup script has the following parameters:

dump.sh host owner owner-password user filename [dbonly]
host The host where the target database resides.
owner The name of the user that own the database.
owner-password The password for the owner of the database. Or set ${JIGLU_OWNER_PASSWORD}.
user The name of the user and database that will be dumped.
filename The filename for the database dump file.
dbonly Specify this to only backup the database and not the lucene indexes. The resulting file will just be the database dump and will not be zipped.

Restore

The restore script has the following parameters:

restore.sh olduser host postgres-superuser postgres-password owner owner-password user user-password filename
olduser The name of the old user of the database before it was dumped out.
oldowner The name of the old owner of the database before it was dumped out.
host The host where the target database resides.
postgres-superuser The name of the postgres superuser.
postgres-password The password of the postgres user.
owner The name of the user that will own the database.
owner-password The password for the owner of the database.
user The name of the user and database that will be restored to.
user-password The password for the database user.
filename The path to the dump file. If this is a zip file, the Lucene indexes will also be restored.

Using PostgreSQL commands

Backup

If you prefer to use the PostgreSQL commands directly you will need to use a command similar to the below to backup the jiglu database:

pg_dump --create --oids --format=c --file=backup.dmp -U postgres jiglu

You will be prompted for the password of the postgres user.

Restore

To restore the database on the same operating system and with the same database name you will first need to create the user and owner user:

psql postgres postgres
create user jiglu password 'secret';
create user jigluowner password 'secret';

You will first be prompted for the password of the postgres user. You will need to change the passwords in the two SQL lines as appropriate. Returning to the OS shell you then need to run:

pg_restore -C -d postgres -U postgres backup.dmp

You will be prompted for the password of the postgres user.

Note that if you restore the database in this way it will then be necessary to rebuild the search indexes.

Written by Stephen Hebditch. Published on .
2.0.0
How to backup and restore the Jiglu database to and from a file.