Setting up streaming replication problems - Mailing list pgsql-general

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.

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'
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:
standby_mode = 'off'
primary_conninfo = 'host=localhost user=repuser port=5433 password=<md5 
value of password>'
restore_command = 'false'


Hot standby postgresql.conf (possible) differences from stock:
wal_level = replica
max_wal_senders = 12
max_replication_slots = 12
synchronous_standby_names = 'main,main2'
wal_receiver_timeout = 60s
log_min_messages = debug5
log_connections = on
log_statement = 'ddl'
log_replication_commands = on
lc_messages = 'C.UTF-8'

Hot standby 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:
standby_mode = 'on'
primary_conninfo = 'host=localhost user=repuser port=5433 password=<md5 
value of password>'
restore_command = 'false'

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.

3) Executing 'create database test;' on the master gets stuck. After 
cancelling (ctrl-c) I have got the message:
psql:/home/thiemo/external_projects/act/test.pg_sql:1: WARNING: 
canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not 
have been replicated to the standby.
CREATE DATABASE
test exists now on master but not on hot standby.
                               Liste der Datenbanken
  Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp  | 
Zugriffsprivilegien
------+------------+-----------+--------------+-------------+---------------------
  test | postgres   | UTF8      | de_DE.UTF-8  | de_DE.UTF-8 |
(1 Zeile)

postgres=# \l test
                               Liste der Datenbanken
  Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | 
Zugriffsprivilegien
------+------------+-----------+--------------+------------+---------------------
(0 Zeilen)


Where did I go wrong? Any hint would be appreciated.

Kind regards Thiemo

-- 
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC