Test setup of repmgr in staging for HA postgres
As a trial by fire, lets try out the steps here: https://gitlab.com/gitlab-org/omnibus-gitlab/issues/1807#note_31216296 on staging to get a first impression of what HA postgres could look like in the future. This is a WIP, so we will be doing it manually (as discussed with @pcarranza, @ibaum and @marin) just so we can get a feeling for our solution, its limitations and possible effort estimations.
Activity
added availability database experiment labels
Issues seen in my instructions so far:
- The attributes for the standby servers should contain all the attributes that are needed. Currently, it just contains the bare minimum that postgresql will need to be able to communicate with the primary. But a
gitlab-ctl reconfigurewould blow that away and break the standby. - The build is supposed to symlink the repmgr binaries from the postgresql install directory, to
/opt/gitlab/embedded/bin, but this didn't appear to work. - The repmgr command for registering the master generates a warning about
transaction already in progress. This is only a warning, but it gives no other indication that the command worked. So the documentation should warn about this.
- The attributes for the standby servers should contain all the attributes that are needed. Currently, it just contains the bare minimum that postgresql will need to be able to communicate with the primary. But a
We were able to successfully add the 2 nodes via a complete sync and the thrid node by adapting its pg_hba.conf and otherwise treating it as we did the others.
Staging PRIMARY-DB root@db3.staging.gitlab.com:~# export PAGER=less;gitlab-psql -d repmgr could not change directory to "/root": Permission denied psql (9.6.1) Type "help" for help. repmgr=# select * from repmgr_gitlab_cluster.repl_nodes; id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active ----+---------+------------------+----------------+-------+-------------------------------------------+-----------+----------+-------- 1 | master | | gitlab_cluster | node1 | host=10.123.2.4 user=repmgr dbname=repmgr | | 100 | t 2 | standby | 1 | gitlab_cluster | node2 | host=10.123.2.6 user=repmgr dbname=repmgr | | 100 | t 3 | standby | 1 | gitlab_cluster | node3 | host=10.123.2.7 user=repmgr dbname=repmgr | | 100 | t 4 | standby | 1 | gitlab_cluster | node4 | host=10.123.2.5 user=repmgr dbname=repmgr | | 100 | t (4 rows) repmgr=# select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication; client_addr | state | sent_location | write_location | flush_location | replay_location -------------+-----------+---------------+----------------+----------------+----------------- 10.123.2.7 | streaming | 343/B8009D28 | 343/B8009D28 | 343/B8009D28 | 343/B8009D28 10.123.2.5 | streaming | 343/B8009D28 | 343/B8009D28 | 343/B8009D28 | 343/B8009D28 10.123.2.6 | streaming | 343/B8009D28 | 343/B8009D28 | 343/B8009D28 | 343/B8009D28 (3 rows)Rather than testing our failover scenario now, we will try it out tomorrow in the AM.
- stop database on primary
- promote a secondary
- tell all 3 nodes to follow new primary
- reconfigure application
Edited by Jason Tevnanwe followed the above scenario but ran into some issues:
- db stop was not an issue
- promoting the secondary worked as well
Staging secondary-db root@db4.staging.gitlab.com:~# su - gitlab-psql -c '/opt/gitlab/embedded/bin/repmgr -f /etc/gitlab/repmgr.conf standby promote' ERROR: connection to database failed: could not connect to server: Connection refused Is the server running on host "10.123.2.4" and accepting TCP/IP connections on port 5432? NOTICE: promoting standby NOTICE: promoting server using '/opt/gitlab/embedded/bin/pg_ctl -D /var/opt/gitlab/postgresql/data promote' server promoting NOTICE: STANDBY PROMOTE successful- when telling the secondaries to follow we had some issues:
- the initial follow command:
su - gitlab-psql -c '/opt/gitlab/embedded/bin/repmgr -h 10.123.2.5 -U repmgr -d repmgr -D /var/opt/gitlab/postgresql/data/ -f /etc/gitlab/repmgr.conf standby follow' NOTICE: restarting server using '/opt/gitlab/embedded/bin/pg_ctl -w -D /var/opt/gitlab/postgresql/data/ -m fast restart' waiting for server to shut down............................................................... failed pg_ctl: server does not shut down ERROR: unable to restart serverfailed while trying to shut the db down.
we manually shutdown the db and executed the same command, which worked fine:
Staging PRIMARY-DB root@db3.staging.gitlab.com:~# su - gitlab-psql -c '/opt/gitlab/embedded/bin/repmgr -h 10.123.2.5 -U repmgr -d repmgr -D /var/opt/gitlab/postgresql/data/ -f /etc/gitlab/repmgr.conf standby follow' NOTICE: restarting server using '/opt/gitlab/embedded/bin/pg_ctl -w -D /var/opt/gitlab/postgresql/data/ -m fast restart' pg_ctl: PID file "/var/opt/gitlab/postgresql/data/postmaster.pid" does not exist Is server running? starting server anyway waiting for server to start....LOG: database system was shut down at 2017-06-07 12:49:49 GMT LOG: entering standby mode FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up LOG: consistent recovery state reached at 347/4C0000D0 LOG: invalid record length at 347/4C0000D0: wanted 24, got 0 FATAL: the database system is starting up LOG: database system is ready to accept read only connections LOG: fetching timeline history file for timeline 2 from primary server LOG: started streaming WAL from primary at 347/4C000000 on timeline 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 at 347/4C0000D0. LOG: new target timeline is 2 LOG: restarted WAL streaming at 347/4C000000 on timeline 2 LOG: redo starts at 347/4C0000D0 done server started NOTICE: STANDBY FOLLOW successfulwe could also see this reflected on the master:
all 3 secondaries connected to the correct master
Staging PRIMARY-DB root@db4.staging.gitlab.com:~# gitlab-psql -d repmgr could not change directory to "/root": Permission denied psql (9.6.1) Type "help" for help. repmgr=# select * from repmgr_gitlab_cluster.repl_nodes; id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active ----+---------+------------------+----------------+-------+-------------------------------------------+-----------+----------+-------- 4 | master | | gitlab_cluster | node4 | host=10.123.2.5 user=repmgr dbname=repmgr | | 100 | t 2 | standby | 4 | gitlab_cluster | node2 | host=10.123.2.6 user=repmgr dbname=repmgr | | 100 | t 3 | standby | 4 | gitlab_cluster | node3 | host=10.123.2.7 user=repmgr dbname=repmgr | | 100 | t 1 | standby | 4 | gitlab_cluster | node1 | host=10.123.2.4 user=repmgr dbname=repmgr | | 100 | t (4 rows)and all of them up2date
repmgr=# select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication; client_addr | state | sent_location | write_location | flush_location | replay_location -------------+-----------+---------------+----------------+----------------+----------------- 10.123.2.7 | streaming | 347/56013D40 | 347/56013D40 | 347/56013D40 | 347/56013D40 10.123.2.6 | streaming | 347/56013D40 | 347/56013D40 | 347/56013D40 | 347/56013D40 10.123.2.4 | streaming | 347/56013D40 | 347/56013D40 | 347/56013D40 | 347/56013D40 (3 rows)however we ran into errors popping up in the logs which @ibaum was taking a look at:
ERROR: relation "pg_stat_statements" does not exist at character 502 STATEMENT: SELECT pg_authid.rolname as user, pg_database.datname as database, pg_stat_statements.queryid, left(pg_stat_statements.query,250) as query, pg_stat_statements.calls, pg_stat_statements.total_time as time_milliseconds, pg_stat_statements.rows, pg_stat_statements.shared_blks_hit, pg_stat_statements.shared_blks_read, pg_stat_statements.shared_blks_dirtied, pg_stat_statements.shared_blks_written, pg_stat_statements.temp_blks_read, pg_stat_statements.temp_blks_written FROM pg_stat_statements JOIN pg_authid ON pg_authid.oid = pg_stat_statements.userid JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid ERROR: permission denied for relation pg_stat_replication STATEMENT: SELECT *, pg_current_xlog_location(), pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::float FROM pg_stat_replication ERROR: relation "pg_stat_statements" does not exist at character 502 STATEMENT: SELECT pg_authid.rolname as user, pg_database.datname as database, pg_stat_statements.queryid, left(pg_stat_statements.query,250) as query, pg_stat_statements.calls, pg_stat_statements.total_time as time_milliseconds, pg_stat_statements.rows, pg_stat_statements.shared_blks_hit, pg_stat_statements.shared_blks_read, pg_stat_statements.shared_blks_dirtied, pg_stat_statements.shared_blks_written, pg_stat_statements.temp_blks_read, pg_stat_statements.temp_blks_written FROM pg_stat_statements JOIN pg_authid ON pg_authid.oid = pg_stat_statements.userid JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid ERROR: permission denied for relation pg_stat_replication STATEMENT: SELECT *, pg_current_xlog_location(), pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::float FROM pg_stat_replication ERROR: permission denied for relation pg_stat_replication STATEMENT: SELECT *, pg_current_xlog_location(), pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::float FROM pg_stat_replicationEdited by Jason TevnanERROR: relation "pg_stat_statements" does not exist at character 502This seem to be due to the
pg_stat_statementsmodule not being installed. This is not a module we install in a stock GitLab install. The repmgr docs don't specify that it should be installed. I also don't see any code in repmgr referencing that, so we would need to trace down what actually is trying to see that.ERROR: permission denied for relation pg_stat_replicationOn a test instance, with a bare bones user, or the
repmgruser, I was able to run this query. So I'm still unsure as to what is getting that error.
@ibaum we do have it installed in production, maybe this is coming from staging being a production replica.
@ibaum i dont see those log errors anymore btw.
also logging in AS repmgr and executing the queries, just returns an empty set, no error.
Maybe we can try it out on another box again but i see no problems currently.
We would like to failover again, to verify that those errors are transient, after which we can close this particular issue.
Next step would be to test the ability of repmgrd (the automated failover mechanism) but since @ibaum is out till monday, I am working on some other issues.
For reference, the error
relation "pg_stat_statements" does not exist at character 502was due to gitlab-org/omnibus-gitlab#2468 (closed)mentioned in issue gitlab-org/omnibus-gitlab#1807 (closed)
mentioned in issue gitlab-org/omnibus-gitlab#2477 (closed)
mentioned in issue #2020 (closed)
-
@pcarranza It is noted starting with https://gitlab.com/gitlab-org/omnibus-gitlab/issues/1807#note_32257419 .
-
@marin that's from the Build perspective, but I assume we can do something from the infrastructure perspective to get us to a better place, or I may be wrong and we need to wait on build
I'm just trying to clarify that
after this issue we tested the automated failover: https://gitlab.com/gitlab-com/infrastructure/issues/2020 and are now in a bit of a holding pattern until this makes it into the package.
We can take a look at the pgbouncer+repmgrd which @ibaum mentioned: https://github.com/2ndQuadrant/repmgr/blob/master/docs/repmgrd-node-fencing.md and start preparing that side of things.
