Date: Fri, 19 Sep 2003 18:19:17 +0400
From: "Alexander M. Pravking" <fduch@antar.bryansk.ru>
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: "pgsql-sql @ postgresql . org" <pgsql-sql@postgresql.org>
Subject: Re: Backup of multiple tables
Message-ID: <20030919141917.GA71229@dyatel.antar.bryansk.ru>
References: <200309191354.06071.andreak@officenet.no>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="HcAYCG3uE/tztfnV"
Content-Disposition: inline
In-Reply-To: <200309191354.06071.andreak@officenet.no>
User-Agent: Mutt/1.4.1i

--HcAYCG3uE/tztfnV
Content-Type: text/plain; charset=koi8-r
Content-Disposition: inline

On Fri, Sep 19, 2003 at 01:54:01PM +0200, Andreas Joseph Krogh wrote:
> I usually backup my database with pg_backup without the -t option. But now I 
> need to only backup certain tables(say tab1 and tab2), is this possible with 
> pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success.

Here's a perl script I used to dump all the tables separately.
I'm not sure most of options do work there, I didn't test ;-)
It won't be hard to make it dump certain tables, I think.


-- 
Fduch M. Pravking

--HcAYCG3uE/tztfnV
Content-Type: application/x-perl
Content-Disposition: attachment; filename="dump.pl"
Content-Transfer-Encoding: quoted-printable

#!/usr/bin/perl -w

use strict;
use Getopt::Std;
use DBI;

sub usage {
    if (my $msg =3D shift) { print STDERR $msg, "\n\n" }
    print STDERR <<USAGE;
usage:	$0 [options] dbname dst-dir
	-c	clean (drop) schema prior to create
	-C	output commands to create database
	-d	dump data as INSERT, rather than COPY, commands
	-D	dump data as INSERT commands with attribute names
	-h HOST	server host name
	-n	suppress most quotes around identifiers
	-N	enable most quotes around identifiers
	-o	dump object ids (oids)
	-O	do not output \\connect commands
	-p PORT	server port number
	-U USER	database user to connect as
	-v	verbose output
	-x	do not dump ACL's (grant/revoke)
USAGE
    exit 1;
}

my %opts;
my $pg_dump =3D '/usr/local/bin/pg_dump';

sub dump_schema {
    my ($dbname, $dstdir) =3D @_;
    my @opts =3D ();
    print STDERR "Dumping schema of $dbname..."	if $opts{v};
    foreach my $arg (qw(h p U)) {
	push @opts, "-$arg" =3D> $opts{$arg}	if $opts{$arg};
    }
    foreach my $arg (qw(c C n N o O x)) {
	push @opts, "-$arg"	if $opts{$arg};
    }
    push @opts, '-f' =3D> "$dstdir/$dbname-schema.sql";
    push @opts, '-s'; # --schema-only

    system($pg_dump, @opts, $dbname) =3D=3D 0
	or die "pg_dump failed on schema\n";
    print STDERR " done.\n"			if $opts{v}
}

sub dump_table {
    my ($dbname, $table, $dstdir) =3D @_;

    print STDERR "Dumping data: $table..."	if $opts{v};
    my @opts =3D ();
    foreach my $arg (qw(h p U)) {
	push @opts, "-$arg" =3D> $opts{$arg}	if $opts{$arg};
    }
    foreach my $arg (qw(d D n N o O)) {
	push @opts, "-$arg"	if $opts{$arg};
    }
    push @opts, '-f' =3D> "$dstdir/$dbname-data-$table.sql";
    push @opts, '-a'; # --data-only
    push @opts, '-t' =3D> $table;

    system($pg_dump, @opts, $dbname) =3D=3D 0
	or die "pg_dump failed on table $table\n";
    print STDERR " done.\n"			if $opts{v}
}


getopts('cCdDh:nNoOp:U:vx', \%opts)	or usage("parameter error");
my $dbname =3D shift @ARGV		or usage("no database name");
my $dstdir =3D shift @ARGV		or usage("no destination dir");
@ARGV					and usage("extra args");

#use Data::Dumper;
#print Dumper(\%opts, $dbname, $dstdir);

dump_schema($dbname, $dstdir);

my $dbh =3D DBI->connect("DBI:Pg:dbname=3D$dbname", $opts{U})
    or die "connect: $DBI::errstr\n";
my $tables =3D $dbh->selectcol_arrayref(q{
	SELECT	*
	FROM	pg_class
	WHERE	relkind =3D 'r'
	    AND	relname !~ '^pg_'
    })
	or die "Can't list tables: $DBI::errstr\n";
$dbh->disconnect;

foreach my $table (@$tables) {
    dump_table($dbname, $table, $dstdir);
}

--HcAYCG3uE/tztfnV--