Re: Setting up streaming replication problems

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