Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tj1Ed-00073s-M4 for pgsql-admin@arkaria.postgresql.org; Thu, 13 Dec 2012 05:21:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Tj1Ec-0003rF-Cy for pgsql-admin@arkaria.postgresql.org; Thu, 13 Dec 2012 05:21:26 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tj1Eb-0003r9-8E for pgsql-admin@postgresql.org; Thu, 13 Dec 2012 05:21:25 +0000 Received: from mail.starpoint.com ([64.190.10.4]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tj1EW-0005fx-Rx for pgsql-admin@postgresql.org; Thu, 13 Dec 2012 05:21:23 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.starpoint.com (Postfix) with ESMTP id AEAEA2C040C for ; Thu, 13 Dec 2012 00:21:17 -0500 (EST) X-DKIM: OpenDKIM Filter v2.6.8 mail.starpoint.com AEAEA2C040C DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=starpoint.com; s=mail; t=1355376077; bh=cfs2Jmq+vQ9juvfriY8Q86xYLYxceYVeg2I+buq7Kyk=; h=From:To:Date:Subject; b=OBO4FcOLbDQVmthongmK8cuxts1Gnggs0/fjZxbmYjikMQ6AAbEaRyCh9BJKnFd/v 6DhcbairnZAgQq8ZBrRBcSUK3UIPd0kXqqg/fqm7NhMxzjqOmgcP23YHktmUYJhajv 3XMpZv+HAqopii1wJ7NMqB6M5oieg4xpTtpgAAS0= X-Virus-Scanned: Debian amavisd-new at starpoint.com Received: from mail.starpoint.com ([127.0.0.1]) by localhost (mail.starpoint.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id JFqIPumSXqdO for ; Thu, 13 Dec 2012 00:21:17 -0500 (EST) Received: from exchange.starpoint.com (NY-HUBT02.WIN.STARPOINT.COM [192.168.43.19]) by mail.starpoint.com (Postfix) with ESMTPS id 5FE9E2C018E for ; Thu, 13 Dec 2012 00:21:17 -0500 (EST) X-DKIM: OpenDKIM Filter v2.6.8 mail.starpoint.com 5FE9E2C018E Received: from NYMBXCLUS.WIN.STARPOINT.COM ([192.168.43.20]) by NY-HUBT02.WIN.STARPOINT.COM ([192.168.43.19]) with mapi; Thu, 13 Dec 2012 00:21:09 -0500 From: Tony Nelson To: "pgsql-admin@postgresql.org" Date: Thu, 13 Dec 2012 00:21:16 -0500 Subject: 9.1 Streaming Replication Thread-Topic: 9.1 Streaming Replication Thread-Index: Ac3Y8aiL9iP+Dm/NS2WahUDB97IaCg== Message-ID: <06DCFE6F-6500-480C-9B12-35AE59CE4813@starpoint.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org I believe I have successfully setup streaming replication but I have a coup= le 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 see= ms to be working just fine. What I can't seem to figure out is what they a= re used for? The target servers seem to connect to the primary and sync fr= om that, not from the wal logs. Are they there in case of an extended disc= onnect? Also, when my target server comes up it always seems to complain that it ca= n't find a wal archive. It's always the one that is currently be written o= n 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 i= t. 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/9870DA= 38 2012-12-13 00:14:20 EST LOG: invalid magic number 0000 in log file 1, segm= ent 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: co= uld 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 i= t finishes, my archive command will copy it to a local directory on the pri= mary 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 t= ransient: On the target.. tnelson@nj-dr1:~$ ps -ef | grep receive postgres 1158 915 0 00:14 ? 00:00:00 postgres: wal receiver proc= ess 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 proces= s postgres 192.168.39.42(37235) streaming 1/98FFA560 postgres 11126 9480 0 00:14 ? 00:00:01 postgres: wal sender proces= s 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 w= hich 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, com= munity 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 informa= tion. Any unauthorized review, use, disclosure or distribution is prohibit= ed. If you are not the intended recipient, please contact the sender by re= ply email and destroy all copies of the original message. Opinions, conclu= sions and other information in this message that do not relate to the offic= ial business of Starpoint Solutions shall be understood as neither given no= r endorsed by it. --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin