How To Take BackUp Of Postgres Database

Recently, I got the requirement to take backup of the Postgres database due to some migration activities. I analyzed and found the two best ways to achieve this. In this PostgreSQL tutorial, you will learn how to backup database in Postgres using the tools pg_dump and pgdumpall.

How To Take BackUp Of Postgres Database

One of the critical tasks of a database administrator is to back up the database. But why is backup important? Due to potential issues with your system or infrastructure, your data may be lost.

These problems can be device or network failure, data corruption, or system software issues. There are various types of backup available, including full or partial backups, point-in-time backups, and data or structure type backups.

In general, PostgreSQL allows you to perform two types of backups: logical and physical backups.

• Logical Backup: Here, your database-related information is stored, including schema, tables, and other relevant data.
• Physical Backup: In this kind of backup, the information related to files and directories is stored.

PostgreSQL has two tools for backing up the database, pg_dump and pg_dumpall. Here you will learn about both the tools with the help of examples.

Approach-1: Using pg_dump

If you want to back up only a single database, use the PostgreSQL pg_dump tool. The pd_dump tool puts your database contents, files, and structures into a file, and that file you can use to create (restore) a new database with the same structure or contents.

The syntax for using the pg_dump tool for backup is provided below.

pg_dump -U username -W -F p database_name > path\backup.sql

• pg_dump: It is the pg_dump tool or command itself.
• -U username: Use this for connecting a specific user to the PostgreSQL server.
• -W: Use it to request the password when connecting to the PostgreSQL server as the user.
• -F: Use it to define the format for the backup file. Use these options for different formats, such as p for SQL file, c for custom format archive file, d for directory format archive, and t for tar file.
• > path\backup.sql: Specify the path where you want to save the backup file with the file name and its format

Let’s take an example where on the PostgreSQL server, there is a database named ‘Employees_Info’. So, I am going to back up this database into a single file called ‘backup.sql‘ at the location ‘D:\Database_Backup\backup.sql’.

To take backup of Postgres database, Use the following command.

pg_dump -U postgres -W -F p Employees_Info > D:\Database_Backup\backup.sql

We got the expected output as shown in the screenshot below.

Now, if you go to the location ‘D:\Database_Backup\‘, then you see the new file named ‘backup.sql’ as shown in the picture below.

You can use the backup file to restore the database on the PostgreSQL server. This is how you can back up a single database on PostgreSQL using the pg_dump utility.

Approach-2: Using pg_dumpall

The pg_dumpall tool allows you to back up all databases at once on the PostgreSQL server, unlike pg_dump, which backs up only a single database at a time.

You can also back up all the databases using the pg_dump tool, but you will have to run this tool individually for each database on the PostgreSQL server. Additionally, restoring all the databases will be a time-consuming task.

The syntax for backing up all databases using pg_dumpall is provided below.

pg_dumpall -U username > path\all_backup.sql

• pg_dumpall: The pg_dumpall tool or command is used to backup all databases.
• -U username: Use this for connecting a specific user to the PostgreSQL server.
• > path\all_backup.sql: Specify the path where you want to save the backup file with the file name and its format

Now, use the command below to back up all the databases on the PostgreSQL server of the user Postgres.

pg_dumpall -U postgres > D:\Database_Backup\all_backup.sql

When you execute the above command to back up all the databases, it prompts for the password many times, because it connects to the PostgreSQL server several times for each database.

However, if you don’t want to enter the password multiple times, you can configure the file called .pgpass, which stores the user login data.

Navigate to the ‘D:\Database_Backup\’ location, where your backup file is stored.

You can open the above backup file using a simple text editor, such as Notepad, in Windows. This is how you can back up all the databases on the PostgreSQL server using the pg_dumpall tool.

If you want to back up the database object definitions, such as schemas, roles, tablespaces, indexes, constraints, views, and functions, then use the –roles-only, –schema-only, and –tablespaces-only options with the pg_dumpall command.

Conclusion

In this PostgreSQL tutorial, you have covered how to backup the database on the PostgreSQL server using the pg_dump and pg_dumpall utilities.

You may also read:

• Postgres Export to CSV | Export PostgreSQL Table To CSV File
• Postgresql import SQL file + Examples
• How to Create Database in PostgreSQL