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.

How to Backup PostgreSQL Database from Command Line

Question

How to Backup PostgreSQL Database from Command Line

Solution

Postgresql comes with two utilities called ‘pg_dump and ‘pgsql’. These can be used to backup and restore database.

To backup postgresql database using command line, you can use this:

pg_dump database_name > database_name.sql

This would take the backup and store it in .sql file.

To restore postgresql (.sql) file to a database using command line, you can use this:

pgsql database_name &lt; database_name.sql

How much data does Mellowhost have in their Backup?

It should be pretty known if you are a Mellowhost customer that we backup our servers on daily basis. We are currently using R1Soft CDP for each of our servers. All the backup servers are offsite, that means they are not hosted in the same server you are using with Mellowhost and not even in Softlayer network. Continue reading “How much data does Mellowhost have in their Backup?”