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

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.

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

How to cold email

How to cold email - a practical guide about how to use cold email in your activity as a CEO of a software agency or startup owner.

Linux disk space alerts on Discord

Linux disk space alerts. Check your space and be responsible with your project. Take care of your space and don't crack the project.

Git clone private repo in docker

A simple how to guide to use git clone of a private repository inside docker container. With vuejs / nodejs app.

How to detect the browser language in javascript

How to detect the browser language in javascript in just a line of code. The method is working on all major browser

You don't need a macbook pro to be a pro

You don't need a macbook pro to be a pro! You need a laptop and internet connection, and a brain, but you already have it

Free templates for startup

Free HTML templates for startup or a new project. A complete list with free resources to build your next startup's website and gain the traction to the sky.

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.