9.1 Streaming Replication

I believe I have successfully setup streaming replication but I have a couple of nagging questions.

If I reboot one of the streaming targets, I see that it syncs right back up with the primary and I see new data appear in the read only copy.

I distribute wal segments to the targets using rsync. One target is in the same rack as the primary, the other is a couple of states aways. That seems to be working just fine. What I can't seem to figure out is what they are used for? The target servers seem to connect to the primary and sync from that, not from the wal logs. Are they there in case of an extended disconnect?

Also, when my target server comes up it always seems to complain that it can't find a wal archive. It's always the one that is currently be written on the primary, that hasn't been archived yet. Is that normal?

For example, I cleared out the log file on the remote target and rebooted it. This is what it logged at startup:

tnelson(at)nj-dr1:~$ cat /var/log/postgresql/postgresql-9.1-main.log
2012-12-13 00:14:19 EST LOG: database system was shut down in recovery at 2012-12-13 00:11:51 EST
2012-12-13 00:14:20 EST LOG: entering standby mode
cp: cannot stat `/var/lib/postgresql/wal_archive/000000010000000100000098': No such file or directory
2012-12-13 00:14:20 EST LOG: redo starts at 1/98524320
2012-12-13 00:14:20 EST LOG: consistent recovery state reached at 1/9870DA38
2012-12-13 00:14:20 EST LOG: invalid magic number 0000 in log file 1, segment 152, offset 7397376
2012-12-13 00:14:20 EST LOG: database system is ready to accept read only connections
cp: cannot stat `/var/lib/postgresql/wal_archive/000000010000000100000098': No such file or directory
2012-12-13 00:14:22 EST FATAL: could not connect to the primary server: could not connect to server: No route to host
Is the server running on host "192.168.39.41" and accepting
TCP/IP connections on port 5432?

cp: cannot stat `/var/lib/postgresql/wal_archive/000000010000000100000098': No such file or directory
cp: cannot stat `/var/lib/postgresql/wal_archive/000000010000000100000098': No such file or directory
2012-12-13 00:14:25 EST LOG: streaming replication successfully connected to primary

The log file 0098 is currently being written on the primary server. When it finishes, my archive command will copy it to a local directory on the primary and the use rsync to distribute it to my 2 target servers.

The TCP connection error I hadn't seen before, but it seems like that was transient:

On the target..

tnelson(at)nj-dr1:~$ ps -ef | grep receive
postgres 1158 915 0 00:14 ? 00:00:00 postgres: wal receiver process streaming 1/98FE4000

On the primary..

root(at)ihdb1:/var/lib/postgresql# ps -ef | grep send
postgres 9742 9480 0 Dec12 ? 00:00:09 postgres: wal sender process postgres 192.168.39.42(37235) streaming 1/98FFA560
postgres 11126 9480 0 00:14 ? 00:00:01 postgres: wal sender process postgres 192.168.6.5(50685) streaming 1/98FFA560

The id's don't match now because the web app is running it's nightly jobs which will keep the database busy for a few hours, but everything certainly seems to be working fine.

Has anyone implemented a nagios monitor for these processes?

Thanks in advance for any help.

Tony Nelson

Since 1982, Starpoint Solutions has been a trusted source of human capital and solutions. We are committed to our clients, employees, environment, community and social concerns. We foster an inclusive culture based on trust, respect, honesty and solid performance. Learn more about Starpoint and our social responsibility at http://www.starpoint.com/social_responsibility

This email message from Starpoint Solutions LLC is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. Opinions, conclusions and other information in this message that do not relate to the official business of Starpoint Solutions shall be understood as neither given nor endorsed by it.