How to Dump and Restore PostgreSQL Database?
Last Updated : 17 Oct, 2024
PostgreSQL remains among the most efficient and widely applied open-source relational database management systems. It provides the superior function of saving, configuring, and extracting information most effectively. In the process of migrating data, creating backups, or transferring databases between environments, knowing how to dump and restore PostgreSQL is essential.
In this article, we will go through the individual steps to dump and restore PostgreSQL databases using both the command-line interface (CLI) and pgAdmin, a popular graphical user interface for PostgreSQL management.
How to Dump a PostgreSQL Database?
A PostgreSQL dump file is a text file consisting of SQL statements that can recreate a database’s structure and content. This "backup" file can be used later to restore the database. Here, we will discuss two methods for dumping a PostgreSQL database: using the pg_dump command-line tool and pgAdmin.
Method 1: Using the pg_dump Command-Line Tool
The pg_dump command-line utility is the most commonly used method to dump a PostgreSQL database. It creates a logical backup, saving the database as a plain-text file or in a custom format.
Step 1: Open Terminal or Command Prompt
Start by opening the Terminal on Linux/Mac or Command Prompt on Windows.
Step 2. Navigate to PostgreSQL Bin Directory (Optional)
If the path to PostgreSQL's bin directory is not added to your system's PATH, a command 'cd' will be used to navigate to the directory. This directory is usually located within your PostgreSQL installation directory.
Step 3. Execute the pg_dump Command
The command for the pg_dump will be "pg_dump database_name". Optional parameters including username, hostname, and port settings can be specified here.
pg_dump -U username -h hostname -p port dbname > dump.sql
- Replace username with your PostgreSQL username.
- Replace hostname with the server where the database is located (use "localhost" if local).
- Replace port with the port number (default is 5432).
- Replace dbname with the name of the database we want to dump.
- Replace dump.sql with the name of your dump file.
Step 4. Enter Password (If Required)
Enter the password associated with the corresponding username when prompted.
Step 5. Verify Dump
After all the commands finish running, check that a file named 'dump.sql' (or any other name we want) has been created inside this directory. It will be in the current directory. The data file saves the SQL statements in order to set up the database structure and to insert the data.
Example:
pg_dump -U postgres -h localhost -p 5432 -d northwind > C:\Users\Sanket\Desktop\northwind_backup.sql
Output
Using the pg_dumpExplanation:
Once we have completed these steps, we dump the Northwind database via the use of 'pg_dump' command line tool. Now the dump file is ready for restoring the database later or moving the database to another PostgreSQL instance.
Database administrators as well as developers perform dumping databases as a regular one-off operation to preserve the database data integrity and uptime in different scenarios.
Method 2: Using pgAdmin
For users who prefer a graphical interface, pgAdmin provides an easy way to create database backups.
Step 1: Open pgAdmin
Launch pgAdmin connect to your PostgreSQL server.
Step 2: Navigate to the Databases Section
In the object browser, click the + button to widen the server node, the Databases section under it.
Step 3: Right-click on the Database
Right-click on the database you want to dump and select "Backup."
BackupStep 4: Specify Backup Options
A dialog box will appear, allowing you to select the backup type (plain, custom, tar) and specify the filename.
Specify backup optionsStep 5: Initiate Backup
Clicking on "Backup" to initiate the backup process. At the finishing point, you are left dump file that holds the database schema and data in it.
Initiate BackupHow to Restore a PostgreSQL Database?
PostgreSQL database recovery is one of the most important tasks in database administration. It can be used to recover from a backup, migrate data, or create a new environment. In this section, we will go over how to restore a PostgreSQL database. We will use the PostgreSQL command-line tool (psql) as well as the popular graphical interface (pgAdmin) to do this.
Method 1: Restoring Backup Using psql Command-Line Tool
The psql tool is commonly used to restore PostgreSQL backups from a SQL dump file.
Step 1: Open Terminal or Command Prompt
Open your terminal or command prompt window.
Step 2: Connect to PostgreSQL Server
Execute the following command to connect to the PostgreSQL server using the psql command-line tool:
psql -U postgres
Enter the password for the Postgres user if prompted.
Step 3: Create a New Database
Inside the psql environment, create a new database named 'recoverdb':
Step 4: Exit psql Environment
Type '\q' to exit the psql environment.
Step 5: Restore Backup
Outside the psql environment, use the following command to restore the backup file to the 'recoverdb' database:
psql -U postgres -d recoverdb -f "C:\Users\Sanket\Desktop\northwind_backup.sql"
Example:
Type the command to recover the dump file in recoverdb database which we created before.
Recovery the dump file Enter password and the database will be recovered from northwind_backup.sql dump file
Recovered data Method 2: Restoring Backup Using pgAdmin
Restoring a database through pgAdmin is easy and user-friendly.
Step 1: Launch pgAdmin
Open pgAdmin and connect to your PostgreSQL server.
Step 2: Create a New Database
Navigate to the Databases section, right-click on the server, and select "Create" > "Database...". Enter recoverdb as the database name and click "Save".
Step 3: Restore Backup
Right-click on the 'recoverdb' database you just created and select "Restore." In the dialog box that appears, navigate to the location of your backup file ('northwind_backup.sql') and select it.
Restore BackupClick "Restore" to initiate the restoration process.
Process startedConclusion
Backup/Restore and database dumping are common tasks for database administrators and developers. Whether you are migrating data, making backups, or transferring databases between environments, understanding how to dump and restore PostgreSQL databases is crucial.
By following the steps outlined in this article, we can reliably and efficiently dump and restore our PostgreSQL databases without risking data integrity or availability. Both pg_dump and pgAdmin offer powerful options for ensuring our database backups are properly created and restored.
Similar Reads
PostgreSQL - Restore Database
Restoring a PostgreSQL database is an essential process for data recovery, especially when dealing with data loss, corruption, or unintentional errors. The ability to efficiently restore a database ensures that an organization can maintain data integrity, availability, and consistency. In this artic
5 min read
How to Setup a PostgreSQL Database Cluster
A PostgreSQL database cluster refers to a collection of databases managed by a single instance of the PostgreSQL server. Setting up a PostgreSQL cluster is an essential task for organizing multiple databases and achieving high availability, scalability, and load balancing. Whether we are working wit
5 min read
PostgreSQL - Size of a Database
Efficient database management is essential for ensuring optimal performance in PostgreSQL. One critical aspect of this is monitoring the database size to manage storage and plan for scaling. PostgreSQL offers powerful built-in functions like pg_database_size() to calculate the size of a specific dat
4 min read
How to use PostgreSQL Database in Django?
This article revolves around how can you change your default Django SQLite-server to PostgreSQL. PostgreSQL and SQLite are the most widely used RDBMS relational database management systems. They are both open-source and free. There are some major differences that you should be consider when you are
2 min read
How to Migrate a PostgreSQL Database to MySQL
Moving a database from one platform to another can be tough, but with careful planning and execution, it can be done smoothly. In this article, we'll go over how to migrate a PostgreSQL database to MySQL, which are both popular RDBMS. We'll cover preparation, schema conversion, data migration, and t
5 min read
How to Export PostgreSQL Database Without Data Using SQL?
When we are working with the PostgreSQL database, there are multiple times we need to export the database structure. This approach is useful when we create a skeleton database or migrate the schema changes for different environments or systems. In this article, we will explore the process of exporti
3 min read
How to set up a PostgreSQL Database with Podman
Podman is a tool that developers and system administrators are using more and more to manage and deploy their software stacks as the need for containerized applications grows. We will look at how to use Podman to set up and maintain a PostgreSQL database in this tutorial. The overview of PostgreSQL
7 min read
How to Import and Export SQL Server Database?
Creating and managing a SQL Server database is an essential skill for database administrators and developers. In this article, We will go through the process of setting up a database in SQL Server, from creating the database and tables to inserting records, and finally, exporting and importing the d
3 min read
How to List Databases and Tables in PostgreSQL using PSQL
PostgreSQL is a powerful, open-source object-relational database system. It provides a wide array of tools and features to manage databases, tables, and other database objects. In this article, we will explain how to list databases and tables in PostgreSQL using the psql command-line interface. We w
3 min read
How to Back Up and Restore a MongoDB Database?
MongoDB is considered one of the classic examples of NoSQL systems. Its documents are made up of key-value pairs, which are the basic unit of data in MongoDB. Whether we're dealing with accidental data loss, hardware failures, or other unforeseen issues, having a solid backup and restoration plan ca
5 min read