How to perform base PostgreSQL backup using standard file system utilities

Today I want to show you how to perform base PostgreSQL backup using standard file system utilities. It is great example as it briefly describes basics required before moving to more advanced topics.

Initial notes

I will use Debian Jessie and PostgreSQL 9.4 database server.

debian:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 8.2 (jessie)
Release:        8.2
Codename:       jessie

postgres@debian:~$ psql -A -t -c "select version()"
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Database configuration

Create archive/wal and archive/base directories inside postgres home directory.

debian:~$ sudo su - postgres

postgres@debian:~$ mkdir -p archive/{wal,base}

archive/wal directory will be used to store Write Ahead Log file segments created during backup process, archive/base will contain basic database backup.

Define logging information required for Write Ahead Log archiving.

debian:~$ sudo sed -i -e "s/^#wal_level = minimal/wal_level = archive/" /etc/postgresql/9.4/main/postgresql.conf

Enable archive mode on database server.

debian:~$ sudo sed -i -e "s/^#archive_mode = off/archive_mode = on/" /etc/postgresql/9.4/main/postgresql.conf

Define shell command that will be used to store each completed Write Ahead Log file segment depending on /var/lib/postgresql/archive/in_progress file existence.

debian:~$ sudo sed -i -e "s/^#archive_command = ''/archive_command = 'test ! -f /var/lib/postgresql/archive/in_progress || (test ! -f /var/lib/postgresql/archive/wal/%f && cp %p /var/lib/postgresql/archive/wal/%f)'/" /etc/postgresql/9.4/main/postgresql.conf

%f parameter will be expanded to file name, %p parameter will be expanded to path name relative to the cluster data directory.

Restart PostgreSQL service to apply changes.

postgres@debian:~$ pg_ctlcluster 9.4 main restart

Backup process

I assume that archive/wal and archive/base directories inside postgres home directory are empty.

Create temporary file used inside archive_command directive to start Write Ahead Log archiving.

postgres@debian:~$ touch /var/lib/postgresql/archive/in_progress

Create checkpoint in the database and write backup label file to start backup mode.

postgres@debian:~$ psql -A -t -c "select pg_start_backup('Backup created $(date +%d/%m/%Y %H:%M) at $(hostname)')"
0/A000028

Copy database to a defined earlier location.

postgres@debian:~$ cp -pr /var/lib/postgresql/9.4/main/* /var/lib/postgresql/archive/base/

Stop backup mode and perform automatic switch to the next Write Ahead Log segment.

$ psql  -A -t -c "select pg_stop_backup()"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
0/D0000B8

Delete created earlier temporary file to stop Write Ahead Log archiving.

postgres@debian:~$ rm /var/lib/postgresql/archive/in_progress

Remove obsolete postmaster.opts and postmaster.pid files from backup.

postgres@debian:~$ rm /var/lib/postgresql/archive/base/postmaster.*

Remove Write Ahead Log file segments stored inside base backup as these files are very likely outdated and we have already gathered current files inside archive/wal directory.

postgres@debian:~$ rm -r /var/lib/postgresql/archive/base/pg_xlog/*

Move separately archived Write Ahead Log file segments to the proper backup location.

postgres@debian:~$ mv /var/lib/postgresql/archive/wal/* /var/lib/postgresql/archive/base/pg_xlog/

Now you have complete backup inside /var/lib/postgresql/archive/base/ directory.

You can always identify it by looking at the base backup.

postgres@debian:~$ cat /var/lib/postgresql/archive/base/backup_label
START WAL LOCATION: 0/A000028 (file 00000001000000000000000A)
CHECKPOINT LOCATION: 0/A000028
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2016-01-20 21:34:46 CET
LABEL: Backup created 20/01/2016 21:34 at debian

Inspect Write Ahead Log directory for further information (after moving files).

postgres@debian:~$ ls -1 /var/lib/postgresql/archive/base/pg_xlog/
000000010000000000000009
00000001000000000000000A
00000001000000000000000A.00000028.backup
00000001000000000000000B
00000001000000000000000C
00000001000000000000000D

postgres@debian:~$ cat /var/lib/postgresql/archive/base/pg_xlog/00000001000000000000000A.00000028.backup
START WAL LOCATION: 0/A000028 (file 00000001000000000000000A)
STOP WAL LOCATION: 0/D0000B8 (file 00000001000000000000000D)
CHECKPOINT LOCATION: 0/A000028
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2016-01-20 21:34:46 CET
LABEL: Backup created 20/01/2016 21:34 at debian
STOP TIME: 2016-01-20 21:35:49 CET

Notice that 000000010000000000000009 file is not required for recovery process (verify start and stop statements) as we need only files from 00000001000000000000000A to 00000001000000000000000D.

Recovery process

Stop database server.

postgres@debian:~$ pg_ctlcluster 9.4 main stop

Backup current database files.

postgres@debian:~$ mv /var/lib/postgresql/9.4/main /var/lib/postgresql/9.4/main.before_recovery

Copy archived database files.

postgres@debian:~$ cp -rp /var/lib/postgresql/archive/base /var/lib/postgresql/9.4/main

Start the database server to perform recovery.

postgres@debian:~$ pg_ctlcluster 9.4 main start

Monitor recovery process.

postgres@debian:~$ tail -f /var/log/postgresql/postgresql-9.4-main.log

2016-01-20 22:00:38 CET [3706-1] LOG:  database system was interrupted; last known up at 2016-01-20 21:34:46 CET
2016-01-20 22:00:38 CET [3706-2] LOG:  creating missing WAL directory "pg_xlog/archive_status"
2016-01-20 22:00:38 CET [3706-3] LOG:  redo starts at 0/A000090
2016-01-20 22:00:38 CET [3706-4] LOG:  consistent recovery state reached at 0/D0000B8
2016-01-20 22:00:38 CET [3706-5] LOG:  redo done at 0/D0000B8
2016-01-20 22:00:38 CET [3706-6] LOG:  last completed transaction was at log time 2016-01-20 21:35:28.853882+01
2016-01-20 22:00:39 CET [3707-1] [unknown]@[unknown] LOG:  incomplete startup packet
2016-01-20 22:00:39 CET [3706-7] LOG:  MultiXact member wraparound protections are now enabled
2016-01-20 22:00:39 CET [3705-1] LOG:  database system is ready to accept connections
2016-01-20 22:00:39 CET [3711-1] LOG:  autovacuum launcher started

Additional notes

You can automatically copy required Write Ahead Log file segments to the proper location using the following commands.

postgres@debian:~$ ls -1 archive/wal/

000000010000000000000043
000000010000000000000044
000000010000000000000045
000000010000000000000046
000000010000000000000047
000000010000000000000048
000000010000000000000049
000000010000000000000049.00000028.backup
00000001000000000000004A
00000001000000000000004B
00000001000000000000004C
00000001000000000000004D
00000001000000000000004E
00000001000000000000004F
000000010000000000000050
000000010000000000000051
000000010000000000000052
000000010000000000000053
000000010000000000000054

postgres@debian:~$ cat archive/wal/000000010000000000000049.00000028.backup

START WAL LOCATION: 0/49000028 (file 000000010000000000000049)
STOP WAL LOCATION: 0/53000050 (file 000000010000000000000053)
CHECKPOINT LOCATION: 0/49000028
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2016-01-22 12:40:36 CET
LABEL: Backup created 22/01/2016 12:40 at debian
STOP TIME: 2016-01-22 12:41:22 CET

postgres@debian:~$ eval $(sed -ne "s/START WAL LOCATION: .* (file (.*))/export wal_start=1/p"  -ne "s/STOP WAL LOCATION: .* (file (.*))/export wal_stop=1/p" $(find archive/wal -name *backup)); find archive/wal/ | sort | sed -ne "/$wal_start/,/$wal_stop/ {p}" | xargs -I {} cp {} archive/base/pg_xlog/; unset wal_start; unset wal_stop;

postgres@debian:~$ ls -1 archive/base/pg_xlog/

000000010000000000000049
000000010000000000000049.00000028.backup
00000001000000000000004A
00000001000000000000004B
00000001000000000000004C
00000001000000000000004D
00000001000000000000004E
00000001000000000000004F
000000010000000000000050
000000010000000000000051
000000010000000000000052
000000010000000000000053

Use the following commands to fix permissions and ownership if you encounter such issues.

postgres@debian:~$ find /var/lib/postgresql/9.4/main -type f -exec chmod 0600 {} ;
postgres@debian:~$ find /var/lib/postgresql/9.4/main -type d -exec chmod 0700 {} ;

postgres@debian:~$ chown -R postgres:postgres /var/lib/postgresql/9.4/main

I have described the simplest possible usage scenario which is a great starting point before trying more advanced solutions. Expect more in the following weeks.

References

PostgreSQL Documentation → Backup and Restore

PostgreSQL Documentation → Backup and Restore → Continuous Archiving and Point-in-Time Recovery (PITR) → Making a Base Backup Using the Low Level API

PostgreSQL Documentation → Server Configuration → Write Ahead Log

Initial notes#

Database configuration#

Backup process#

Recovery process#

Additional notes#

References#