Backup / Restore of PostgreSQL Database Servers

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.

• 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:

• For PostgreSQL 7.x: Enable TCP/IP connection. In <pg_data_path>/postgresql.conf, uncomment the line “tcpip_socket=false” and set the option to true:

• For PostgreSQL 8.x / 9.x: In <pg_data_path>/postgresql.conf, add the line “listen_addresses='*' as follows:

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.

• 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.

• 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.

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.

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:

3. In the Select the source for this backup set screen, select Ask for database credentials.

4. In the credentials screen, 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

• 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).

Troubleshooting: Linux DS-Client with an Embedded database backing up PostgreSQL versions 9.2 (and up)

The v12 Linux DS-Client ships with an embedded PostgreSQL 9.1.1 database. If you want this DS-Client to back up a higher version of PostgreSQL (e.g. 9.2), you must install the compatible version on the DS-Client computer for it to use the compatible pg_dump tools.

However, due to a startup timing issue (encountered if your Linux DS-Client is configured to automatically start after reboot), the DS-Client defaults to using the embedded (9.1.1) pg_dump tools. This issue will occur every time the machine is rebooted.

Each time after rebooting the machine, restart the DS-Client service.

Edit the /etc/init.d/dsclient file by adding /opt/PostgreSQL/9.2/bin (or whatever the correct path is) to the PATH variable.