Re: [solved] Setting up streaming replication problems

Oh, I forgot again already. *shame on me*

I tried to set up streaming replication without cloning standby from the
master by pg_basebackup. This lead to the error of unequal identifier
error. Maybe I was not reading carefully enough, however, I was not sure
what to do with the created files and directories. I figured I just
copied it into the data directory of the standby and it worked. No
guarantee that this was the correct process and would work for
none-empty master databases.

On 01/31/18 13:21, Thiemo Kellner wrote:
> 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