Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMZtg-0007fo-FM for pgsql-bugs@arkaria.postgresql.org; Thu, 09 Apr 2020 16:19:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jMZtf-0007dX-Bk for pgsql-bugs@arkaria.postgresql.org; Thu, 09 Apr 2020 16:19:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMZtf-0007dQ-5Y for pgsql-bugs@lists.postgresql.org; Thu, 09 Apr 2020 16:19:19 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMZtY-0006Li-48 for pgsql-bugs@postgresql.org; Thu, 09 Apr 2020 16:19:18 +0000 Received: from [192.168.127.1] (port=60055 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1jMZtV-000LLb-IN; Thu, 09 Apr 2020 16:19:09 +0000 Received: from localhost ([127.0.0.1]:26192 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1jMZtV-0008nH-Dy; Thu, 09 Apr 2020 16:19:09 +0000 From: Andrew Gierth To: hubert depesz lubaczewski Cc: pgsql-bugs mailing list Subject: Re: Loss of replication after simple misconfiguration In-Reply-To: <20200409140836.GA5990@depesz.com> (hubert depesz lubaczewski's message of "Thu, 9 Apr 2020 16:08:36 +0200") Message-ID: <878sj4skmj.fsf@news-spur.riddles.org.uk> References: <20200409140836.GA5990@depesz.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.3 (berkeley-unix) Date: Thu, 09 Apr 2020 17:19:09 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "hubert" == hubert depesz lubaczewski writes: hubert> PostgreSQL 9.5.15 on Ubuntu bionic. [...] hubert> tried to restart only to be greeted by: hubert> 2020-04-07T15:13:49.729943+00:00 postgres[20491]: [7-1] db=,user= LOG: restored log file "000000030001779200000061" from archive hubert> 2020-04-07T15:13:49.757222+00:00 postgres[20491]: [8-1] db=,user= FATAL: could not access status of transaction 4275781146 hubert> 2020-04-07T15:13:49.757314+00:00 postgres[20491]: [8-2] db=,user= DETAIL: Could not read from file "pg_commit_ts/27D4B" at offset 245760: Success. hubert> 2020-04-07T15:13:49.757380+00:00 postgres[20491]: [8-3] db=,user= CONTEXT: xlog redo Transaction/COMMIT: 2020-04-07 02:40:10.065859+00 hubert> 2020-04-07T15:13:49.761239+00:00 postgres[20487]: [2-1] db=,user= LOG: startup process (PID 20491) exited with exit code 1 hubert> 2020-04-07T15:13:49.761387+00:00 postgres[20487]: [3-1] db=,user= LOG: terminating any other active server processes So I've been assisting hubert with analysis of this on IRC, and what we have found so far suggests: 1. the max_worker_processes thing is a red herring 2. It is virtually certain that the restart, in addition to changing max_worker_processes on the master, also changed the master's setting of track_commit_timestamp from off to on (which is clearly relevant to the issue) (We established #2 from the fact that we _do_ have the WAL files from the failed recovery, and they don't contain any COMMIT_TS_ZEROPAGE records despite covering many thousands of transactions.) I've suggested trying to reproduce the issue by changing this parameter across a crash. I did notice that 9.5.15 does have a fix for an issue in this area, but I didn't see any more recent changes - did I miss anything? -- Andrew (irc:RhodiumToad)