Skip to main content
The pg_dump and pg_restore utilities back up and restore PostgreSQL databases from a remote server without requiring direct filesystem access. Back up Gcore Managed PostgreSQL databases regularly to protect against data loss.

​
Install external utilities

Install the backup and restore utilities on the virtual or local server. Installation instructions are provided below for Ubuntu, CentOS, and Windows.
Administrator privileges may be required to install the PostgreSQL utilities.
  1. Connect to the virtual server.
  2. Download the latest available updates and upgrade apt:
  1. To install pg_dump and pg_restore tools, install the Postgres package, which includes them:
  1. Connect to the virtual server.
  2. Install the PostgreSQL package:
The package contains all required tools.
  1. Download the PostgreSQL installer for the relevant version.
  2. Run the installation process.

​
Get credentials

The backup and restore tools require credentials (username, password, and dbname) to connect to the Gcore Managed PostgreSQL server. Gcore Managed PostgreSQL uses port 5432. Retrieve credentials from the Customer Portal: Get credentials.

​
Back up databases

pg_dump backs up a single database. Gcore Managed PostgreSQL does not provide the superuser access required by pg_dumpall, so to back up multiple databases, run pg_dump separately for each one.

​
Back up a specific database

Use one of the following commands depending on the required backup format:
  • Default SQL format: $ pg_dump -U username -W -h hostname -d database_name > database_name.sql
  • TAR format: $ pg_dump -F t -U username -W -h hostname -d database_name > backup_file.tar
  • Custom format: $ pg_dump -F c -U username -W -h hostname -d database_name > backup_file.dump
  • Directory format: $ pg_dump -F d -U username -W -h hostname -d database_name -f backup_dir
Replace username, hostname, and database_name with the actual values from the Customer Portal. For backup_file or backup_dir, specify the name for the backup file or directory. Additional flag details:
  • -W prompts the password before connecting to the PostgreSQL server
  • -F specifies the output format of the backup and can be followed by the following flags:
    • c for custom format
    • d for directory format
    • t for tar

​
Restore databases

The restore command depends on the backup format used in the previous section. SQL format (default) — restore with psql:
TAR, custom, or directory format — restore with pg_restore:
Where backup_path is the path to the backup file (.tar, .dump) or directory created in the previous section.
When restoring to a Gcore Managed PostgreSQL server, pg_restore may encounter permission errors on internal system schemas. If the database uses only the public schema, restrict the restore to that schema to skip those errors:
If the database uses application schemas beyond public, omit --schema=public. pg_restore may report permission errors for system objects; review these errors separately from errors affecting application schemas.
Where:
  • username: the username from the Customer Portal
  • hostname: the hostname from the Customer Portal
  • database_name: the name of the database to restore into
  • backup_path: the backup file (.tar, .dump) or directory to restore from