Backing up and Restoring a Single Database via pgAdmin
At the moment, taking backups for a single database is not available in the Control Panel. The PgAdmin is the most popular open source tool for managing PostgreSQL. We can use pgAdmin to back up and restore a single PostgreSQL database remotely. This article will explain how you can do it.
Backup
1. Locate the target deployment
Log in to the Control Panel and locate your target deployment on the Home page or the My Applications page.
2. Get connection string information
Click the "Manage" button on the Home page or the My Applications page to go to the DB & User page.
Scroll down to the Connection Information section to get the connection string for your database.
3. Connect to databases using pgAdmin
Download PgAdmin tool at https://www.pgadmin.org/download/
Start the PgAdmin tool and set up a new database connection as the screenshot.
Fill in the requested information to connect the database.
General name – The name that will be shown in pgAdmin (Does not affect connection)
Server group – Select Servers
Connect now – Enable it
Hostname/address – The IP address or hostname for the PostgreSQL server
Port – Port information is defined in the control panel
Maintenance database- The database name that you want to connect
Username – The database user
Password– Enter your PostgreSQL database's password and save it
4. Back up databases using pgAdmin
Select the database you want to back up: right-click the database and select Backup.
1> Click the button with three-dot icon at the end of the filename field,
2> Select the storage path of the backup file,
3> Enter the name of the backup file and click Backup
PostgreSQL backup comes in three forms: custom, tar, and plain. Custom and tar formats form a.backup file while unformatted forms a text file. Choose a custom or tar one so that your backup file can be restored via pgAdmin later.
The following image shows that the backup was completed successfully. As you can see from the image, the database backup was performed via pg_dump.
Restoration
1. Connect to the database
Start the PgAdmin tool and set up a new database connection as the screenshot shows.
2. Restore a backup file
If the to-be-restored database does not exist in the current PostgreSQL instance, you should first create a database with the same name as the one in the backup file. If you want to restore the database to another database, you can overwrite the original database.
The format can only be Custom or tar format, which means that pg_dump files backed up in an unformatted way cannot be restored with gp_restore; pg_restore can only restore backup files.
Format - Select Custom or tar
Filename - Enter the complete path to the backup file
Number of Jobs - specify if pg_restore should use multiple (concurrent) jobs to process the restore.
Rolename - specify the role that will be used to authenticate with the server during the restore process.
The following image shows that the backup was restored successfully. As you can see from the image, the database backup was performed via pg_restore.
