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