Re: [solved] Setting up streaming replication problems - Mailing list pgsql-general
| From | Thiemo Kellner |
|---|---|
| Subject | Re: [solved] Setting up streaming replication problems |
| Date | |
| Msg-id | 8a664d37-8b0f-da64-7631-80196cca679c@gelassene-pferde.biz Whole thread Raw |
| In response to | Re: [solved] Setting up streaming replication problems (Thiemo Kellner <thiemo@gelassene-pferde.biz>) |
| Responses |
Re: [solved] Setting up streaming replication problems
|
| List | pgsql-general |
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
Attachment
pgsql-general by date: