Postgres DB backup script

To export a postgres database We use the pg_dump command:

pg_dump -U postgres -v DATABASE -f FILENAME.sql

And to import it back we use psql:

psql -U postgres -W -d "FILENAME.sql" < FILENAME.sql

Now that we know these commands We can easily create a bash that will backup 2 postgres databases from the server and upload them to the backup Windows server while keeping 7 backups locally.

#!/bin/bash
ntpdate pool.ntp.org
cd /root/
now="$(date +'%d%m%Y%H%M')"
export PGPASSWORD="strong"
pg_dump -U postgres -h localhost DB1_NAME  -f /root/tmp_dbs/backup_DB1_$now.sql

pg_dump -U postgres -h localhost DB2_NAME -f /root/tmp_dbs/backup_DB2_$now.sql
tar cvfz  /root/backup_dbs/backup_$now.tar.gz /root/tmp_dbs/*

rm /root/tmp_dbs/*

mount  -t nfs 172.20.20.6:/mnt/backup /mnt/

rsync -ruv /root/backup_dbs/ /mnt/172.20.20.12/

find /root/backup_dbs/* -mtime +7 -delete

umount /mnt/

exit 0

Explained:

#!/bin/bash
ntpdate pool.ntp.org
#store backups in /root

cd /root/
#add timestamp to each backup

now="$(date +'%d%m%Y%H%M')"
#postgres db password for our databases

export PGPASSWORD="strong"
#1st database

pg_dump -U postgres -h localhost DB1_NAME  -f /root/tmp_dbs/backup_DB1_$now.sql
#2nd database

pg_dump -U postgres -h localhost DB2_NAME -f /root/tmp_dbs/backup_DB2_$now.sql
#compress and remove originals

tar cvfz  /root/backup_dbs/backup_$now.tar.gz /root/tmp_dbs/*

rm /root/tmp_dbs/*
#mount Windows server

mount  -t nfs 172.20.20.6:/mnt/backup /mnt/
#copy files

rsync -ruv /root/backup_dbs/ /mnt/172.20.20.12/
#remove files older than 7 days

find /root/backup_dbs/* -mtime +7 -delete
umount /mnt/

exit 0

Source: https://www.postgresql.org/docs/11/app-pgdump.html