Hi
Thank you all for the helpful tips. Many thanks to Alex Orlov for the test. He works.
I made a second version of the patch and took into account all the comments.
Patch description: When checking a new cluster, the value of the track_commit_timestamp parameter is determined.
If this parameter is set and the Latest checkpoint's oldestCommitTsXid and Latest checkpoint's newestCommitTsXid are set in the old cluster, then pg_comit_ts is copied.
The files were processed by pgindent.
From 24a8a631313a236a745d48dd4bd5e40f413de7e0 Mon Sep 17 00:00:00 2001 From: Sergey Levin <[email protected]> Date: Sat, 4 Oct 2025 20:22:46 +0500 Subject: [PATCH] Migration of the pg_commit_ts directory
---
src/bin/pg_upgrade/check.c | 2 +
src/bin/pg_upgrade/controldata.c | 26 +++-
src/bin/pg_upgrade/info.c | 21 +++
src/bin/pg_upgrade/pg_upgrade.c | 21 ++-
src/bin/pg_upgrade/pg_upgrade.h | 5 +
.../pg_upgrade/t/007_transfer_commit_ts.pl | 129 ++++++++++++++++++
6 files changed, 200 insertions(+), 4 deletions(-)
create mode 100644 src/bin/pg_upgrade/t/007_transfer_commit_ts.pl
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 1e17d64b3ec..b17603d11bb 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -767,6 +767,8 @@ check_new_cluster(void)
check_new_cluster_replication_slots();
check_new_cluster_subscription_configuration();
+
+ check_track_commit_timestamp_parameter(&new_cluster);
}
diff --git a/src/bin/pg_upgrade/controldata.c b/src/bin/pg_upgrade/controldata.c
index 90cef0864de..b218ed92389 100644
--- a/src/bin/pg_upgrade/controldata.c
+++ b/src/bin/pg_upgrade/controldata.c
@@ -208,7 +208,11 @@ get_control_data(ClusterInfo *cluster)
cluster->controldata.data_checksum_version = 0;
got_data_checksum_version = true;
}
-
+ if (GET_MAJOR_VERSION(cluster->major_version) < 905)
+ {
+ cluster->controldata.chkpnt_oldstCommitTsxid = 0;
+ cluster->controldata.chkpnt_newstCommitTsxid = 0;
+ }
/* we have the result of cmd in "output". so parse it line by line now */
while (fgets(bufin, sizeof(bufin), output))
{
@@ -321,6 +325,26 @@ get_control_data(ClusterInfo *cluster)
cluster->controldata.chkpnt_nxtmulti = str2uint(p);
got_multi = true;
}
+ else if ((p = strstr(bufin, "Latest checkpoint's oldestCommitTsXid:")) != NULL)
+ {
+ p = strchr(p, ':');
+
+ if (p == NULL || strlen(p) <= 1)
+ pg_fatal("%d: controldata retrieval problem", __LINE__);
+
+ p++; /* remove ':' char */
+ cluster->controldata.chkpnt_oldstCommitTsxid = str2uint(p);
+ }
+ else if ((p = strstr(bufin, "Latest checkpoint's newestCommitTsXid:")) != NULL)
+ {
+ p = strchr(p, ':');
+
+ if (p == NULL || strlen(p) <= 1)
+ pg_fatal("%d: controldata retrieval problem", __LINE__);
+
+ p++; /* remove ':' char */
+ cluster->controldata.chkpnt_newstCommitTsxid = str2uint(p);
+ }
else if ((p = strstr(bufin, "Latest checkpoint's oldestXID:")) != NULL)
{
p = strchr(p, ':');
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index 7ce08270168..2428c52d0e9 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -819,6 +819,27 @@ get_subscription_info(ClusterInfo *cluster)
PQfinish(conn);
}
+/*
+ * check_track_commit_timestamp_parameter(ClusterInfo *cluster)
+ *
+ * Gets track_commit_timestamp parameter in the cluster.
+ */
+void
+check_track_commit_timestamp_parameter(ClusterInfo *cluster)
+{
+ PGconn *conn;
+ PGresult *res;
+ int is_set;
+
+ conn = connectToServer(cluster, "template1");
+ res = executeQueryOrDie(conn, "SELECT count(*) AS is_set "
+ "FROM pg_settings WHERE name = 'track_commit_timestamp' and setting = 'on'");
+ is_set = PQfnumber(res, "is_set");
+ cluster->track_commit_timestamp_on = atoi(PQgetvalue(res, 0, is_set)) == 1;
+
+ PQclear(res);
+ PQfinish(conn);
+}
static void
free_db_and_rel_infos(DbInfoArr *db_arr)
{
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 490e98fa26f..dc91da78d9b 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -772,6 +772,8 @@ copy_subdir_files(const char *old_subdir, const char *new_subdir)
static void
copy_xact_xlog_xid(void)
{
+ bool is_copy_commit_ts;
+
/*
* Copy old commit logs to new data dir. pg_clog has been renamed to
* pg_xact in post-10 clusters.
@@ -781,6 +783,16 @@ copy_xact_xlog_xid(void)
GET_MAJOR_VERSION(new_cluster.major_version) <= 906 ?
"pg_clog" : "pg_xact");
+ /*
+ * Copy pg_commit_ts only if three conditions are met
+ */
+ is_copy_commit_ts = old_cluster.controldata.chkpnt_oldstCommitTsxid > 0
+ && old_cluster.controldata.chkpnt_newstCommitTsxid > 0
+ && new_cluster.track_commit_timestamp_on;
+
+ if (is_copy_commit_ts)
+ copy_subdir_files("pg_commit_ts", "pg_commit_ts");
+
prep_status("Setting oldest XID for new cluster");
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/pg_resetwal\" -f -u %u \"%s\"",
@@ -798,12 +810,15 @@ copy_xact_xlog_xid(void)
"\"%s/pg_resetwal\" -f -e %u \"%s\"",
new_cluster.bindir, old_cluster.controldata.chkpnt_nxtepoch,
new_cluster.pgdata);
- /* must reset commit timestamp limits also */
+
+ /*
+ * must reset commit timestamp limits also or copy from the old cluster
+ */
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/pg_resetwal\" -f -c %u,%u \"%s\"",
new_cluster.bindir,
- old_cluster.controldata.chkpnt_nxtxid,
- old_cluster.controldata.chkpnt_nxtxid,
+ is_copy_commit_ts ? old_cluster.controldata.chkpnt_oldstCommitTsxid : old_cluster.controldata.chkpnt_nxtxid,
+ is_copy_commit_ts ? old_cluster.controldata.chkpnt_newstCommitTsxid : old_cluster.controldata.chkpnt_nxtxid,
new_cluster.pgdata);
check_ok();
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 0ef47be0dc1..4bbfdfa881e 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -238,6 +238,8 @@ typedef struct
uint32 chkpnt_nxtmxoff;
uint32 chkpnt_oldstMulti;
uint32 chkpnt_oldstxid;
+ uint32 chkpnt_oldstCommitTsxid;
+ uint32 chkpnt_newstCommitTsxid;
uint32 align;
uint32 blocksz;
uint32 largesz;
@@ -306,6 +308,8 @@ typedef struct
int nsubs; /* number of subscriptions */
bool sub_retain_dead_tuples; /* whether a subscription enables
* retain_dead_tuples. */
+ bool track_commit_timestamp_on; /* track_commit_timestamp for
+ * new cluster */
} ClusterInfo;
@@ -444,6 +448,7 @@ FileNameMap *gen_db_file_maps(DbInfo *old_db,
void get_db_rel_and_slot_infos(ClusterInfo *cluster);
int count_old_cluster_logical_slots(void);
void get_subscription_info(ClusterInfo *cluster);
+void check_track_commit_timestamp_parameter(ClusterInfo *cluster);
/* option.c */
diff --git a/src/bin/pg_upgrade/t/007_transfer_commit_ts.pl b/src/bin/pg_upgrade/t/007_transfer_commit_ts.pl
new file mode 100644
index 00000000000..7e77189bafc
--- /dev/null
+++ b/src/bin/pg_upgrade/t/007_transfer_commit_ts.pl
@@ -0,0 +1,129 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Tests for transfer pg_commit_ts directory.
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+sub command_output
+{
+ my ($cmd) = @_;
+ my ($stdout, $stderr);
+
+ print("# Running: " . join(" ", @{$cmd}) . "\n");
+
+ my $result = IPC::Run::run $cmd, '>', \$stdout, '2>', \$stderr;
+ ok($result, "@$cmd exit code 0");
+ is($stderr, '', "@$cmd no stderr");
+
+ return $stdout;
+}
+
+# Can be changed to test the other modes
+my $mode = $ENV{PG_TEST_PG_UPGRADE_MODE} || '--copy';
+
+# Initialize old cluster
+my $old = PostgreSQL::Test::Cluster->new('old');
+$old->init;
+$old->append_conf('postgresql.conf', 'track_commit_timestamp = on');
+$old->start;
+$old->command_ok([ 'pgbench', '-i', '-s', '1' ], 'init pgbench');
+$old->command_ok([ 'pgbench', '-t', '100', '-j', '2' ], 'pgbench it');
+$old->stop;
+
+# Initialize new cluster
+my $new = PostgreSQL::Test::Cluster->new('new');
+$new->init;
+$new->append_conf('postgresql.conf', 'track_commit_timestamp = on');
+
+command_ok(
+ [
+ 'pg_upgrade', '--no-sync',
+ '-d', $old->data_dir,
+ '-D', $new->data_dir,
+ '-b', $old->config_data('--bindir'),
+ '-B', $new->config_data('--bindir'),
+ '-s', $new->host,
+ '-p', $old->port,
+ '-P', $new->port,
+ $mode
+ ],
+ 'run of pg_upgrade');
+
+$new->start;
+$new->command_ok([ 'pgbench', '-t', '10' ], 'pgbench it');
+$new->stop;
+
+sub xact_commit_ts
+{
+ my ($node) = @_;
+ my ($oldest, $newest);
+ my $out = command_output([ 'pg_controldata', '-D', $node->data_dir ]);
+
+ if ($out =~ /oldestCommitTsXid:(\d+)/) {
+ $oldest = $1;
+ }
+ if ($out =~ /newestCommitTsXid:(\d+)/) {
+ $newest= $1;
+ }
+
+ for my $line (grep { /\S/ } split /\n/, $out) {
+ if ($line =~ /Latest checkpoint's NextXID:/) {
+ print $line . "\n";
+ }
+ if ($line =~ /Latest checkpoint's oldestXID:/) {
+ print $line . "\n";
+ }
+ if ($line =~ /Latest checkpoint's oldestCommitTsXid/) {
+ print $line . "\n";
+ }
+ if ($line =~ /Latest checkpoint's newestCommitTsXid:/) {
+ print $line . "\n";
+ }
+ }
+
+ $node->start;
+ my $res = $node->safe_psql('postgres', "
+ WITH xids AS (
+ SELECT v::text::xid AS x
+ FROM generate_series($oldest, $newest) v
+ )
+ SELECT x, pg_xact_commit_timestamp(x::text::xid)
+ FROM xids;
+ ");
+ $node->stop;
+ return grep { /\S/ } split /\n/, $res;
+}
+
+my @a = xact_commit_ts($old);
+my @b = xact_commit_ts($new);
+my %h1 = map { $_ => 1 } @a;
+my %h2 = map { $_ => 1 } @b;
+
+#
+# All timestamps from the old cluster should appear in the new one.
+#
+my $count = 0;
+print "Commit timestamp only in old cluster:\n";
+for my $line (@a) {
+ unless ($h2{$line}) {
+ print "$line\n";
+ $count = $count + 1;
+ }
+}
+ok($count == 0, "all the timestamp transferred successfully");
+
+#
+# The new cluster should contain timestamps created during the pg_upgrade and
+# some more created by the pgbench.
+#
+print "\nCommit timestamp only in new cluster:\n";
+for my $line (@b) {
+ print "$line\n" unless $h1{$line};
+}
+
+done_testing();
--
2.42.2
