How To:Use Amanda to Back Up PostgreSQL

This article is a part of the How Tos collection.

The ampgsql(8) application uses the continuous WAL archiving feature of PostgreSQL to provide online, incremental, full-database backups.

NOTE:

Tablespaces are not supported.

NOTE:

At the time of this writing, no community release includes this application. You'll need to build from a daily snapshot.

Setup

Amanda Server Configuration

You need to add the ampgsql application and a corresponding dumptype to your amanda.conf(5)

define application-tool app_ampgsql {
    comment "ampgsql"
    plugin "ampgsql"
    property "TMPDIR" "/tmp"
}

define dumptype dt_ampgsql {
    program "APPLICATION"
    application "app_ampgsql"
}

More information about application properties can be found in the man page (ampgsql(8))

NOTE:

The directory specified by TMPDIR needs to have enough free space to store an entire copy of the database

You can then add a disklist(5) entry for the server you want to backup. For example:

foo.example.com bar dt_ampgsql

PostgreSQL Server Configuration

You need to edit your server configuration (usually postgresql.conf) to enable continuous archiving. Add the following line:

archive_command = 'test ! -f /path/to/archivedir/%f && cp %p /path/to/archivedir/%f'

For PostgreSQL 8.3 and newer, you also need to add

archive_mode = on

Amanda Client Configuration

On the client, you need to add the connection information to your amanda-client.conf(5)

property "PG-DATADIR" "/var/pgsql/data"
property "PG-ARCHIVEDIR" "/var/pgsql/archive"
property "PG-HOST" "/tmp"

• PG-DATADIR should be the data/cluster directory for your PostgreSQL server
• PG-ARCHIVEDIR should be the directory that your archive_command copies files to
• PG-HOST can either be a hostname or a directory. TCP and UNIX sockets are used to connect to the server, respectively

More information about application properties can be found in the man page (ampgsql(8))

Restore

After extracting the backup images (e.g. using amrestore(8), you should have an archive directory with the archived WAL log files and, if you restored a base backup, a data directory with the database/cluster directory.

You need to copy the contents of the data directory to an appropriate location, then write a restore.conf file. This is usually simple:

restore_command = 'cp /path/to/restoredir/archive/%f "%p"'

See the PostgreSQL manual for more options.

Once that's done, the database server should recover once you start it. You can monitor its log messages for progress information.