How To:Use Amanda to Back Up PostgreSQL: Difference between revisions
(Add a brief how-to) |
(add information about the backup user) |
||
Line 33: | Line 33: | ||
For PostgreSQL 8.3 and newer, you also need to add | For PostgreSQL 8.3 and newer, you also need to add | ||
archive_mode = on | archive_mode = on | ||
{{Note|Amanda will need access to superuser privileges. You can create a new role using the [http://www.postgresql.org/docs/8.3/static/app-createuser.html createuser] program or [[http://www.postgresql.org/docs/8.3/static/role-attributes.html#AEN25855 CREATE ROLE]. }} | |||
== Amanda Client Configuration == | == Amanda Client Configuration == | ||
Line 40: | Line 42: | ||
property "PG-ARCHIVEDIR" "/var/pgsql/archive" | property "PG-ARCHIVEDIR" "/var/pgsql/archive" | ||
property "PG-HOST" "/tmp" | property "PG-HOST" "/tmp" | ||
property "PG-USER" "amandabackup" | |||
property "PG-PASSFILE" "/etc/amanda/pg_passfile" | |||
* <code>PG-DATADIR</code> should be the data/cluster directory for your PostgreSQL server | * <code>PG-DATADIR</code> should be the data/cluster directory for your PostgreSQL server | ||
* <code>PG-ARCHIVEDIR</code> should be the directory that your <code>archive_command</code> copies files to | * <code>PG-ARCHIVEDIR</code> should be the directory that your <code>archive_command</code> copies files to | ||
* <code>PG-HOST</code> can either be a hostname or a directory. TCP and UNIX sockets are used to connect to the server, respectively | * <code>PG-HOST</code> can either be a hostname or a directory. TCP and UNIX sockets are used to connect to the server, respectively | ||
* <code>PG-USER</code> determines the user Amanda will connect as. It must have superuser priveleges | |||
* <code>PG-PASSFILE</code> is the credentials file that Amanda will use to connect. | |||
The credentials file will need to have a line that matches the connection parameters. Based on the example, the following would be appropriate: | |||
/tmp:*:*:amandabackup:my_backup_password | |||
{{Note|The credentials file needs to be owned by the user Amanda will run as and must have read (and perhaps write) access for that user ''only''. Otherwise it will be ignored.}} | |||
More information about application properties can be found in the man page ({{man|8|ampgsql}}) | More information about application properties can be found in the man page ({{man|8|ampgsql}}) |
Revision as of 00:31, 27 February 2009
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
{{Note|Amanda will need access to superuser privileges. You can create a new role using the createuser program or [CREATE ROLE. }}
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" property "PG-USER" "amandabackup" property "PG-PASSFILE" "/etc/amanda/pg_passfile"
PG-DATADIR
should be the data/cluster directory for your PostgreSQL serverPG-ARCHIVEDIR
should be the directory that yourarchive_command
copies files toPG-HOST
can either be a hostname or a directory. TCP and UNIX sockets are used to connect to the server, respectivelyPG-USER
determines the user Amanda will connect as. It must have superuser privelegesPG-PASSFILE
is the credentials file that Amanda will use to connect.
The credentials file will need to have a line that matches the connection parameters. Based on the example, the following would be appropriate:
/tmp:*:*:amandabackup:my_backup_password
NOTE: | The credentials file needs to be owned by the user Amanda will run as and must have read (and perhaps write) access for that user only. Otherwise it will be ignored. |
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.