Backup and Restore Postgres Database from local to remote.
Tags: postgres, psql, bash, database
Published: 2024-01-29 12:50:37

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a81m8zv5rtnp0k4x1817.jpg)


## 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`.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zaid5f6gudmj60kxqknt.jpg)

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.