Hi allI 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
<<attachment: thiemo.vcf>>
