Migration Service: Online migration error with JSON malform error when cutover or insert/update records after initial sync

Question

Hi,

Target DB: Azure DB for postgresql Flexible server version 15

Source DB: on-prem PG version 9.5 on centos

Below pgcopydb error pops up and job fails when migrating with migration service extension in Azure DB for PG flexible server. Error looks like something to do with json parsing with pgcopydb.

I've raised a SR for this but was wondering if someone here could shed some light.

Thanks

DMS error Error message 1 (when cutover initiated): One or more errors occurred while migrating 'tcsi_dev' : pgcopydb failed with unknown errors. Please contact Microsoft support. Logs from the migration: 2025-02-12 05:39:46.158 72 FATAL ld_apply.c:948 Failed to parse KEEPALIVE message: -- KEEPALIVE {"lsn":"2/46052C50","timestamp":""} 2025-02-12 05:39:46.158 72 ERROR src/ffi/file_utils.rs:25 file_stream_iter_lines: Failed to iterate over lines of file stream, see above for details 2025-02-12 05:39:46.158 72 ERROR ld_replay.c:72 Failed to read SQL lines from input stream, see above for details 2025-02-12 05:39:46.221 38 ERROR follow.c:1062 Subprocess catchup with pid 72 has exited with error code 12 2025-02-12 05:39:46.371 38 ERROR follow.c:659 Some sub-process exited with errors, see above for details 2025-02-12 05:39:46.371 38 ERROR follow.c:299 Failed to follow changes from source, see above for details 2025-02-12 05:39:46.487 33 ERROR cli_clone_follow.c:764 follow process 38 has terminated [12]

• Error message 2 (when insert was done after initial sync)

One or more errors occurred while migrating 'tcsi_dev' : pgcopydb failed with unknown errors. Please contact Microsoft support. Logs from the migration: 2025-02-12 07:44:01.554 71 ERROR ld_stream.c:1854 Failed to parse JSON message 2025-02-12 07:44:01.554 71 ERROR ld_transform.c:617 Failed to parse JSON message metadata: ction":"K","lsn":"2/49000968","timestamp":"2025-02-12 07:37:42.472898+0000"} 2025-02-12 07:44:01.554 71 ERROR src/ffi/file_utils.rs:25 file_stream_iter_lines: Failed to iterate over lines of file stream, see above for details 2025-02-12 07:44:01.554 71 ERROR ld_transform.c:233 Failed to transform JSON messages from input stream, see above for details 2025-02-12 07:44:01.570 72 FATAL ld_apply.c:948 Failed to parse KEEPALIVE message: -- KEEPALIVE {"lsn":"2/490000B8","timestamp":""} 2025-02-12 07:44:01.570 72 ERROR src/ffi/file_utils.rs:25 file_stream_iter_lines: Failed to iterate over lines of file stream, see above for details 2025-02-12 07:44:01.570 72 ERROR ld_replay.c:72 Failed to read SQL lines from input stream, see above for details 2025-02-12 07:44:01.595 38 ERROR follow.c:1062 Subprocess tra... (rest of the failure message is truncated.)

Answer 1

Hi @CHUNSIK KIM,

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

We apologize for the inconvenience caused to you.

As we understand that, you are facing a problem with the pgcopydb tool during the migration to Azure Database for PostgreSQL Flexible Server.

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.

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 refer to the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/postgresql/migrate/concepts-single-to-flexible#improve-migration-speed---parallel-migration-of-tables

https://learn.microsoft.com/en-us/azure/postgresql/single-server/whats-happening-to-postgresql-single-server#migrate-from-azure-database-for-postgresql-single-server-to-azure-database-for-postgresql-flexible-server

https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_follow.html#pgcopydb-follow

If my response helped, kindly click 'Accept Answer' and select 'Yes' for 'Was this answer helpful.' this can be helpful to others in the community.

If you have any further questions or require additional assistance, please do not hesitate to let us know.