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 install Kali Linux in UTM Virtual Machine on M2 Macbook

How I installed a UTM virtual machine with Kali Linux on a Macbook PRO M2 chip

Validating CIF for Romanian Company in JS

Validate CIF for Romanian companies in JS. Easily verify company information with our user-friendly tool. Ensure accuracy and reliability.

My cat ruin my SaaS LoyalXpert

After half year, LoyalXpert, the loyalty system builder for coffee shop oweners is shutdown

TikTok Ads Strategy from a SaaS founder

Here's a short tutorial from my disrupting strategy of doing Tiktok Ads as a SaaS Founder

Curated SEO Resources: Essential Tools and Tips

Discover a handpicked selection of indispensable SEO resources, including tools and tips, to optimize your website's performance.

A Week in the Life of an Invoice Wrangler: Navigating Ridesharing and Food Delivery Chaos

As an app founder in the ridesharing and food delivery industry, I found myself knee-deep in invoice reports from companies like Bolt, Uber, Glovo, and Bolt Food

Free HTML templates list for Startups

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

Deal with client requests in SaaS

How to deal with client requests in Saas which are seeing only their interests, not the product interest.

The first client of LoyalXpert is not answering anymore

Trying to implement LoyalXpert app, I lost my first customer, he's not answering anymore

Experiments with Tiktok Ads

Recently tried out TikTok ads for the first time and here are some of my learnings and challenges