Unknown error occurred on migrate azure postgres single server to azure postgre flexible server

Question

I follow the online migration set up to migrate the single server from version 10.0 to flexble server version 11.0.But after passing the the validation the migration was failed.This is the error log.

pgcopydb failed with unknown errors. Please contact Microsoft support. Logs from the migration: 2024-12-31 02:53:18.702 62 ERROR pgcmd.c:640 Failed to run vacuumdb: exit code 1 2024-12-31 02:53:18.702 62 ERROR copydb_schema.c:666 Failed to vacuum analyze source database, see above for details

Accepted Answer

Hi njnjkkl ,

Thanks for reaching out to Microsoft Q&A.

There are some restrictions for online migration please check the doc here. https://www.postgresql.org/docs/current/logical-replication-restrictions.html

pgcopydb, the underlying program, might not be able to handle double-quoted entities. Please make sure to enable quote_all_identifiers and try again**.**

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Answer

Hi @njnjkkl,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We apologize for the inconvenience caused to you.

We would like to inform you that, online migration makes use of pgcopydb follow, and some of the logical decoding restrictions apply. We also recommend that you have a primary key in all the tables of a database that's undergoing online migration. If a primary key is absent, the deficiency results in only insert operations being reflected during migration, excluding updates or deletes. Add a temporary primary key to the relevant tables before you proceed with the online migration.

Note: In the case of online migration of tables without a primary key, only insert operations are replayed on the target. This can potentially introduce inconsistency in the database if records that are updated or deleted on the source don't reflect on the target.

An alternative is to use the ALTER TABLE command where the action is REPLICA IDENTIY with the FULL option. The FULL option records the old values of all columns in the row so that even in the absence of a primary key, all CRUD operations are reflected on the target during the online migration. If none of these options work, perform an offline migration as an alternative.

Note: For online migration with Azure Database for PostgreSQL single server, the Azure replication support is set to logical under the replication settings of the single server page in the Azure portal. And also, Change the password_encryption server parameter on your flexible server from SCRAM-SHA-256 to MD5 before initiating the migration. This is essential for the existing credentials on single server to work on your flexible server.