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.