Managing PostgreSQL replication slots for HA and Geo

In Geo, we would really like to mandate the use of PostgreSQL replication slots to ensure that the secondary can recover if the primary goes offline for an arbitrary amount of time.

One of the challenges here is to create/add replication slots when a node is added or removed. Ideally, we'd be able to modify this within the application, but the normal gitlab login doesn't have permission to do this:

$ sudo gitlab-rails dbconsole                                                                                                                           
psql (9.6.1)
Type "help" for help.

gitlabhq_production=> select * from pg_replication_slots;
 slot_name | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)

gitlabhq_production=> select * from pg_create_physical_replication_slot('geo_secondary');
ERROR:  must be superuser or replication role to use replication slots

That leaves the options:

1. Manually creating them with the superuser account (e.g. gitlab-psql)
2. Having omnibus add/remove them with the superuser account
3. Having some other process that manages this

With GitLab PostgreSQL HA, have you considered how you might do this?

/cc: @ibaum, @joshlambert, @brodock

marked this issue as related to #2571

@stanhu repmgr is capable of managing these for you if you want to leverage that. Once #2712 (closed) is complete, it should be a matter of setting the following on each node (along with the rest of the repmgr setup).

repmgr['use_replication_slots'] = true
postgresql['max_replication_slots'] = NUMBER_OF_NODES_IN_CLUSTER

This won't require using repmgrd. I've tested automated and manual failover, and it handles creation/deletion of replication slots automatically.

@ibaum Thanks. We currently have this in place for Geo users to set up replication with slots: https://docs.gitlab.com/ee/gitlab-geo/database.html#step-3-initiate-the-replication-process

Does repmgr simplify this?

@stanhu From the user perspective, only in that repmgr handles naming the replication slot automatically. So probably not worth switching over just for that.

But if you'd like to leverage more of the HA functionality, you might consider using the repmgr tools we're building.

Thanks, I think we'll want to look at repmgr when we start to figure out how failover will work with Geo nodes.

/cc: @nick.thomas