postgres database backup script using database user password inside

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:~# su -l postgres

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

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@tuxworld:~$ mkdir ~/dbbackup
postgres@tuxworld:~$ ls -ld dbbackup/
drwxrwxr-x 2 postgres postgres 4096 Oct 20 09:25 dbbackup/
postgres@tuxworld:~$ pwd

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

vi ~/

# Author: Sharad Chhetri
# Date: 20-Oct-2013
# website:
# Desc: Single Database postgres backup script
# We are using username postgres here which is similar to mysql root user


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

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"
    echo "$FILE.gz File found, Actual size after compression is $FILESIZE " | mail -s "postgres database name $DATABASE backup is completed"

Note: In script replace the following thing with your information


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

chmod 750

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 ~/
$ 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 ~/

1 thought on “postgres database backup script using database user password inside”

  1. 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


Leave a Comment

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