Search code examples
azurepsqlpg-restoreazure-postgresql

PostGreSQL pg_dump psql and pg_restore errors with Azure Flexible SQL Server

I am trying to dump and then restore PostGreSQL databases using Azure PostGreSQL Flexible.

I am using pg_dump and psql previously for these purposes.

I have been succesfully using following commands to backup and restore my PostGreSQL instances on other providers and on premise for multiple years without any issues.

pg_dump -U postgres -h localhost -p 5432 staging -n public --format=p > project-io-staging-4-Sep-2024.sql

psql -U postgres -h localhost -p 5432 production -c "drop schema public cascade;"
psql -U postgres -h localhost -p 5432 production -n public -1 -f ./project-io-production-4-Sep-2024.sql

However, when I try to do exact same thing with Azure Flexible Server, here below is what I get :

% psql "host=project-development2.postgres.database.azure.com port=5432 user=myuser dbname=staging sslmode=require" -1 -f ./project-io-staging-4-Sep-2024.sql
Password for user myuser: 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
COMMENT
SET
SET
CREATE TABLE
psql:project-io-staging-4-Sep-2024.sql:60: ERROR:  must be able to SET ROLE "postgres"
psql:project-io-staging-4-Sep-2024.sql:72: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:project-io-staging-4-Sep-2024.sql:75: ERROR:  current transaction is aborted, commands ignored until end of transaction block
...

I also cannot set role as azuresu user is not available for login.

I have used the following command to create a dbase without UI, but still the roles are not available for the newly created dbase although the very same user has created the dbase.

createdb staging -h project-development2.postgres.database.azure.com -p 5432 -U project

I am trying to use the following as well, but still no luck, I get same error messages :

pg_dump -Fd -j 2 staging -h localhost -p 5432 -U postgres -N cron -f ./project-io-staging-5-Sep-2024.sql

pg_restore -j 2 -d staging -h project-development2.postgres.database.azure.com -p 5432 -U project project-io-staging-5-Sep-2024.sql

There is mention of this flag for pg_restore "--no-owner", however I cannot risk that as well.

How can I make this migration to succesfully happen, as it were happening for the last many years with other cloud providers ?

Solution

  • must be able to SET ROLE "postgres"

    Role management in the dump file is cause of1 the above error. Excluding ownership and ACLs (privileges) from the dump process is the easiest method to handle this kind of errors.

    • --no-owner: This flag prevents commands from trying to modify who owns what in the database. It guarantees that you won't run into ownership authorization problems when restoring.

    • --no-acl: This parameter prevents privilege mismatches between your local and Azure PostgreSQL environments throughout the dump by skipping instructions related to privileges.

    Sample command:

    pg_dump -U postgres -h localhost -p 5432 staging -n public --format=p --no-owner --no-acl > project-io-staging-4-Sep-2024.sql