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.
Top comments (0)