Avoid serial/sequence column collisions by omitting column from sync

[abgoldberg]

I'm wondering if there's a configuration option to exclude certain columns when doing a sync. I see there's the data protection rules, and an option of "null" but I suspect that means a null is inserted in place of the value. In my case, I would rather a column just be excluded altogether. The reason is that the column is a serial type / sequence, and so I may have id values from my first DB that overlap with the id values already generated in the other DB. I just want to ensure that the data in the rows is synced but would want the destination to auto-generate the id according to its own next sequence value.

This is a simplified version -- where I have posts in the from DB that will get synced to the to DB. But the to DB may have more posts already, and I want to ensure that all the source posts get inserted into the destination DB with unique IDs. The sequence itself (like posts_id_seq) is getting synced, which partially helps here in terms of the destination not getting out of sync and trying to generate sequence values that were copied from the source. But there's still a chance of id collisions. Is there a way to address this currently, or some workaround you could recommend? Maybe it's possible to either omit a column or explicitly list only specific columns to copy? Thanks!

createdb pgsync_from
createdb pgsync_to

psql pgsync_from << SQL
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  name TEXT
);
INSERT INTO posts VALUES (1, 'hello'), (2, 'world');
SQL

psql pgsync_to << SQL
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  name TEXT
);
SQL

pgsync --from pgsync_from --to pgsync_to

[ankane]

Hi @abgoldberg, this isn't supported (as pgsync isn't designed for this use case), but you could probably fork and make it work for your application.