Dumping PostgreSQL Database into File with Date Attribute

Modified article from the previous article titled ‘Dumping PostgreSQL Database into File via Command Line‘ where in this article the process of dumping database in PostgreSQL has the same steps but adding an information containing the date of the file is being created will be very helpful.

Those circumstances do exist whenever the backup process of databases is periodically executed for an example every day. To be able to separate or to identify all backup files generated besides using the information on the time stamp attribute attached to the file created, it is also a benefit or an advantaged to have the information itself attached to the backup files as part of their names. To be able to dump files in PostgreSQL, the right user is needed where creating one of it in PostgreSQL Database Server can can be read in the following article named ‘Create User for Dumping Database PostgreSQL‘.

In order to dump the selected database in PostgreSQL, it is important to have the service is in active mode. To be able to check it whether it is active or not, it can be done by checking the article titled ‘Check PostgreSQL Service Status‘.

Having an active PostgreSQL Database Server’s service allows for the dump or database backup process to be continued by executing the following command to start :

pg_dump -Uuser --format=c --file=database_dump_file_name_$(date +%Y-%m-%d).sqlc database_name
pg_dump -Uuser --format=c --file=database_dump_file_name_$(date +%Y-%m-%d).backup database_name

The format of the backup file can be in .sqlc or .backup file or in any file type wished as long as it is easy to be remembered as PostgreSQL backup file.

Below is the sample of the above command for dumping PostgreSQL database file :

pg_dump -Udbdump_user --format=c --file=mydb_$(date +%Y-%m-%d).sqlc mydb
Description : 
pg_dump : It is a command used to dump certain database in PostgreSQL Database Server
-U : It is a command option used to specify user used for PostgreSQL Database Server's connection 
dbdump_user : It is the value of -U additional parameter and it is describing user specified for PostgreSQL Database Server dumping process. Shortly described, it is the user used to connect and dump certain database in PostgreSQL Database Server
--format=c : It is an additional parameter used to specify the format of the PostgreSQL Database dump file which in this case is 'c' or customized.  
--file=mydb_$(date +%Y-%m-%d).sqlc : It is an additional parameter used to specify the name of the PostgreSQL dump database file. The file is generated in the current working directory where the command is executed. The file itself is generated and the part of the name has a date attribute in it which is made with the date function. The pattern is YYYY-mm-dd.  
mydb : It is the name of the database. 

For an example :

user@hostname:~$ pg_dump -Udbdump_user --format=c --file=mydb.sqlc mydb
Password:
user@hostname:~$ pg_dump -Udbdump_user --format=c --file=mydb.backup mydb
Password:
user@hostname:~$ ls -al | grep mydb
-rw-rw-r--  1 user user   14347 Des  9 18:02 mydb_2016-12-09.sqlc
-rw-rw-r--  1 user user   14347 Des 11 15:58 mydb_2016-12-11.backup   
-rw-rw-r--  1 user user   14347 Des  9 14:37 mydb.sqlc
user@hostname:~/netbeans-8.2/bin$

In the above output, it is already seen that the command successfully generated dump file with any kind of file type, whether it is .sqlc or .backup as long as the above command pattern is executed. And based on the list of files exist in the directory and also the output of directory listing, it can be easily identify and separate the file based on the name only. Because the name of the backup database file contains the date attribute where the file was generated.