postgresql database not replicating. No errors

Question

Background:

I am trying to set up streaming replication between two servers. Although postgresql is running on both boxes without any errors, when I add or change a record from the primary, these changes are not reflected on the secondary server.

I have the following set up on my primary database server:

(Note: I'm using fake ip addresses but 10.222.22.12 represents the primary server and .21 the secondary)

primary server - posgresql.conf

listen_addresses = '10.222.22.12'
unix_socket_directory = '/tmp/postgresql'
wal_level = hot_standby
max_wal_senders = 5             # max number of walsender processes
                                # (change requires restart)
wal_keep_segments = 32          # in logfile segments, 16MB each; 0 disables

primary server - pg_hba.conf

host    all             all             10.222.22.21/32         trust
host    replication     postgres        10.222.22.0/32          trust

primary server - firewall

I've checked to make sure all incoming to the fw is open and that all traffic out is allowed.

secondary server - posgresql.conf

listen_addresses = '10.222.22.21'
wal_level = hot_standby
max_wal_senders = 5             # max number of walsender processes
                                # (change requires restart)
wal_keep_segments = 32          # in logfile segments, 16MB each; 0 disables
hot_standby = on

secondary server - pg_hba.conf

host    all             all             10.222.22.12/32         trust
host    all             all             10.222.22.21/32         trust
host    replication     postgres        10.222.22.0/24          trust

secondary server - recovery.conf

standby_mode='on'
primary_conninfo = 'host=10.222.22.12 port=5432 user=postgres'

secondary server firewall

everything is open here too.

What I've tried so far

Made a change in data on the primary. Nothing replicated over.
Checked the firewall settings on both servers.
Checked the arp table on the secondary box to make sure it can communicate with the primary.
checked the postmaster.log file on both servers. They are empty.
Checked the syslog file on both servers. no errors noticed.
restarted postgresql on both servers to make sure it starts without errors.

I'm not sure what else to check. If you have any suggestions, I'd appreciate it.

EDIT 1

I've checked the pg_stat_replication table on the master and I get the following results:

psql -U postgres testdb -h 10.222.22.12 -c "select * from pg_stat_replication;"

 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | repl
ay_location | sync_priority | sync_state 
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+-----
------------+---------------+------------
(0 rows)

And on the slave, notice the results from the following query:

testdb=# select now()-pg_last_xact_replay_timestamp();
 ?column? 
----------

(1 row)

openser=#

Where you able to resolve this @dot? I'm facing similar issue. — Ani
– Ani, Commented Aug 30, 2017 at 12:20

dot · Accepted Answer · 2014-09-16 12:53:42Z

1

I changed the pg_hba.conf file on the primary and added the exact ip addr of my slave like so:

host    all             all             10.222.22.21/32         trust
host    replication     postgres        10.222.22.0/32          trust
#added the line below
host    replication     postgres        10.222.22.12/32         trust

Then I restarted postgresql and it worked. I guess I was expecting that the line above the new line I added would work, but it's not. I have to do more reading on subnetting.

edited Sep 16, 2014 at 12:53

answered Sep 16, 2014 at 12:41

dot

15.9k45 gold badges133 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jishi Over a year ago

If you want to allow 10.222.22.X, you need to use 10.222.22.0/24 (that's the 24 starting bits in the IP-address as network mask). What you have is that EXACTLY the address 10.222.22.0 need to match (32 bits, which means the complete IP address).

dot Over a year ago

+1 @jishi. Thanks for the explanation. I plan to do more research on subnets but in the mean time, this explanation is great.

Soni Harriz · Accepted Answer · 2014-09-15 23:38:32Z

0

On master, listen address should allow connection from slave, i.e

listen_addresses = '10.222.22.21'

It seems your postgres logging not well configured.

My guess is, the slave cannot stream because it falls behind the master, it can be due to network latency.

My suggestion is, You should archive the wal files, so if the slave falls behind the master, it can replay wal files from the archive.

You can also check by doing

select * from pg_stat_replication;

on master. If it does not show any rows, it means that streaming fails, probably due to slave falls behind the master.

You can also check by issuing :

select now()-pg_last_xact_replay_timestamp();

on slave. The query count how far the slave falls behind the master.

Usually, streaming replication lag is under 1s. Lag larger than 1s, then streaming will be cut off.

answered Sep 15, 2014 at 23:38

Soni Harriz

3,6481 gold badge15 silver badges8 bronze badges

3 Comments

dot Over a year ago

Thank you very much for your response! I do believe the listen_address setting I have on the primary is correct. I actually have two instances of postgresql running for separate databases on the primary server. There a db on localhost (127.0.0.1) and another database on 10.222.22.12. This second instance is what's being replicated over to a slave server.

dot Over a year ago

How can I prove that it's an issue with network latency? Please see Edit 1 in my post for results of the queries you've suggested I run.

Soni Harriz Over a year ago

It seems you didn't configured streaming replication well. To make sure, please check this query on streaming replication slave : select pg_is_in_recovery(); a well configured streaming replication slave will return true. others will return false. You can also check : select pg_is_xlog_replay_paused(); more details on : postgresql.org/docs/current/static/functions-admin.html btw, what is your PG version?

Collectives™ on Stack Overflow

postgresql database not replicating. No errors

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related