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

Tools for Analyzing Your Competition

Before knowing where your business stands, you should look at the market and your competition. You need to know what marketing tactics and strategies are working for your audience, what traffic the competition has, and how they position.

Free Online Photo Editor with Cool Effects | Edit Images Easily

Transform your images with our free online photo editor with cool effects. Add filters, adjust colors, and apply creative enhancements. Edit your photos easily and make them stand out.

How to find the size of a table in SQL?

Learn how to determine the size of a SQL table with this informative guide. Discover efficient methods for measuring table size.

MongoDB Query date by _id field

Query date by _id in mongodb. Straighforward guide to solve your issue right away.

Unleashing the Power of Free API for Scrape Reverse Geocoding

Learn how to harness the potential of reverse geocoding with our beginner's guide to scraping APIs for free. Unlock the power today.

Discover the Power of Whatsapp-web.js for Safe and Easy Automation

Automate WhatsApp with ease using Whatsapp-web.js, a NodeJS client library that connects through the official WhatsApp Web app, reducing ban risks. Perfect for user or business accounts.

Website tours js tools

Tools to create live tours for your web project using js libraries.

Unlocking SaaS Marketing Success: Essential Resources to Learn and Grow

Discover a comprehensive collection of valuable resources to master the art of SaaS marketing.

Where to launch your startup

Do you wanna launch grandiosely your startup? Get a list of platforms where people can discover your project and can push your project directly to the sky.

Email campaign improving my SaaS convertion - Manager Flota

Users subscribe to your SaaS, but don't let the money on your table. Here's how I do it!