• Backup Formats
• Restore Backups
• Example 1: Custom formatted backup
• Example 2: Plain-text formatted backup

Backup Formats

There are 2 recommended output formats for a backup of a postgres database using pg_dump:

1. Plain-text format

• Default format for backups
• This is simply a text file which contains the commands necessary to rebuild the database.
• Can be restored using psql or pg_restore

2. Custom archive format

• Most flexible format.
• When using this format, the default action compresses the backup. Compression however can be disabled using the —compress option.
• Must use pg_restore to restore the database
• The backup file can be opened and the contents reviewed using pg_restore
• Individual tables CAN be restored using the format.

Restore Backups

For restoring backups, you can use psql for plain-text formatted backups or you can use pg_restore to restore plain-text or custom formatted backups.

To restore a database, we recommend the following actions:

1. Drop the target database.
2. Create a new database to replace the database you dropped. This database will be empty. Make sure you're using the same database name.
3. Run the pg_restore or psql command to perform your database restore to the new, empty database you created.

Example 1: Custom-formatted backup

Example of restoring a database on a local PostgreSQL server, using a custom formatted database backup:

Linux:

Step 1: Run the su command to switch to the postgres user (Note: You will either need to be the root user or run the sudo command with su):

su - postgres

Step 2: Drop the database using dropdb:

POSTGRESQL_DIR/bin/dropdb target_database_name

Step 3: Create the database using createdb:

POSTGRESQL_DIR/bin/createdb target_database_name

Step 4: Restore the database using pg_restore:

POSTGRESQL_DIR/bin/pg_restore --verbose --username=labkey --password --host=dbhostname.amazonaws.com --format=c --clean --if-exists --no-owner --dbname=labkey dbbackupfilename.dump

In step 4, the --clean and --no-owner options are critical, especially when moving from one system to another.

Example 2: Plain text formatted backup

Example of restoring a database on a remote or local PostgreSQL server, using a plain-text formatted database backup:

Step 1: Access your PostgreSQL server via psql and specifically access the postgres database, not the LabKey one:

psql -U database_user -W -h database_server_address postgres

Step 2: Drop the database:

drop database target_database_name;

Step 3: Create the database:

create database target_database_name with owner labkey_database_user;

Step 4: Revoke access for the public role:

revoke all on database target_database_name from public;

Step 5: Use the quit command to exit PSQL:

\q

Step 6: Once back at a command prompt, run the following command to restore the database:

psql -U database_user -W -h database_server_address -d target_database_name -f BACKUP_DIR/backup_file_name.bak

The same commands can be used locally as well, just replace the database_server_address with localhost or run the psql command as the postgres user and then connect to the default postgres database by using the \l postgres; command.

Related Topics

• Backup Guidelines