Postgresql – There is a lot of Client Locks and IPC Locks in PostgreSQL

Streaming replication fires the triggers on the master only. The slaves never see the actual table writes, but rather re-run the recovery logs from the master. Because this is essentially a recovery situation, it means that the binary tables are guaranteed to be brought up to a consistent level. No triggers are necessary. Triggers may be possible with Slony or Bucardo but I am not 100% sure. My initial thought suggests these should allow triggers in downstream databases but you don;t want to run the same triggers on both databases!

In current releases you can't cascading replication outside of Slony. In 9.2 streaming replication will allow for cascading.

Hope this helps.

I seemed to have partially figured out the first answer of my question and I have also found a solution that has reduced the lag time altogether however it may pose later issues down the road.

First how I was able to decipher some meaningful debugging:

• /bin/sh -c /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf -d -n Pass the -d parameter for debugging.

• edit pgpool conf to show the following:

  log_connections = on
  log_hostname = on
  log_statement = on
  log_per_node_statement = on
  

• restart pgpool and run tcpdump to grep the particular incoming client connection

  tcpdump -i eth1 | grep '[clientname] * > [pgpoolhosname]'

• in a separate window grep the pgpool logs for LOG and any other useful information:

  tail -f /var/log/pgpool/pgpool.log | grep "LOG\|error\|anythingelseuseful"

I was able to determine that a TCP connection was being established but pgpool was not responsive with anything until several seconds later. This leads me to believe that there was an issue with the idle connection accepting new requests.

In pgpool the number of idle connection is specified with:

num_init_children = 20
                               # Number of pools
                               # (change requires restart)
max_pool = 4
                               # Number of connections per pool
                               # (change requires restart)

I simply changed num_init_children to 100 and max_pool to 2

This may however introduce different issues down the road as there are now up to 200 idle processes running on the server at once. It seems that messing around with the settings in the pool connection seem to resolve the lag. More information about these variables can be found here:

http://www.pgpool.net/mediawiki/index.php/Relationship_between_max_pool,_num_init_children,_and_max_connectionshttp://www.pgpool.net/mediawiki/index.php/Relationship_between_max_pool,_num_init_children,_and_max_connections