How to Dump and Restore PostgreSQL Database?

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 the necessity of knowledge on how to dump and restore Postgresql is major. In this guide, we together want to move through the steps individually.

How to Dump a PostgreSQL Database?

A PostgreSQL dump file represents a text file consisting of SQL statements that can rebuild a database’s structure and content. This file, also known as a “backup“, is thereafter used in reciprocity to restore the database.

• Method 1: Using the pg_dump Command-Line Tool
• Method 2: Using pgAdmin

In this section, we will discuss two methods for dumping a PostgreSQL database: using the pg_dump command-line tool and utilizing pgAdmin, a popular graphical user interface for PostgreSQL management.

Using the pg_dump Command-Line Tool

Step 1: Open Terminal or Command Prompt

Firstly start from your terminal or command prompt 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. Usually, this directory is placed 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

Use your PostgreSQL username instead of it, hostname to indicate the server where the database resides, port for the database server (usually 5432), dbname to be your desired database name and dump.sql to a file name you want for database dump.

Step 4. Enter Password (If Required)

Please type password associated with the corresponding username when asked for one.

Step 5. Verify Dump

After all the commands finish running, check that a file named dump.sql(or any other name you 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

Using the pg_dump

Once you have completed these steps, you dump the Northwind database via the use of pg_dump command line tool. Now the dump file is ready for restoring of 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.

Using pgAdmin

If you prefer a graphical user interface (GUI), you can use pgAdmin, a popular PostgreSQL administration and development tool, to perform the database dump: If you prefer a graphical user interface (GUI), you can use pgAdmin, a popular PostgreSQL administration and development tool, to perform the database dump:

Step 1: Open pgAdmin

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

Choose “Backup” after right clicking the database you want, then choose…”

Backup

Step 4: Specify Backup Options

An appearing dialog box will give you the option to select backup type including encoding (plain, custom, tar) and filename.

Specify Backup Options

Step 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 Backup

How 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
• Method 2: Restoring Backup Using pgAdmin

Restoring Backup Using psql Command-Line Tool

Step 1: Open Terminal or Command Prompt

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

\q

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

Step 1: Launch pgAdmin

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. Click “Restore” to initiate the restoration process.

Click “Restore” to initiate the restoration process.

Process started

Conclusion

Backup/Restore and database dumping are quite common workscopes for the database administrators, developers, and even general PostgreSQL users. Whatever the case may be, you may be migrating data, making backups, or transferring databases from one environment to another, proper handling of these circumstances is essential. Through adherence to the steps described in this guide, you’ll be assured of dumping and restoring PostgreSQL databases with ease and reliability, despite disturbing the integrity and availability of your data.