A single sequence for all id columns across all tables?
How is the sequence value landing in the id column?
In most cases it is by using "nextval(seq_name)" in the SQL statement. But sometimes the sequence value is taken first, and then multiple inserts are sent with fixed increasing values. (Sometimes records reference each other, such records are inserted within a short transaction with deferred foreign key constraints.)
It might be possible to "select nextval" 1M times per second from an Pl/SQL function that does nothing else in an infinite loop. But that would not write too much data on the disk.
On an old laptop:
create table insert_test(id int);
insert into insert_test select val from generate_series(1, 1000000) as t(val); INSERT 0 1000000 Time: 943.918 ms
I would say there is more then just the id being inserted, unless all the other fields allow NULL.
And there is nothing in the logs in that time period besides "checkpoints are happening too frequently"?
The "set transaction" warning is due to a bug in an application that calls SET TRANSACTION before starting a transaction.
And what is that app doing when it does SET TRANSACTION?
It is the main app that is using the database, using jdbc. Multiple instances were running when the sequence jumped.
How are the snapshots being taken?
zfs snapshot is taken on the standby, then zfs clone is created on the snapshot, and a new postgresql instance is started on top of the clone. It recovers within one minute. In a very few cases (two or three times in a year), it fails to recover. Then we use a different snapshot. When the dev instance starts up, then we immediately delete everything from repmgr nodes, and disable repmgr completely. Today we noticed that the dev db was created in a network that made it possible to connect to the prod primary. (dev db's network was not separated from the prod db's network, fixed today). The dev db might have connected to the prod/primary after startup. But that dev instance was created 7 days ago, so probably it is not related.
What I know about ZFS would fit in the navel of flea, so someone else will have to comment on this.
Dev connected to prod/primary how?
When the dev db starts up (from the cloned data directory), it still has the old repmgr conf. That config is deleted, and repmgr is disabled right after the startup, but there is the possibility that the dev db has connected the primary when it was cloned (7 days ago), because at the beginning of startup, it is the exact clone of the standby from a previous point of time.
