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 is connecting the localhost using default port number 5432 to the database you specified and the backup file will be copied to "D" drive. Change these params based on your environment.

If you are not familiar or happy with such implementation, simply you can install some free GUI tools available for database backups. 

For example, SQLBackupAndFTP

This is free and supports two database backup.

Hope you find this article helpful, do let me know if you need my assistance.