As long as you have
correctly backed up your database and other files, the instructions in this topic will help you restore your system from those backups if necessary.
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
Note: Individual tables CANNOT be restored using the format. In order to restore an individual table, you will need to restore the entire table first. Then copy the table contents from the restored database to the working database. For information about backing up and restoring tables, please consult the PostgreSQL.org docs for your specific version of PostgreSQL.
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.
Note: Backup file size (uncompressed) can be roughly 2x the size of the database. This is due overhead necessary to allow pg_restore the ability to restore individual tables, etc.
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:
- Drop the target database.
- Create a new database to replace the database you dropped. This database will be empty. Make sure you're using the same database name.
- 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):
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:
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
