PostgreSQL like any other DB, has to be backed up from time to time. From one side to assure recovery from other to sleep better during the night :). In order to simplify things, there are couple major concepts to sync first:

• Restore - The physical restore of files (datafiles, configuration files, logs and so on)
• Recovery - The synchronization and adjustments between these files. For example, replying an XLOG file is a recovery operation, while restoring the file for that XLOG to be applied on is the restore operation.

Basically there are two ways how a PostgreSQL database can be backed up:

• Physically
• Logically

Base backup is done in 3 steps

1. Ensure that WAL archiving is enabled and working.
2. Connect to the database as a superuser and issue the command:
3. SELECT pg_start_backup('label'); you can use SELECT pg_start_backup('label', true); - to minimaze the checkpoint and start ASAP
4. Perform a backup (either via rsync or cp or others)
5. SELECT pg_stop_backup();

-bash-4.1$ psql -c "select pg_start_backup('initial_backup');"
 pg_start_backup
-----------------
 0/14000028
(1 row)

rsync -cva --inplace --exclude=*pg_xlog* /var/lib/pgsql/9.6/data/ 192.168.0.11:/var/lib/pgsql/9.6/data/
[email protected]'s password:
sending incremental file list
./
backup_label
postgresql.conf
postmaster.pid
base/13275/
base/13275/16384
base/13275/pg_internal.init
global/
global/pg_control
global/pg_internal.init
pg_log/postgresql-Thu.log
pg_notify/
pg_stat/
pg_stat_tmp/
pg_stat_tmp/db_0.stat
pg_stat_tmp/db_13275.stat
pg_stat_tmp/global.stat
 
sent 152804 bytes  received 2005 bytes  20641.20 bytes/sec
total size is 22936284  speedup is 148.16

-bash-4.1$ psql -c "select pg_stop_backup();"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/14000130
(1 row)
 
-bash-4.1$

This will copy all files from the source folder into the destination folder, while the PostgreSQL server is running. Please do not forget to exit the backup mode. Even though it will be safe to leave it as it is. It is a good practice to finish the backup mode as soon as possible.

You can use pg_basebackup which will perform all steps automatically.

-bash-4.1$ pg_basebackup -U postgres -D /backup/
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
-bash-4.1$ ls -lart
total 128
dr-xr-xr-x. 23 root     root      4096 Mar 20 09:53 ..
-rwxrwx---.  1 postgres postgres 22417 Mar 20 10:49 postgresql.conf
-rwxrwx---.  1 postgres postgres     4 Mar 20 10:49 PG_VERSION
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_snapshots
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_serial
-rwxrwx---.  1 postgres postgres  1636 Mar 20 10:49 pg_ident.conf
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_dynshmem
-rw-------.  1 postgres postgres   208 Mar 20 10:49 backup_label
drwxrwx---.  6 postgres postgres  4096 Mar 20 10:49 base
-rwxrwx---.  1 postgres postgres    88 Mar 20 10:49 postgresql.auto.conf
drwxrwx---.  3 postgres postgres  4096 Mar 20 10:49 pg_xlog
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_twophase
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_tblspc
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_subtrans
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_stat_tmp
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_notify
drwxrwx---.  4 postgres postgres  4096 Mar 20 10:49 pg_multixact
drwxrwx---.  4 postgres postgres  4096 Mar 20 10:49 pg_logical
-rwxrwx---.  1 postgres postgres  4403 Mar 20 10:49 pg_hba.conf
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_stat
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_replslot
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_log
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_commit_ts
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 pg_clog
drwxrwx---.  2 postgres postgres  4096 Mar 20 10:49 global
drwxr-xr-x. 20 postgres postgres  4096 Mar 20 10:49 .
-bash-4.1$ pwd
/backup
-bash-4.1$

You can of course configure more sophisticated options, such as a tar file using gzip utilities. After the base backup is done, it a copy of the database to a specific point of time, that copy can be pushed forward (recovered) via XLOGs. The barman application, which is configured in the next section, uses pg_basebackup in order to perform the backups.

You can also use the pg_basebackup tool to take the backup, instead of manually copying the files. This tool will do the equivalent of pg_start_backup(), copy and pg_stop_backup() steps automatically, and transfers the backup over a regular PostgreSQL connection using the replication protocol, instead of requiring file system level access. pg_basebackup does not interfere with file system level backups taken using pg_start_backup()/pg_stop_backup().

Some file system backup tools emit warnings or errors if the files they are trying to copy change while the copy proceeds. When taking a base backup of an active database, this situation is normal and not an error. However, you need to ensure that you can distinguish complaints of this sort from real errors. For example, some versions of rsync return a separate exit code for “vanished source files”, and you can write a driver script to accept this exit code as a non-error case. Also, some versions of GNU tar return an error code indistinguishable from a fatal error if a file was truncated while tar was copying it. Fortunately, GNU tar versions 1.16 and later exit with 1 if a file was changed during the backup, and 2 for other errors.

It is not necessary to be concerned about the amount of time elapsed between pg_start_backup and the start of the actual backup, nor between the end of the backup and pg_stop_backup; a few minutes' delay won't hurt anything. (However, if you normally run the server with full_page_writes disabled, you might notice a drop in performance between pg_start_backup and pg_stop_backup, since full_page_writes is effectively forced on during backup mode.) You must ensure that these steps are carried out in sequence, without any possible overlap, or you will invalidate the backup.

Logical backup refers to simple export of a data, achieved by the pg_dump. It is important to note that logical backup cannot be used in conjunction of XLOG or other incremental features, but it is very convenient for data migration.

Logical backup can also be configured in many ways, as simple as to dump a particular database in SQL script file. In nutshell there are two things which you should remember:

1. Dump global data: like roles and users with pg_dumpall
2. Dump a particular database: with pg_dump

Furthermore, the best way is to use a customer format “-Fc” in order to preserve consistency across versions:

-bash-4.2$ pg_dump -Fc -C hunter_dev > hunter_dev_backup.sql   <- This will dump database "hunter_dev" with "c"ustom format and it will include "C"reate statement.

-bash-4.2$
-bash-4.2$ ls -lart
-rw-r--r--.  1 postgres postgres 17187218 Aug  1 12:43 hunter_dev_backup.sql

Our backup will not be so much readable with text editors:

-bash-4.2$ head hunter_dev_backup.sql                                            +
PGDMP
w
hunter_dev10.310.3p0ENCODINENCODINGSET client_encoding = 'UTF8';
falseq00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
falser00
SEARCHPATH
SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false);
falses126232979
hunter_deDATABASE|CREATE DATABASE hunter_dev WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8';

Or even, further to dump the database in a directory using 5 slaves:

-bash-4.1$ pg_dump -Fd sales -j 5 -f salesdir
-bash-4.1$
-bash-4.1$ ls -lart
total 20
dr-xr-xr-x. 23 root     root     4096 Mar 20 09:53 ..
-rw-r--r--.  1 postgres postgres 1313 Mar 20 10:04 sales.sql
drwx------.  2 postgres postgres 4096 Mar 20 10:05 dumpdir
drwxr-xr-x.  4 postgres postgres 4096 Mar 20 10:05 .
drwx------.  2 postgres postgres 4096 Mar 20 10:05 salesdir
-bash-4.1$ cd salesdir/
-bash-4.1$ ls -lart
total 20
-rw-r--r--. 1 postgres postgres 2048 Mar 20 10:05 toc.dat
-rw-r--r--. 1 postgres postgres   27 Mar 20 10:05 2993.dat.gz
-rw-r--r--. 1 postgres postgres   25 Mar 20 10:05 2992.dat.gz
drwx------. 2 postgres postgres 4096 Mar 20 10:05 .
drwxr-xr-x. 3 postgres postgres 4096 Mar 20 10:05 ..

Restore and Recovery have been ALWAYS a different topics in the eyes of a DBA:

• Restore - Relates to the physical restore of files, not for making the files work together and be consistent between themselves, but only to put some version of the dataifiles somewhere, that is all what restore stands for.
• Recovery - Relates to the synchronization between the datafiles and log files, in other words making everything consistent.

With Barman we can easily restore your, if you have one :). Please ensure that YOU NEVER RESTORE IN YOUR ORIGINAL DATA DIRECTORY, this might cause problems.

To list existing backups, log in on your central barman server and issue the following command:

-bash-4.2$ barman list-backup pg
pg 20180603T074928 - Sun Jun  3 13:48:58 2018 - Size: 28.9 MiB - WAL Size: 53.2 KiB
pg 20180603T073804 - Sun Jun  3 13:37:35 2018 - Size: 28.9 MiB - WAL Size: 53.2 KiB
pg 20180404T061948 - Wed Apr  4 12:19:20 2018 - Size: 28.9 MiB - WAL Size: 117.2 KiB


-bash-4.2$  barman show-backup pg latest
Backup 20180603T074928:
  Server Name            : pg
  Status                 : DONE
  PostgreSQL Version     : 90607
  PGDATA directory       : /var/lib/pgsql/9.6/data

  Base backup information:
    Disk usage           : 28.9 MiB (28.9 MiB with WALs)
    Incremental size     : 8.8 KiB (-99.97%)
    Timeline             : 1
    Begin WAL            : 00000001000000000000003C
    End WAL              : 00000001000000000000003C
    WAL number           : 1
    WAL compression ratio: 99.84%
    Begin time           : 2018-06-03 13:48:57.072346+02:00
    End time             : 2018-06-03 13:48:58.647049+02:00
    Copy time            : less than one second
    Estimated throughput : 13.9 KiB/s
    Begin Offset         : 40
    End Offset           : 248
    Begin LSN           : 0/3C000028
    End LSN             : 0/3C0000F8

  WAL information:
    No of files          : 2
    Disk usage           : 53.2 KiB
    WAL rate             : 15.19/hour
    Compression ratio    : 99.84%
    Last available       : 00000001000000000000003E

  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20180603T073804
    Next Backup          : - (this is the latest base backup)

This will show us the list of the backups, their status and the late command, will show us the latest one:

-bash-4.2$ barman recover --remote-ssh-command "ssh [email protected]" pg 20180603T074928 /var/lib/pgsql/9.6/data_restore --target-time '2018-06-03 13:48:58.647049+02:00'
Starting remote restore for server pg using backup 20180603T074928
Destination directory: /var/lib/pgsql/9.6/data_restore
Doing PITR. Recovery target time: '2018-06-03 13:48:58.647049+02:00'
Copying the base backup.
Copying required WAL segments.
Generating recovery.conf
Identify dangerous settings in destination directory.

IMPORTANT
These settings have been modified to prevent data losses

postgresql.conf line 216: archive_command = false

Your PostgreSQL server has been successfully prepared for recovery!

The barman will prepare also your recovery.conf script.

[root@postegresqlmaster data_restore]# cat recovery.conf
restore_command = 'cp barman_xlog/%f %p'
recovery_end_command = 'rm -fr barman_xlog'
recovery_target_time = '2018-06-03 13:48:58.647049+02:00'

You can also instruct PostgreSQL to use barman for the recovery of the xlog files. Here example of such recovery.conf file.

standby_mode = 'on'
primary_conninfo = 'host=192.168.18.101 port=5432 user=postgres'

# The 'barman-wal-restore' command is provided in the 'barman-cli' package
restore_command = 'barman-wal-restore -U barman tain-mt-backup1.net.tain.com tain-mt-cdb2 %f %p'

So in order to start the recovery, just rename the data folder and restart the service:

[root@postegresqlmaster data]# service postgresql-9.6 start
Starting postgresql-9.6 service:                           [  OK  ]
[root@postegresqlmaster data]#

Once the recovery has performed, the server will change the recovery.conf file to recovery.done, signaling to the DBA that the recovery is completed:

[root@postegresqlmaster data]# cat recovery.done
restore_command = 'cp barman_xlog/%f %p'
recovery_end_command = 'rm -fr barman_xlog'
recovery_target_time = '2018-06-03 13:48:58.647049+02:00'
[root@postegresqlmaster data]# su - postgres
-bash-4.1$
-bash-4.1$ psql
psql (9.6.7)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sales     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# select * from testtable
postgres-# ;
 id
----
  1
  1
  2
  2

The recovery depends on the backup, like any other database, backup and recovery tools come in pairs. So let's see how to recover a file generated by pg_dump.

-bash-4.2$ psql -a -f hunter_dev_pgsql.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.0
-- Dumped by pg_dump version 10.0
SET statement_timeout = 0;
SET
SET lock_timeout = 0;
SET
SET idle_in_transaction_session_timeout = 0;
SET
SET client_encoding = 'UTF8';
SET
SET standard_conforming_strings = on;
SET
SET check_function_bodies = false;
SET
SET client_min_messages = warning;
SET
SET row_security = off;
SET

This command will execute and show all the input of file: hunter_dev_pgsql.sql

Here is a nice script to automate the export of all databases on a server:

#!/bin/bash
# => location to place backups
backup_dir="/dati/backups/"
backup_file="/var/lib/pgsql/croncmd/backup/logs/backup.log"
# => string to append to the name of the backup files
backup_date=`date +%F`
# => number of days you want to keep copies of your databases
#number_of_days=2
number_of_days=1

#elimina il file di log vecchio
rm -rf $backup_file

# get a list of databases to back up
databases=`psql -l -t | egrep -v "^ ( |template[01])" | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`

echo $databases >> $backup_file

# starting time
echo [`date`] START PostgreSQL backups >> $backup_file

# back up each database
numero_database=0
for i in $databases; do
numero_database=$(( $numero_database + 1 ))
outfile=$backup_dir$backup_date-$i.dump.gz
echo -n "–> Dumping $i to $outfile" >> $backup_file
/usr/pgsql-9.1/bin/pg_dump -Fc $i | gzip > $outfile
outfilesize=`ls -lh $outfile | awk '{ print $5 }'`
echo " ($outfilesize)"  >> $backup_file
done

# clean old backups
echo "–> Cleaning up backups older than $number_of_days days" >> $backup_file
rm -f `find $backup_dir -type f -prune -mtime +$number_of_days`

# finish time
echo [`date`] STOP PostgreSQL backups >> $backup_file

data=`date +%Y-%m-%d`
backup_file_today=$backup_dir$data"*"
backup_creati=`find $backup_file_today -maxdepth 1 -type f -print | wc -l`

#Controllo che il numero di backup creati sia uguale al numero dei database
if [ $numero_database == $backup_creati ] ; then
        echo "PROCEDURA BACKUP TERMINATA SENZA ERRORI" >> $backup_file
else
        echo "ERRORE NELLA PROCEDURA BACKUP" >> $backup_file
fi

Which can be put in crontab as follows:

# Minute   Hour   Day of Month       Month          Day of Week        Command
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)
    0       4          *             *                   *              /path/startCron.sh