Обсуждение: Setting up streaming replication problems
Hi all
I 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
Вложения
Am 22.01.2018 um 07:39 schrieb Thiemo Kellner:
> Hi all
>
> I 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.
>
you have one cluster with 2 nodes ;-)
> 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'
Note: it's a bad idea to build a synchronous cluster with only 2 nodes,
you need at least 3 nodes
> 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:
that's the recovery.conf, not pg_hba.conf. And you don't need it on the
master.
> standby_mode = 'off'
> primary_conninfo = 'host=localhost user=repuser port=5433
> password=<md5 value of password>'
> restore_command = 'false'
why that?
>
>
> Master pg_hba.conf (possible) differences from stock:
master or standby? confused...
> standby_mode = 'on'
> primary_conninfo = 'host=localhost user=repuser port=5433
> password=<md5 value of password>'
the same port as above?
>
> restore_command = 'false'
why?
>
>
> 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.
are the 2 nodes running on different ports?
You need only 1 recovery.conf, on the standby. restore_command = 'false'
is useless, i'm guessing that's the reason that the standby doesn't
connect to the master.
And, again, a synchronous replication needs at least 3 nodes. if the
standby doesn't work, the master can't do any write operations, he has
to wait wait for the standby - as you can see ;-)
Greetings from Dresden, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
Andreas, thanks for your reply. >> I 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. > > you have one cluster with 2 nodes ;-) Ähm, right. Been more in a hurry than I thought as it shows below as well. > Note: it's a bad idea to build a synchronous cluster with only 2 nodes, > you need at least 3 nodes I am aware that synchronous clustering might deadlock the master. To have a hot standby on the same metal does not make much sense either. This is just a try out though. The proof of me being stupid, so to speak. ;-) > that's the recovery.conf, not pg_hba.conf. And you don't need it on the > master. Copy paste blunder (see above ;-)) >> standby_mode = 'off' >> primary_conninfo = 'host=localhost user=repuser port=5433 >> password=<md5 value of password>' >> restore_command = 'false' > > why that? Uhm, cannot remember *blush*, I removed it but it made no difference. > master or standby? confused... C&p blunder again. Standby. >> standby_mode = 'on' >> primary_conninfo = 'host=localhost user=repuser port=5433 >> password=<md5 value of password>' > > the same port as above? I keep repeating myself: C&p blunder again: nope >> 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. Anyone two dimes on that? >> 2) Starting the clusters, I do not see any attempt of the hot standby >> to connect to the master. I put logging back to more sane info as debug did not give me any useful information on that (maybe due to my noobdom). But I cannot see any connection attempt... > are the 2 nodes running on different ports? Yes, 5432 master, 5433 standby > You need only 1 recovery.conf, on the standby. But does it hurt? I just had the idea that role change would be easier. master <-> standby > restore_command = 'false' > is useless, i'm guessing that's the reason that the standby doesn't > connect to the master. Uhm, is it useless or does it prevent connection? Anyway, I removed it and it did not make a difference. > Greetings from Dresden, Andreas :-) Greetings from Bannholz@Hochrhein :-D -- Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote: > 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. > > Anyone two dimes on that? Password, not an md5. Or, at least, that's what's been working for me :) Even better, I guess, would be to not put a password in there at all and use a .pgpass. -- Martin Goodson "Have you thought up some clever plan, Doctor?" "Yes, Jamie, I believe I have." "What're you going to do?" "Bung a rock at it."
On Jan 23, 2018 11:34 PM, "Martin Goodson" <kaemaril@googlemail.com> wrote:
Password, not an md5. Or, at least, that's what's been working for me :)
On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote:1) I am not sure whether to put the md5 value of the repuser passwordinto primary conninfo or the plain one. I don't feel the documentation
or the book is clear on that.
Anyone two dimes on that?
Even better, I guess, would be to not put a password in there at all and use a .pgpass.
--
Martin Goodson
"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."
Hello,
Instead you can keep trust connection for that user from only that ip instead specifying in .Pgpass file.
Anything can be done as per yourconvenience.
Regards,
Pavan
On 23/01/2018 18:08, Pavan Teja wrote:
M.
Absolutely you can do that, yup. But I'm super paranoid, and don't trust anyone enough to use 'trust' :)On Jan 23, 2018 11:34 PM, "Martin Goodson" <kaemaril@googlemail.com> wrote:Password, not an md5. Or, at least, that's what's been working for me :)
On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote:1) I am not sure whether to put the md5 value of the repuser passwordinto primary conninfo or the plain one. I don't feel the documentation
or the book is clear on that.
Anyone two dimes on that?
Even better, I guess, would be to not put a password in there at all and use a .pgpass.
--
Martin Goodson
"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."
Hello,Instead you can keep trust connection for that user from only that ip instead specifying in .Pgpass file.
M.
-- Martin Goodson "Have you thought up some clever plan, Doctor?" "Yes, Jamie, I believe I have." "What're you going to do?" "Bung a rock at it."
Do you mind me bumping the issue? Zitat von "Thiemo Kellner, NHC Barhufpflege" <thiemo.kellner@gelassene-pferde.biz>: > Andreas, thanks for your reply. > >>> I 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. >> >> you have one cluster with 2 nodes ;-) > > Ähm, right. Been more in a hurry than I thought as it shows below as well. > >> Note: it's a bad idea to build a synchronous cluster with only 2 nodes, >> you need at least 3 nodes > > I am aware that synchronous clustering might deadlock the master. To > have a hot standby on the same metal does not make much sense > either. This is just a try out though. The proof of me being stupid, > so to speak. ;-) > >> that's the recovery.conf, not pg_hba.conf. And you don't need it on the >> master. > > Copy paste blunder (see above ;-)) > >>> standby_mode = 'off' >>> primary_conninfo = 'host=localhost user=repuser port=5433 >>> password=<md5 value of password>' >>> restore_command = 'false' >> >> why that? > > Uhm, cannot remember *blush*, I removed it but it made no difference. > >> master or standby? confused... > > C&p blunder again. Standby. > >>> standby_mode = 'on' >>> primary_conninfo = 'host=localhost user=repuser port=5433 >>> password=<md5 value of password>' >> >> the same port as above? > > I keep repeating myself: C&p blunder again: nope > >>> 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. > > Anyone two dimes on that? > >>> 2) Starting the clusters, I do not see any attempt of the hot standby >>> to connect to the master. > > I put logging back to more sane info as debug did not give me any > useful information on that (maybe due to my noobdom). But I cannot > see any connection attempt... > >> are the 2 nodes running on different ports? > > Yes, 5432 master, 5433 standby > >> You need only 1 recovery.conf, on the standby. > > But does it hurt? I just had the idea that role change would be > easier. master <-> standby > >> restore_command = 'false' >> is useless, i'm guessing that's the reason that the standby doesn't >> connect to the master. > > Uhm, is it useless or does it prevent connection? Anyway, I removed > it and it did not make a difference. > >> Greetings from Dresden, Andreas > > :-) Greetings from Bannholz@Hochrhein :-D > > -- > Öffentlicher PGP-Schlüssel: > http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF > > ---------------------------------------------------------------- > This message was sent using IMP, the Internet Messaging Program. > > -- +49 (0)1578-772 37 37 +41 (0)78 947 36 21 Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
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
Вложения
On 28/01/2018 22: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. > 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. The documentation seems pretty clear on the subject, if you look at the standby server settings documentation and look at primary_conninfo it says: "A password needs to be provided too, if the primary demands password authentication. It can be provided in the primary_conninfo string, or in a separate ~/.pgpass file on the standby server (use replication as the database name)" That's not an md5 version of the password, that's the actual clear text password. That's why putting the password into the password file instead of the recovery file (or specifying trust if you actually do) might not be such a bad idea, really. But are you sure the password is the issue? What messages are you seeing in your logs on both the master and the standby? Could you post the logs here, or at least a representative sample? What are you seeing in the standby logs when you start it? What are you seeing in the master's logs at the same time? M. -- Martin Goodson "Have you thought up some clever plan, Doctor?" "Yes, Jamie, I believe I have." "What're you going to do?" "Bung a rock at it."
Thanks for your patience. On 01/29/18 00:11, Martin Goodson wrote: > "A password needs to be provided too, if the primary demands password > authentication. It can be provided in the primary_conninfo string, or in > a separate ~/.pgpass file on the standby server (use replication as the > database name)" I tried plain text. > But are you sure the password is the issue? What messages are you seeing > in your logs on both the master and the standby? Could you post the logs > here, or at least a representative sample? What are you seeing in the > standby logs when you start it? What are you seeing in the master's logs > at the same time? No, I am not. == Master log of start gives me == 2018-01-29 05:55:39.996 CET [1307] DEBUG: registering background worker "logical replication launcher" 2018-01-29 05:55:39.996 CET [1307] LOG: listening on IPv6 address "::1", port 5432 2018-01-29 05:55:39.996 CET [1307] LOG: listening on IPv4 address "127.0.0.1", port 5432 2018-01-29 05:55:40.027 CET [1307] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2018-01-29 05:55:40.061 CET [1307] DEBUG: mmap(148897792) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory 2018-01-29 05:55:40.153 CET [1308] LOG: database system was shut down at 2018-01-29 05:50:37 CET 2018-01-29 05:55:40.154 CET [1308] DEBUG: checkpoint record is at 0/1649758 2018-01-29 05:55:40.155 CET [1308] DEBUG: redo record is at 0/1649758; shutdown TRUE 2018-01-29 05:55:40.155 CET [1308] DEBUG: next transaction ID: 0:583; next OID: 16398 2018-01-29 05:55:40.156 CET [1308] DEBUG: next MultiXactId: 1; next MultiXactOffset: 0 2018-01-29 05:55:40.156 CET [1308] DEBUG: oldest unfrozen transaction ID: 548, in database 1 2018-01-29 05:55:40.156 CET [1308] DEBUG: oldest MultiXactId: 1, in database 1 2018-01-29 05:55:40.156 CET [1308] DEBUG: commit timestamp Xid oldest/newest: 0/0 2018-01-29 05:55:40.156 CET [1308] DEBUG: transaction ID wrap limit is 2147484195, limited by database with OID 1 2018-01-29 05:55:40.156 CET [1308] DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 1 2018-01-29 05:55:40.156 CET [1308] DEBUG: starting up replication slots 2018-01-29 05:55:40.158 CET [1308] DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 1 2018-01-29 05:55:40.158 CET [1308] DEBUG: MultiXact member stop limit is now 4294914944 based on MultiXact 1 2018-01-29 05:55:40.212 CET [1312] DEBUG: autovacuum launcher started 2018-01-29 05:55:40.213 CET [1307] DEBUG: starting background worker process "logical replication launcher" 2018-01-29 05:55:40.216 CET [1307] LOG: database system is ready to accept connections 2018-01-29 05:55:40.217 CET [1314] DEBUG: logical replication launcher started 2018-01-29 05:55:40.822 CET [1315] [unknown]@[unknown] LOG: connection received: host=[local] 2018-01-29 05:55:40.822 CET [1315] [unknown]@[unknown] LOG: incomplete startup packet 2018-01-29 05:55:41.344 CET [1318] [unknown]@[unknown] LOG: connection received: host=[local] 2018-01-29 05:55:41.349 CET [1318] postgres@postgres LOG: connection authorized: user=postgres database=postgres 2018-01-29 05:55:41.900 CET [1321] [unknown]@[unknown] LOG: connection received: host=[local] 2018-01-29 05:55:41.905 CET [1321] postgres@postgres LOG: connection authorized: user=postgres database=postgres 2018-01-29 05:55:42.440 CET [1324] [unknown]@[unknown] LOG: connection received: host=[local] 2018-01-29 05:55:42.447 CET [1324] postgres@postgres LOG: connection authorized: user=postgres database=postgres == Standby log gives me == 2018-01-28 05:55:32.703 CET [5214] DEBUG: autovacuum: processing database "postgres" 2018-01-29 05:55:47.724 CET [1333] DEBUG: registering background worker "logical replication launcher" 2018-01-29 05:55:47.724 CET [1333] LOG: listening on IPv6 address "::1", port 5433 2018-01-29 05:55:47.725 CET [1333] LOG: listening on IPv4 address "127.0.0.1", port 5433 2018-01-29 05:55:47.759 CET [1333] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433" 2018-01-29 05:55:47.793 CET [1333] DEBUG: mmap(148897792) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory 2018-01-29 05:55:47.887 CET [1334] LOG: database system was shut down at 2018-01-29 05:50:37 CET 2018-01-29 05:55:47.887 CET [1334] DEBUG: checkpoint record is at 0/1636408 2018-01-29 05:55:47.889 CET [1334] DEBUG: redo record is at 0/1636408; shutdown TRUE 2018-01-29 05:55:47.889 CET [1334] DEBUG: next transaction ID: 0:556; next OID: 16385 2018-01-29 05:55:47.889 CET [1334] DEBUG: next MultiXactId: 1; next MultiXactOffset: 0 2018-01-29 05:55:47.889 CET [1334] DEBUG: oldest unfrozen transaction ID: 548, in database 1 2018-01-29 05:55:47.889 CET [1334] DEBUG: oldest MultiXactId: 1, in database 1 2018-01-29 05:55:47.889 CET [1334] DEBUG: commit timestamp Xid oldest/newest: 0/0 2018-01-29 05:55:47.889 CET [1334] DEBUG: transaction ID wrap limit is 2147484195, limited by database with OID 1 2018-01-29 05:55:47.889 CET [1334] DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 1 2018-01-29 05:55:47.889 CET [1334] DEBUG: starting up replication slots 2018-01-29 05:55:47.890 CET [1334] DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 1 2018-01-29 05:55:47.890 CET [1334] DEBUG: MultiXact member stop limit is now 4294914944 based on MultiXact 1 2018-01-29 05:55:47.943 CET [1338] DEBUG: autovacuum launcher started 2018-01-29 05:55:47.944 CET [1333] DEBUG: starting background worker process "logical replication launcher" 2018-01-29 05:55:47.945 CET [1333] LOG: database system is ready to accept connections 2018-01-29 05:55:47.945 CET [1340] DEBUG: logical replication launcher started 2018-01-29 05:55:48.470 CET [1341] [unknown]@[unknown] LOG: connection received: host=[local] 2018-01-29 05:55:48.471 CET [1341] [unknown]@[unknown] LOG: incomplete startup packet 2018-01-29 05:55:48.994 CET [1344] [unknown]@[unknown] LOG: connection received: host=[local] 2018-01-29 05:55:48.999 CET [1344] postgres@postgres LOG: connection authorized: user=postgres database=postgres 2018-01-29 05:55:49.545 CET [1347] [unknown]@[unknown] LOG: connection received: host=[local] 2018-01-29 05:55:49.551 CET [1347] postgres@postgres LOG: connection authorized: user=postgres database=postgres 2018-01-29 05:55:50.088 CET [1350] [unknown]@[unknown] LOG: connection received: host=[local] 2018-01-29 05:55:50.093 CET [1350] postgres@postgres LOG: connection authorized: user=postgres database=postgres Chhers Thiemo -- +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
Вложения
Hi, Am 29.01.2018 um 06:03 schrieb Thiemo Kellner: > Thanks for your patience. you are welcome. From the other mail (in german) i think i know the issue now: you have placed the recovery.conf NOT in the data_directoy but in /etc/... PostgreSQL is looking for the recovery.conf in the data_directory. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
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
Вложения
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
Вложения
On 01/31/18 13:26, Thiemo Kellner wrote: > 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 Addendum: The error shows up in the standby log. -- +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