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
begin:vcard
fn:Thiemo Kellner
n:Kellner;Thiemo
adr:;;Landstr. 34;Weilheim-Bannholz;BW;79809;Deutschland
email;internet:thiemo@xxxxxxxxxxxxxxxxxxxx
tel;work:+49 1578 772 37 37
tel;cell:+41 78 947 36 21
note;quoted-printable:Auf Gelassene Pferde kann man bauen!=0D=0A=
+49 (0)1578-772 37 37 (Mo, Di)=0D=0A=
+41 (0)78 947 36 21 (Mi - Fr)=0D=0A=
sip: thiemo.kellner@xxxxxxxxx=0D=0A=
Skype: thiemo.kellner=0D=0A=
http://www.gelassene-pferde.biz=0D=0A=
Mitglied bei http://www.keep-it-natural.org=0D=0A=
=C3=96ffentlicher PGP-Schl=C3=BCssel: http://pgp.mit.edu/pks/lookup?op=3D=
get&search=3D0x8F70EFD2D972CBEF
x-mozilla-html:FALSE
url:http://www.gelassene-pferde.biz
version:2.1
end:vcard
