Backup / restore of PostgreSQL database servers
Creation Date: November 08, 2006
Revision Date: April 23, 2013
Product: DS‑Client (LINUX)
Summary
This article covers various backup/restore issues for PostgreSQL database backup sets.
The following requirements must be met to perform backup / restore of PostgreSQL Database Servers.
General requirements:
• The user who performs PostgreSQL backup / restore should have knowledge about the PostgreSQL database structure.
• Configure each PostgreSQL database to be able to connect to other PostgreSQL instances:
• In <pg_data_path>/postgresql.conf, add the line “listen_addresses='*' as follows:
# CONNECTIONS AND AUTHENTICATION
listen_addresses='*'
where <pg_data_path> is the path to the configuration files (usually "/var/lib/pgsql/data" for the free installation from www.postgresql.org, or "/usr/local/pgsql/data" for the commercial package from www.commandprompt.com.
DS-Client computer requirements:
• The psql and pg_dump utilities must be listed in the PATH environment variable of the user 'root' on the DS-Client computer.
• The DS-Client computer's psql and pg_dump utilities must be compatible with the PostgreSQL version running on the source machine (PostgreSQL server).
• Configure the PostgreSQL database on the DS-Client computer to be able to connect to other PostgreSQL instances as described in the General Requirements above.
PostgreSQL server requirements:
• PostgreSQL Server should be a certified product.
• Configure each target PostgreSQL database to be able to connect to other PostgreSQL instances (to perform backup and restore) as described in the General Requirements above.
Database dump configuration
You have the option to specify the path of the PostgreSQL database dump app that you want to use (either when creating a new backup set or editing an existing one). If you do not specify a path when creating the backup set, DS-Client will pick one on its own and this will be displayed in the Backup Set Properties - Connection Options tab.
Connecting to PostgreSQL using non-standard ports
To back up a PostgreSQL Server that is using non-standard ports, you must specify the port number when creating the PostgreSQL backup set.
• You can specify the port numbers when creating the PostgreSQL backup set:
1. On the Sets menu, click New Backup Set.
2. Select PostgreSQL, and then click Next.
3. In the Select the source for this backup set dialog box, select Ask for database credentials.
4. In the credentials dialog box, enter the port number that the PostgreSQL Server is configured to use.
• You can also change the port number by editing the backup set properties: Backup Set Properties > Connection Options > Ask for database credentials: Port Number
Restore requirements
• The PostgreSQL database must not be in use when you perform the restore. This requirement applies to all restores (to original or to alternate location).
• The PostgreSQL server where you are restoring must be compatible with the version of the PostgreSQL server that was backed up. This is only an issue if the target restore server is different (Alternate Restore Location), or has been upgraded since the backup.
In general, backups of databases residing on an older DB instance can be restored on a newer DB instance (but not the other way around).