Am 22.01.2018 um 07:39 schrieb Thiemo Kellner:
Hi all
I try to set up synchronous streaming replication as try-out. I use my
laptop with Debian 9 and PostgreSQL package 10+189.pgdg90+1. And of
this PostgreSQL installation I have two clusters main (master) and
main2 (hot standby). I tried with Rigg's book and the PostgreSQL
documentation and some pages on the web, but fail miserably.
you have one cluster with 2 nodes ;-)
Master postgresql.conf (possible) differences from stock:
wal_level = replica
archive_mode = off
max_wal_senders = 12
max_replication_slots = 12
synchronous_standby_names = 'main2,main'
Note: it's a bad idea to build a synchronous cluster with only 2 nodes,
you need at least 3 nodes
wal_receiver_timeout = 60s
log_min_messages = debug5
log_connections = on
log_statement = 'ddl'
log_replication_commands = on
lc_messages = 'C.UTF-8'
Master pg_hba.conf (possible) differences from stock:
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
local replication repuser peer
host replication repuser 0.0.0.1/0 md5
host replication repuser ::1/0 md5
Master pg_hba.conf (possible) differences from stock:
that's the recovery.conf, not pg_hba.conf. And you don't need it on the
master.
standby_mode = 'off'
primary_conninfo = 'host=localhost user=repuser port=5433
password=<md5 value of password>'
restore_command = 'false'
why that?
Master pg_hba.conf (possible) differences from stock:
master or standby? confused...
standby_mode = 'on'
primary_conninfo = 'host=localhost user=repuser port=5433
password=<md5 value of password>'
the same port as above?
restore_command = 'false'
why?
I have created repuser on master and equally on hot standby:
postgres=# \du+ repuser
Liste der Rollen
Rollenname | Attribute | Mitglied von | Beschreibung
------------+----------------+--------------+--------------
repuser | Replikation +| {} |
| 2 Verbindungen | |
1) I am not sure whether to put the md5 value of the repuser password
into primary conninfo or the plain one. I don't feel the documentation
or the book is clear on that.
2) Starting the clusters, I do not see any attempt of the hot standby
to connect to the master.
are the 2 nodes running on different ports?
You need only 1 recovery.conf, on the standby. restore_command = 'false'
is useless, i'm guessing that's the reason that the standby doesn't
connect to the master.
And, again, a synchronous replication needs at least 3 nodes. if the
standby doesn't work, the master can't do any write operations, he has
to wait wait for the standby - as you can see ;-)
Greetings from Dresden, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
