Add handling of sequences which are not associated with a table column

[bonesmoses]

I ran across this project in the ever-expanding ecosystem of Postgres tools. Given it's a replication / sync tool, I took a look at the sequence handling code, as this is an area that's often covered improperly.

I noticed in your refresh_sequences method that you're only "refreshing" sequences which are directly associated with a table column, and doing so by implicitly using the max() function on the underlying table.column.

This does not cover cases where bare sequences are referenced by application code and are thus not associated with a table at all. A safer approach would be to obtain the current values of the upstream publishing system and adjust all sequences based on those values. You can do this by leveraging the last_value column in the sequence itself.

[shayonj]

Thank you so much for taking the time and opening this report. I think because writes are stopped on the source (upstream publisher) database, its safe to read the sequence value from there.

For all the use cases so far, its been sequences that are directly tied to the tables. I will put a PR (sometime in the next week) to refresh sequences for all sequences regardless of the association with the table. Great catch.

[shayonj]

Finally, getting around to this. I think i misunderstood the original request at first. Its actually intentional that we refresh sequences by table, because pg_easy_replicate today doesn't support replicating sequences (same as logical replication), hence why we are not refreshing or porting over sequences from the source DB (reference to limits in the readme).

That said, it could be a nice feature to support this. That is, recreate sequences when sync begins and then post switchover refresh them, like you mentioned.

I think we can accept a --replicate-all-sequences (default true) or similar option in CLI to begin this stream of work. Will try to give it a spin.