Backup

確定 PostgreSQL 數據庫上次更改的時間

  • March 31, 2017

我正在考慮更改備份的完成方式，並且想知道是否有辦法確定 postgreql 集群中的哪些數據庫最近沒有更改？

而不是使用 pg_dumpall，我想使用 pg_dump 並且只轉儲自上次備份以來已更改的那些數據庫（某些數據庫不會經常更新）——這個想法是，如果沒有任何變化，那麼目前備份應該還是不錯的。

有誰知道確定特定數據庫上次更新/更改時間的方法？

謝謝…

更新：

我希望不必在整個地方編寫觸發器，因為我無法控制在一個特定集群中創建數據庫（更不用說在數據庫中創建 db 對象了）。

進一步探勘，看起來 $ PGDATA/global/pg_database file (specifically the second field) and the directory names under $ PGD​​ATA/基地。

走出去，我猜 pg_database 文件的第二個欄位是數據庫 oid 並且每個數據庫都有自己的子目錄 $ PGDATA/base (with the oid for the subdirectory name). Is that correct? If so, is it reasonable to use the file timestamps from the files under $ PGD​​ATA/base/* 作為需要備份的觸發器？

…或者，還有更好的方法？

再次感謝…

雖然select datname, xact_commit from pg_stat_database;按照@Jack Douglas 的建議使用並不能很好地工作（顯然是由於 autovacuum），select datname, tup_inserted, tup_updated, tup_deleted from pg_stat_database但似乎確實有效。DML 和 DDL 更改都會更改 tup_* 列的值，而 avacuum不會（vacuum analyze另一方面…）。

萬一這可能對其他人有用，我將包括我已經放置的備份腳本。這適用於 Pg 8.4.x 但不適用於 8.2.x– YMMV，具體取決於所使用的 Pg 版本。

#!/usr/bin/env perl
=head1 Synopsis

pg_backup -- selectively backup a postgresql database cluster

=head1 Description

Perform backups (pg_dump*) of postgresql databases in a cluster on an
as needed basis.

For some database clusters, there may be databases that are:

a. rarely updated/changed and therefore shouldn't require dumping as 
   often as those databases that are frequently changed/updated.

b. are large enough that dumping them without need is undesirable.

The global data is always dumped without regard to whether any 
individual databses need backing up or not.

=head1 Usage

pg_backup [OPTION]...

General options:

 -F, --format=c|t|p    output file format for data dumps 
                         (custom, tar, plain text) (default is custom)
 -a, --all             backup (pg_dump) all databases in the cluster 
                         (default is to only pg_dump databases that have
                         changed since the last backup)
 --backup-dir          directory to place backup files in 
                         (default is ./backups)
 -v, --verbose         verbose mode
 --help                show this help, then exit

Connection options:

 -h, --host=HOSTNAME   database server host or socket directory
 -p, --port=PORT       database server port number
 -U, --username=NAME   connect as specified database user
 -d, --database=NAME   connect to database name for global data

=head1 Notes

This utility has been developed against PostgreSQL version 8.4.x. Older 
versions of PostgreSQL may not work.

`vacuum` does not appear to trigger a backup unless there is actually 
something to vacuum whereas `vacuum analyze` appears to always trigger a 
backup.

=head1 Copyright and License

Copyright (C) 2011 by Gregory Siems

This library is free software; you can redistribute it and/or modify it 
under the same terms as PostgreSQL itself, either PostgreSQL version 
8.4 or, at your option, any later version of PostgreSQL you may have 
available.

=cut

use strict;
use warnings;
use Getopt::Long;
use Data::Dumper;
use POSIX qw(strftime);

my %opts = get_options();

my $connect_options = '';
$connect_options .= "--$_=$opts{$_} " for (qw(username host port));

my $shared_dump_args = ($opts{verbose})
   ? $connect_options . ' --verbose '
   : $connect_options;

my $backup_prefix = (exists $opts{host} && $opts{host} ne 'localhost')
   ? $opts{backup_dir} . '/' . $opts{host} . '-'
   : $opts{backup_dir} . '/';

do_main();


########################################################################
sub do_main {
   backup_globals();

   my $last_stats_file = $backup_prefix . 'last_stats';

   # get the previous pg_stat_database data
   my %last_stats;
   if ( -f $last_stats_file) {
       %last_stats = parse_stats (split "\n", slurp_file ($last_stats_file));
   }

   # get the current pg_stat_database data
   my $cmd = 'psql ' . $connect_options;
   $cmd .= " $opts{database} " if (exists $opts{database});
   $cmd .= "-Atc \"
       select date_trunc('minute', now()), datid, datname, 
           xact_commit, tup_inserted, tup_updated, tup_deleted 
       from pg_stat_database 
       where datname not in ('template0','template1','postgres'); \"";
   $cmd =~ s/\ns+/ /g;
   my @stats = `$cmd`;
   my %curr_stats = parse_stats (@stats);

   # do a backup if needed
   foreach my $datname (sort keys %curr_stats) {
       my $needs_backup = 0;
       if ($opts{all}) {
           $needs_backup = 1;
       }
       elsif ( ! exists $last_stats{$datname} ) {
           $needs_backup = 1;
           warn "no last stats for $datname\n" if ($opts{debug});
       }
       else {
           for (qw (tup_inserted tup_updated tup_deleted)) {
               if ($last_stats{$datname}{$_} != $curr_stats{$datname}{$_}) {
                   $needs_backup = 1;
                   warn "$_ stats do not match for $datname\n" if ($opts{debug});
               }
           }
       }
       if ($needs_backup) {
           backup_db ($datname);
       }
       else {
           chitchat ("Database \"$datname\" does not currently require backing up.");
       }
   }

   # update the pg_stat_database data
   open my $fh, '>', $last_stats_file || die "Could not open $last_stats_file for output. !$\n";
   print $fh @stats;
   close $fh;
}

sub parse_stats {
   my @in = @_;
   my %stats;
   chomp @in;
   foreach my $line (@in) {
       my @ary = split /\|/, $line;
       my $datname = $ary[2];
       next unless ($datname);
       foreach my $key (qw(tmsp datid datname xact_commit tup_inserted tup_updated tup_deleted)) {
           my $val = shift @ary;
           $stats{$datname}{$key} = $val;
       }
   }
   return %stats;
}

sub backup_globals {
   chitchat ("Backing up the global data.");

   my $backup_file = $backup_prefix . 'globals-only.backup.gz';
   my $cmd = 'pg_dumpall --globals-only ' . $shared_dump_args;
   $cmd .= " --database=$opts{database} " if (exists $opts{database});

   do_dump ($backup_file, "$cmd | gzip");
}

sub backup_db {
   my $database = shift;
   chitchat ("Backing up database \"$database\".");

   my $backup_file = $backup_prefix . $database . '-schema-only.backup.gz';
   do_dump ($backup_file, "pg_dump --schema-only --create --format=plain $shared_dump_args $database | gzip");

   $backup_file = $backup_prefix . $database . '.backup';
   do_dump ($backup_file, "pg_dump --format=". $opts{format} . " $shared_dump_args $database");
}

sub do_dump {
   my ($backup_file, $cmd) = @_;

   my $temp_file = $backup_file . '.new';
   warn "Command is: $cmd > $temp_file" if ($opts{debug});

   chitchat (`$cmd > $temp_file`);
   if ( -f $temp_file ) {
       chitchat (`mv $temp_file $backup_file`);
   }
}

sub chitchat {
   my @ary = @_;
   return unless (@ary);
   chomp @ary;
   my $first   = shift @ary;
   my $now     = strftime "%Y%m%d-%H:%M:%S", localtime;
   print +(join "\n                  ", "$now $first", @ary), "\n";
}

sub get_options {
   Getopt::Long::Configure('bundling');

   my %opts = ();
   GetOptions(
       "a"             => \$opts{all},
       "all"           => \$opts{all},
       "p=s"           => \$opts{port},
       "port=s"        => \$opts{port},
       "U=s"           => \$opts{username},
       "username=s"    => \$opts{username},
       "h=s"           => \$opts{host},
       "host=s"        => \$opts{host},
       "F=s"           => \$opts{format},
       "format=s"      => \$opts{format},
       "d=s"           => \$opts{database},
       "database=s"    => \$opts{database},
       "backup-dir=s"  => \$opts{backup_dir},
       "help"          => \$opts{help},
       "v"             => \$opts{verbose},
       "verbose"       => \$opts{verbose},
       "debug"         => \$opts{debug},
       );

   # Does the user need help?
   if ($opts{help}) {
       show_help();
   }

   $opts{host}         ||= $ENV{PGHOSTADDR} || $ENV{PGHOST}     || 'localhost';
   $opts{port}         ||= $ENV{PGPORT}     || '5432';
   $opts{host}         ||= $ENV{PGHOST}     || 'localhost';
   $opts{username}     ||= $ENV{PGUSER}     || $ENV{USER}       || 'postgres';
   $opts{database}     ||= $ENV{PGDATABASE} || $opts{username};
   $opts{backup_dir}   ||= './backups';

   my %formats = (
       c       => 'custom',
       custom  => 'custom',
       t       => 'tar',
       tar     => 'tar',
       p       => 'plain',
       plain   => 'plain',
   );
   $opts{format} = (defined $opts{format})
       ? $formats{$opts{format}} || 'custom'
       : 'custom';

   warn Dumper \%opts if ($opts{debug});
   return %opts;
}

sub show_help {
   print `perldoc -F $0`;
   exit;
}

sub slurp_file { local (*ARGV, $/); @ARGV = shift; <> }

__END__

**更新：**腳本已經放在 github here上。

引用自：https://dba.stackexchange.com/questions/5110

相關問答

Postgresql

pg_dump 將文件轉儲到 ROOT

  • September 13, 2022
檢視問答
Postgresql

pg_basebackup 期間出錯

  • September 9, 2022
檢視問答
Postgresql

pg_start_backup 與 pg_basebackup

  • September 9, 2022
檢視問答
Postgresql

如何在一台伺服器上製定多個數據庫的備份策略

  • September 1, 2022
檢視問答
Postgresql

如何迭代備份postgresql中的所有數據庫？

  • July 30, 2022
檢視問答
Postgresql

pg_restore 在外鍵約束上失敗

  • March 31, 2022
檢視問答