Backup procedure

The PostgreSQL command pg_dump is used to extract the database into a script file every single hour.
In the master server and in the slave configdb(a) there are scripts (attached below) running every hour, day, month and year from the user postgres

bash-3.2$ crontab -l
1 0 * * * /var/lib/pgsql/backups/
3 0 1 * * /var/lib/pgsql/backups/
5 0 1 1 * /var/lib/pgsql/backups/
7 * * * * /var/lib/pgsql/backups/

(NB: order does matter) creating on configdb:/var/lib/pgsql/backups several snapshots with the following granularity

cdb_pg_dump-hour[00..23].backup.gz created every new hour
cdb_pg_dump-day[1..31].backup.gz created at midnight
cdb_pg_dump-month[1..12].backup.gz created the first day of the month
cdb_pg_dump-year[..].backup.gz created the first day of the year

more or less 10 Gb per year. All the files are copied from configdb:/var/lib/pgsql/backups into backup1:/backup/configdba every night; backup1 sends an email every week with the status of all the backups stored on it.

Restore from a backup

In case you need to recreate the CDB from a snapshot, you should find the dumps on

and to recreate the CDB
su postgres
gunzip cdb_pg_dump-???.backup.gz
dropdb cdb
pg_restore -C -d cdb cdb_pg_dump-???.backup

Another backup

The script

[root@configdb cron.d]# cat /etc/cron.d/pg-archive
*/1 * * * * postgres /var/lib/pgsql/scripts/ &> /tmp/pg-archive-cron.log

creates a file /var/lib/pgsql/data/pg_xlog/archive/current from the last chunk in /var/lib/pgsql/data/pg_xlog/archive/.

backup_scripts.tar (10 KB) Franchini, Paolo, 18 March 2019 17:27