Re: Setting up streaming replication problems - Mailing list pgsql-general

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