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 1jMmS8-00066Y-AI for pgsql-bugs@arkaria.postgresql.org; Fri, 10 Apr 2020 05:43:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jMmS5-0002Vt-Fu for pgsql-bugs@arkaria.postgresql.org; Fri, 10 Apr 2020 05:43:41 +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 1jMmS3-0002UJ-I6 for pgsql-bugs@lists.postgresql.org; Fri, 10 Apr 2020 05:43:41 +0000 Received: from [64.147.123.25] (helo=wout2-smtp.messagingengine.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMmRw-0001fJ-9G for pgsql-bugs@postgresql.org; Fri, 10 Apr 2020 05:43:38 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.west.internal (Postfix) with ESMTP id 7AE0A647; Fri, 10 Apr 2020 01:43:20 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute3.internal (MEProxy); Fri, 10 Apr 2020 01:43:20 -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=16CxS+C7KVhn8QG5nRKFEWy+/p7 GNSbbX6z0K5Pm+sg=; b=iQSPMKY+WSR8e9Vo1rlAluaQZknMK/PrxVfzlSz4cUi s3LVzdnVZeEME5efW1C0puwDkSi6Q6kKYec2+yhvIeNPJHHW07XZS0AdNNVhkWCy boKSsDG/ot+MtLYRSO23bK0bIT8y9F3d0iFI5VmL6u2AMKsH6SHxJpoApSAuDk0Z /IyV8DUHosc82OJtxhEzdtHVLpYWtP6M7WvUaUq+QDiW+/Qi1kCQtT72iAYHDIWG U3dSvo5mA4Ef/e7GANk+6+7gn+f0Uogc1dGBXS8Veux793SJBCdiPEyEO/7L174H m/3rIW251ma8HvIY6pYBfiDCfwZMzUhOFsjQWH6IRyQ== 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=16CxS+ C7KVhn8QG5nRKFEWy+/p7GNSbbX6z0K5Pm+sg=; b=fv5dDuaV7CyYwopWl95F0g pDYgayH9RAVLa7C7iSOQvlbrrvmO8/Owcwf633eEeYdR+O2GlJ59smdHPa7mtQj4 yZ5e4pP7pnMNxALk0Wln5s9Zx5OGcoHVNCSdkqpMP3DEcZcKICvVrk+LPedHy715 aLZ1MVXlLxuC1o4b57y+EMyCYSCl7dBhALMVgCxsRdi16ruED8I2D9Pyb9538nYQ du9qMXUErgFqVUXPmqYRg6mQSV1Rae8lOf1CMUOs8Cnhv4Jo/NMROTsX0BI4MKPV r9wqLM6H5EJjerGsK21fL2ptdayuu5iZsU7hf2uyChiBDfbMRpmd244eb0WATTQA == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduhedrvddugdeljecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfg hrlhcuvffnffculdejtddmnecujfgurhepfffhvffukfhfgggtuggjsehgtderredttddv necuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesphgrqh huihgvrhdrgiihiieqnecukfhppeduuddurddutddvrddukedtrddukeehnecuvehluhhs thgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomhepmhhitghhrggvlhesph grqhhuihgvrhdrgiihii 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 3CD72328005D; Fri, 10 Apr 2020 01:43:18 -0400 (EDT) Date: Fri, 10 Apr 2020 14:43:15 +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: <20200410054315.GV1606@paquier.xyz> References: <20200409140836.GA5990@depesz.com> <878sj4skmj.fsf@news-spur.riddles.org.uk> <20200410041434.GU1606@paquier.xyz> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="bNvn4bxlrUVLu6cT" Content-Disposition: inline In-Reply-To: <20200410041434.GU1606@paquier.xyz> X-Host-Lookup-Failed: Reverse DNS lookup failed for 64.147.123.25 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --bNvn4bxlrUVLu6cT Content-Type: multipart/mixed; boundary="wwU9tsYnHnYeRAKj" Content-Disposition: inline --wwU9tsYnHnYeRAKj Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Fri, Apr 10, 2020 at 01:14:34PM +0900, Michael Paquier wrote: > 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 = > on, then it got disabled once? I have been looking at more scenarios involving switching back and forth track_commit_timestamps on/off during a switchover with a standby failing to replay a XLOG_CHANGE_PARAMETER record during recovery, still I cannot see a failure in scenarios close to what is discussed here (the test includes transactions replayed with and without the switch). Attached is a more advanced test. Any thoughts? -- Michael --wwU9tsYnHnYeRAKj Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="committs-switchover-test-v2.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..e6db21f656 --- /dev/null +++ b/src/test/modules/commit_ts/t/005_switchover.pl @@ -0,0 +1,95 @@ +# 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 => 3; +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 = on + 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, and first stop cleanly the primary. +$master->stop; + +# Promote the standby with larger max_worker_processes, and enable +# track_commit_timestamp while on it. Generate a XLOG_PARAMETER_CHANGE +# record with those values. +$standby->promote; +$standby->append_conf('postgresql.conf', 'track_commit_timestamp = off'); +$standby->append_conf('postgresql.conf', 'max_worker_processes = 10'); +$standby->restart; + +# Enable streaming on the previous primary, and start it. The node will +# shut down once it finds the record XLOG_PARAMETER_CHANGE generated +# previously. +$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 at recovery'); + +# Consume some transactions on the standby without the primary knowing +# about it yet, generating some commit timestamps. +for my $i (11 .. 20) +{ + $standby->safe_psql('postgres', "create table t$i()"); +} + +# Toggle the configuration back on, and do more transactions. +$standby->append_conf('postgresql.conf', 'track_commit_timestamp = on'); +$standby->restart; +for my $i (21 .. 30) +{ + $standby->safe_psql('postgres', "create table t$i()"); +} + +# Reconfigure the new standby and start it so as recovery is able to +# go through with a setting of max_worker_processes much larger than +# the primary. +$master->append_conf('postgresql.conf', 'max_worker_processes = 50'); +$master->start; +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 = 't30'} +); +isnt($standby_ts, '', + "standby gives valid non-empty value after promotion"); + +$standby_ts = $standby->safe_psql('postgres', + qq{SELECT ts.* FROM pg_class, pg_xact_commit_timestamp(xmin) AS ts WHERE relname = 't11'} +); +is($standby_ts, '', + "standby gives valid empty value after promotion"); --wwU9tsYnHnYeRAKj-- --bNvn4bxlrUVLu6cT Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl6QB3MACgkQnvQgOdby QH2ReQ//TR0XXoveiRjNJSY6cFNvASxelHYUYx7URsvgJA9pkMFvul6DuIiFOb6g 1BWH+eZGzkJ2N0Hd33gXZJjL+qPImpsdp8HVMaSjSypuD2MC2KQb4BOqgDHk0M7K 5/bkkypDOYgsPgrZAk31KsqUTh6KEnICROAGcAsH/VXxWlMlONseKwk6lOm1zl5c +4S6X+8dImg43IMxFnhxlhk5dJW3p5Iw9B65oWmX6Gy8v3xm2Ip6PskdqmGFEjwS 9U4BMvYPiWwaNc0zltnfQXbCygoy9OuvRoiTdeh9JOpqi1JYCX19++fDUZd6swZz 9mXeVi78ejA+8w0ZNfuGgTPDBRkRStRQ2FWw03/LkGctXXsLEDnGa1PIioJcECmh qJ9BQLzPAuIeeU9gW5iG9t+3v+/3SaS6mlaEgTAO7z+7aMMXVRD0RT4owqQzUTRR iGIdHAnA5cpr/u9PA7yH/KIJc128Y1RFJiXzDzLesxZD5Ku6TSHBBJ/Z/dXVCKpr gMLVi/hrPpdHsq6aCffaaQ3KrGcC1SGNK1NaYeh7DEhOWZGyxoS2u4JO6/1gKP38 4cFKAYocZIAbNeHaFIrVbOncpbhTv+0DGpOqOcmG7niqydqrGptMJWFjbJWhH2Xd etk6DRqO3wAgVjySgK2I1imiXTqlDn4ZeVDGHbZy+sfr4wgcjcI= =bZy7 -----END PGP SIGNATURE----- --bNvn4bxlrUVLu6cT--