How to Dump PostgreSQL Database

Posted on

Introduction

This is another article discussing on how to dump a database in PostgreSQL Database Server. Basically, it is showing on how to create a backup file of a PostgreSQL Database Server. Previously, there are several articles which have the same content with this article. Those articles are the article with the title of ‘Create User for dumping database PostgreSQL’ in this link. Another one exist with the title of ‘Dumping PostgreSQL Database into File with Date Attribute’ in this link.The steps for dumping or generating backup files of PostgreSQL Database consist of two parts as follows :

1. Preparation for dumping the PostgreSQL Database

2. Execute the dumping the PostgreSQL Database

 

Preparation for the Dumping Process of PostgreSQL Database Server

The first part are for preparing the dumping process of the PostgreSQL Database. The following are the steps for the preparation of PostgreSQL Database to create a full backup of an existing database in PostgreSQL Database Server :

1. First of all, check the running process of the PostgreSQL Database Server. The following is the pattern for checking the running process of the PostgreSQL Database Server

netstat -tulpn | grep 5432

The additional parameter : 
-t : display only the tcp connection
-u : display only the udp connection
-l : display only the listening ports
-p : display the PID and program name of the connection
-n : display in a numerical format

The above command is a specific command to print network connections, routing tables, interface statistics, masquerade connections, and multicast memberships according to the manual page. It will display list of open sockets for connection. The description of the list of sockets for connection exist in the additional parameter of the command. Execute the above command to check for any available port connections listening for any connections using UDP and also TCP protocols, the following output will appear :

[user@hostname ~]$ netstat -tulpn | grep 5432
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      2308/postgres       
tcp        0      0 xxx.xxx.xxx.xxx:5432    0.0.0.0:*               LISTEN      2308/postgres       
[user@hostname ~]$ 

2. Next, check for the owner of the running process of the PostgreSQL Database Server. Since the PID of the running process is available in the previous output, execute the following command pattern :

ps -aux | grep syntax_pattern

Execute the above command pattern as follows :

[user@hostname ~]$ ps -aux | grep 
user        29668  0.0  0.0  23960  1088 pts/18   S+   16:21   0:00 grep --color=auto 2308
postgres    29951  0.0  0.0 182140 11360 ?        S    Aug23   0:00 /opt/postgresql-11.4/app/bin/postgres -D /opt/postgresql-11.4/data
[user@hostname ~]$ 

3. As it shows in the above output command execution, there is a running process of PostgreSQL Database Server where the owner of it is ‘postgres’. So, in order to dump or to backup the database, switch first to ‘postgres’ user account. Execute the following command to switch to ‘postgres’ user account. Just use any user account which is responsible for running the process to dump or to backup the database. It appears as follows :

sudo su - postgres

The execution of the above command patern exist with the following output :

user@hostname:~$ sudo su - postgres
postgres@hostname:~$ 

Executing the Dumping Process of PostgreSQL Database Server

Finally, the most important step, it is the dump process itself. Execute it with the following command pattern :

pg_dump -Uuser db_name -f /home/pgsql/backup_file_name.sql

Using the above command pattern, below is the execution of the dump or the backup process :

[postgres@hostname ~]$ pg_dump -U postgres mydb -f /home/postgres/mydb.sql
[postgres@hostname ~]$ cd /home/postgres/
[postgres@hostname ~]$ du -sh mydb.sql 
xxxx    mydb.sql
[user@hostname ~]$  

Apparently, the dump or the backup process is a success. Looking to the output of the above command for listing to see the content in the directory.

Leave a Reply