Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f7zLK-0008Bc-A6 for pgsql-admin@arkaria.postgresql.org; Mon, 16 Apr 2018 08:18:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f7zLI-0004bo-O2 for pgsql-admin@arkaria.postgresql.org; Mon, 16 Apr 2018 08:18:28 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f7zLI-0004bJ-DL for pgsql-admin@lists.postgresql.org; Mon, 16 Apr 2018 08:18:28 +0000 Received: from cat-porwal-prod-mail1.catalyst.net.nz ([2404:130:4080::4]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f7zLD-0004J8-IZ for pgsql-admin@lists.postgresql.org; Mon, 16 Apr 2018 08:18:26 +0000 Authentication-Results: auth=pass smtp.auth=mark.kirkwood@catalyst.net.nz smtp.mailfrom=mark.kirkwood@catalyst.net.nz Received: from [IPv6:2406:e001:e07:1:c89e:967f:bc74:3fe8] (unknown [IPv6:2406:e001:e07:1:c89e:967f:bc74:3fe8]) (Authenticated sender: mark.kirkwood@catalyst.net.nz) by cat-porwal-prod-mail1.catalyst.net.nz (Postfix) with ESMTPSA id 672B380A27 for ; Mon, 16 Apr 2018 20:18:15 +1200 (NZST) Subject: Re: How to tell if PGSQL 8.4 is in standby mode To: pgsql-admin@lists.postgresql.org References: <002701d3d529$76a8dd70$63fa9850$@telium.ca> From: Mark Kirkwood Message-ID: <037ec8bb-72d2-b0c3-d218-2211c9b5e3cf@catalyst.net.nz> Date: Mon, 16 Apr 2018 20:18:15 +1200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.7.0 MIME-Version: 1.0 In-Reply-To: <002701d3d529$76a8dd70$63fa9850$@telium.ca> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, (see below) On 16/04/18 14:20, TSG wrote: > > I have a PostgreSQL 8.4 database that I'm am trying to start in > standby mode (I am shipping my own logs from the active DB). I > /think/ my standby DB is in standby/recovery mode but I'm not sure how > to tell. > > First of all, pg_controldata says the database is "shut down": > > [root@node2 pgsql]# pg_controldata data > > pg_control version number: 843 > > Catalog version number: 200904091 > > Databasesystem identifier: 6514583873281163231 > > Databasecluster state:               shut down > > pg_control last modified:             Sun 15Apr 201805:39:25PM EDT > > Latest checkpointlocation: 7/56000020 > > Prior checkpointlocation: 7/55000020 > > Latest checkpoint's REDO location:    7/56000020 > > Latest checkpoint's TimeLineID: 116 > > Latest checkpoint's NextXID:          0/40171 > > Latest checkpoint's NextOID: 20826 > > Latest checkpoint's NextMultiXactId:  1 > > Latest checkpoint's NextMultiOffset: 0 > > Time oflatest checkpoint: Sun 15Apr 201805:39:24PM EDT > > Minimum recovery ending location: 0/0 > > Maximum data alignment: 8 > > Databaseblock size: 8192 > > Blocks per segment oflarge relation: 131072 > > WAL block size: 8192 > > Bytes per WAL segment: 16777216 > > Maximum length ofidentifiers: 64 > > Maximum columns inan index: 32 > > Maximum size ofa TOAST chunk: 1996 > > Date/time type storage: 64-bit integers > > Float4 argument passing: byvalue > > Float8 argument passing: byvalue > > But that contradicts the status command output: > > [root@node2 pgsql]# sudo -u postgres pg_ctl status -D /var/lib/pgsql/data > > pg_ctl: server isrunning (PID: 35852) > > /usr/bin/postgres "-D""/var/lib/pgsql/data" > > And when I try to start the database it says it's already running: > > [root@node2 pgsql]# sudo -u postgres pg_ctl start-D /var/lib/pgsql/data > > pg_ctl: another server might be running; trying tostartserver anyway > > [2018-04-1523:06:11GMT] FATAL:  lock file"postmaster.pid"already exists > > [2018-04-1523:06:11GMT] HINT: Isanother postmaster (PID 35852) running > indata directory "/var/lib/pgsql/data"? > > pg_ctl: could notstartserver > > Examine the log output. > > And I can see that my recovery.conf is still in place: > > [root@node2 pgsql]# cat data/recovery.conf > > restore_command='pg_standby -r 10 -t /var/run/myapp.trigger > /var/lib/pgsql/data/myapp-archive "%f" "%p"' > > And finally, I see an every growing list of files in the archive > directory. > > So, is there a way to tell if the wal (archive) files are being > applied to the database? > 8.4? Seriously, there are so many reasons to be using a later version! Among them are easier ways to checking if your standby is actually still applying wal! Now, dragging back memories from many years ago I note that pg_standby writes its own log file, so you should be able to see if/when wal files are being applied in there (usually written in the data directory not /etc). But. Come on - use a later version - this replication stuff is much better these days regards Mark