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

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

People don’t care about you, until they know you care about them.

People don’t care about you, until they know you care about them. The same happens in business, you need to take care of your clients.

The One Word That Can Ruin Your SaaS Business Anyone

As a SaaS founder, you probably know how important it is to have a clear and specific target market for your product.

How I got my digital certificate connected it with ANAF

How I got my digital certificate from certSIGN and connected it with ANAF

The Ultimate List of Company Directories to Boost Your Networking

Discover a wide range of company directories to boost your business's visibility and connect with potential clients.

Discover the Best Free AI Art Tools for Your Next Masterpiece

Explore a curated collection of the finest free AI art tools, designed to help you bring your artistic vision to life.

SaaS vs. Software Agency

Discover the key differences between SaaS and Software Agencies to make informed business decisions and stay ahead in the competitive market.

Top ChatGPT Prompts for Technical People and Startup Founders

Discover the ultimate list of ChatGPT prompts designed specifically for technical people and startup founders. Enhance your AI interactions and boost productivity with these powerful prompts. Revolutionize the way you work and stay ahead of the competition. Read now!