Backup Database in Postgres

In this PostgreSQL tutorial, you will learn how to backup database in Postgres using the tools pg_dump and pgdumpall.

Also, you will learn why database backup is important and the types of backup in PostgreSQL.

Backup Database in Postgres

One of the critical tasks of a database administrator is to back up the database. But why backup is important, because of some problems that may occur to your system or infrastructure and your data can be lost.

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

In general, with PostgreSQL, you can perform two kinds of backup, logical and physical backup.

• Logical Backup: Here your database-related information is stored such as schema, tables and etc.
• 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, the pg_dump and pg_dumpall. Here you will learn about both the tools with help of examples.

Backup Database in Postgres using pg_dump

If you want to backup only the single database, then use the tool pg_dump of PostgreSQL. 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 of how to use the pg_dump tool for backup is given 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 ask for the password while connecting to the PostgreSQL server for 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 backup this database into a single file called ‘backup.sql‘ at the location ‘D:\Database_Backup\backup.sql’.

Use the below command.

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

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

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

Backup Database in Postgres using pg_dumpall

The pg_dumpall tool allows you to backup all the databases at one time on the PostgreSQL server, unlike pg_dump which backup only a single database at one time.

You can also backup all the databases using the pg_dump tool but you will have to run this tool one by one for each database on the PostgreSQL server and also restoring all the databases will be a time-consuming task.

The syntax to backup all the databases using pg_dumpall is given below.

pg_dumpall -U username > path\all_backup.sql

• pg_dumpall: It is the pg_dumpall tool or command itself to backup all the database.
• -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 below command to backup 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, this is because connecting to PostgreSQL server several times for each database.

But remember if you don’t want to enter the password several times, then configure the file called .pgpass file which stores the user login data.

Go to the location ‘D:\Database_Backup\’ where your backup file is saved.

You can open the above backup file with a simple text editor like 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 backup the database object definitions such as schema, roles, tablespaces, indexes, constraints, views, functions and etc, then use these options, –roles-only, –schema-only, –tablespaces-only with 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 utility.

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