Setup a “tombstone” table, to help signal the end of the data replication
In the migration procedure (https://gitlab.com/gitlab-com/migration/blob/master/.gitlab/issue_templates/failover.md), check of replication lag is supposed to be performed using:
sudo gitlab-psql -d gitlabhq_production -c "SELECT now() - pg_last_xact_replay_timestamp();"
There is a problem with this approach – if no activity happens on the master's side, no WAL data is written and this query start to show some delay which is not actually a replication lag (because there is nothing to replicate).
Solution: create a “tombstone” table, write to it before checking the replication lag and get the actual value for the lag using the the same query.
It is better if this table itself is created in a separate Postgres database (inside the same Postgres cluster with the name, say, admin
). The table's possible structure:
create table tombstone (
created_at timestamptz default now() primary key,
note text
);
with usage like this:
insert into tombstone(note) select 'migration dry-run';