summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-02-24 10:28:56 +0100
committerYorhel <git@yorhel.nl>2021-02-24 15:07:48 +0100
commit76c24137126832513614c58b16372b9b0342c0de (patch)
tree6aab4c12e62bd4441500addb2758305d88c2bbb5
parent530a9a65f90001678f3340dff5edc155cafec827 (diff)
dbdump.pl: Add export-data command to dump everything
Roughly similar to 'pg_dump --data-only', but with added includes for sql/ scripts, without the SET commands (these will cause the includes to fail) and with a "free" built-in CLUSTER on the primary key for most tables.
-rw-r--r--lib/VNDB/Schema.pm2
-rwxr-xr-xutil/dbdump.pl53
2 files changed, 52 insertions, 3 deletions
diff --git a/lib/VNDB/Schema.pm b/lib/VNDB/Schema.pm
index 2d6eb6f5..63c0f258 100644
--- a/lib/VNDB/Schema.pm
+++ b/lib/VNDB/Schema.pm
@@ -48,7 +48,7 @@ sub schema {
} elsif(/^\s*\);/) {
$table = undef;
- } elsif(/^\s+CHECK/) {
+ } elsif(/^\s+(?:CHECK|CONSTRAINT)/) {
# ignore
} elsif($table && /^\s+PRIMARY\s+KEY\s*\(([^\)]+)\)/i) {
diff --git a/util/dbdump.pl b/util/dbdump.pl
index 754981e1..3a088804 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -12,6 +12,14 @@ util/dbdump.pl export-img output-dir
Create or update a directory with hardlinks to images.
+util/dbdump.pl export-data data.sql
+
+ Create an SQL script that is usable as replacement for 'sql/all.sql'.
+ (Similar to the dump created by devdump.pl, except this one includes *all* data)
+
+ This allows recreating the full database using the definitions in sql/*.
+ The script does not rely on column order, so can be used to re-order table columns.
+
util/dbdump.pl export-votes output.gz
util/dbdump.pl export-tags output.gz
util/dbdump.pl export-traits output.gz
@@ -114,6 +122,14 @@ $db->do('SET TIME ZONE +0');
$db->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
+sub table_order {
+ my $s = $schema->{$_[0]};
+ my $c = $tables{$_[0]};
+ my $o = $s->{primary} ? join ', ', map "\"$_\"", $s->{primary}->@* : $c ? $c->{order} : '';
+ $o ? "ORDER BY $o" : '';
+}
+
+
sub export_timestamp {
my $dest = shift;
open my $F, '>', $dest;
@@ -133,11 +149,11 @@ sub export_table {
# Truncate all timestamptz columns to a day, to avoid leaking privacy-sensitive info.
my $cols = join ', ', map $_->{type} eq 'timestamptz' ? "date_trunc('day', \"$_->{name}\")" : qq{"$_->{name}"}, @cols;
my $where = $table->{where} ? "WHERE $table->{where}" : '';
- my $order = $schema->{primary} ? join ', ', map "\"$_\"", @{$schema->{primary}} : $table->{order};
+ my $order = table_order $table->{name};
die "Table '$table->{name}' is missing an ORDER BY clause\n" if !$order;
my $start = time;
- $db->do(qq{COPY (SELECT $cols FROM "$table->{name}" $where ORDER BY $order) TO STDOUT});
+ $db->do(qq{COPY (SELECT $cols FROM "$table->{name}" $where $order) TO STDOUT});
open my $F, '>:utf8', $fn;
my $v;
print $F $v while($db->pg_getcopydata($v) >= 0);
@@ -278,6 +294,37 @@ sub export_img {
}
+sub export_data {
+ my $dest = shift;
+ my $F = *STDOUT;
+ open $F, '>', $dest if $dest ne '-';
+ binmode($F, ":utf8");
+ select $F;
+ print "\\set ON_ERROR_STOP 1\n";
+ print "\\i sql/schema.sql\n";
+ # Would be nice if VNDB::Schema could list sequences, too.
+ my @seq = sort @{ $db->selectcol_arrayref(
+ "SELECT oid::regclass::text FROM pg_class WHERE relkind = 'S' AND relnamespace = 'public'::regnamespace"
+ ) };
+ printf "SELECT setval('%s', %d);\n", $_, $db->selectrow_array("SELECT last_value FROM \"$_\"", {}) for @seq;
+ for my $t (sort { $a->{name} cmp $b->{name} } values %$schema) {
+ my $cols = join ',', map "\"$_->{name}\"", $t->{cols}->@*;
+ my $order = table_order $t->{name};
+ print "\nCOPY \"$t->{name}\" ($cols) FROM STDIN;\n";
+ $db->do("COPY (SELECT $cols FROM \"$t->{name}\" $order) TO STDOUT");
+ my $v;
+ print $v while($db->pg_getcopydata($v) >= 0);
+ print "\\.\n";
+ }
+ print "\\i sql/func.sql\n";
+ print "\\i sql/editfunc.sql\n";
+ print "\\i sql/tableattrs.sql\n";
+ print "\\i sql/triggers.sql\n";
+ print "\\set ON_ERROR_STOP 0\n";
+ print "\\i sql/perms.sql\n";
+}
+
+
sub export_votes {
my $dest = shift;
require PerlIO::gzip;
@@ -355,6 +402,8 @@ if($ARGV[0] && $ARGV[0] eq 'export-db' && $ARGV[1]) {
export_db $ARGV[1];
} elsif($ARGV[0] && $ARGV[0] eq 'export-img' && $ARGV[1]) {
export_img $ARGV[1];
+} elsif($ARGV[0] && $ARGV[0] eq 'export-data' && $ARGV[1]) {
+ export_data $ARGV[1];
} elsif($ARGV[0] && $ARGV[0] eq 'export-votes' && $ARGV[1]) {
export_votes $ARGV[1];
} elsif($ARGV[0] && $ARGV[0] eq 'export-tags' && $ARGV[1]) {