PostgreSQL, Automatic Backup and Restore

Automatic Backup

Here is a very simple steps for creating the postgreql backup automatically using the linux cron job. you just need to download simple script and have to apply few configurations as listed below. 

Create a cron job that will create a back based on your requirements. Run the following command to edit the cron job settings

$  sudo crontab -e

Add this line at the end of this file
@hourly <path to file>/pg_backup.sh service /<path to backup folder>/

For me it seems like this
@hourly /home/mantavya/pg_backup.sh service /home/mantavya/backups/

If you wanted to take the backup once a day you should use add like as follows
@daily /home/mantavya/pg_backup.sh service /home/mantavya/backups/

Your file should be looks like

# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command
@hourly /home/mantavya/pg_backup.sh service /home/mantavya/backups/

When cron job runs you will get the backup in your backup directory as follows

Backup directory with Hourly and Daily Backup

Once you setup the above steps, create a root user in PostgreSQL database as this cron job will be execute as a root user, so please create a root using following command in postgresql.


$ sudo su postgres 
[sudo] password for mantavya: 
$ createuser root
Shall the new role be a superuser? (y/n) y

If you wanted to configure automatic backup on external drive please go through OpenERP forum Post1 and Post2.

Manual Backup

If you wanted to take your back manually you can do it, its even simple then the automatic backup configuration.

$ pg_dump db_name > db_name.sql

Restore Database

If you have taken backup automatically or manually, restore process is common in all and its just two lines to execute. First is to create  a database for OpenERP and second is for restore the database from .sql file.

$ createdb db_name --encoding=UNICODE

$ psql dbname < db_name.sql > import_log.txt

If everything goes well you will not get anything on screen and everything on import_log.txt file as a result of success.