How to perform base PostgreSQL backup using pg_basebackup utility

I have already described a simple way to perform base PostgreSQL backup using file system utilities. Now I want to show you a different approach using pg_basebackup utility.

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

Configure database server

Set wal_level to at least archive degree to ensure that enough information is logged to perform Write Ahead Log archiving.

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

Increase upper limit of the number of simultaneously running Write Ahead Log sender processes.

debian:~$ sudo sed -i -e "s/^#max_wal_senders = 0/max_wal_senders = 2/" /etc/postgresql/9.4/main/postgresql.conf 

Create database role which is allowed to carry out replication/backup mode.

postgres@debian:~$ psql -c "CREATE ROLE rep REPLICATION LOGIN ENCRYPTED PASSWORD 'password';"

Allow postgres user to perform local backup.

debian:~$ cat << EOF | sudo tee -a /etc/postgresql/9.4/main/pg_hba.conf
local   replication     postgres                                peer
EOF

Allow rep role to perform remote database backup.

debian:~$ cat << EOF | sudo tee -a /etc/postgresql/9.4/main/pg_hba.conf
host    replication     rep              0.0.0.0/0              md5
EOF

Restart PostgreSQL service to apply changes.

postgres@debian:~$ pg_ctlcluster 9.4 main restart

Backup process

Create ready to restore local backup as postgres user and store it inside pgbackup directory.

postgres@debian:~$ pg_basebackup -x -D pgbackup
postgres@debian:~$ ls pgbackup/
$ ls -l pgbackup/
total 76
-rw------- 1 postgres postgres  208 Jan 22 03:02 backup_label
drwx------ 5 postgres postgres 4096 Jan 22 03:02 base
drwx------ 2 postgres postgres 4096 Jan 22 03:02 global
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_clog
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_dynshmem
drwx------ 4 postgres postgres 4096 Jan 22 03:02 pg_logical
drwx------ 4 postgres postgres 4096 Jan 22 03:02 pg_multixact
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_notify
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_replslot
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_serial
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_snapshots
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_stat
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_subtrans
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_tblspc
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_twophase
-rwx------ 1 postgres postgres    4 Jan 22 03:02 PG_VERSION
drwx------ 3 postgres postgres 4096 Jan 22 03:02 pg_xlog
-rwx------ 1 postgres postgres   88 Jan 22 03:02 postgresql.auto.conf
postgres@debian:~$ cat pgbackup/pgbackup/backup_label 
START WAL LOCATION: 0/2D000028 (file 00000001000000000000002D)
CHECKPOINT LOCATION: 0/2D000028
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-01-22 03:02:47 CET
LABEL: pg_basebackup base backup

Create ready to restore local backup as postgres user, store it inside pglabbackup directory and define custom label.

postgres@debian:~$ pg_basebackup -l "Backup created at $(hostname) on $(date)" -x -D pglabbackup
postgres@debian:~$ cat pglabbackup/base_backup
START WAL LOCATION: 0/2E000028 (file 00000001000000000000002E)
CHECKPOINT LOCATION: 0/2E000028
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-01-22 03:04:14 CET
LABEL: Backup created at debian on Fri Jan 22 03:04:14 CET 2016

Create ready to restore backup from remote server using rep role with defined replication attribute, store it inside pgrembackup directory.

postgres@debian:~$ pg_basebackup -x -h 10.0.2.15 -U rep -D pgrembackup

Create ready to restore backup using two connections in parallel (to stream the transaction log while the backup is created) from remote server using rep role with defined replication attribute, store it inside pgremsbackup directory.

postgres@debian:~$ pg_basebackup -X stream -h 10.0.2.15 -U rep -D pgremsbackup

Create ready to restore gzipped tar backup from remote server using rep role with defined replication attribute, store it inside pgremtarbackup directory.

postgres@debian:~$ pg_basebackup -x -Ft -z -h 10.0.2.15 -U rep -D pgremtarbackup
postgres@debian:~$ ls -l pgremtarbackup/
total 2396
-rw-r--r-- 1 postgres postgres 2453147 Jan 22 03:11 base.tar.gz

Recovery process

Stop database server.

postgres@debian:~$ pg_ctlcluster 9.4 main stop

Backup current database files.

postgres@debian:~$ mv main main.backup

Copy archived database files.

postgres@debian:~$ cp -r../pgbackup main

Start the database server to perform recovery.

postgres@debian:~$ pg_ctlcluster 9.4 main start

Monitor recovery process using log file.

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

Additional notes

I have described the simplest possible scenario, so it does not require creating recovery.conf file to perform more advanced archive recovery of a database.

References

PostgreSQL Documentation → Backup and Restore

PostgreSQL Documentation → pg_basebackup

PostgreSQL Documentation → CREATE ROLE

PostgreSQL Documentation → Client Authentication → The pg_hba.conf File

PostgreSQL Documentation → Server Configuration → Replication

PostgreSQL Documentation → Server Configuration → Write Ahead Log

Milosz Galazka's Picture

About Milosz Galazka

Milosz is a system administrator working for a successful Polish company and a long time supporter of Free Software Foundation and Debian operating system.

Gdansk, Poland https://sleeplessbeastie.eu