Automation – PostgreSQL Restore Data

There are three methods to backing up PostgreSQL databases

  • SQL dump
  • File System Level backup
  • Continuous archiving

SQL Dump command used to take a full database backup. The backup file consists of SQL commands which will create the database when it is gets restored. The below command is used to take a backup through the CLI.

pg_dump dbname > dumpfile

The dumpfile can be restored in other testing or staging servers by using the following command.

psql dbname < dumpfile

Now, if we want both to be handled in a single transaction, like taking a backup and restoring it in another environment –

pg_dump -h host1 dbname | psql -h host2 dbname

The ability of pg_dump and psql to write to or read from pipes makes it feasible to dump a database immediately from one server to another in a single session. In other words, pg_dump helps in creating the dump of “dbname” database from “host1” server, and psql helps in restoring the same into database “dbname” in “host2” server.

These are all good for instant operation however we all prefer to automate both database backup and restore operations. But, it is a bit difficult in PostgreSQL to achieve such a scheduled or automated process to backup and restoration. Free tools may not offer much in this case and most of the companies might not prefer to install any third-party tools without knowing how safe they are. 

So, this leaves us to create windows scheduler tasks to automate them.

Automating the data restoration in another instance/server:

Go the server in which you want to restore the database and create the windows task with the help of the below steps.

1) Go to Task Scheduler.
2) Right-click on “Task Scheduler Library”
3) Go To Actions
4) Click New
5) Action should be “Start a program”
6) In the Settings, Program/script – copy the below executable file.
C:\Windows\System32\cmd.exe
7) In the “Add arguments (optional)” add the following command.
/c “psql -U yourUserName yourDatabaseName  < D:\dbname.sql”
8) Click OK
9) Go to “Triggers” and Select “New” to configure the schedule
10) Select “Daily” and select the recurrence details.
11) Tick on Stop task if it runs longer than “1 Hour” (this is optional)
12) Click OK, FINISH.

 Automating the backup of the database.

All the above steps to be followed and the command should be replaced with –

/c “pg_dump -h localhost -p 5432 -U yourUserName yourDatabaseName > D:\dbname.sql”

Here, pg dump connects to the selected database on localhost using the default port number 5432, and the backup file is copied to the “D” disc. Change these parameters to suit your needs.

If you are unfamiliar or dissatisfied with such an approach, you may simply install various free GUI tools for database backups.

For example, SQLBackupAndFTP

This is free and supports database backup.

 

Hope you find this article helpful.

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 )

Connecting to %s