Dump Import/Export to PostgreSQL

Import and Export Dump Files to PostgreSQL

Use pgAdmin 4 to export a PostgreSQL database to a dump file and restore that dump into another PostgreSQL environment. The source example uses two database environments and verifies the restored content on both Master and Slave nodes.

In this article
Two PostgreSQL database environments
Create two PostgreSQL database environments and connect to them with pgAdmin 4.

Prepare the PostgreSQL Environments

Create two PostgreSQL database environments and connect to both of them with a desktop client. The source guide uses pgAdmin 4.

In the source example, the environments remotepostgres.vip.jelastic.cloud and destination.vip.jelastic.cloud are added to the pgAdmin server groups backupsource and destination.

pgAdmin server groups
Add the source and destination PostgreSQL environments to separate pgAdmin server groups.

Dump Export from PostgreSQL

1

Select the source database

Right-click the database that should be backed up. The source example uses a database named Jelastic. Select Backup.

Backup required PostgreSQL database
Right-click the source database and choose Backup.
2

Set the backup filename and format

Specify the dump filename and output format. The source example uses the filename mybackup and the Tar format.

PostgreSQL backup dialog
Specify the backup filename and output format.
3

Configure dump options

Open the Dump options tab and select the required backup options for database objects.

PostgreSQL dump options
Select the required database-object backup options.
4

Create the dump

Click Backup. When the export completes successfully, pgAdmin displays a success notification.

Successful PostgreSQL backup
A success window confirms that the PostgreSQL dump was created.
✓

Dump file location

The dump file is stored in the user home directory or in the path specified on the host where pgAdmin 4 is running.

Dump Import to PostgreSQL

1

Create an empty destination database

On the destination PostgreSQL server, create a new empty database.

Create new PostgreSQL database
Create an empty destination database before restoring the dump.
2

Name the destination database

Enter the database name. It can match the source name, such as Jelastic, or use any other required name.

Create PostgreSQL database dialog
Give the destination database the same or any required name.
3

Open Restore

Right-click the newly created destination database and select Restore.

Restore PostgreSQL database
Right-click the new destination database and choose Restore.
4

Select the backup file

Select the same file format that was used during export. In the source example, this is Tar. Enter the backup filename or use the file-selection dialog beside the Filename field.

PostgreSQL restore dialog
Select the backup format and backup file.
5

Configure advanced restore options

Set additional restoration options when required.

PostgreSQL restore options
Configure advanced restore options when required.
6

Restore the dump

Click Restore to import the selected dump into the destination PostgreSQL database.

Verify the Restored Database

Return to the destination server group and open the restored database. Confirm that its database content was restored successfully and, for the replicated PostgreSQL example, is available on both Master and Slave servers.

PostgreSQL database restored and replicated
Verify that the restored database and its content are available on both Master and Slave.

Expected Result

The source PostgreSQL database is exported from pgAdmin 4 as a dump file, a new destination database is created, and the dump is restored into it using the same backup format. The restored content is then verified on the destination database servers.

Important Notes

  • The source example uses pgAdmin 4.
  • The backup filename and output format are selected during the Backup operation.
  • The source example uses the Tar format.
  • Create an empty database on the destination server before restoration.
  • Use the same backup format during Restore that was selected during Backup.
  • The destination database can use the original database name or another name.
  • The dump file is stored on the machine where pgAdmin 4 is running.
  • After restoration, verify the database content on the destination server.