auto backup + restore PGSQL.md

creat backup script ~/scripts/db_backup.sh

#!/bin/bash
DIR=`date +%d-%m-%y`
DEST=/db_backups/$DIR
mkdir $DEST

PGPASSWORD='postgres_password' pg_dump --inserts --column-inserts --username=postgres_user --host=postgres_host --port=postgres_port postgres_database_name > dbbackup.sql

Now chmod the script to allow it to for execution

chmod +x ~/scripts/db_backup.sh

How to set up Cron (to automate the process)

crontab -e

Paste the below commands at the bottom to automate the process

0 0 * * * ~/scripts/db_backup.sh # take a backup every midnight

Db Restore script ~/scripts/db_restore.sh

pg_restore -d db_name /path/to/your/file/dump_name.sql -c -U db_user