Add --system-identifier / -s option to pg_resetwal

From 4b41965b84470a33254c3aaf9b2b458027a9a176 Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <nik@postgres.ai>
Date: Sat, 31 May 2025 11:36:41 -0700
Subject: [PATCH] Add --system-identifier option to pg_resetwal

This patch adds a new --system-identifier option to pg_resetwal that allows
users to change the database cluster's system identifier. This feature is
useful in recovery scenarios where a restored cluster needs to be made
distinct from the original.

Key features:
- Accepts positive 64-bit integers only (zero not allowed)
- Requires interactive confirmation or --force flag for safety
- Detects non-TTY environments and requires --force in scripts
- Comprehensive input validation and error handling
- Updated documentation with clear warnings about compatibility
- Extensive test coverage including edge cases and automation scenarios

The system identifier change makes the cluster incompatible with existing
backups, standby servers, and replication setups, so the feature includes
appropriate safety checks and user warnings.

Author: Nikolay Samokhvalov <nik@postgres.ai>
---
 doc/src/sgml/ref/pg_resetwal.sgml  |  32 ++++++
 src/bin/pg_resetwal/pg_resetwal.c  |  69 ++++++++++-
 src/bin/pg_resetwal/t/001_basic.pl | 179 +++++++++++++++++++++++++++++
 3 files changed, 279 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/pg_resetwal.sgml b/doc/src/sgml/ref/pg_resetwal.sgml
index 2c019c2aac6..e58befd5b09 100644
--- a/doc/src/sgml/ref/pg_resetwal.sgml
+++ b/doc/src/sgml/ref/pg_resetwal.sgml
@@ -356,6 +356,38 @@ PostgreSQL documentation
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><option>-s <replaceable class="parameter">system_identifier</replaceable></option></term>
+    <term><option>--system-identifier=<replaceable class="parameter">system_identifier</replaceable></option></term>
+    <listitem>
+     <para>
+      Manually set the database system identifier.
+     </para>
+
+     <para>
+      The system identifier is a unique 64-bit number that identifies the
+      database cluster. It is used by replication systems and backup tools
+      to ensure they are working with the correct cluster. Changing the
+      system identifier makes the cluster incompatible with existing
+      backups, standby servers, and replication setups.
+     </para>
+
+     <para>
+      This option should only be used in recovery scenarios where you need
+      to make a restored cluster distinct from the original, or when cloning
+      a cluster for testing purposes. The value must be a positive 64-bit
+      integer and cannot be zero.
+     </para>
+
+     <warning>
+      <para>
+       Changing the system identifier will break compatibility with existing
+       backups and standby servers. Use this option with extreme caution.
+      </para>
+     </warning>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><option>--char-signedness=<replaceable class="parameter">option</replaceable></option></term>
     <listitem>
diff --git a/src/bin/pg_resetwal/pg_resetwal.c b/src/bin/pg_resetwal/pg_resetwal.c
index e876f35f38e..eecd9fd3733 100644
--- a/src/bin/pg_resetwal/pg_resetwal.c
+++ b/src/bin/pg_resetwal/pg_resetwal.c
@@ -76,6 +76,7 @@ static XLogSegNo minXlogSegNo = 0;
 static int	WalSegSz;
 static int	set_wal_segsize;
 static int	set_char_signedness = -1;
+static uint64 set_sysid = 0;
 
 static void CheckDataVersion(void);
 static bool read_controlfile(void);
@@ -105,6 +106,7 @@ main(int argc, char *argv[])
 		{"next-oid", required_argument, NULL, 'o'},
 		{"multixact-offset", required_argument, NULL, 'O'},
 		{"oldest-transaction-id", required_argument, NULL, 'u'},
+		{"system-identifier", required_argument, NULL, 's'},
 		{"next-transaction-id", required_argument, NULL, 'x'},
 		{"wal-segsize", required_argument, NULL, 1},
 		{"char-signedness", required_argument, NULL, 2},
@@ -140,7 +142,7 @@ main(int argc, char *argv[])
 	}
 
 
-	while ((c = getopt_long(argc, argv, "c:D:e:fl:m:no:O:u:x:", long_options, NULL)) != -1)
+	while ((c = getopt_long(argc, argv, "c:D:e:fl:m:no:O:s:u:x:", long_options, NULL)) != -1)
 	{
 		switch (c)
 		{
@@ -321,6 +323,29 @@ main(int argc, char *argv[])
 					break;
 				}
 
+			case 's':
+				/* Check for negative sign first */
+				if (optarg[0] == '-')
+				{
+					pg_log_error("system identifier must be greater than 0");
+					pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+					exit(1);
+				}
+				errno = 0;
+				set_sysid = strtou64(optarg, &endptr, 0);
+				if (endptr == optarg || *endptr != '\0' || errno != 0 || set_sysid == 0)
+				{
+					if (errno == ERANGE)
+						pg_log_error("system identifier value is out of range");
+					else if (set_sysid == 0)
+						pg_log_error("system identifier must be greater than 0");
+					else
+						pg_log_error("invalid argument for option %s", "-s");
+					pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+					exit(1);
+				}
+				break;
+
 			default:
 				/* getopt_long already emitted a complaint */
 				pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -478,6 +503,41 @@ main(int argc, char *argv[])
 	if (set_char_signedness != -1)
 		ControlFile.default_char_signedness = (set_char_signedness == 1);
 
+	if (set_sysid != 0)
+	{
+		/* Safety check: prompt for confirmation when changing system identifier unless force flag is used */
+		if (!force)
+		{
+			char response[10];
+			
+			/* Check if stdin is a TTY for interactive confirmation */
+			if (!isatty(fileno(stdin)))
+			{
+				pg_log_error("standard input is not a TTY and --force was not specified");
+				pg_log_error_hint("Cannot prompt for system identifier change confirmation. "
+								  "Use --force to proceed without confirmation in non-interactive mode.");
+				exit(1);
+			}
+			
+			printf(_("WARNING: Changing the system identifier will make this cluster incompatible with existing backups and standby servers.\n"));
+			printf(_("Current system identifier: " UINT64_FORMAT "\n"), ControlFile.system_identifier);
+			printf(_("New system identifier: " UINT64_FORMAT "\n"), set_sysid);
+			printf(_("Continue? (y/n) "));
+			fflush(stdout);
+			
+			if (fgets(response, sizeof(response), stdin) == NULL ||
+				(response[0] != 'y' && response[0] != 'Y'))
+			{
+				printf(_("System identifier change cancelled.\n"));
+				exit(1);
+			}
+		}
+		
+		printf(_("Changing system identifier from " UINT64_FORMAT " to " UINT64_FORMAT "\n"), 
+			   ControlFile.system_identifier, set_sysid);
+		ControlFile.system_identifier = set_sysid;
+	}
+
 	if (minXlogSegNo > newXlogSegNo)
 		newXlogSegNo = minXlogSegNo;
 
@@ -875,6 +935,12 @@ PrintNewControlValues(void)
 		printf(_("Bytes per WAL segment:                %u\n"),
 			   ControlFile.xlog_seg_size);
 	}
+
+	if (set_sysid != 0)
+	{
+		printf(_("System identifier:                    " UINT64_FORMAT "\n"),
+			   ControlFile.system_identifier);
+	}
 }
 
 
@@ -1212,6 +1278,7 @@ usage(void)
 	printf(_("  -O, --multixact-offset=OFFSET    set next multitransaction offset\n"));
 	printf(_("  -u, --oldest-transaction-id=XID  set oldest transaction ID\n"));
 	printf(_("  -x, --next-transaction-id=XID    set next transaction ID\n"));
+	printf(_("  -s, --system-identifier=SYSID    set system identifier (requires confirmation or --force)\n"));
 	printf(_("      --char-signedness=OPTION     set char signedness to \"signed\" or \"unsigned\"\n"));
 	printf(_("      --wal-segsize=SIZE           size of WAL segments, in megabytes\n"));
 
diff --git a/src/bin/pg_resetwal/t/001_basic.pl b/src/bin/pg_resetwal/t/001_basic.pl
index d6bbbd0ceda..760722e744d 100644
--- a/src/bin/pg_resetwal/t/001_basic.pl
+++ b/src/bin/pg_resetwal/t/001_basic.pl
@@ -7,6 +7,7 @@ use warnings FATAL => 'all';
 use PostgreSQL::Test::Cluster;
 use PostgreSQL::Test::Utils;
 use Test::More;
+use IPC::Run;
 
 program_help_ok('pg_resetwal');
 program_version_ok('pg_resetwal');
@@ -179,6 +180,184 @@ command_fails_like(
 	qr/error: invalid argument for option --char-signedness/,
 	'fails with incorrect --char-signedness option');
 
+# -s / --system-identifier
+command_fails_like(
+	[ 'pg_resetwal', '-s' => 'foo', $node->data_dir ],
+	qr/error: invalid argument for option -s/,
+	'fails with incorrect -s option');
+command_fails_like(
+	[ 'pg_resetwal', '--system-identifier' => 'bar', $node->data_dir ],
+	qr/error: invalid argument for option -s/,
+	'fails with incorrect --system-identifier option');
+command_fails_like(
+	[ 'pg_resetwal', '-s' => '0', $node->data_dir ],
+	qr/error: system identifier must be greater than 0/,
+	'fails with zero system identifier');
+command_fails_like(
+	[ 'pg_resetwal', '-s' => '-123', $node->data_dir ],
+	qr/error: system identifier must be greater than 0/,
+	'fails with negative system identifier');
+
+# Test system identifier change with dry-run
+command_like(
+	[ 'pg_resetwal', '-s' => '1234567890123456789', '--dry-run', $node->data_dir ],
+	qr/System identifier:\s+1234567890123456789/,
+	'system identifier change shows in dry-run output');
+
+# Test actual system identifier change with force flag
+$node->stop;
+my $new_sysid = '9876543210987654321';
+command_ok(
+	[ 'pg_resetwal', '-f', '-s' => $new_sysid, $node->data_dir ],
+	'pg_resetwal -s with force flag succeeds');
+
+# Verify the change was applied by checking pg_control
+$node->start;
+my $controldata_output = $node->safe_psql('postgres', 
+	"SELECT system_identifier FROM pg_control_system()");
+is($controldata_output, $new_sysid, 'system identifier was changed correctly');
+
+# Test that the server works normally after system identifier change
+is($node->safe_psql("postgres", "SELECT 1;"),
+	1, 'server running and working after system identifier change');
+
+$node->stop;
+
+# Test that system identifier change requires force flag when control values are guessed
+# Note: Interactive prompt testing is challenging due to stdin handling limitations
+command_fails_like(
+	[ 'pg_resetwal', '-s' => '1111111111111111111', $node->data_dir ],
+	qr/not proceeding because control file values were guessed/,
+	'system identifier change fails without force flag when control values are guessed');
+
+# Test non-TTY stdin handling (when stdin is not interactive)
+my $non_tty_test_node = PostgreSQL::Test::Cluster->new('non_tty_test');
+$non_tty_test_node->init;
+$non_tty_test_node->stop;
+
+# Test with stdin redirected from /dev/null (non-TTY)
+my ($stdin_null, $stdout_null, $stderr_null) = ('', '', '');
+my $null_harness = IPC::Run::start(
+	[ 'pg_resetwal', '-s', '3333333333333333333', $non_tty_test_node->data_dir ],
+	'<', '/dev/null', '>', \$stdout_null, '2>', \$stderr_null
+);
+$null_harness->finish();
+
+like($stderr_null, qr/standard input is not a TTY and --force was not specified/, 
+	'non-TTY stdin properly detected and rejected without --force');
+
+# Test that --force works with non-TTY stdin
+command_ok(
+	[ 'pg_resetwal', '-f', '-s', '4444444444444444444', $non_tty_test_node->data_dir ],
+	'system identifier change with --force works in non-TTY environment');
+
+# Verify the change was applied in non-TTY test
+$non_tty_test_node->start;
+my $non_tty_sysid = $non_tty_test_node->safe_psql('postgres', 
+	"SELECT system_identifier FROM pg_control_system()");
+is($non_tty_sysid, '4444444444444444444', 
+	'system identifier changed correctly with --force in non-TTY environment');
+$non_tty_test_node->stop;
+
+# Test interactive confirmation with 'n' response (cancellation)
+# We can test this by providing 'n' as input to stdin
+my $interactive_test_node = PostgreSQL::Test::Cluster->new('interactive_test');
+$interactive_test_node->init;
+$interactive_test_node->stop;
+
+# Create a test that simulates user saying 'n' to the confirmation prompt
+my ($stdin, $stdout, $stderr);
+my $harness = IPC::Run::start(
+	[ 'pg_resetwal', '-s', '7777777777777777777', $interactive_test_node->data_dir ],
+	'<', \$stdin, '>', \$stdout, '2>', \$stderr
+);
+
+# Send 'n' to decline the confirmation
+$stdin = "n\n";
+$harness->finish();
+
+like($stderr, qr/System identifier change cancelled/, 
+	'interactive confirmation properly cancels on n response');
+
+# Test interactive confirmation with 'y' response (acceptance)
+($stdin, $stdout, $stderr) = ('', '', '');
+$harness = IPC::Run::start(
+	[ 'pg_resetwal', '-s', '8888888888888888888', $interactive_test_node->data_dir ],
+	'<', \$stdin, '>', \$stdout, '2>', \$stderr
+);
+
+# Send 'y' to accept the confirmation
+$stdin = "y\n";
+$harness->finish();
+
+like($stdout, qr/Changing system identifier/, 
+	'interactive confirmation proceeds on y response');
+
+# Verify the change was applied
+$interactive_test_node->start;
+my $interactive_sysid = $interactive_test_node->safe_psql('postgres', 
+	"SELECT system_identifier FROM pg_control_system()");
+is($interactive_sysid, '8888888888888888888', 
+	'system identifier changed via interactive confirmation');
+$interactive_test_node->stop;
+
+# Test maximum valid 64-bit value
+my $max_sysid = '18446744073709551615';  # 2^64 - 1
+command_like(
+	[ 'pg_resetwal', '-s' => $max_sysid, '-f', '--dry-run', $node->data_dir ],
+	qr/System identifier:\s+18446744073709551615/,
+	'maximum 64-bit system identifier value accepted');
+
+# Test overflow detection
+command_fails_like(
+	[ 'pg_resetwal', '-s' => '99999999999999999999999999999', $node->data_dir ],
+	qr/error: system identifier value is out of range/,
+	'overflow system identifier value rejected');
+
+# Test hexadecimal input (should fail - only decimal accepted)
+command_fails_like(
+	[ 'pg_resetwal', '-s' => '0x123456789ABCDEF0', $node->data_dir ],
+	qr/error: invalid argument for option -s/,
+	'hexadecimal system identifier input rejected');
+
+# Test leading/trailing whitespace (should fail)
+command_fails_like(
+	[ 'pg_resetwal', '-s' => ' 123456789 ', $node->data_dir ],
+	qr/error: invalid argument for option -s/,
+	'system identifier with whitespace rejected');
+
+# Test empty string
+command_fails_like(
+	[ 'pg_resetwal', '-s' => '', $node->data_dir ],
+	qr/error: invalid argument for option -s/,
+	'empty system identifier rejected');
+
+# Test boundary values
+command_like(
+	[ 'pg_resetwal', '-s' => '1', '-f', '--dry-run', $node->data_dir ],
+	qr/System identifier:\s+1/,
+	'minimum valid system identifier (1) accepted');
+
+# Test very large but valid value
+command_like(
+	[ 'pg_resetwal', '-s' => '9223372036854775807', '-f', '--dry-run', $node->data_dir ],
+	qr/System identifier:\s+9223372036854775807/,
+	'large valid system identifier accepted');
+
+# Test another system identifier change to verify functionality
+my $another_sysid = '5555555555555555555';
+command_ok(
+	[ 'pg_resetwal', '-f', '-s' => $another_sysid, $node->data_dir ],
+	'second system identifier change succeeds');
+
+# Verify the second change
+$node->start;
+my $second_controldata = $node->safe_psql('postgres', 
+	"SELECT system_identifier FROM pg_control_system()");
+is($second_controldata, $another_sysid, 'second system identifier change verified');
+
+$node->stop;
+
 # run with control override options
 
 my $out = (run_command([ 'pg_resetwal', '--dry-run', $node->data_dir ]))[0];
-- 
GitLab