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 1jMXrH-0003SY-CH for pgsql-bugs@arkaria.postgresql.org; Thu, 09 Apr 2020 14:08:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jMXrG-0005zr-7A for pgsql-bugs@arkaria.postgresql.org; Thu, 09 Apr 2020 14:08:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMXrF-0005z2-VO for pgsql-bugs@lists.postgresql.org; Thu, 09 Apr 2020 14:08:42 +0000 Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMXrD-0002mZ-75 for pgsql-bugs@postgresql.org; Thu, 09 Apr 2020 14:08:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=depesz.com; s=20170201; h=Content-Type:MIME-Version:Reply-To:Message-ID:Subject:To: Sender:From:Date: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=9rXWMIyLAvB/vCKQVMEQGf/dY7Mp+9LmLZjicu/s7po=; b=oeQnvaIn29o74s/Dd5tjv2H70w VgGwkjxgr+KeLzmb9V/I6GTkQVOVkXivazxS6dbGyGbv7jwtq6Uq/loSQZBdwKwuLW9xqyHWGAewN UNa718Gr1750R8AAYpvRufW+6L5LrKlKdUDDjyBd7PRYfY5MCYKRQbQpV001HvZWYljk=; Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com) by depesz.com with esmtpa (Exim 4.92) (envelope-from ) id 1jMXrA-0006wF-Gy for pgsql-bugs@postgresql.org; Thu, 09 Apr 2020 16:08:36 +0200 Date: Thu, 9 Apr 2020 16:08:36 +0200 From: hubert depesz lubaczewski Sender: depesz@depesz.com To: pgsql-bugs mailing list Subject: Loss of replication after simple misconfiguration Message-ID: <20200409140836.GA5990@depesz.com> Reply-To: depesz@depesz.com MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, I was trying to get it to replicate, but I couldn't in controlled environment, so I have no repeatable proof of concept, but we've seen the problem at least 4 times. Configuration: PostgreSQL 9.5.15 on Ubuntu bionic. Streaming replication from master to slave, working without problem. max_worker_processes set to 8 on both servers. Preparation: At some point in time, to accommodate upcoming (yes, really) upgrade to pg12, we pushed change that write "max_worker_processes = 50" to all configs, but didn't restart Pgs. Fail scenario: Master died (underlying AWS EC2 instance got hosed, was restarted). Master got back up, but loaded new configuration from conf file. Immediately it crashed slave with message: 2020-04-09 15:55:45.653 CEST 1304738 LOG: started streaming WAL from primary at 3/D000000 on timeline 1 2020-04-09 15:55:45.679 CEST 505710 FATAL: hot standby is not possible because max_worker_processes = 8 is a lower setting than on the master server (its value was 50) 2020-04-09 15:55:45.679 CEST 505710 CONTEXT: xlog redo XLOG/PARAMETER_CHANGE: max_connections=100 max_worker_processes=50 max_prepared_xacts=0 max_locks_per_xact=64 wal_level=logical wal_log_hints=off track_commit_timestamp=off (this message is from my test environment, but the idea is clear). Given that we have max_worker_processes = 50 in config on slave, we tried to restart only to be greeted by: 2020-04-07T15:13:49.729943+00:00 postgres[20491]: [7-1] db=,user= LOG: restored log file "000000030001779200000061" from archive 2020-04-07T15:13:49.757222+00:00 postgres[20491]: [8-1] db=,user= FATAL: could not access status of transaction 4275781146 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. 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 2020-04-07T15:13:49.761239+00:00 postgres[20487]: [2-1] db=,user= LOG: startup process (PID 20491) exited with exit code 1 2020-04-07T15:13:49.761387+00:00 postgres[20487]: [3-1] db=,user= LOG: terminating any other active server processes We had similar case (could not access status of transaction) on at least 4 separate pg installations, so it looks to be related to the restart of master while bad max_worker_processes are set. We have since rolled back the change to max_worker_processes, and will be doing it in much saner/safer way, but the fact that slave can't start after such master restart is troubling. Unfortunately we don't have any instances broken in this way anymore, and we don't have logs either - we had to rebuild the Pg setup (master/slave) ASAP, and thus the evidence is lost. Is there anything we could have done to fix it once it happened? Is it a known problem? Or maybe the max_worker_processes mismatch is redherring and the problem was simply because master died while there was heavy write traffic with lots of transactions? Best regards, depesz