It is essential to back up your PostgreSQL database regularly, regardless of how large or small it is. For this purpose, Postgres offers a couple of standard tools, such as pg_dump and pg_dumpall. Using these utilities, users can back up single or all databases.
This post will guide you on how to back up a PostgreSQL database via pg_dump and pg_dumpall tools. This blog post will cover the below-listed learning outcomes:
- What is pg_dump, and How to Use it in Postgres?
- How Do I Back up a Single Database?
- What is pg_dumpall, and How to Use it in Postgres?
- How to Back up All Databases in Postgres?
So, let's start!
What is pg_dump, and How to Use it in Postgres?
pg_dump is a standard tool/utility in Postgres that assists users in backing up the content of a database into a single file. Follow the below syntax to dump a database into a plain text file:
pg_dump db_name > file_name.sql
How Do I Back up a Single Database?
Step-by-step guidelines for backing up a database are listed in this section:
Step1: Access Bin Directory
Firstly, open the command prompt and run the following command to navigate to the Postgres bin folder:
cd C:\Program Files\PostgreSQL\14\bin
Hit the “Enter” button to access the desired directory/path:
The above snippet indicates the successful entry into the “bin” directory.
Step2: Backup the Database
Now, execute the below-mentioned command to back up the desired database, in our case, its “example” database:
pg_dump -U postgres -F t example > c:\backupFiles\example.tar
In the command mentioned above:
- pg_dump is a command used to back up a particular database.
- U specifies a user; in our case, the user is “postgres”.
- F option is used to specify the file format in which the database will be backed up.
- t represents that the desired database will be backed up in “tar” format. However, you can specify any other format of your choice, such as “custom format”, “directory format”, or plain text.
- “example” is a database to be backed up.
- “backupFiles” is a directory in which the desired database will be backed up:
Executing the above command will ask for a password, provide the password, and hit the “Enter” button. As a result, the desired database will be restored/backed up.
Step3: Check/Verify the Backed Up Database
Now access the directory where you backed up the database; in our case, it’s “C:\backupFiles”:
The above snippet shows that the desired database has been successfully backed up in the destination folder.
What is pg_dumpall, and How to Use it in Postgres?
The pg_dump tool allows you to back up all databases one by one; however, PostgreSQL offers a tool called pg_dumpall that allows you to back up all databases simultaneously:
pg_dumpall user_name> file_name.format
Note: Database experts prefer backing up databases sequentially(using pg_dump) instead of simultaneously(using pg_dumpall). This is because the pg_dumpall toll takes more time as compared to pg_dump.
How to Back up All Databases in Postgres?
This section presents step-by-step guidelines to backup all the databases using pg_dumpall:
Step1: Access Bin Directory
First, open the CMD and run the below command to navigate to the Postgres bin folder:
cd C:\Program Files\PostgreSQL\14\bin
Press the “Enter” button to reach the specified path:
The above snippet indicates the successful entry into the “bin” directory.
Step2: Back Up All the Database
Now utilize the pg_dumpall utility to back up all the databases:
pg_dumpall -U postgres > c:\backupFiles\allFiles.tar
Provide the password for each database and hit the enter button. Consequently, all the databases will be backed up in the destination directory.
Conclusion
Postgres offers a couple of standard tools/utilities, such as pg_dump and pg_dumpall, to back up a single or all databases. Using these utilities, users can back a database into any format of their choice, such as “custom format”, “tar format”, “directory format”, or “plain text SQL format”. This blog post presented an in-depth overview of backing up a Postgres database using pg_dump and pg_dumpall utilities.
