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 1jMl4F-0001DJ-V4 for pgsql-bugs@arkaria.postgresql.org; Fri, 10 Apr 2020 04:15:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jMl4E-00078s-O1 for pgsql-bugs@arkaria.postgresql.org; Fri, 10 Apr 2020 04:14:58 +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 1jMl4C-00078l-Vv for pgsql-bugs@lists.postgresql.org; Fri, 10 Apr 2020 04:14:58 +0000 Received: from [64.147.123.21] (helo=wout5-smtp.messagingengine.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMl48-0004vc-2z for pgsql-bugs@postgresql.org; Fri, 10 Apr 2020 04:14:56 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.west.internal (Postfix) with ESMTP id 66DE964C; Fri, 10 Apr 2020 00:14:39 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute3.internal (MEProxy); Fri, 10 Apr 2020 00:14:39 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm1; bh=SVXGCk84qF0V0J2y9ZgFlpZEFTZ 7dhp8p9WI6X/MmvA=; b=KYFM4H6EGdKD8PumE6pQZBIF3QqapzZTyp65wUgWvlg mEEz1yU34wX9+6YEB0wBRNrq3o8OdFWzhlFJCSqZIrECNOpMZ/ggqmbS+UNx1Ipn UuyZFolR7Quuqu68RzwVKwb/ddMkO+bbvQYk1F0Je004daMYby4VXuvYtRlAgtRf T68xkd8Zx8DeccP+5JTG6xTZrR/rB+VxQzPIrVqYE0b3shqJ7RcpxGk6s4ckw8Ab Q1/U1yYbRFqznebjBnVVUDadmYGz9bvLfarm1svRwSNKJduJiINZqLsdYCltAPn0 3XWAXW+kBzdHvAk4xbbKhONWKHbZkUBjFeoEWLmh6vg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm2; bh=SVXGCk 84qF0V0J2y9ZgFlpZEFTZ7dhp8p9WI6X/MmvA=; b=lA+H157WUkEF5eu/wNuYYx 7/poAXB5Z68FPhfwy+KLfQVsVtIhdXar2gb/s70ldXECYausFZibJpbixXkgtRrF /La+n74a2AegucsZL/GuFzr83hIhPasp2HjPHfS5zEuWPQ/jzZqJiRPHGB/ll0mq jBnEF04lzULZy+brGxhAiDYa+Y6RSnIWYdPiz7rdyubnhi9sXTEh8MK9YtL0ZjZO n+dQE0yssqqSmfEYpaiH5Iox1HDx46n8bFle2pNMa2WhOsJDjQ84lu3I03rBLokA /aTPPHF07+xrId2cs70zX5iSvZdlsIC2tpwRWOJSJYdi0FjIjorV5snvHgZ2XCvA == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduhedrvddugdejlecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfg hrlhcuvffnffculdefhedmnecujfgurhepfffhvffukfhfgggtuggjsehgtderredttdej necuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesphgrqh huihgvrhdrgiihiieqnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrghenucfk phepudduuddruddtvddrudektddrudekheenucevlhhushhtvghrufhiiigvpedtnecurf grrhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiii X-ME-Proxy: Received: from paquier.xyz (ee0822lan1.rev.em-net.ne.jp [111.102.180.185]) by mail.messagingengine.com (Postfix) with ESMTPA id EC204328005D; Fri, 10 Apr 2020 00:14:36 -0400 (EDT) Date: Fri, 10 Apr 2020 13:14:34 +0900 From: Michael Paquier To: Victor Yegorov Cc: Andrew Gierth , hubert depesz lubaczewski , pgsql-bugs mailing list Subject: Re: Loss of replication after simple misconfiguration Message-ID: <20200410041434.GU1606@paquier.xyz> References: <20200409140836.GA5990@depesz.com> <878sj4skmj.fsf@news-spur.riddles.org.uk> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="V2AZ19bMCakBLong" Content-Disposition: inline In-Reply-To: X-Host-Lookup-Failed: Reverse DNS lookup failed for 64.147.123.21 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --V2AZ19bMCakBLong Content-Type: multipart/mixed; boundary="l5oECiFRo5dp+2y7" Content-Disposition: inline --l5oECiFRo5dp+2y7 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Thu, Apr 09, 2020 at 07:48:17PM +0300, Victor Yegorov wrote: > We've hit similar issue last week, but on 11.5 =E2=80=94 we > had track_commit_timestamp enabled on master after switchover, > replica failed to start. >=20 > It might be, that fix was here: > https://git.postgresql.org/pg/commitdiff/180feb8c7e > (For 9.5 branch it is: https://git.postgresql.org/pg/commitdiff/69a568636= 8) >=20 > We're not in the position to test it again, though=E2=80=A6 Hmm. We have a gap in tests here as we don't have any tests stressing switchovers when it comes to track_commit_timestamps. Anyway, could you confirm that I got the problem right? Here is the flow I am getting =66rom the information of upthread, roughly: 1) Primary/standby cluster, both using max_worker_processes =3D 8, and track_commit_timestamp =3D off. 2) In order to begin the switchover, first stop cleanly the primary. 3) Update configuration of the standby as follows, promote it and restart it: track_commit_timestamp =3D on max_worker_processes =3D 50 4) Enable streaming on the old primary to make it a standby, starting it fails because of the unmatching setting for max_worker_processes. 5) Re-adjust max_worker_processes correctly on the new standby, start it. Then this startup should fail at the lookup of pg_commit_ts/. I have been able to write a TAP test to reproduce this exact scenario, though it succeeds for me (it could be a good idea to add some coverage for that actually..). Perhaps I am missing a step though? For example, perhaps the original setting was track_commit_timestamp =3D on, then it got disabled once? -- Michael --l5oECiFRo5dp+2y7 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="committs-switchover-test.patch" diff --git a/src/test/modules/commit_ts/t/005_switchover.pl b/src/test/modules/commit_ts/t/005_switchover.pl new file mode 100644 index 0000000000..f414f632c5 --- /dev/null +++ b/src/test/modules/commit_ts/t/005_switchover.pl @@ -0,0 +1,85 @@ +# Test master/standby switchover scenario where the track_commit_timestamp +# GUC is originally off, then enabled on the new primary after a switchover. +use strict; +use warnings; + +use TestLib; +use Test::More tests => 2; +use PostgresNode; + +my $bkplabel = 'backup'; +my $master = get_new_node('master'); +$master->init(allows_streaming => 1); +$master->append_conf( + 'postgresql.conf', qq{ + track_commit_timestamp = off + max_wal_senders = 5 + max_worker_processes = 8 + }); +$master->start; +$master->backup($bkplabel); + +my $standby = get_new_node('standby'); +$standby->init_from_backup($master, $bkplabel, has_streaming => 1); +$standby->start; + +for my $i (1 .. 10) +{ + $master->safe_psql('postgres', "create table t$i()"); +} +my $master_lsn = + $master->safe_psql('postgres', 'select pg_current_wal_lsn()'); +$standby->poll_query_until('postgres', + qq{SELECT '$master_lsn'::pg_lsn <= pg_last_wal_replay_lsn()}) + or die "standby never caught up"; + +$standby->safe_psql('postgres', 'checkpoint'); +$standby->restart; + +# Begin the switchover, stop the primary. Note that attempting to +# start this cluster with a value of max_worker_processes lower than +# the original causes its startup to fail. +$master->append_conf('postgresql.conf', 'max_worker_processes = 4'); +$master->stop; + +# Promote the standby with larger max_worker_processes, and enable +# track_commit_timestamp while on it. +$standby->append_conf('postgresql.conf', 'track_commit_timestamp = on'); +$standby->promote; +$standby->restart; + +# Enable streaming on the previous primary, and attempt to start it. +# This fails as max_worker_processes is lower than on the root +# cluster. +$master->enable_streaming($standby); +command_fails( + [ 'pg_ctl', '-D', $master->data_dir, '-l', $master->logfile, 'start' ], + 'start fails because of max_worker_processes lower on standby'); + +# Consume some transactions on the standby without the primary knowing +# about it yet. +for my $i (1 .. 100) +{ + $standby->safe_psql('postgres', "select txid_current();"); +} +$standby->safe_psql('postgres', "create table t11()"); + +# Add now a proper setting for max_worker_processes to allow startup +# to go through. This new standby will replay all information +# received from the transactions of the standby previously done. +$master->append_conf('postgresql.conf', 'max_worker_processes = 8'); +$master->start; + +# Check that old master is correctly streaming as a new standby. +my $standby_lsn = + $standby->safe_psql('postgres', 'select pg_current_wal_lsn()'); +$master->poll_query_until('postgres', + qq{SELECT '$standby_lsn'::pg_lsn <= pg_last_wal_replay_lsn()}); + +# And finally check that commit timestamps are generated on the +# new primary. +my $standby_ts = $standby->safe_psql('postgres', + qq{SELECT ts.* FROM pg_class, pg_xact_commit_timestamp(xmin) AS ts WHERE relname = 't11'} +); +isnt($standby_ts, '', + "standby gives valid value ($standby_ts) after promotion"); --l5oECiFRo5dp+2y7-- --V2AZ19bMCakBLong Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl6P8qoACgkQnvQgOdby QH2ijQ//S3C/cG8aZFIkHOonVBC/T9C/ye3zOxpZi29D8u8hO84dw03IQml+63+p iSV7rRLn7yMmK6WDg+hKyXC/OzkZIUznF18wrgOEioEsi98vI729utQr1UChp4Mk Tfj/SI/MEOSBBdDFn52zNCBi5uGdk5EOtwkcZw3cU/eqe0uLtbCa5eWKNW7+VB54 tkBoXfxVyksEpK8T5mjnJRGvJNW5oV0Lb05pK8vjGI75mH8BHIHTvoFrgrWG/80j xMQUT1+1OBvUopE9iL+IjGdBK+Hsf+X9vvtK/kcp3vZG2RhiioOI72+Dsiyzr4t2 Vu/XJguwJd9WeAgJjTbXgT5rTI/V5c4fyXlPGuGx+8g9Y/oBGrokoK/3REuUvN1q F4rTLp3jb1wa1kaIxUq+Pa77QuKgNO/DYHcfCNvQqRWEbSEMdabmH9oU9VYldtOI OVvi8mEfLz5tLF6OeS8lSRM1khA4vgHtBChTCUf/79D1wJlDP5L7cR+uZ0xjNQxi SvKOnivvbIRuSUmET4gAmXB9yYR/JioaCP3vJ9riolOeSqfcpADoidrrlfNoB9nI 3Dvu7toX5Tqpw54eklLS1mMS0PotuJqpCeZg5Cjnl/3L1oUFmbLJGmfM9cVtbSEm Zs9RwTGRuA9N78rsV1fkT69Y0Te5jRRd2Sur3s5NKMVpuCq3hRg= =3DLC -----END PGP SIGNATURE----- --V2AZ19bMCakBLong--