Dumping PostgreSQL Database into File via Command Line

Another important feature in most of Database Server which exist is the backup database through dumping database file backup mechanism. It can be considered as one mechanism of backup process which is a necessity for every Database Server type running. That thing also goes with PostgreSQL Database Server which in this article, the backup process of a certain database through dumping database backup file from the selected database as the backup target will be explained further.

Database backup process can be done easily with manual execution in PostgreSQL Database Server in any running operating system where PostgreSQL Database Server itself has already installed and active. To be able to do the backup process manually, below are the steps taken to backup database in PostgreSQL Database Server :

1. Check PostgreSQL Database Server’s service. It is active or not to process request. For a reference on how to do it, read the article which can be found in the article titled ‘Check PostgreSQL Service Status‘.

2. To be able to backup a certain database, obviously it takes the right and valid account so that after successfully connected through the validation provided by PostgreSQL Database Server, the account itself is also granted the suitable privilege to choose the database which is going to be used for backup process or dumping database file process. It is important to make sure that it can be used for purpose described. Read the article titled ‘Create User for dumping database PostgreSQL‘ to know how to do it.

3. Access PostgreSQL Command Console trhough command line because the dump process in this article is done by executing a command in the command line. The command itself is actually a command used to call tool or utility exist as part of PostgreSQL Database Server installation. The tool is ‘pg_dump’. Below is the pattern of simple pg_dump tool executed command :

pg_dump -U user --format=c --file=mydb.sqlc mydb
Description : 
pg_dump : It is a command used to execute or to call pg_dump utility which is required for database backup or dump process. 
-U : It is an additional parameter used to specify user which is used for connecting to and dumping database from PostgreSQL Database Server
--format=c : It is an additional parameter used to specify the format of the dump database file. The value is 'c' which is stands for custom format. 
--file=mydb.sqlc : It is an additional paramater used to specify the name of the file used for the dump database file.. 
mydb : It is the name of the selected database for dumping or backup process.

For an example :

user@hostname:~$ pg_dump -Uuserapp --format=c --file=dbapp.sqlc dbapp 
Password :
user@hostname:~$

In the display shown above, the dumping process is showing on generating file named dbapp.sqlc from the selected PostgreSQL Database named ‘dbapp’. Using user with the name of ‘userapp’, the file backup database has created and saved in its local /home/user.