pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tony Nelson <tnelson@starpoint.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: 9.1 Streaming Replication
Date: Thu, 13 Dec 2012 00:21:16 -0500
Message-ID: <06DCFE6F-6500-480C-9B12-35AE59CE4813@starpoint.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>

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@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@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@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.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



view thread (2+ messages)  latest in thread

Message-ID: <06DCFE6F-6500-480C-9B12-35AE59CE4813@starpoint.com>
Permalink:  ../06DCFE6F-6500-480C-9B12-35AE59CE4813@starpoint.com/
Also on:    postgresql.org/message-id/06DCFE6F-6500-480C-9B12-35AE59CE4813@starpoint.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tnelson@starpoint.com
  Subject: Re: 9.1 Streaming Replication
  In-Reply-To: <06DCFE6F-6500-480C-9B12-35AE59CE4813@starpoint.com>
  References: <06DCFE6F-6500-480C-9B12-35AE59CE4813@starpoint.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox