An automated Backup Script for PostgreSQL
Article Posted by Anas V for www.wisdombay.com
This article presents you with a linux shell script which will automate the process of taking dumps or backups with the pg_dump utility on a PostgreSQL Server.
This shell script is fully automated in such a way that no user interaction is needed while it's execution. This makes this script suitable for at, batch or cron jobs. This script can be configured as a batch job to run at periodic intervals to take backups or dumps of exsisting databases. And by the way, we are naming this script 'pg_dmp.sh'
What is this Script?
The 'pg_dump' utility found with the PostgreSQL Server can be utilized by Database Administrators for taking dumps or backups of databases into a file. The syntax of pg_dump is :
#pg_dump databasename -f outputfile -i -x -O -R -S username
If the above given command is executed at the shell PostgreSQL will prompt the user to enter the password for the username. So when ever you need to execute this command to take a dump file a user interaction is needed in the form of typing in the password.
In this script we are making the pg_dump utility to take the username and password direct from the command line without any user interaction by means of utilizing 2 environment variables 'PGUSER' and 'PGPASSWORD'.
NOTE : Security wise, this approach is not that wise. Since any other process can peek into our two environment variables 'PGUSER' and 'PGPASSWORD'. But this approach is a simple way of achieving a handsfree pg_dump execution.
Here is the ScriptYou can download this script Source Code from HERE
How to use this script
Download the script from the link given above. Copy it to your folder. You should have sufficient previlages with PostgreSQL for taking backups for this script to work.
At your folder after copying the script, give it execute permission by typing in the below given command
chmod +x pg_dmp.sh
After that you can use your pg_dmp script by the following syntax
pg_dmp.sh databasetodump [outputpath]
The 'outputpath' is optional and if it is not given then a new folder with a naming convention like 'pg_backup_yourdatabasename' will be created in your home directory and a dump file with the name 'databasename_date' will be created in that folder.
To take backup of a database named 'cookies' the command will be :
Since no output path is given here, a folder named 'pg_backup_cookies' will be created by the script in your Home directory. In this directory the dump file of 'cookies' database will be created with the name 'cookies_date', where date will be replaced by the date on which the script is executed.