<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=2826169&amp;fmt=gif">
Start  trial

    Start trial

      There are situations where you may want to restore a database to a different database cluster - to migrate one of the databases in your development cluster to a test cluster, for example.

      But some precautions need to be taken. If the owner of the database object does not exist in the restore destination, an error will occur during the restore. Therefore, the roles related to the objects backed up need to be created in the restore destination in advance, before restoring.

      Execution example

      It is presumed that a user with role 'admin' has created all database objects of the database mydb. This database is assumed to be backed up in the archive file format using the pg_dump command by a user with role 'admin' as follows.

      $ pg_dump -Fc mydb > mydb_dump.custom
      If the role 'admin' does not exist at the restore destination, then create it.
      postgres=# CREATE ROLE admin WITH LOGIN PASSWORD 'pw';
      Restore from the backed up archive file (in our example, mydb_dump.custom)
      Specify any connectable database name (e.g., 'postgres') in the -d option of the pg_restore command.
      $ pg_restore -C -d postgres mydb_dump.custom

      Key points

      • Role information is managed as a global object that is common to all databases in the cluster, so it cannot be backed up by the pg_dump command that backs up each database.

        Therefore, if the same role that performed the backup does not exist in the restore destination, the following error message will be output during the restore.

        could not execute query: ERROR: Role "admin" does not exist
        Command: ALTER SCHEMA myschema OWNER TO admin;

        In the example above, the administrator name of the backup source is 'admin', but an error is output because the role that executed the restore is different.
        Since the tablespace is a global object, it is not backed up by the pg_dump command. Use the pg_dumpall command to back up roles and tablespaces.

      • If the message Ignored error during restore is output at the end of the above error message, it suggests that some database objects have been restored after the error occurred, potentially causing unintended results.

        Before re-executing the pg_restore command, delete the restored objects, othewise additional data may be written in the table.

      • After executing the pg_restore command, check the error message to see if any database objects failed to be restored.
      For more information on PostgreSQL bakup/restore, refer to the PostgreSQL documentation, section Backup and Restore.
      For more information on PostgreSQL roles, refer to the PostgreSQL documentation, section Backup and Restore.

      Related PostgreSQL Insider articles

      PostgreSQL backup and recovery
      This article introduces the basic concepts and types of backup and recovery in PostgreSQL, as well as examples of how to choose a method for a desired outcome.
      Logical backup and recovery
      This article covers the various methods of logical backup and recovery, and which method is useful in what kind of use cases.
      logcial-backup-and-recoery
      Ransomware attacks - prepare and protect with backup planning
      This article explains measures against ransomware attacks that must be set in advance, and why backup planning is the key to business continuity.
      ransomware-attack-and-backup

      Fujitsu Enterprise Postgres is designed for developers, data architects, and DBAs seeking the efficiency of a leading open source based database technology with the enhanced security and performance features plus support required in a serious business environment.

      Brochure

      See how FUJITSU Enterprise Postgres enhances PostgreSQL providing 5 key enterprise capabilities for your mission-critical applications.

      1st  page brochure FUJITSU Enterprise Postgeres - Fujitsu's enhanced open source PostgreSQL
      View brochure

      Read our latest blogs

      Read our most recent articles regarding all aspects of PostgreSQL and Fujitsu Enterprise Postgres.