How To:Use Amanda to Back Up PostgreSQL: Difference between revisions
(→Amanda Client Configuration: add link to more information about the credentials file) |
Bluethundr (talk | contribs) |
||
(19 intermediate revisions by 6 users not shown) | |||
Line 1: | Line 1: | ||
{{How To Header}} | {{How To Header}} | ||
The {{man|8|ampgsql}} application uses the [http://www.postgresql.org/docs/ | The {{man|8|ampgsql}} application uses the [http://www.postgresql.org/docs/current/static/continuous-archiving.html continuous WAL archiving] feature of PostgreSQL (8.0+) to provide online, incremental, full-database backups. | ||
{{Note|Tablespaces are not currently supported.}} | {{Note|Tablespaces are not currently supported.}} | ||
= Setup = | = Setup = | ||
Line 18: | Line 16: | ||
define dumptype dt_ampgsql { | define dumptype dt_ampgsql { | ||
global # You might need this if you're setting 'auth "bsdtcp"' in your global dumptype settings, for example | |||
program "APPLICATION" | program "APPLICATION" | ||
application "app_ampgsql" | application "app_ampgsql" | ||
} | } | ||
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}}) | ||
Line 29: | Line 29: | ||
== PostgreSQL Server Configuration == | == PostgreSQL Server Configuration == | ||
First, create a directory for PostgreSQL to archive WAL files to, commonly a sibling of the postgres data directory. This example uses <code>/var/lib/pgsql/archive</code>. Make sure that the user postmaster (the PostgreSQL server) runs as can create files in <code>/var/lib/pgsql/archive</code>, and that the Amanda user has at least read and execute permissions on the directory, and preferably also write permissions. | |||
You need to edit your server configuration (usually <code>postgresql.conf</code>) to enable continuous archiving. Add the following line: | You need to edit your server configuration (usually <code>postgresql.conf</code>) to enable continuous archiving. Add the following line: | ||
archive_command = 'test ! -f / | archive_command = 'test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f' | ||
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 | {{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]. }} | ||
With the postgresql admin user, try: | |||
createuser -s amandabackup -P | |||
Then add this user permission to connect to all databases from local or localhost in pg_hba.conf: | |||
echo "host all amandabackup 127.0.0.1/32 md5" >> pg_hba.conf | |||
== Amanda Client Configuration == | == Amanda Client Configuration == | ||
On the client, you need to add the connection information to your {{man|5|amanda-client.conf}} | On the client, you need to add the connection information to your {{man|5|amanda-client.conf}} | ||
property "PG-DATADIR" "/var/pgsql/data" | property "PG-DATADIR" "/var/lib/pgsql/data" | ||
property "PG-ARCHIVEDIR" "/var/pgsql/archive" | property "PG-ARCHIVEDIR" "/var/lib/pgsql/archive" | ||
property "PG-HOST" "/tmp" | property "PG-HOST" "/tmp" | ||
property "PG-USER" "amandabackup" | property "PG-USER" "amandabackup" | ||
Line 51: | Line 61: | ||
* <code>PG-PASSFILE</code> is the credentials file that Amanda will use to connect. | * <code>PG-PASSFILE</code> is the credentials file that Amanda will use to connect. | ||
The [http://www.postgresql.org/docs/ | The [http://www.postgresql.org/docs/current/static/libpq-pgpass.html 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 | /tmp:*:*:amandabackup:my_backup_password | ||
Line 59: | Line 69: | ||
= Restore = | = Restore = | ||
You need to | == Using amfetchdump or amrestore == | ||
restore_command = 'cp / | {{man|8|amfetchdump}} and {{man|8|amrestore}} extract the backup images from the tape, but do not run {{man|8|ampgsql}} to extract the files from the backup images. | ||
See the [http://www.postgresql.org/docs/ | |||
First, you need to extract the base image. The following example assumes that the base backup image is named <code>example.com.foo.20090312161303.0</code> | |||
# tar --extract --file example.com.foo.20090312161303.0 | |||
# mkdir data archive | |||
# tar --extract --directory data --file data_dir.tar | |||
# tar --extract --directory archive --file archive_dir.tar | |||
Next, you need to extract each incremental. The following example assumes that there are two incremental backup images, named <code>example.com.foo.20090313161303.1</code> (a level 1 incremental) and <code>example.com.foo.20090314161303.2</code> (a level 2 incremental) | |||
# tar --extract --directory archive example.com.foo.20090313161303.1 | |||
# tar --extract --directory archive example.com.foo.20090314161303.2 | |||
== Using amrecover == | |||
After running {{man|8|amrecover}}, you should have a newly-created <code>archive</code> directory and, if you restored a base backup, a <code>data</code> directory. | |||
== Common steps == | |||
The <code>archive</code> directory will contain any archived WAL log files. It may be empty. The <code>data</code> directory will contain a complete backup of the database/cluster directory (directories like pg_xlog and pg_clog). | |||
You need to move the contents of the <code>data</code> directory to an appropriate location. Afterwards, you may need to change the ownership and permissions of the files. they should be owned by the user that PostgreSQL runs as and only be accessible to that user (0600 for files, 0700 for directories). | |||
Then put a <code>recovery.conf</code> file in the (relocated) <code>data</code> directory. This is usually simple: | |||
restore_command = 'cp /var/lib/pgsql/archive/%f "%p"' | |||
See the [http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-PITR-RECOVERY 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. | Once that's done, the database server should recover once you start it. You can monitor its log messages for progress information. | ||
If you encounter problems, once thing to try is removing the <code>data/pg_xlog</code> directory before starting the database server for recovery. |
Latest revision as of 15:32, 1 June 2011
This article is a part of the How Tos collection.
The ampgsql(8) application uses the continuous WAL archiving feature of PostgreSQL (8.0+) to provide online, incremental, full-database backups.
NOTE: | Tablespaces are not currently supported. |
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 { global # You might need this if you're setting 'auth "bsdtcp"' in your global dumptype settings, for example 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
First, create a directory for PostgreSQL to archive WAL files to, commonly a sibling of the postgres data directory. This example uses /var/lib/pgsql/archive
. Make sure that the user postmaster (the PostgreSQL server) runs as can create files in /var/lib/pgsql/archive
, and that the Amanda user has at least read and execute permissions on the directory, and preferably also write permissions.
You need to edit your server configuration (usually postgresql.conf
) to enable continuous archiving. Add the following line:
archive_command = 'test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%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. |
With the postgresql admin user, try:
createuser -s amandabackup -P
Then add this user permission to connect to all databases from local or localhost in pg_hba.conf:
echo "host all amandabackup 127.0.0.1/32 md5" >> pg_hba.conf
Amanda Client Configuration
On the client, you need to add the connection information to your amanda-client.conf(5)
property "PG-DATADIR" "/var/lib/pgsql/data" property "PG-ARCHIVEDIR" "/var/lib/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
Using amfetchdump or amrestore
amfetchdump(8) and amrestore(8) extract the backup images from the tape, but do not run ampgsql(8) to extract the files from the backup images.
First, you need to extract the base image. The following example assumes that the base backup image is named example.com.foo.20090312161303.0
- tar --extract --file example.com.foo.20090312161303.0
- mkdir data archive
- tar --extract --directory data --file data_dir.tar
- tar --extract --directory archive --file archive_dir.tar
Next, you need to extract each incremental. The following example assumes that there are two incremental backup images, named example.com.foo.20090313161303.1
(a level 1 incremental) and example.com.foo.20090314161303.2
(a level 2 incremental)
- tar --extract --directory archive example.com.foo.20090313161303.1
- tar --extract --directory archive example.com.foo.20090314161303.2
Using amrecover
After running amrecover(8), you should have a newly-created archive
directory and, if you restored a base backup, a data
directory.
Common steps
The archive
directory will contain any archived WAL log files. It may be empty. The data
directory will contain a complete backup of the database/cluster directory (directories like pg_xlog and pg_clog).
You need to move the contents of the data
directory to an appropriate location. Afterwards, you may need to change the ownership and permissions of the files. they should be owned by the user that PostgreSQL runs as and only be accessible to that user (0600 for files, 0700 for directories).
Then put a recovery.conf
file in the (relocated) data
directory. This is usually simple:
restore_command = 'cp /var/lib/pgsql/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.
If you encounter problems, once thing to try is removing the data/pg_xlog
directory before starting the database server for recovery.