pg_restore not restoring the database roles to azure postgres flexi server

Question

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.

Answer 1

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.