Database objects missed in standby

Ok, this is a stupid one of mine. I just did not qualify the object in the create statements with schema name etc. so they were created in the default locations where I was looking for them in the freshly database and schema.

Streaming replication problem: Master makes committed change which standby follows but master gets locked

Problem

Streaming replication works somewhat in the sense that a transaction in master gets locked after commit but the changes can be seen in standby.

Cause

Master cannot associate success of replication with standby. I do not now why this is needed anyway, why it is not sufficient to know that there are as many success stories.

Solution

resolve.config (standby) contains the variable primary_conninfo which needs to contain the parameter application_name. Its associated value must show up in the list of masters postgres.config synchronous_standby_names.

primary_conninfo = 'application_name=main2 ...'
synchronous_standby_names = main2,main3,main4

Streaming replication FATAL: database system identifier differs between the primary and standby

Cause (in my case)

I tried to set-up replication without making standby a clone of master.

Solution

Take a base backup with pg_basebackup and make standby use it. For freshly initialised databases copying the generated folder and file structure into the data directory of the standby was sufficient.

Streaming replication problem: connection from repuser@standby fails

Apparently, libpq is a bit picky with passwords provided in connection strings. If no special characters are present, it works like a charm. No matter what password parameter of primary_conninfo is expected to get the plain text password.

Working config for synchronous stream replication with both nodes on same host

== 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>'