pg_restore not restoring the database roles to azure postgres flexi server

Prudhvi Tavva 16 Reputation points
2022-12-21T07:29:44.86+00:00

We are using azure postgres flexi server, as part of disaster recovery we have created a backup script which takes backup using pg_dump command and stores backup in storage account. we use pg_restore command to restore the database in the new server. As part of restoration - tables, indexes, constraints were restored but database roles were not getting restored. Below are pg_dump, pg_restore commands we are using in the backup and restore script.

PGPASSWORD="$PGPASSWORD" pg_dump -Fc -v -h "$HOSTNAME" -U "$USERNAME" -d "$DATABASE" -p "$PORT" -f testdb.dump
pg_restore -v --no-owner --host=<server name> --port=<port> --username=<user-name> --dbname=<target database name> <database>.dump

Error
pg_restore: error: could not execute query: ERROR: role "role_name" does not exist

Kindly provide advise to restore the database with roles.

Azure Database for PostgreSQL
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Boris Von Dahle 3,221 Reputation points
    2022-12-21T08:00:33.52+00:00

    Hello,

    Roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility.

    To restore all roles, just copy and paste the output of pg_dumpall --roles-only into the desired psql shell. Or specific CREATE ROLE and ALTER ROLE lines.

    For disaster recovery, i would strongly advise to use Microsoft Azure managed backup instead of pg_dump:

    az postgres server restore \
    --name mydemoserver-restored \
    --resource-group myresourcegroup \
    --restore-point-in-time "2021-05-05T13:10:00Z" \
    --source-server mydemoserver.

    More on this subject here : https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-backup-restore.

    Hope this will help you.

    Have a nice day.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.