• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
sharadchhetri

sharadchhetri

Tutorials On Linux, Unix & Open Source

  • Home
  • Linux Commands
  • Resources
    • Learn Linux
  • My WordPress plugins

postgres database backup script using database user password inside

October 20, 2013 by Sharad Chhetri 1 Comment

Postgresql is open source object-relational database system and it is also widely use likewise mysql database system.
If you are familiar with mysql you must be aware that in single line you can pass the password of mysql user for eg. mysql -u root -p password. But in postgresql it is not like that.

Solution: To overcome from passing the postgres user password we can set environment variable PGPASSWORD temporarily within script and after taking backup it will unset the environment variable also.

Applied on: postgresql 9.1 version

How to implement the postgres database backup script

Step 1: Login with system user called postgres if you know the password of it OR first login as root and then use the command su -l postgres. See below example.

root@tuxworld:~# 
root@tuxworld:~# su -l postgres
postgres@tuxworld:~$

Step 2: Now you are in home directory of postgres system user.Lets check its home directory path. Use the command $HOME

postgres@tuxworld:~$ echo $HOME
/var/lib/postgresql
postgres@tuxworld:~$

Step 3: You should be login with postgres system user and create a directory called dbbackup to keep the database backup in local system.Later you can transfer this file to some other server or backup server by using scp or any other mean.

postgres@tuxworld:~$ whoami
postgres
postgres@tuxworld:~$ mkdir ~/dbbackup
postgres@tuxworld:~$ 
postgres@tuxworld:~$ ls -ld dbbackup/
drwxrwxr-x 2 postgres postgres 4096 Oct 20 09:25 dbbackup/
postgres@tuxworld:~$ 
postgres@tuxworld:~$ pwd
/var/lib/postgresql
postgres@tuxworld:~$

Step 4: Now create a backup script file called dbbackupscript.sh and paste the given below code in it.

vi ~/dbbackupscript.sh

#!/bin/bash
# Author: Sharad Chhetri
# Date: 20-Oct-2013
# website: www.sharadchhetri.com
# Desc: Single Database postgres backup script
# We are using username postgres here which is similar to mysql root user
#
#

DATABASE=Give-Database-name

FILE=$DATABASE-`date +%F-%H%M%S`.sql

export PGPASSWORD=give_postgress-db_password

## backup command
pg_dump -U postgres $DATABASE > ~/dbbackup/$FILE

## compressing the backup file
gzip  ~/dbbackup/$FILE

## Removing export
unset PGPASSWORD


FILESIZE=$( du -sh ~/dbbackup/$FILE.gz )
## Using condition

if [ ! -f  ~/db-backup/$FILE.gz ]; then
    echo "$FILE.gz File not found!" | mail -s "postgres database name $DATABASE  backup is failed" youremailid@example.com
else
    echo "$FILE.gz File found, Actual size after compression is $FILESIZE " | mail -s "postgres database name $DATABASE backup is completed" youremailid@example.com
fi

Note: In script replace the following thing with your information

Give-Database-name
give_postgress-db_password

Step 5: Now save the file and give execute permission to it.
Note: You should still be login as postgres system user

chmod 750 dbbackupscript.sh

Run the script and check if it is working or not. In dbbackup directory new file will be created if script is successfully completed

$ sh ~/dbbackupscript.sh
$ ls -l ~/dbbackup

Step 6: Now you can put this script in crontab of postgres system user.And schedule as per your requirement. Here as per crontab we are running the script daily at 12:01 AM

crontab -u postgres -e

01 00 * * * sh ~/dbbackupscript.sh

Share this:

  • Twitter
  • Facebook
  • More
  • Print
  • Email
  • LinkedIn
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram
  • WhatsApp
  • Mastodon

Related posts:

  1. How to set user postgres password in postgresql 9.1
  2. send email after mysql backup through bash script in simple way
  3. Set postgres password on PostgreSQL 9.4
  4. Set and reset user password by bash script
  5. Linux: Set user password by using encrypted password in command
  6. MySQL backup bash script
  7. Cassandra backup script on linux system
  8. How to increase Password Expire date without resetting the password
  9. Password prompt in single user mode is not secure : CentOS/Red Hat
  10. Read the file inside compressed .gz files without extract

Filed Under: Linux, Tips And Tricks Tagged With: database, postgresql

Reader Interactions

Comments

  1. MR Njuguna says

    January 6, 2021 at 8:48 am

    Hi There i followed exactly what you said but im getting a database with only 50 mbs and it should be over 2 gb database.

    what could be the issue

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Our Social Media Presence

  • Facebook
  • GitHub
  • Twitter

Linux Command

What is Linux Internal And External Command

Linux Basic Commands With Examples For Every Beginner

tr command to convert lines to space , tab and vertical tab

smbpasswd command not found on CentOS 7 and RHEL 7

Solution : semanage command not found

Unix / Linux : How to print duplicate lines from file

More Posts from this Category

You Might Like These Articles!

simplecodesyntax wordpress plugin

SimpleCodeSyntax : My Another WordPress Plugin

Install Nginx

How To Install Nginx On Ubuntu 22.04 LTS

Install Latest Git package in Ubuntu Operating System

How To Always Install Latest Git Package In Ubuntu Operating System

Bash script for installing VirtualBox on Ubuntu 22.04 LTS Desktop

Install VirtualBox On Ubuntu 22.04 LTS Desktop (Bash Script)

libfuse

dlopen(): error loading libfuse.so.2 – Got Error On Ubuntu

Failed to open/create the internal network

VirtualBox Error: Failed to open/create the internal network

Always Useful Tips And Tricks

How to empty Trash through command line in Ubuntu

Do not show line haveing particular keyword by grep command

How to install pam_mysql in CentOS or Red Hat

fatal error: error writing to /tmp/ccwAjc9Z.s: No space left on device

How to find when Operating system was installed in linux CentOS and Red Hat

How to increase Password Expire date without resetting the password

shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory

Explore 90+ Article On "Linux Tips And Tricks"

Copyright © 2023 ยท
The material in this site cannot be republished either online or offline, without our permission.
Proudly Blogging From Bharat.

  • Contact
  • About Me
  • My WordPress plugins
  • Privacy Policy