Setting up streaming replication problems

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&amp;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:

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."

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 your​convenience.

Regards,
Pavan

On 23/01/2018 18:08, Pavan Teja wrote:

On Jan 23, 2018 11:34 PM, "Martin Goodson" <kaemaril@googlemail.com
<mailto:kaemaril@googlemail.com>> wrote:

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."

Hello,

Instead you can keep trust connection for that user from only that ip
instead specifying in .Pgpass file.

Absolutely you can do that, yup. But I'm super paranoid, and don't trust
anyone enough to use 'trust' :)

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&amp;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&amp;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&amp;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&amp;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