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

Andreas Kretschmer and others of the german mailing list put me on the 
right track again. My working config changes from standard is as listed 
below. My problem was, that application_name in primary_conninfo of the 
standby was missing. This lead the master not to recognise standby 
having taken over the changes. Finally, I had forgotten that that my 
test was somewhat faulty in the sense that the test schema and table 
where created in the default db and schema so that I, looking in the 
test database, were unable to find them on standby.

Thanks all for the patience with and help for me!

== Hot standby ==

/etc/postgresql/10/main2/pg_hba.conf
host    replication     all             ::1/128                 md5
host    replication     all             127.0.0.1/32            md5
host    replication     repuser         ::1/0                   md5
host    replication     repuser         0.0.0.1/0               md5
local   replication     repuser                                 peer

/etc/postgresql/10/main2/postgresql.conf
wal_level = replica
#synchronous_commit = on
max_replication_slots = 12
synchronous_standby_names = 'main'
hot_standby = on
log_min_messages = warning
log_connections = on
log_statement = 'ddl'
log_replication_commands = on
lc_messages = 'C.UTF-8'

/etc/postgresql/10/main2/recovery.conf
standby_mode = 'on'
primary_conninfo = 'application_name=main2 host=localhost user=repuser 
port=5432 password=<plain text>'

== master ==
/etc/postgresql/10/main/pg_hba.conf
host    replication     all             ::1/128                 md5
host    replication     all             127.0.0.1/32            md5
host    replication     repuser         ::1/0                   md5
host    replication     repuser         0.0.0.1/0               md5
local   replication     repuser                                 peer

/etc/postgresql/10/main/postgresql.conf
wal_level = replica
#synchronous_commit = on
archive_mode = off
max_wal_senders = 12
max_replication_slots = 12
synchronous_standby_names = 'main2'
hot_standby = on
wal_receiver_timeout = 60s
log_min_messages = warning
log_connections = on
log_statement = 'ddl'
log_replication_commands = on
lc_messages = 'C.UTF-8'

/etc/postgresql/10/main/recovery.conf
standby_mode = 'off'
primary_conninfo = 'application_name=main host=localhost user=repuser 
port=5433 password=<plain text>'


On 01/28/18 23:24, Thiemo Kellner wrote:
> Me again. Hope you wont feel to bothered by me. I just summarise so far 
> and am
> still in dire need of guidance.
> 
> Debian 9 with PostreSQL 10. I have two nodes in the cluster I use as 
> master and
> as standby. I tried to setup replication with Rigg's book and the official
> documentation and a couple of web pages.
> 
> I am aware that there is danger of dead lock with synchronous 
> replication with
> only two host as well there is no point in having replicated servers on 
> the same
> metal as the master - but in trying to figure out how to setup as I am 
> trying to
> do - unless replication within a cluster does not work anyway.
> 
> 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. I thought to have tried both ways to no avail.
> 
> I could not find a hint in the logs, that standby tried to connect to 
> master.
> 
> Find below my configs
> 
> Cheers Thiemo
> 
> == Hot standby ==
> 
> /etc/postgresql/10/main2/pg_hba.conf
> 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
> 
> /etc/postgresql/10/main2/postgresql.conf
> wal_level = replica
> max_replication_slots = 12
> synchronous_standby_names = 'main,main2'
> hot_standby = on
> log_min_messages = debug1
> log_connections = on
> log_statement = 'ddl'
> log_replication_commands = on
> lc_messages = 'C.UTF-8'
> 
> /etc/postgresql/10/main2/recovery.conf
> standby_mode = 'on'
> primary_conninfo = 'host=localhost user=repuser port=5432 password=<md5 
> value or
> plain text?>'
> 
> == master ==
> /etc/postgresql/10/main/pg_hba.conf
> 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
> 
> /etc/postgresql/10/main/postgresql.conf
> wal_level = replica
> archive_mode = off
> max_wal_senders = 12
> max_replication_slots = 12
> synchronous_standby_names = 'main2,main'
> hot_standby = on
> wal_receiver_timeout = 60s
> log_min_messages = debug1
> log_connections = on
> log_statement = 'ddl'
> log_replication_commands = on
> lc_messages = 'C.UTF-8'
> 
> /etc/postgresql/10/main/recovery.conf
> standby_mode = 'off'
> primary_conninfo = 'host=localhost user=repuser port=5433 password=<md5 
> value or
> plain text?>'
> 
> -- Öffentlicher PGP-Schlüssel:
> http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC

-- 
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
SIP/iptel.org: thiemo.kellner
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC