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 /pg_backup.sh service //

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

3 thoughts on “PostgreSQL, Automatic Backup and Restore

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s