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 amstill 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 officialdocumentation 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 todo - unless replication within a cluster does not work anyway. I am not sure whether to put the md5 value of the repuser password intoprimary_conninfo or the plain one. I don't feel the documentation or the book isclear 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 orplain 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 orplain 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
<<attachment: thiemo.vcf>>
