Backing Up All Databases In PostgreSQL

Introduction

Backing up a database is one of the most critically important tasks in any type of database administration. Regular backups ensure that any data lost due to corruption, crashes and breaches can be successfully recovered. This tutorial will explain how implement the PostgreSQL builtin tools and administrative options used to back up all database in PostgreSQL.

Prerequisites to Back Up All Database in PostgreSQL

• The PostgreSQL server must be properly installed, configured and running.

Download PostgreSQL for Linux and Windows systems here

How to use the PostgreSQL pg_dump Command

The pg_dump command must be run in the terminal of the computer where the database is installed and in the command line on the computer where the database is stored.

The following code is used to back up a PostgreSQL database:

How to use the PostgreSQL Backup Extension Format

Multiple formats or extensions may be used to store the PostgreSQL database backup. Examples include:

• The .tar format for tarball
• The .bak format for compressed binary format
• The .sql format for plaintext dump

Understanding the Various PostgreSQL pg_admin Backup Options

The following options can also be used to furnish additional instructions when executing the pg_dump command:

• The -U option will specify the specific user connecting to the PostgreSQL database.

• The -W option requires the user to furnish the proper password when connecting to the PostgreSQL server.

• The -F option can be used by the backup file as explained in the above “PostgreSQL Backup Extension Format” section.

How to Backup a Single Database in PostgreSQL

With an understanding of how the pg_dump can aid in performing a database backup, this section will cover exactly how to use the pg_dump command to backup a database.

First, connect to the PostgreSQL database using the PostgreSQL psql interactive terminal program. Next, list the current PostgreSQL database in the system, in its entirety, by using PostgreSQL command \l.

Execute the following commands in the given sequence:

Now execute the \l command.

The results should resemble the following:

From here the decision on which database to back up can be made using the testdatabase command.

Now type the \q command to quit the database session and then execute the following code in the command line:

The above code instructs the pg_dump command to store the backup as the user risa in the following path: /Users/risa/documents/demo_single_backup.sql

The below image should be visible by navigating to the specified directory:

How to Backup All Database in PostgreSQL

The previous section covered how to backup a single PostgreSQL database. This section will explain how to backup all of the information existing in the database in PostgreSQL. This is accomplished by using the PostgreSQL pg_dumpall command to enable a backup of the entire database(s) at one time. However, note there are some caveats that should be taken into account before using this approach.

• While it’s very convenient to be able to export all database at once, using a script file prohibits processing a parallel restore. The means that some of the processes will require more time than usual to execute a back performed in this manner.

• In addition to the dumping of the database requiring more time, there is no way to determine what dump for which database is being performed while the process is ongoing.

The syntax for the pg_dumpall command is as follows:

Now execute the below command to backup all of the existing database in PostgreSQL:

Notice that the syntax for both pg-dumpall and pg_dump is similar, but not identical. However, note that the -W option that instructs pg_dump to require a password was not used.

The following is the result of the pg_dumpall command:

The above image compares the two files that were created in this tutorial, one for the single database backup and one for the multiple database backup.

Conclusion

This tutorial explained how to back up all database in PostgreSQL using the builtin PostgreSQL pg_dump and pg_dumpall tools. The article specifically covered how to use the PostgreSQL pg_dump command and use the PostgreSQL backup extension format. The tutorial also explained the PostgreSQL pg_admin backup options and how to backup both single and multiple databases in PostgreSQL. When performing backups, remember that the syntax for both pg-dumpall and pg_dump is similar, but not identical.