DEV Community

loading...
Cover image for How to back-up Postgres database on Linux using cronjob

How to back-up Postgres database on Linux using cronjob

boobo94 profile image Bogdan Alexandru Militaru Originally published at boobo94.xyz on ・1 min read

Learn how to backup Postgres database on Linux, using cronjobs. Very simple and very straight forward guide step by step using only scripts.

  1. Make new .pgpass file in the root folder
$ vi .pgpass
Enter fullscreen mode Exit fullscreen mode

And paste the content from /root/.pgpass

  1. Create new file /root/pg_backup.sh and paste the content
  2. Change permissions
$ chmod 700 /root/pg_backup.sh
Enter fullscreen mode Exit fullscreen mode
  1. Add new cron
$ crontab -e
Enter fullscreen mode Exit fullscreen mode

Add at the end of the file the content:

0 0 * * * /root/pg_backup.sh
Enter fullscreen mode Exit fullscreen mode

.pgpass

localhost:5432:DATABASE:USER:PASSWORD
Enter fullscreen mode Exit fullscreen mode

pg_backup.sh

#!/bin/bash
# This script will backup the postgresql database
# and store it in a specified directory

# Constants

USER="user_name_of_db"
DATABASE="name"
HOST="localhost"
BACKUP_DIRECTORY="/root/backup_db"

# Date stamp (formated YYYYMMDD)
# just used in file name
CURRENT_DATE=$(date "+%Y%m%d")

# Database named (command line argument) use pg_dump for targed backup
pg_dump -U $USER $DATABASE -h $HOST | gzip - > $BACKUP_DIRECTORY/$DATABASE\_$CURRENT_DATE.sql.gz

# Cleanup old backups

find $BACKUP_DIRECTORY/* -mtime +7 -exec rm {} \;
Enter fullscreen mode Exit fullscreen mode

The post How to back-up Postgres database on Linux using cronjob appeared first on boobo94.

Discussion (0)

pic
Editor guide