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.
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.customIf 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)
$ pg_restore -C -d postgres mydb_dump.custom
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.
Before re-executing the pg_restore command, delete the restored objects, othewise additional data may be written in the table.
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.
Read our most recent articles regarding all aspects of PostgreSQL and Fujitsu Enterprise Postgres.