Backup Database PostgreSQL Command Line

Posted on

Backup a database from PostgreSQL database server can be done via command line. By typing the right command in a command line represented with a bash prompt the dump file database can be retrieved.

There are several things need to be looked at first before the execution of the command can be performed successfully. Below are the steps :

1. Check the service of PostgreSQL whether it is active or not. It can be done by executing the following command :

service postgres status

Example of an output from the execution of the above command :

user@hostname:~$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Tue 2016-09-15 10:56:27; 1 day 5h ago
Process: 2095 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 2095 (code=exited, status=0/SUCCESS)
Tasks: 0
Memory: 0B
CPU: 0
CGroup: /system.slice/postgresql.service
‣ 2095 /usr/lib/chromium-browser/chromium-browser --type=renderer --enable-pinch --enable-features=use-new-media-cache<use-new

Sep 15 10:56:27 hostname systemd[1]: Starting PostgreSQL RDBMS...
Sep 15 10:56:27 hostname systemd[1]: Started PostgreSQL RDBMS.
user@hostname:~$

2. Another thing which is needed to be check further is the port of PostgreSQL database server. Normally, it is using 5432 as default port of PostgreSQL database server.

3. After checking all through the first and second steps, below is actually how to execute a command to be used to backup database exists in PostgreSQL database server :

pg_dump -Uusername --port port_number database_name > /directory_path_folder/postgresql_file_name

Description :

pg_dump : It is a command which is part of installed PostgreSQL package. It is used to dump a database from a PostgreSQL database server.
-U : It is a parameter which is used to specify username that is going to used together with the command. 
username : It is the value of the parameter -U, the value is going to be used to dump the database as a username to connect to the database. 
--port : It is another parameter which is used to specify port used to handle request of PostgreSQL database server
port_number :It is the value of the parameter --port, the value is going to be used to dump the database as a port number to connect to the database.  
database_name : It is the name of the database which is used to be dumped. 
> : It is the redirect output used to specify the output of the command before the > sign to be the input or just to redirect the output into a file which the name and the path specified after the > sign.  
/directory/path_folder/postgresql_file_name : the path of folder or directory specified and also the name of file which is destined as the dump file. 

This the output display from the command’s execution :

username@hostname:~$ pg_dump -Upostres --p 5432 test > test-dump-file

Save

Save

Save

Leave a Reply