PostgreSQL: Backup and Restore Databases

In order to backup PostgreSQL, you can use the:

• SQL dumps (pg_dump and pg_dumpall)
• Copying PostgreSQL files
• Continuous archiving (backing up) with pg_basebackup and WAL files.

Backup PostgreSQL with SQL Dumps

You can use the pg_dump tool to take an SQL dump of any database and export it to a file:

# pg_dump mytestdb -f /backup/mytestdb.dump

You can immediately compress the dump file with gzip:

# pg_dump -U postgres mytestdb | gzip > /backup/mytestdb.gz

The advantage of this backup method:

• The database is not locked
• Backup is independent of SQL and Linux version
• The dump text file can be edited manually

However, it can take a long time to back up large PostgreSQL databases using pg_dump.

If you want to restore a PostgreSQL database from a text dump, you need to create it first. To create a new database run:

$ sudo -u postgres psql -U postgres -c \
"CREATE DATABASE mynewdb"

To restore data from a dump to a new database:

# psql mynewdb < /backup/mytestdb.dump

Add the -1 parameter to perform a database restore in a transaction:

# psql -1 mynewdb < /backup/mytestdb.dump

In this case, if an error occurs while importing data, all changes are automatically rolled back.

How to Backup PostgreSQL Files?

You must stop the PostgreSQL service to copy the PostgreSQL files:

# systemctl stop postgresql.service
# tar -cjf /backup/mypsql.tar.gz /usr/local/pgsql/data
# systemctl start postgresql.service

In this case, the speed of the PostgreSQL backup will be several times faster than if pg_dump is used.

But:

• The PostgreSQL service must be stopped;
• The paths depend on the PostgreSQL version and the operating system;
• You cannot select which database to back up (all databases will be archived).

To restore PostgreSQL from a file backup, simply stop PostgreSQL, delete the data directory, and extract the archive:

# tar -xf /backup/mypsql.tar.gz

Configure PostgreSQL Continuous Archiving

PostgreSQL creates WAL (Write Ahead Log) log files when writing data. You can create a full backup using pg_basebackup and then archive the WAL files on a regular basis.

The following settings must be made in the postgresql.conf configuration file in order to use continuous archiving:

wal_level = replica
archive_mode = on
archive_command = ‘test ! -f /mnt/archivedir/%f && cp %p /mnt/archivedir/%f’

Restart PostgreSQL:

# systemctl restart postgresql.service

Create a basic backup:

# pg_basebackup -D p /mnt/archivedir -Ft –z