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
Leave a Reply