Adding upstream version 258.upstream/258

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 659 insertions, 0 deletions

diff --git a/pg_backupcluster b/pg_backupcluster
new file mode 100755
index 0000000..2959056
--- /dev/null
+++ b/pg_backupcluster
@@ -0,0 +1,659 @@
+#!/usr/bin/perl -wT
+
+# simple pg_basebackup front-end
+#
+# Copyright (C) 2021-2023 Christoph Berg <myon@debian.org>
+#
+#  This program is free software; you can redistribute it and/or modify
+#  it under the terms of the GNU General Public License as published by
+#  the Free Software Foundation; either version 2 of the License, or
+#  (at your option) any later version.
+#
+#  This program is distributed in the hope that it will be useful,
+#  but WITHOUT ANY WARRANTY; without even the implied warranty of
+#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#  GNU General Public License for more details.
+
+use strict;
+use warnings;
+use Fcntl qw(LOCK_EX);
+use Getopt::Long;
+use JSON;
+use PgCommon;
+use POSIX qw(strftime);
+
+my ($version, $cluster);
+
+# untaint environment
+$ENV{'PATH'} = '/sbin:/bin:/usr/sbin:/usr/bin';
+delete @ENV{'IFS', 'CDPATH', 'ENV', 'BASH_ENV'};
+chdir '/';
+umask 027;
+
+sub help () {
+    print "Syntax: $0 [options] <version> <cluster> <action>
+Actions:
+  createdirectory        Create /var/backups/version-cluster
+  basebackup             Backup using pg_basebackup
+  dump                   Backup using pg_dump
+  expiredumps <N>        Remove all but last N dumps
+  expirebasebackups <N>  Remove all but last N basebackups
+  receivewal             Launch pg_receivewal
+  compresswal            Compress WAL files in archive
+  archivecleanup         Remove obsolete WAL files from archive
+  list                   Show dumps, basebackups, and WAL
+Options:
+  -c --checkpoint <spread|fast>  Passed to pg_basebackup
+  -k --keep-on-error     Keep faulty backup directory on error
+  -v --verbose           Verbose output
+";
+}
+
+my $checkpoint = 'spread';
+my $keep_on_error;
+my $verbose;
+
+exit 1 unless GetOptions (
+    'c|checkpoint=s' => sub { $checkpoint = $_[1] =~ /^f/ ? "fast" : "spread" },
+    'k|keep-on-error' => \$keep_on_error,
+    'v|verbose' => \$verbose,
+);
+
+$verbose = 1 if (-t 1); # verbose output when running on terminal
+
+# accept both "version cluster action" and "version[-/]cluster action"
+if (@ARGV >= 2 and $ARGV[0] =~ m!^(\d+\.?\d)[-/]([^/]+)$!) {
+    ($version, $cluster) = ($1, $2);
+    shift @ARGV;
+} elsif (@ARGV >= 3 and $ARGV[0] =~ /^(\d+\.?\d)$/) {
+    $version = $1;
+    ($cluster) = ($ARGV[1]) =~ m!^([^/]+)$!;
+    shift @ARGV;
+    shift @ARGV;
+} else {
+    help();
+    exit 1;
+}
+my $action = $ARGV[0];
+
+error "specified cluster $version $cluster does not exist" unless $version && $cluster && cluster_exists $version, $cluster;
+
+my %info = cluster_info($version, $cluster);
+validate_cluster_owner \%info;
+
+my $rootdir = "/var/backups/postgresql";
+my $clusterdir = "$rootdir/$version-$cluster";
+my $waldir = "$clusterdir/wal";
+
+# functions to be run as root
+
+sub create_directory() {
+    if (! -d $rootdir) {
+        my ($pg_uid, $pg_gid) = (getpwnam 'postgres')[2,3];
+        ($pg_uid and $pg_gid) or error "getpwnam postgres: $!";
+        mkdir $rootdir, 0755 or error "mkdir $rootdir: $!";
+        chown $pg_uid, $pg_gid, $rootdir or error "chown $rootdir: $!";
+    }
+    if (! -d $clusterdir) {
+        mkdir($clusterdir, 0750) or error "mkdir $clusterdir: $!";
+        chown $info{owneruid}, $info{ownergid}, $clusterdir or error "chown $clusterdir: $!";
+    }
+}
+
+sub switch_to_cluster_owner() {
+    change_ugid $info{owneruid}, $info{ownergid};
+}
+
+# backup functions
+
+sub get_backupdir($$) {
+    my ($starttime, $suffix) = @_;
+    my $timestamp = strftime("%FT%H%M%SZ", gmtime($starttime));
+    my $backupdir = "$clusterdir/$timestamp.$suffix";
+    error "$backupdir already exists" if (-d $backupdir);
+    print "Creating $suffix in $backupdir\n" if ($verbose);
+    return $backupdir;
+}
+
+sub remove_backup_on_error($) {
+    my $backupdir = shift;
+    if ($keep_on_error) {
+        print "Not removing $backupdir (--keep-on-error)\n";
+    } else {
+        print "Removing $backupdir ...\n" if ($verbose);
+        system_or_error "rm", "-rf", $backupdir;
+    }
+}
+
+sub create_basebackup($) {
+    my $backupdir = shift;
+
+    system_or_error "pg_basebackup",
+        "--cluster", "$version/$cluster",
+        "--verbose",
+        (-t 2 ? "--progress" : ()),
+        "--checkpoint=$checkpoint",
+        "--format", "tar", "--gzip",
+        ($version < 10 ? "--xlog" : ()),
+        "-D", $backupdir;
+}
+
+sub create_dumpall($) {
+    my $backupdir = shift;
+    mkdir($backupdir, 0750) or error "mkdir $backupdir: $!";
+
+    my $pg16 = $version >= 16 ? "|| ' --icu-rules ' || daticurules" : "";
+    my $pg15 = $version >= 15 ? "CASE datlocprovider::text
+        WHEN 'c' THEN '--locale-provider libc'
+        WHEN 'i' THEN '--locale-provider icu --icu-locale ' || daticulocale $pg16
+      END," : "";
+    my $clusterquery = "SELECT
+  '--encoding', pg_catalog.pg_encoding_to_char(encoding),
+  '--lc-collate', datcollate,
+  '--lc-ctype', datctype,
+  $pg15
+  CASE WHEN current_setting('data_checksums')::boolean THEN '-- --data-checksums' END
+FROM pg_database WHERE datname = 'template0'";
+    system_or_error "psql",
+        "--cluster", "$version/$cluster",
+        "-XAtF", " ", "-c", $clusterquery,
+        "-o", "$backupdir/createcluster.opts";
+
+    system_or_error "pg_dumpall",
+        "--cluster", "$version/$cluster",
+        "--globals-only",
+        "--file", "$backupdir/globals.sql";
+
+    $pg16 = $version >= 16 ? "|| ' ICU_RULES ' || quote_literal(coalesce(daticurules, ''))" : "";
+    $pg15 = $version >= 15 ? "|| CASE datlocprovider::text
+        WHEN 'c' THEN 'LOCALE_PROVIDER libc'
+        WHEN 'i' THEN 'LOCALE_PROVIDER icu ICU_LOCALE ' || quote_literal(daticulocale) $pg16
+      END" : "";
+    my $dbquery = "/* database creation */
+SELECT
+  format('CREATE DATABASE %I WITH OWNER %I TEMPLATE template0 ENCODING %L LC_COLLATE %L LC_CTYPE %L',
+    datname, rolname, pg_encoding_to_char(encoding), datcollate, datctype) $pg15 || ';' AS command
+FROM pg_database
+LEFT JOIN pg_roles r ON r.oid = datdba
+WHERE datallowconn AND datname NOT IN ('postgres', 'template1')
+
+UNION ALL
+
+/* database options */
+SELECT
+  CASE
+    WHEN rolname IS NULL THEN format('ALTER DATABASE %I', datname)
+    ELSE format('ALTER ROLE %I IN DATABASE %I', rolname, datname)
+  END ||
+  format(' SET %I = ', match[1]) ||
+  CASE
+    WHEN match[1] IN ('local_preload_libraries', 'search_path', 'session_preload_libraries', 'shared_preload_libraries', 'temp_tablespaces', 'unix_socket_directories')
+    THEN match[2]
+    ELSE quote_literal(match[2])
+  END ||
+  ';' AS command
+FROM pg_db_role_setting s
+JOIN pg_database d ON d.oid = setdatabase
+LEFT JOIN pg_roles r ON r.oid = setrole,
+unnest(setconfig) u(setting),
+regexp_matches(setting, '(.+)=(.+)') m(match)";
+    system_or_error "psql",
+        "--cluster", "$version/$cluster",
+        "-XAtc", $dbquery,
+        "-o", "$backupdir/databases.sql";
+
+    my $dblist = 'SELECT datname FROM pg_database WHERE datallowconn ORDER BY datname';
+    my $databases = `psql --cluster '$version/$cluster' -XAtc '$dblist'`;
+    for my $datname ($databases =~ /(.+)/g) {
+        print "Dumping $datname to $backupdir/$datname.dump ...\n" if ($verbose);
+        system_or_error "pg_dump",
+            "--cluster", "$version/$cluster",
+            "--format=custom",
+            "--file", "$backupdir/$datname.dump",
+            $datname;
+    }
+}
+
+sub create_configbackup($) {
+    my $backupdir = shift;
+    $info{configdir} or error "cluster has no configdir";
+    system_or_error "tar",
+        "-C", $info{configdir},
+        "-cz", "-f", "$backupdir/config.tar.gz",
+        ".";
+}
+
+sub create_status($$$$) {
+    my ($type, $starttime, $backupdir, $status) = @_;
+    my $statusfile = "$backupdir/status";
+    my $endtime = time;
+    my $statusjson = {
+        cluster => $cluster,
+        duration => $endtime - $starttime,
+        end => strftime("%FT%H%M%SZ", gmtime($endtime)),
+        start => strftime("%FT%H%M%SZ", gmtime($starttime)),
+        status => $status,
+        type => $type,
+        version => $version,
+    };
+    if (my $hostname = `hostname`) {
+        chomp $hostname;
+        $statusjson->{hostname} = $hostname;
+    }
+    if (-e '/etc/machine-id') {
+        open my $fh, '/etc/machine-id';
+        my $machine_id = <$fh>;
+        close $fh;
+        if ($machine_id) {
+            chomp $machine_id;
+            $statusjson->{'machine-id'} = $machine_id;
+        }
+    }
+    if (-e '/etc/machine-info') {
+        open my $fh, '/etc/machine-info';
+        while (<$fh>) {
+            if (/^DEPLOYMENT=(.*)/) {
+                $statusjson->{'machine-deployment'} = $1;
+            }
+        }
+        close $fh;
+    }
+
+    my $json = JSON->new->canonical;
+    open F, '>', $statusfile or error "$statusfile: $!";
+    print F $json->encode($statusjson) . "\n" or error "$statusfile: $!";
+    close F or error "$$statusfile: $!";
+}
+
+sub sync($) {
+    my $backupdir = shift;
+    system_or_error "sync $backupdir/*";
+}
+
+sub expire_backups($$) {
+    my ($suffix, $number) = @_;
+    my @backups = glob("$clusterdir/*.$suffix");
+    my $found = 0;
+    for my $backup (reverse @backups) {
+        # iterate reversely over backups until we have found enough valid ones
+        if ($found >= $number) {
+            print "Removing $backup ...\n" if ($verbose);
+            $backup =~ /(.*)/; # untaint
+            system_or_error "rm", "-rf", $1;
+        } else {
+            if (-f "$backup/status") {
+                $found++;
+            }
+        }
+    }
+}
+
+sub delete_broken() {
+    my @backups = (glob("$clusterdir/*.backup"), glob("$clusterdir/*.dump"));
+    for my $backup (@backups) {
+        if (! -f "$backup/status") {
+            print "Removing $backup ...\n" if ($verbose);
+            $backup =~ /(.*)/; # untaint
+            system_or_error "rm", "-rf", $1;
+        }
+    }
+}
+
+# wal handling
+
+sub create_wal_directory() {
+    if (! -d $waldir) {
+        mkdir($waldir, 0750) or error "mkdir $waldir: $!";
+    }
+}
+
+sub receivewal() {
+    my $pg_receivewal = $version >= 10 ? 'pg_receivewal' : 'pg_receivexlog';
+
+    # create slot
+    system_or_error $pg_receivewal, "--cluster=$version/$cluster", "--slot", "pg_receivewal_service", "--create-slot", "--if-not-exists";
+
+    # launch pg_receivewal
+    $ENV{PGAPPNAME} = "pg_receivewal\@$version-$cluster.service";
+    my @cmd = ($pg_receivewal, "--cluster", "$version/$cluster",
+        "-D", $waldir, "--slot", "pg_receivewal_service");
+    push @cmd, "--compress=5" if ($version >= 10);
+    exec {$pg_receivewal} @cmd or error "exec $pg_receivewal: $!";
+}
+
+sub compresswal() {
+    chdir $waldir or return; # ok if not yet created
+    open my $lock, $waldir or error "open $waldir: $!"; # protect against concurrent runs
+    flock $lock, LOCK_EX or error "flock $waldir: $!";
+
+    for my $wal (glob "0???????????????????????") {
+        $wal =~ /^([0-9A-F]+)$/ or continue;
+        $wal = $1; # untaint
+        if (-f "$wal.gz") {
+            print STDERR "$waldir/$wal.gz already exists, skipping compression\n";
+            next;
+        }
+        system_or_error "if ! gzip < $wal > $wal.tmp.gz; then rm -f $wal.tmp.gz; exit 1; fi";
+        system_or_error "touch --reference=$wal $wal.tmp.gz";
+        system_or_error "mv $wal.tmp.gz $wal.gz";
+        system_or_error "sync", "$wal.gz";
+        unlink $wal;
+    }
+
+    close $lock;
+}
+
+sub archivecleanup() {
+    chdir $waldir or return; # ok if not yet created
+    my @backups = sort glob "$clusterdir/*.backup";
+    for my $backup (@backups) {
+        next unless (-f "$backup/status");
+
+        $backup =~ /(.*)/; # untaint
+        my $basetar = "$1/base.tar.gz";
+        my $backup_label = `tar --extract --occurrence=1 --to-stdout --file '$basetar' backup_label` or error "failed to extract backup_label from $basetar";
+
+        # START WAL LOCATION: 0/2B000028 (file 00000001000000000000002B)
+        $backup_label =~ /^START WAL LOCATION: .* \(file ([0-9A-F]+)\)/ or error "no start wal location in $basetar";
+        my $keep_file = $1;
+        system_or_error "pg_archivecleanup", "-x", ".gz", $waldir, $keep_file;
+
+        return 0; # process first backup only
+    }
+    error "no valid basebackups found in $clusterdir";
+}
+
+# info functions
+
+sub dirsize($) {
+    my $dir = shift;
+    my $size = 0;
+    my $files = 0;
+    for my $f (glob "$dir/*") {
+        $size += (stat $f)[7];
+        $files++;
+    }
+    return $size, $files;
+}
+
+sub list() {
+    print "Cluster $version $cluster backups in $clusterdir:\n";
+    my $totalsize = 0;
+    print "Dumps:\n";
+    for my $dir (sort glob "$clusterdir/*.dump") {
+        my ($size, $files) = dirsize($dir);
+        my $status = -f "$dir/status" ? '' : ' BROKEN';
+        print "  $dir: $size Bytes$status\n";
+        $totalsize += $size;
+    }
+    print "Basebackups:\n";
+    for my $dir (sort glob "$clusterdir/*.backup") {
+        my ($size, $files) = dirsize($dir);
+        my $status = -f "$dir/status" ? '' : ' BROKEN';
+        print "  $dir: $size Bytes$status\n";
+        $totalsize += $size;
+    }
+    if (-d "$clusterdir/wal") {
+        print "WAL:\n";
+        my ($size, $files) = dirsize("$clusterdir/wal");
+        print "  $clusterdir/wal: $size Bytes, $files Files\n";
+        $totalsize += $size;
+    }
+    print "Total: $totalsize Bytes\n";
+}
+
+# main
+
+if ($action eq 'createdirectory') {
+    create_directory();
+
+} elsif ($action eq 'basebackup') {
+    error "basebackups of pre-9.1 servers are not supported" if ($version < 9.1);
+    my $starttime = time;
+    create_directory();
+    switch_to_cluster_owner();
+    my $backupdir = get_backupdir($starttime, 'backup');
+    $SIG{__DIE__} = sub { remove_backup_on_error($backupdir) };
+    create_basebackup($backupdir);
+    create_configbackup($backupdir);
+    create_status($action, $starttime, $backupdir, 'ok');
+    $SIG{__DIE__} = undef;
+    sync($backupdir);
+    compresswal();
+
+} elsif ($action eq 'dump') {
+    error "dumps of pre-9.3 servers are not supported" if ($version < 9.3);
+    my $starttime = time;
+    create_directory();
+    switch_to_cluster_owner();
+    my $backupdir = get_backupdir($starttime, 'dump');
+    $SIG{__DIE__} = sub { remove_backup_on_error($backupdir) };
+    create_dumpall($backupdir);
+    create_configbackup($backupdir);
+    create_status($action, $starttime, $backupdir, 'ok');
+    $SIG{__DIE__} = undef;
+    sync($backupdir);
+
+} elsif ($action eq 'expiredumps' and @ARGV == 2 and $ARGV[1] =~ /^(\d+)$/) {
+    switch_to_cluster_owner();
+    expire_backups('dump', $1);
+
+} elsif ($action eq 'expirebasebackups' and @ARGV == 2 and $ARGV[1] =~ /^(\d+)$/) {
+    switch_to_cluster_owner();
+    expire_backups('backup', $1);
+    archivecleanup();
+    compresswal();
+
+} elsif ($action eq 'deletebroken') {
+    switch_to_cluster_owner();
+    delete_broken();
+
+} elsif ($action eq 'receivewal') {
+    create_directory();
+    switch_to_cluster_owner();
+    create_wal_directory();
+    compresswal();
+    receivewal();
+
+} elsif ($action eq 'compresswal') {
+    switch_to_cluster_owner();
+    compresswal();
+
+} elsif ($action eq 'archivecleanup') {
+    switch_to_cluster_owner();
+    archivecleanup();
+    compresswal();
+
+} elsif ($action eq 'list') {
+    switch_to_cluster_owner();
+    list();
+
+} else {
+    help();
+    exit(1);
+}
+
+__END__
+
+=head1 NAME
+
+pg_backupcluster - simple pg_basebackup and pg_dump front-end
+
+=head1 SYNOPSIS
+
+B<pg_backupcluster> [I<options>] I<version> I<cluster> I<action>
+
+=head1 DESCRIPTION
+
+B<pg_backupcluster> provides a simple interface to create PostgreSQL cluster
+backups using L<pg_basebackup(1)> and L<pg_dump(1)>.
+
+To ease integration with B<systemd> operation, the alternative syntax
+"B<pg_basebackup> I<version>B<->I<cluster> I<action>" is also supported.
+
+=head1 ACTIONS
+
+=over 4
+
+=item B<createdirectory>
+
+Create /var/backups and /var/backups/I<version>-I<cluster>.
+This action can be run as root to create the directories required for backups.
+All other actions will also attempt to create the directories when missing, but
+can of course only do that when running as root. They will switch to the
+cluster owner after this step.
+
+=item B<basebackup>
+
+Backup using L<pg_basebackup(1)>. The resulting basebackup contains the WAL
+files required to run recovery on startup.
+
+=item B<dump>
+
+Backup using L<pg_dump(1)>. Global objects (users, tablespaces) are dumped
+using L<pg_dumpall(1)> B<--globals-only>. Individual databases are dumped into
+PostgreSQL's custom format.
+
+=item B<expirebasebackups> I<N>
+
+Remove all but last the I<N> basebackups.
+
+=item B<expiredumps> I<N>
+
+Remove all but last the I<N> dumps.
+
+=item B<receivewal>
+
+Launch pg_receivewal. WAL files are gzip-compressed in PG 10+.
+
+=item B<compresswal>
+
+Compress WAL files in archive.
+
+=item B<archivecleanup>
+
+Remove obsolete WAL files from archive using L<pg_archivecleanup(1)>.
+
+=item B<list>
+
+Show dumps, basebackups, and WAL, with size.
+
+=back
+
+=head1 OPTIONS
+
+=over 4
+
+=item B<-c --checkpoint=spread|fast>
+
+Passed to B<pg_basebackup>. Default is B<spread>.
+
+=item B<-k --keep-on-error>
+
+Keep faulty backup directory on error. By default backups are delete on error.
+
+=item B<-v --verbose>
+
+Verbose output, even when not running on a terminal.
+
+=back
+
+=head1 FILES
+
+=over 4
+
+=item /var/backups
+
+Default root directory for cluster backup directories.
+
+=item /var/backups/I<version>-I<cluster>
+
+Default directory for cluster backups.
+
+=item /var/backups/I<version>-I<cluster>/I<timestamp>B<.basebackup>
+
+Backup from B<pg_backupcluster ... basebackup>.
+
+=over 4
+
+=item C<config.tar.gz>
+
+Tarball of cluster configuration directory (postgresql.conf, pg_hba.conf, ...)
+in /etc/postgresql.
+
+=item I<tablespace>C<.tar.gz>, C<pg_wal.tar.gz>, C<backup_manifest>
+
+Tablespace and WAL tarballs and backup info written by B<pg_basebackup>.
+
+=item C<status>
+
+Completion timestamp of backup run.
+
+=back
+
+=item /var/backups/I<version>-I<cluster>/I<timestamp>B<.dump>
+
+Backup from B<pg_backupcluster ... dump>.
+
+=over 4
+
+=item C<config.tar.gz>
+
+Tarball of cluster configuration directory (postgresql.conf, pg_hba.conf, ...)
+in /etc/postgresql.
+
+=item C<createcluster.opts>
+
+Options (encoding, locale, data checksums) to be passed to B<pg_createcluster>
+for restoring this cluster.
+
+=item C<globals.sql>
+
+Global objects (roles, tablespaces) from B<pg_dumpall --globals-only>.
+
+=item C<databases.sql>
+
+SQL commands to create databases and restore database-level options.
+
+=item I<database>C<.dump>
+
+Database dumps from B<pg_dump --format=custom>.
+
+=item C<status>
+
+Completion timestamp of backup run.
+
+=back
+
+=item /var/backups/I<version>-I<cluster>/B<wal>
+
+WAL files from B<pg_receivewal>.
+
+=back
+
+=head1 CAVEATS
+
+For dump-style backups, not all properties of the original cluster are preserved:
+
+=over 2
+
+=item * In PostgreSQL 10 and earlier, ALTER ROLE ... IN DATABASE is not supported.
+
+=item * Not all B<initdb> options are carried over. Currently supported are B<--encoding>,
+B<--lc-collate>, B<--lc-collate>, and B<-k --data-checksums>.
+
+=back
+
+The earliest PostgreSQL version supported for dumps is 9.3.
+For basebackups, the earliest supported version is 9.1.
+B<receivewal> (and hence archive recovery) are supported in 9.5 and later.
+
+=head1 SEE ALSO
+
+L<pg_restorecluster(1)>,
+L<pg_dump(1)>, L<pg_dumpall(1)>,
+L<pg_basebackup(1)>, L<pg_receivewal(1)>, L<pg_archivecleanup(1)>.
+
+=head1 AUTHOR
+
+Christoph Berg L<E<lt>myon@debian.orgE<gt>>