Use postgresql logical replication for Geo
Description
Currently, we use "ordinary" postgresql replication to get a read-only copy of the primary's database on the secondary.
We also need to write some data per secondary node, and perform queries that join across both datasets. These get written to a separate, geo-only postgresql process (since the main one is read-only).
Currently, we either use multiple queries in application code, building queries on the read-only replica with the result of queries on the tracking database (huge queries, very slow) or use FDW to
Proposal
We might be able to switch to logical replication: https://www.postgresql.org/docs/10/static/logical-replication.html
This comes with a minimum postgresql version requirement of 10.0, while we're currently standardised on 9.6 for geo. However, it allows a database to be a logical replica of the primary, while also allowing read-write tables (or at least, read-write other database) in the same process. This means we can go back to having a single postgresql database server (reducing omnibus management burden) and so get rid of cross-database manual joins and FDW.
Downsides: schema changes aren't replicated, so we'd need to run migrations on both primary and secondary. We mix together structure and data migrations, so I'm not clear on how this could work at present.
Various other restrictions apply, including truncate
not working: https://www.postgresql.org/docs/10/static/logical-replication-restrictions.html
I don't know if this is feasible or not. If it is, then we might well want to consider it just in terms of reducing maintenance burden in omnibus.
We're also currently seeing that FDW isn't an improvement on the testbed, for unknown reasons: https://gitlab.com/gitlab-org/gitlab-ee/issues/4491 so it's worth thinking about alternatives in case those reasons turn out to be "FDW is unsuitable for our use case".
In any event, the requirement on pg 10 would make this a big jump.