Re: Proposal: manipulating pg_control file from Perl

[Dagfinn Ilmari Mannsåker]

Aleksander Alekseev <aleksan...@timescale.com> writes:

> Hi Dagfinn,
>
>> Notwitstanding Tom's objections, these are not reasons for not being
>> able to manipulate these values from Perl.  The `i` and `I` formats for
>> pack/unpack (see https://perldoc.perl.org/functions/pack) use what the C
>> compiler calls `int`, in terms of both endianness and size.
>>
>> > For named reasons, manipulating pg_upgrade from Perl is impractical,
>> > considering the number of environments we support.
>> >
>> > I see two possible solutions:
>> >
>> > 1) Provide a tool written in C that allows changing pg_control, e.g.
>> > `pg_writecontoldata` or maybe a flat like `pg_controldata -w`. The
>> > tool can be executed from Perl, so it shouldn't know about
>> > sizeof(int), alignment and endiness.
>>
>> 1.5) Use Perl's pack/unpack functions, which are explicitly desgined for
>>      exactly this use case.
>
> Thanks for your reply.
>
> It is my understanding that Perl is not extremely aware of alignment.
> For instance if I want to modify the checksum of the file and the
> offset of the checksum is let's say 200 bytes on one platform, 204 on
> another and 208 on a third, pack/unpack will not help me.
>
> Or did I miss something?

You have to specify alignment manually using the `x![$type]` notation.
I actually wrote up a pack template for the entire control file
(assuming all types have alignof==sizeof), and it seems to work.  Run
the attached with a Postgres 17 data directory as the arguemnt, and it
should show all the fields.

- ilmari


use v5.14;
use strict;
use warnings FATAL => 'all';

use File::Spec;
use List::Util qw(max pairmap mesh);

# pack templates for C types
my $XLogRecPtr = 'x![Q]Q';
my $TimeLineID = 'x![L]L';
my $FullTransactionId = 'x![Q]Q';
my $MultiXactId = 'x![L]L';
my $TransactionId = 'x![L]L';
my $MultiXactOffset = 'x![L]L';
my $DBState = 'x![i]i';
my $pg_time_t = 'x![q]q';
my $Oid = 'x![I]I';
my $bool = 'x![C]C';
my $int = 'x![i]i';
my $uint64 = 'x![Q]Q';
my $uint32 = 'x![L]L';
my $int32 = 'x![l]l';
my $double = 'x![d]d';
my $char = 'x![a]a';
my $pg_crc32c = 'x![L]L';

my $CheckPoint = qq{
	$XLogRecPtr			# redo
	$TimeLineID			# ThisTimeLineID
	$TimeLineID			# PrevTimeLineID
	$bool				# fullPageWrites
	$int				# wal_level
	$FullTransactionId	# nextXid
	$Oid				# nextOid
	$MultiXactId		# nextMulti
	$MultiXactOffset	# nextMultiOffset
	$TransactionId		# oldestXid
	$Oid				# oldestXidDB
	$MultiXactId		# oldestMulti
	$Oid				# oldestMultiDB
	$pg_time_t			# time
	$TransactionId		# oldestCommitTsXid
	$TransactionId		# newestCommitTsXid
	$TransactionId		# oldestActiveXid
};

my @CheckPointField = qw{
	redo
	ThisTimeLineID
	PrevTimeLineID
	fullPageWrites
	wal_level
	nextXid
	nextOid
	nextMulti
	nextMultiOffset
	oldestXid
	oldestXidDB
	oldestMulti
	oldestMultiDB
	time
	oldestCommitTsXid
	newestCommitTsXid
	oldestActiveXid
};

my $ControlFileData = qq{
	$uint64		# system_identifier
	$uint32		# pg_control_version
	$uint32		# catalog_version_no
	$DBState	# state
	$pg_time_t	# time
	$XLogRecPtr	# checkPoint
	$CheckPoint	# checkPointCopy
	$XLogRecPtr	# unloggedLSN
	$XLogRecPtr	# minRecoveryPoint
	$TimeLineID	# minRecoveryPointTLI
	$XLogRecPtr	# backupStartPoint
	$XLogRecPtr	# backupEndPoint
	$bool		# backupEndRequired
	$int		# wal_level
	$bool		# wal_log_hints
	$int		# MaxConnections
	$int		# max_worker_processes
	$int		# max_wal_senders
	$int		# max_prepared_xacts
	$int		# max_locks_per_xact
	$bool		# track_commit_timestamp
	$uint32		# maxAlign
	$double		# floatFormat
	$uint32		# blcksz
	$uint32		# relseg_size
	$uint32		# xlog_blcksz
	$uint32		# xlog_seg_size
	$uint32		# nameDataLen
	$uint32		# indexMaxKeys
	$uint32		# toast_max_chunk_size
	$uint32		# loblksize
	$bool		# float8ByVal
	$uint32		# data_checksum_version
	${char}32	# mock_authentication_nonce[MOCK_AUTH_NONCE_LEN]
	$pg_crc32c	# crc
};

my @ControlFileDataField = (qw{
	system_identifier
	pg_control_version
	catalog_version_no
	state
	time
	checkPoint},
	@CheckPointField,
	qw{
	unloggedLSN
	minRecoveryPoint
	minRecoveryPointTLI
	backupStartPoint
	backupEndPoint
	backupEndRequired
	wal_level
	wal_log_hints
	MaxConnections
	max_worker_processes
	max_wal_senders
	max_prepared_xacts
	max_locks_per_xact
	track_commit_timestamp
	maxAlign
	floatFormat
	blcksz
	relseg_size
	xlog_blcksz
	xlog_seg_size
	nameDataLen
	indexMaxKeys
	toast_max_chunk_size
	loblksize
	float8ByVal
	data_checksum_version
	mock_authentication_nonce
	crc
});
my $MaxFieldLen = max map length, @ControlFileDataField;

my $pgdata = shift or die "Usage $0 <pgdata>\n";
my $controlfile = File::Spec->catfile($pgdata, qw(global pg_control));

my $control_file_data = do {
	local $/;
	open my $fh, '<:raw', $controlfile
		or die "Can't open $controlfile: $!\n";
	<$fh>;
} or die "Can't read $controlfile: $!\n";

my @control_file_data = unpack $ControlFileData, $control_file_data;

for my ($i) (0..$#control_file_data) {
	printf "%-*s %s\n",
		$MaxFieldLen+1,
		$ControlFileDataField[$i].':',
		$control_file_data[$i];
}

my %ControlData = mesh \@ControlFileDataField, \@control_file_data;
say "last checkpoint: ", scalar gmtime $ControlData{time};