0

We are using the pglogical extension for replicating a PostgreSQL database into Google Cloud, using the Database Migration Service. We are able to reach the continuous replication phase successfully. The problem is after this.

Over the source database is running a system that executes some checks in the database, specifically in the "public" schema. This check is detecting changes in this schema and is removing all the changes, and for this reason, the replication is failing. Could you give me information about what modifications performs pglogical/Database Migration Service in the "public" schema?

Thanks in advance.

alek6dj
  • 334
  • 3
  • 17
  • Can you share more information about your setup? How is the source database hosted (CloudSQL as well or externally)? This is to verify what kind of service could be running on the database and affecting the schema. I found the following [guide](https://cloud.google.com/blog/topics/developers-practitioners/upgrade-postgres-pglogical-and-database-migration-service) as well, which goes into detail about using `pglogical` with CloudSQL PostgreSQL and Database Migration Service, and it includes several important points about pglogical and DMS limitations. – ErnestoC Dec 01 '21 at 19:11
  • Hello @ErnestoContrerasPinon thanks for answering. The source database is externall to GCP. We can reach the continuos replication phase successfully. But when we launch a specific system functionality over the source database, it detects changes in the schema, revert them, and the replication fails. We want to know which changes are, to ignore them. The error we have in DMS after reverting the changes in the source is: "[DATABASE]: null value in column [COLUMN] violates not-null constraint", but there are not null values in this column in the source. We are aware of DMS limitations. – alek6dj Dec 02 '21 at 09:26
  • It would be good if you could share the schema you are currently using, or a dummy schema instead that can be used to troubleshoot this issue. This [document](https://cloud.google.com/database-migration/docs/postgres/known-limitations) lists all the important points about migration, including some scenarios in which schemas cannot be properly migrated, and this other [document](https://cloud.google.com/database-migration/docs/postgres/migration-fidelity) touches on the continuous migration. Without seeing the actual details of your schema, it would be hard to see if any of these apply for you. – ErnestoC Dec 02 '21 at 19:00
  • Hello @ErnestoContrerasPinon I had read the docs but my issue is not related with that. I can achieve the replication. If no other action is performed in the source database, the replication works fine for all DMLs. My problem is not to replicate a databse, my problem is that for some reason, some modifications are performed in the "public" schema of the source database. These schema modifications are detected and reverted by a process we have. And this is causing the replication to fail, only after the revert, otherwise works fine. That's why I'm trying to find the modifications DMS is doing – alek6dj Dec 02 '21 at 19:55
  • Are you running any DDL/DML process when you are trying to make the migration? Make sure that no process (DDL/DML) is active at the moment the migration is being done since it will fail if any of these processes are running. – Eduardo Ortiz Dec 03 '21 at 23:38

0 Answers0