How to back-up Postgres database on Linux using cronjob


How to back-up Postgres database on Linux using cronjob

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

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
  1. Add new cron
$ crontab -e

Add at the end of the file the content:

0 0 * * * /root/pg_backup.sh

.pgpass

localhost:5432:DATABASE:USER:PASSWORD

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 {} \;

Newsletter


Related Posts

How to launch a 6 figures startup with 0$ investment

Are you curious how to launch a startup in the fast and effective way? Here's not the answer, but you can understand how to achieve it

Resources to generate free legal pages for your website

Are you looking for resources to generate free legal pages for your website

Netopiajs library for nodejs

Unofficial library of Netopia to integrate with Nodejs

Where to find free assets and images list

Find a multitude of places with free assets and images for your project

Convert bson file to json with javascript

How to convert bson file to json with javascript with a simple script file

Managerflota idea validation

Managerflota idea validation thread. Manage the car hailing business in one web app.

Generate hexagons in JS based on center coordinates and radius

How to generate hexagons in javascript based on the center point coordinates and radius length

How to generate referral codes in javascript

How to generate referral codes in javascript very fast with less code

Loop over directories in bash and execute commands

How to loop over directories in bash and execute multiple commands

Get your Jenkins Passwords from secrets

How to get your Jenkins passwords from your secrets credentials. Follow this simple tutoriale and find your password or ssh private key.