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

What I learned to never do in a presentation meeting

What I learned to never do in a presentation meeting. I had a very bad meeting experience with a gateway provider.

Startup graveyard is a series of killed projects that made millions in revenue per year

Startup graveyard is a series of killed projects that made millions in revenue per year. Just watch their graveyard and do it your own. What better validation do you expect.

How do you detect Credit card type based on the number?

How do you detect Credit card type based on the number using JavaScript? Simple solution using only code, no library required.

Download files in Javascript from Node.js server

How to download files in Javascript, either you use Vue.js, React, Angular, jQuery, or Vanilla JS. On the backend side, we run on Node.js using Express.js.

What books to read

The list of books I read. I decided one year ago to challenge myself and read as many books as I can this year. Take a look at this article and find all of them.

Startup tools for a better version

Are you a startup founder and are you looking for startup tools like invoice, planning or just making money with much automation? Check this article, from founder to founders.

How to use express validator?

How to use express validator through full examples. I found express validator very powerful, but having a poor documentation.

How to check DNS propagation

How to check DNS propagation? Did you changed the DNS records recently and now is not working? Stop crying and check the DNS propagation.

LibraPay library for Nodejs

LibraPay library for Nodejs - integrate Libra Bank online payment gateway using this library for NodeJs.

Opensource Object Storage with Minio

Opensource Object Storage with Minio using Docker. An alternative to AWS S3, Linode Storage, Google Storage, Azure Storage.