Backup and restore PostgreSQL database for Managed Database for PostgreSQL using external utilities

This guide will explain how to backup and restore PostgreSQL databases with the pg_dump (pg_dumpall) and pg_restore tools.

To protect yourself against data loss, we recommend frequently backing up the databases you manage with Gcore Managed Database for PostgreSQL.

1. Install external utilities

Install backup and restore utilities on your virtual or local server. Below are guides on how to do so for Ubuntu, CentOS, and Windows.

Note: You must have root rights to run the commands successfully.

      Copy
      
    $ sudo  apt update && sudo apt upgrade

      Copy
      
    $ sudo apt install postgresql postgresql-contrib

      Copy
      
    $ sudo yum install postgresql-server

2. Get your credentials

To launch backup and restore tools, you need credentials (username, password, port, and dbname) to connect to the Gcore Managed Database server, where databases are stored. You can find credentials in the Customer Portal, with instructions available in our dedicated guide.

3. Back up databases

Back up a specific database

Use one of the following commands depending on your choice of 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

Instead of username, hostname, and database_name, enter custom values, which you can access using our guide. For backup_file or backup_dir, specify the relevant name for the backup file or directory.

Below are some other details for the commands:

• -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

Back up all databases

Run the command:

      Copy
      
    $ pg_dumpall -U username -W -h hostname > all_pg.sql

Where:

• all_pg.sql is the output backup name
• username is your username from the Customer Portal
• hostname is your hostname from the Customer Portal

4. Restore databases

Run the command:

      Copy
      
    $ pg_restore -U username -W -h hostname -d database_name < backup_file.tar

Where:

• username is your username from the Customer Portal
• hostname is your hostname from the Customer Portal
• database_name is your database name from the Customer Portal
• backup_file.tar is the file or directory with your backup