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
begin:vcard
fn:Thiemo Kellner
n:Kellner;Thiemo
adr:;;Landstr. 34;Weilheim-Bannholz;BW;79809;Deutschland
email;internet:thiemo@xxxxxxxxxxxxxxxxxxxx
tel;work:+49 1578 772 37 37
tel;cell:+41 78 947 36 21
note;quoted-printable:Auf Gelassene Pferde kann man bauen!=0D=0A=
+49 (0)1578-772 37 37 (Mo, Di)=0D=0A=
+41 (0)78 947 36 21 (Mi - Fr)=0D=0A=
sip: thiemo.kellner@xxxxxxxxx=0D=0A=
Skype: thiemo.kellner=0D=0A=
http://www.gelassene-pferde.biz=0D=0A=
Mitglied bei http://www.keep-it-natural.org=0D=0A=
=C3=96ffentlicher PGP-Schl=C3=BCssel: http://pgp.mit.edu/pks/lookup?op=3D=
get&search=3D0x8F70EFD2D972CBEF
x-mozilla-html:FALSE
url:http://www.gelassene-pferde.biz
version:2.1
end:vcard
