1

I am trying to setup a primary/standby for failover; however, the standby flips to read/write and no longer functions as a replication database after the copy from the primary. Additionally, the recovery.conf does not turn to recovery.done.

Here are my configuration files:

Master Server Configurations:

pg_hba.conf - added the line:

host replication replication_user 0.0.0.0/0 trust

postgresql.conf

listen_addresses = '*'
max_wal_senders = 10
port = 5432
wal_keep_segments = 500
wal_level = replica

Slave Server Configurations:

postgresql.conf:

hot_standby = on

recovery.conf:

standby_mode = 'on' primary_conninfo = 'user=replication_user password=replication_pass port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any host=pgmaster application_name=node2 connect_timeout=2' recovery_target_timeline = 'latest' primary_slot_name = repmgr_slot_2

The log file shows that the standby is copied properly, but then flips to not be in readonly:

2018-09-21 16:48:09.990 UTC [153] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-09-21 16:48:09.990 UTC [153] LOG:  listening on IPv6 address "::", port 5432
2018-09-21 16:48:10.003 UTC [153] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-09-21 16:48:10.029 UTC [161] LOG:  database system was interrupted; last known up at 2018-09-21 16:48:09 UTC
2018-09-21 16:48:10.214 UTC [161] LOG:  entering standby mode
2018-09-21 16:48:10.223 UTC [161] LOG:  redo starts at 0/2000028
2018-09-21 16:48:10.228 UTC [161] LOG:  consistent recovery state reached at 0/20000F8
2018-09-21 16:48:10.229 UTC [153] LOG:  database system is ready to accept read only connections
2018-09-21 16:48:10.238 UTC [165] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
>>> Cloning is done
>>>>>> WAL id: 000000010000000000000003
>>>>>> WAL_RECEIVER_FLAG=1!
>>> Not in recovery state (anymore)
>>> Waiting for local postgres server start...
>>> Wait schema replication_db.public on pgslave1:5432(user: replication_user,password: *******), will try 9 times with delay 10 seconds (TIMEOUT=90)
>>>>>> Schema replication_db.public exists on host pgslave1:5432!
>>> Unregister the node if it was done before
DELETE 0
>>> Registering node with role standby
INFO: connecting to standby database
INFO: connecting to master database
INFO: retrieving node list for cluster 'pg_cluster'
INFO: registering the standby
[REPMGR EVENT] Node id: 2; Event type: standby_register; Success [1|0]: 1; Time: 2018-09-21 16:48:40.143418+00;  Details:
INFO: standby registration complete
NOTICE: standby node correctly registered for cluster pg_cluster with id 2 (conninfo: user=replication_user password=replication_pass host=pgslave1 dbname=replication_db port=5432 connect_timeout=2)
 Locking standby (NEW_UPSTREAM_NODE_ID=1)...
>>> Starting repmgr daemon...
[2018-09-21 16:48:40] [NOTICE] looking for configuration file in current directory
[2018-09-21 16:48:40] [NOTICE] looking for configuration file in /etc
[2018-09-21 16:48:40] [NOTICE] configuration file found at: /etc/repmgr.conf
[2018-09-21 16:48:40] [INFO] connecting to database 'user=replication_user password=replication_pass host=pgslave1 dbname=replication_db port=5432 connect_timeout=2'
[2018-09-21 16:48:40] [INFO] connected to database, checking its state
[2018-09-21 16:48:40] [INFO] connecting to master node of cluster 'pg_cluster'
[2018-09-21 16:48:40] [INFO] retrieving node list for cluster 'pg_cluster'
[2018-09-21 16:48:40] [INFO] checking role of cluster node '1'
[2018-09-21 16:48:40] [INFO] checking cluster configuration with schema 'repmgr_pg_cluster'
[2018-09-21 16:48:40] [INFO] checking node 2 in cluster 'pg_cluster'
[2018-09-21 16:48:40] [INFO] reloading configuration file
[2018-09-21 16:48:40] [INFO] configuration has not changed
[2018-09-21 16:48:40] [INFO] starting continuous standby node monitoring

I am also using pgpool and I think it looks to be correct:

root@5f05159b998d:/etc# gosu postgres repmgr cluster show
INFO: connecting to database
Role      | Name  | Upstream | Connection String
----------+-------|----------|----------------------------------------------------------------------------------------------------------------
* master  | node1 |          | user=replication_user password=replication_pass host=pgmaster dbname=replication_db port=5432 connect_timeout=2
  standby | node2 | node1    | user=replication_user password=replication_pass host=pgslave1 dbname=replication_db port=5432 connect_timeout=2

and

root@0d95e8dc83f8:/# pcp_attach_node -U pcp_user -h localhost -n 0
Password:
pcp_attach_node -- Command Successful
root@0d95e8dc83f8:/# PGPASSWORD=$CHECK_PASSWORD psql -U $CHECK_USER -h localhost template1 -c "show pool_nodes"
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | pgmaster | 5432 | up     | 0.500000  | primary | 0          | true              | 0
 1       | pgslave1 | 5432 | up     | 0.500000  | standby | 0          | false             | 0
(2 rows)

I think either the original postgresql.conf file is being run when the standby is coming up or it is something in the pg_hba.conf but I cannot figure this out.

I am attempting to build this in docker from the postdock/postgres latest image, just to make it more complicated, but I can overwrite whatever may need to be done.

Any help would be greatly appreciated!

Pete

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.