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 1f7tn2-0002NJ-D2 for pgsql-admin@arkaria.postgresql.org; Mon, 16 Apr 2018 02:22:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f7tmz-0004PE-RR for pgsql-admin@arkaria.postgresql.org; Mon, 16 Apr 2018 02:22:41 +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 1f7tmz-0004P4-0U for pgsql-admin@lists.postgresql.org; Mon, 16 Apr 2018 02:22:41 +0000 Received: from a2nlsmtp01-02.prod.iad2.secureserver.net ([198.71.225.36]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f7tmv-00056J-8g for pgsql-admin@lists.postgresql.org; Mon, 16 Apr 2018 02:22:39 +0000 Received: from a2plcpnl0131.prod.iad2.secureserver.net ([198.71.226.50]) by : HOSTING RELAY : with SMTP id 7tlbfh1XN6Zjp7tlbfPJCO; Sun, 15 Apr 2018 19:21:15 -0700 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=telium.ca; s=default; h=Content-Type:MIME-Version:Message-ID:Date:Subject:To:From: Sender:Reply-To:Cc:Content-Transfer-Encoding:Content-ID:Content-Description: Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID: In-Reply-To:References:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=qvKQ5y2LiDQD4YE35zIrFvDohdrya+C5ZS40uTJt4as=; b=rQI5oCPK6hyK/S1pKBXj6lrNlp BYwYWH5BfROeGQG8+bkkWT3WYoMgRBovJm/4pPXuPdaPWIvQxge4nKZAwkepFvVGT/2o4AjGG7pZZ 3booUvldosoVy14gAhsXkfX/mZeGIm2ByJI0lgHB9C7OrZtHEH/9v2chaf43KSdUzWUYjPi1mL01+ 1M5ra4Ju4UM/ENzEh7leaYNT+levb/3B3Pgf7hx7drRfMguFdFrceBx3Bgvb3kq3K6FiHWk+GG2p3 dhpsDlmthIGVo1tFLjwYkBOCQO5/LtRxWiBlnKHwBqdbpg007ipDmVqpRtW7w1g63HHf8iA/Kf6ZR 8IkypxOA==; Received: from [173.239.164.42] (port=54766 helo=vmltms) by a2plcpnl0131.prod.iad2.secureserver.net with esmtpsa (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.89_1) (envelope-from ) id 1f7tla-001Zqg-PS for pgsql-admin@lists.postgresql.org; Sun, 15 Apr 2018 19:21:15 -0700 From: "TSG" To: Subject: How to tell if PGSQL 8.4 is in standby mode Date: Sun, 15 Apr 2018 22:20:11 -0400 Organization: Telium Message-ID: <002701d3d529$76a8dd70$63fa9850$@telium.ca> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0028_01D3D507.EF9B5C20" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AdPVKV7PJuMegnxUSB+PjbpBOuDdcA== Content-Language: en-us X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - a2plcpnl0131.prod.iad2.secureserver.net X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - telium.ca X-Get-Message-Sender-Via: a2plcpnl0131.prod.iad2.secureserver.net: authenticated_id: support@telium.ca X-Authenticated-Sender: a2plcpnl0131.prod.iad2.secureserver.net: support@telium.ca X-Source: X-Source-Args: X-Source-Dir: X-CMAE-Envelope: MS4wfNaaNjajSLCx18s95t8b7aZwiZ2YV5iD5MDDYOyCbGZ1fJNv3qTVTRHkuAiMVdvK8+m46rvpoWgU9nMggcOrcFNYod1QJnZOTh3zUekFa0T8Rh4XyJU3 rv3PfklyRJBtSbJe8sWO9Ke83I4QgLD7O18hTm/HLyuXTNAwUrd7an/ZATGYB1a2asWgO18+nRyEYTGpAVYK45ms3xsHznwNygYkins1NJ4RqA5+8cUbYGRT List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0028_01D3D507.EF9B5C20 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 Database system identifier: 6514583873281163231 Database cluster state: shut down pg_control last modified: Sun 15 Apr 2018 05:39:25 PM EDT Latest checkpoint location: 7/56000020 Prior checkpoint location: 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 of latest checkpoint: Sun 15 Apr 2018 05:39:24 PM EDT Minimum recovery ending location: 0/0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value But that contradicts the status command output: [root@node2 pgsql]# sudo -u postgres pg_ctl status -D /var/lib/pgsql/data pg_ctl: server is running (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 to start server anyway [2018-04-15 23:06:11 GMT] FATAL: lock file "postmaster.pid" already exists [2018-04-15 23:06:11 GMT] HINT: Is another postmaster (PID 35852) running in data directory "/var/lib/pgsql/data"? pg_ctl: could not start server 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? ------=_NextPart_000_0028_01D3D507.EF9B5C20 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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&= nbsp;my standby DB is in standby/recovery mode but I'm not sure how to = tell.

<= o:p> 

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'>F= irst of all, pg_controldata says the database is "shut = down":

[root@node2 = pgsql]# pg_controldata = data           &nb= sp;           &nbs= p;         =

pg_control = version = number:           = 843

Catalog version = number:           =     200904091

Database system = identifier:        =    6514583873281163231

Database cluster = state:           &= nbsp;   shut down

pg_control last = modified:          &nbs= p;  Sun 15 Apr = 2018 05:39:25 PM = EDT

Latest = checkpoint = location:           = 7/56000020

Prior = checkpoint = location:          &nbs= p; 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 = of latest = checkpoint:     &nbs= p;      Sun 15 Apr = 2018 05:39:24 PM = EDT

Minimum recovery = ending location:     0/0

Maximum data = alignment:          &nb= sp;    8

Database block = size:           &n= bsp;      8192

Blocks per = segment of large relation: = 131072

WAL block = size:         =             &= nbsp; 8192

Bytes per WAL = segment:           = ;     16777216

Maximum length = of = identifiers:        64

Maximum columns = in an index:     &nbs= p;    32

Maximum size = of a TOAST = chunk:        1996

Date/time type = storage:           = ;    64-bit = integers

Float4 argument = passing:           = ;   by = value

Float8 argument = passing:           = ;   by = value=

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'><= o:p> 

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'>B= ut that contradicts the status command output:

[root@node2 = pgsql]# sudo -u postgres pg_ctl status -D /var/lib/pgsql/data  =

pg_ctl: server = is running (PID: = 35852)

/usr/bin/postgres "-D" "/var/lib/pgsql/data"=

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'><= o:p> 

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'>A= nd 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 to start server = anyway

[2018-04-15 23:06:11 GMT] = FATAL:  lock file "postmaster.pid" already = exists

[2018-04-15 23:06:11 GMT] = HINT:  Is another = postmaster (PID 35852) running = in data directory = "/var/lib/pgsql/data"?

pg_ctl: could = not start = server

Examine the log = output.=

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'><= o:p> 

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'>A= nd I can see that my recovery.conf is still in = place:

[root@node2 = pgsql]# cat data/recovery.conf

restore_command=3D'pg_standby -r = 10 -t /var/run/myapp.trigger /var/lib/pgsql/data/myapp-archive = "%f" "%p"'=

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'>A= nd finally, I see an every growing list of files in the archive = directory.

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'>S= o, is there a way to tell if the wal (archive) files are being applied = to the database?

 

------=_NextPart_000_0028_01D3D507.EF9B5C20--