Header Shadow Image

PostgreSQL Full Backup

Let's setup some PostgreSQL backups.  

In this post, we'll set up a pull methodology in order to get backups of our PostgreSQL Cluster .  The backups will be saved remotely so any failures in our cluster will be independent of our PostgreSQL backups.

First, let's prepare the postgres account so we can login remotely without a pass.  This will allow us to run the pg_dump command remotely.  So generate a pair of keys in whatever account of your choice and exchange the keys with the remote hosts like this:

[root@mbpc-pc .ssh]# cat id_rsa.pub
[root@mbpc-pc .ssh]#
[root@mbpc-pc .ssh]#
[root@mbpc-pc .ssh]# ssh postgres@psql01
FIPS integrity verification test failed.
Last login: Sun Jun  2 18:46:53 2019
-bash-4.2$ cat .ssh/authorized_keys
-bash-4.2$ logout
Connection to psql01 closed.
[root@mbpc-pc .ssh]# ssh postgres@psql02
FIPS integrity verification test failed.
Last login: Sun Jun  2 17:15:30 2019 from mbpc-pc.nix.mds.xyz
-bash-4.2$ logout
Connection to psql02 closed.
[root@mbpc-pc .ssh]# ssh postgres@psql03
FIPS integrity verification test failed.
Last login: Sun Jun  2 18:30:51 2019 from psql01.nix.mds.xyz

Once you have the keys exchanged, you'll need a .pgpass within the root folder of the postgres account on each of the above cluster hosts:

-bash-4.2$ cat .pgpass

Next, we'll write a short script to login to a postgres node and take a backup saving the gzip file locally on the calling server:

[root@mbpc-pc .ssh]# cat /mnt/postgres-backup.sh


# For a reason I've yet to investigate, cluster IP doesn't work here.  So determening the node with a running instance the real shitty way.
for KEY in $( echo psql-c01 psql01 psql02 psql03 ); do
        PSQLH=$( ssh postgres@$KEY "hostname" 2>/dev/null );
        PSQLR=$( ssh postgres@$KEY "ps -ef|grep -Ei \"pgsql-10.*postgres\"|grep -v grep" 2>/dev/null);
        [[ PSQLR != “” ]] && {
                echo $PSQLH"|"$PSQLR; break;

[[ PSQLH == “” ]] && {
        echo "ERROR: PSQLH var was empty.  Should be a hostname.";
        exit 0;

ssh postgres@psql-c01.nix.mds.xyz "pg_dumpall -U postgres -h $PSQLH -p 5432 | gzip -vc" > ./psql-c01.sql.$(date +%s).gz && find /mnt/SomeBigDisk/psql-backup/ -type f -name '*.sql.*.gz' -mtime +180 -exec rm {} \;
[root@mbpc-pc .ssh]#


Notice the find line in the above.  It will clear out any old backups after 180 saves.

Schedule this via cron or any other scheduling software you prefer:

[root@mbpc-pc .ssh]# crontab -l|grep postgres
30 3 * * * /mnt/postgres-backup.sh
[root@mbpc-pc .ssh]#

The beauty of this method is that pg_dump will always be the correct version to match the PostgreSQL software running there and this will leave no intermediate files.  You won't need to ensure the pg_dump always matches your DB Cluster.  


Leave a Reply

You must be logged in to post a comment.

  Copyright © 2003 - 2013 Tom Kacperski (microdevsys.com). All rights reserved.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License