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 ?