How to Backup/Dump and Restore PostgreSQL Databases

Here is the situation. We have a customer, who runs a POS backed by PostgreSQL. Customers want a second POS with the updated SQL till last night. Point to note, he didn’t want to use the Master/Replica or Master/Master replication, instead, a day earlier backup to be restored.

A similar situation is probably applicable for developers who are trying to work with staging and production environments and vice versa. In such a situation, you need a technique for dumping and restoring databases.

Backup/Dump Postgresql Databases

To dump the PostgreSQL database, you need to use pg_dump. First ssh to your server, change the user to Postgres, and then run the dump

su - postgres
pg_dump your_database > your_database_2021_13_11.sql

Here, your_database is the name of the database you are trying to dump.

Copy the SQL to the Remote Server

Once the dump is done, now you may copy the SQL file to the remote server. You may do that using rsync:

rsync -vrplogDtH --progress your_database_2021_13_11.sql [email protected]:/var/lib/pgsql/

Replace your_database_2021_13_11.sql with your SQL dump name and ‘ip.ip.ip.ip’ with the IP of your remote destination.

Restore the Dump

Now, ssh to the remote server, and continue with the following command:

# change to postgres user
su - postgres
# drop the old database
psql -c 'drop database your_database;'
# create database (replace your_username with the one you want to use for the database)
psql -c 'create database your_database with owner your_username;'
# restore database
psql your_database < /var/lib/pgsql/your_database_2021_13_11.sql

Once done, you should be able to use the new restored database. Good luck.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.