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. <\/p>\n\n\n\n
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. <\/p>\n\n\n\n
Backup\/Dump Postgresql Databases<\/strong><\/p>\n\n\n\n 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<\/p>\n\n\n\n Here, your_database is the name of the database you are trying to dump.<\/p>\n\n\n\n Copy the SQL to the Remote Server<\/strong><\/p>\n\n\n\n Once the dump is done, now you may copy the SQL file to the remote server. You may do that using rsync:<\/p>\n\n\n\n Replace your_database_2021_13_11.sql with your SQL dump name and ‘ip.ip.ip.ip’ with the IP of your remote destination.<\/p>\n\n\n\n Restore the Dump<\/strong><\/p>\n\n\n\n Now, ssh to the remote server, and continue with the following command:<\/p>\n\n\n\n Once done, you should be able to use the new restored database. Good luck.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading “How to Backup\/Dump and Restore PostgreSQL Databases”<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[412,523],"tags":[37,610,524,438,611],"_links":{"self":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/817"}],"collection":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/comments?post=817"}],"version-history":[{"count":1,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/817\/revisions"}],"predecessor-version":[{"id":818,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/817\/revisions\/818"}],"wp:attachment":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/media?parent=817"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/categories?post=817"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/tags?post=817"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}su - postgres\npg_dump your_database > your_database_2021_13_11.sql<\/pre>\n\n\n\n
rsync -vrplogDtH --progress your_database_2021_13_11.sql root@ip.ip.ip.ip:\/var\/lib\/pgsql\/<\/pre>\n\n\n\n
# change to postgres user\nsu - postgres\n# drop the old database\npsql -c 'drop database your_database;'\n# create database (replace your_username with the one you want to use for the database)\npsql -c 'create database your_database with owner your_username;'\n# restore database\npsql your_database < \/var\/lib\/pgsql\/your_database_2021_13_11.sql<\/pre>\n\n\n\n