0

I am currently using knex migrations to manage postgres schema changes. I have a source db and a target db with logical replication enabled using posgres 10.7. I am trying to figure out the best way to keep the schemas identical otherwise replication will break if schemas do not match exactly. To do this, I am wondering if there's a way to run knex migrations to two separate databases at the same time, and if not the same time the very quickly after the initial migrations are run on the source db.

acob801
  • 1
  • Doesn't logical replication handle that automatically? So that your replica DB will get updated schema form master? – Mikael Lepistö Sep 04 '19 at 09:42
  • Unfortunately no. Logical replication will not replicate schema changes from source to target. – acob801 Sep 05 '19 at 11:17
  • Only robust way to run migrations to both is to make sure that when replication is happening both server instances has the same schema. There is no way to guarantee that migrations are ran to both servers at the same time. So you need to update first one and then another and stop replication for the time when schemas might be different. For example you could first take target off from replication and run migrations to it. Then you could run migrations to source and continue replication and let it to handle updating any data that was modified during the target was offline. – Mikael Lepistö Sep 09 '19 at 07:25
  • Or you could just do it the way https://www.postgresql.org/docs/10/logical-replication-restrictions.html describes and let replication fail when there are incompatibilities in schema. – Mikael Lepistö Sep 09 '19 at 07:33

0 Answers0