Backup and Restore Postgres Database from local to remote.

Shafayat Jamil — Mon, 29 Jan 2024 12:50:37 +0000

Our application runs on the production server smoothly. But we don't have any test/QA environment where some completed modules can be tested. So my team-lead assigned me a task to deploy the application in the QA environment. The first
challenge i faced was not having a backup database. As these completed modules can't be tested with empty data.
After thinking for a few minutes, I made a plan to do the task.

My plan was :

Dumping the local database -> copy the dump file to the remote server -> create a database in remote server -> restore the dump file in newly created database in server.

Now I will go through every step elaborately.

Dumping database using pg_dump

I used pg_dump to create backup files from my local database as pg_dump is flexible and powerful utility for backing up PostgreSQL databases.



pg_dump -U <username> -h localhost -d <database_name> -F c -f <backup_file_name.dump

Break down of the command are as follows:

-U Specifies the PostgreSQL username to connect to the database.
-h Specifies the host where the PostgreSQL server is running
-d Specifies the name of the database to dump
-F c Specifies the format of the output file. c means custom format.
-f Specifies the name of the file to which the database dump will be written.

After executing the command, a sql file will be created.

Move the dump file to the remote server

Our next work is to copy the dump file to the remote server. To do that I used scp command. scp command is used for securely copying files between a local host and a remote host.



scp file_path/file_name remote_username@remote_ip_address

file_path and file_name specify the file to copy. remote_username and ip_address is the remote destination to specify where the file should be copied.

Create a Database in remote.

My next target was to create a database in remote machine where I would restore the data.To create a database we can either execute the following command.



createdb -U username -h localhost -p port_number -e db_name

Or we can use psql console. psql is a command-line interface (CLI) utility for interacting with PostgreSQL databases.



sudo su postgres

It will ask for password. After completing authentication we have to write psql and press enter. And then it will take us to the psql console.
In psql console we can create database by following:



create database db_name;

It will create a new database. To view the list of databases that exists in our remote machine, we can type \l and it will show the list of databases.

Restore the data

To restore data, I used pg_restore.



pg_restore -d database_name file_name

-d database_name Specifies the name of the target database where we want to restore the data. file_name is our backup file which we copied. Whenever we execute this command, data will be restored in our newly created database. To view data is present in the database, we can type:



psql -U username -h localhost -p port_number -d db_name

Let's break down the command.

-U username Specifies the PostgreSQL username to connect to the database
-h localhost Specifies the host where the PostgreSQL server is running.
-p port_number Specifies the port number on which the PostgreSQL server is listening
-d db_name Specifies the name of the database to connect

This command will connect to our database. If we type \dt; it will show all the table inside the database. To view more details, we can type \dt+;.

If we want to connect to the database through our application, we have to edit pg_hba.conf file of postgres. It is a configuration file used by PostgreSQL to control client authentication to the server. In this file, administrators can define rules that specify which hosts are allowed to connect, which users they can connect as, and the authentication methods they should use.
We have to entry our port, host and authentication type under replication privilege.

To edit pg_hba.conf file we have to enter the following command.



sudo nano file_path/pg_hba.conf;

To find out where hba.conf file located in, we can type SHOW hba_file;. It will show the path of conf file.

That's it. Hope You will get benefit from it.

DEV Community: Shafayat Jamil

Backup and Restore Postgres Database from local to remote.

Dumping database using pg_dump

Move the dump file to the remote server

Create a Database in remote.

Restore the data