Return-Path: owner-postman
Received: from localhost.Berkeley.EDU (localhost.Berkeley.EDU [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.9/8.6.3) with SMTP id NAA28905 for postgres-redist; Mon, 24 Oct 1994 13:16:31 -0700
Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199410242016.NAA28905@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
X-Return-Path: owner-postman
Received: from faerie.CS.Berkeley.EDU (faerie.CS.Berkeley.EDU [128.32.37.53]) by nobozo.CS.Berkeley.EDU (8.6.9/8.6.3) with ESMTP id NAA28895 for <postgres@postgres.Berkeley.EDU>; Mon, 24 Oct 1994 13:16:30 -0700
Received: from localhost.Berkeley.EDU (localhost.Berkeley.EDU [127.0.0.1]) by faerie.CS.Berkeley.EDU (8.6.9/8.1B) with SMTP id NAA06568; Mon, 24 Oct 1994 13:16:22 -0700
Message-Id: <199410242016.NAA06568@faerie.CS.Berkeley.EDU>
X-Authentication-Warning: faerie.CS.Berkeley.EDU: Host localhost.Berkeley.EDU didn't use HELO protocol
From: aoki@cs.berkeley.edu (Paul M. Aoki)
To: Anne POSSOZ <possoz@slsun2.epfl.ch>
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: copy of a database in postgres 
Reply-To: aoki@cs.berkeley.edu (Paul M. Aoki)
In-reply-to: Your message of Mon, 24 Oct 1994 12:34:37 +0100 
	     <9410241134.AA13075@slsun2.epfl.ch> 
Date: Mon, 24 Oct 94 13:16:16 -0700
X-Sender: aoki@postgres.Berkeley.EDU
Resent-To: postgres-redist@postgres.Berkeley.EDU
X-Mts: smtp
Resent-Date: Mon, 24 Oct 94 13:16:31 -0700
Resent-XMts: smtp

Anne POSSOZ <possoz@slsun2.epfl.ch> writes:
> Using postgres and libpq, I can't find a way to copy an entire
> database (like unloaddb for ingres)?  My aim is to be able to unloas
> an entire database and reload it in a new, empty database.

people have generated pgperl scripts to do (part of) this.  but
there's no built-in mechanism.

----------------------------------------------------------------

From: David Allan Finch <sarum@vger.demon.co.uk>
Message-Id: <9310072315.AA00525@vger.demon.co.uk>
To: postgres@postgres.berkeley.edu
Subject: Dump/restore a postgres database
Date: Fri, 8 Oct 93 00:07:18 BST

Version 0.3 of the ongoing pgperl script...
this version will dump all databases except template1 and
postgres. With a little more work it will store all the stuff
nead to recreate a full database. Tommorow I will hopeful have
a restore script. Which I will use to upgrade to V4.1, If anyone
can see any bugs please tell me. Also it would be nice to dump
indexes, function etc, anyone got any ideas about how to do this?

#!/usr/postgres/bin/pgperl
###########################################################################=
#####
###########################################################################=
#####
# Version 000.001 : 03/Aug/1993
#  This code is non-copyright of David Allan Finch
#  and does nothing. If it does then its your
#  problem for running it, You have been warned.
###########################################################################=
#####
# Version 000.002 : 05/Aug/1993 : 15:25 BST
#  This version will look up all the classes in the
#  pg_inherit and pg_class and then produce a pg-include file
#  to re-create them. NOTE it will not work if
#  a class has no data (don't know why, but giving
#  a top level class some dummy data will fix the problem).
#  Also this version assumes that a child class will
#  have the same form a the parent. Also I do not
#  know how to create a class for a specific user
#  so this infomation will be lost.
###########################################################################=
#####
# Version 000.003 : 07/Oct/1993 : 19:10 BST
#   This version gets all the database names first and then does each datab=
ase.
#   The structure of this utility is f**kup will have to rewrite.
###########################################################################=
#####
###########################################################################=
#####

$version = shift || die( "Usage: $0 <version>\n" );

$PGH = "/usr/postgres";
$PGH = "/usr/postgres" if( $version eq "4.0.1" );
$PGH = "/usr/local/postgres" if( $version eq "4.1" );

if( ! -d "$PGH/dump" )
	{
	mkdir( "$PGH/dump", 0777 );
	}

@database = &get_database_list();

foreach $d ( @database )
	{
	undef @inherit;
	undef @class;
	undef %done;

	( $database ) = split( "#", $d );

	next if ( ! -d "$PGH/data/base/$database" );

	print "<<<<< $database >>>>>\n";

	open( SCRIPT, ">$PGH/dump/SCRIPT_$database" );

	# get all the system stuff
	&get_types();
	&get_classes();
	&get_inherits();

	# create the inherited classes and their parents
	foreach $class ( @inherit )
		{
		print STDERR "do iherited and parent: $class\n";
		&print_classes( split( ',', $class ) );
		}

	# create the rest of the classes
	foreach $class ( @class )
		{
		next if( $class eq "" );
    		if( $done{$class} ne "y" )
        		{
			print STDERR "do class: $class\n";
        		&print_class( $class );
        		$done{$class} = "y";
        		}
		}
	}

exit(0);

###########################################################################=
#####
###########################################################################=
#####

sub	get_database_list
	{
	local( @LIST );
	&PQsetdb( "template1" );
	&PQexec( "begin" );
	&PQexec( "retrieve portal tmp ( pg_database.all )" );
	&PQexec( "fetch all in tmp" );
	$p = &PQparray( "tmp" );
	$g = &PQngroups( $p );

	for( $k = 0; $k < $g; $k++ )
		{
		$n = &PQntuplesGroup( $p, $k );
		$m = &PQnfieldsGroup( $p, $k );

		for( $i = 0; $i < $n; $i++ )
			{
			$name	= &PQgetvalue( $p, $i, 0 );
			$uid	= &PQgetvalue( $p, $i, 1 );
			$path	= &PQgetvalue( $p, $i, 2 );

			next if( $name =~ /template1/ );
			next if( $name =~ /postgres/ );

			push( @LIST, join( "#", ( $name, $uid ) ) );
			}
		}

	&PQexec( "close tmp" );
	&PQexec( "end" );
	&PQfinish();

	return( @LIST );
	}

###########################################################################=
#####
###########################################################################=
#####

sub	get_inherits
	{
    &PQsetdb( "$database" );
    &PQexec( "begin" );
 
    &PQexec( "retrieve portal tmp ( pg_inherits.all )" );
    &PQexec( "fetch all in tmp" );
 
    $p = &PQparray( "tmp" );
    $g = &PQngroups( $p );
    $t = 0;
 
    for( $k = 0; $k < $g; $k++ )
        {
        $n = &PQntuplesGroup( $p, $k );
        $m = &PQnfieldsGroup( $p, $k );
 
        for( $i = 0; $i < $n; $i++ )
            {
	        $child   = $class[&PQgetvalue( $p, $i, 0 )];
	        $parent   = $class[&PQgetvalue( $p, $i, 1 )];
			next if( $child eq "" );
			push( @inherit, join( ',', $child, $parent ) );
            }
        }
 
    &PQexec( "close tmp" );
    &PQexec( "end" );
    &PQfinish();
	}

###########################################################################=
#####
###########################################################################=
#####

sub	get_classes
	{
	&PQsetdb( "$database" );
    &PQexec( "begin" );

    &PQexec( "retrieve portal tmp ( pg_class.relname, pg_class.relowner, pg=
_class.oid ) where pg_class.relkind = 'r'" );
    &PQexec( "fetch all in tmp" );

    $p = &PQparray( "tmp" );
    $g = &PQngroups( $p );
    $t = 0;

    for( $k = 0; $k < $g; $k++ )
        {
        $n = &PQntuplesGroup( $p, $k );
        $m = &PQnfieldsGroup( $p, $k );

		for( $j = 0; $j < $m; $j++ )
			{
            $field_name = &PQfnameGroup( $p, $k, $j );
            $field_type = &PQftype( $p, $k, $j );
			}

        for( $i = 0; $i < $n; $i++ )
            {
			$name	= &PQgetvalue( $p, $i, 0 );
			$owner	= &PQgetvalue( $p, $i, 1 );
			$oid	= &PQgetvalue( $p, $i, 2 );
			next if( $name =~ /pg_/ );
			next if( $name =~ // );

			$class{$name}	= $oid;
			$class[$oid]	= $name;
            }
        }    

    &PQexec( "close tmp" );
    &PQexec( "end" );
    &PQfinish();
	}

###########################################################################=
#####
###########################################################################=
#####

sub get_types
	{
	&PQsetdb( "$database" );
	&PQexec( "begin" );

	&PQexec( "retrieve portal tmp ( pg_type.typname, pg_type.oid )" );
	&PQexec( "fetch all in tmp" );

	$p = &PQparray( "tmp" );
	$g = &PQngroups( $p );
	$t = 0;

	for( $k = 0; $k < $g; $k++ )
		{
		$n = &PQntuplesGroup( $p, $k );
		$m = &PQnfieldsGroup( $p, $k );
	
		for( $i = 0; $i < $n; $i++ )
			{
			$name	= &PQgetvalue( $p, $i, 0 );
			$value	= &PQgetvalue( $p, $i, 1 );
			$types{$name}	= $value;
			$types[$value]	= $name;
			}
		}

    &PQexec( "close tmp" );
	&PQexec( "end" );
	&PQfinish();
	}

###########################################################################=
#####
###########################################################################=
#####

sub print_classes
    {
	local( $class, $inherit ) = @_;

	if( $done{$inherit} ne "y" )
		{
		&print_class( $inherit );
		$done{$inherit} = "y";
		}

	print SCRIPT "create $class inherits $inherit ()\n";
	print SCRIPT "copy $class from \"$PGH/dump/$database#$class\"\n";

    	&PQsetdb( "$database" );
    	&PQexec( "begin" );
	&PQexec( "copy $class to \"$PGH/dump/$database#$class\"" );
	&PQexec( "end" );
	&PQfinish();
	$done{$class} = "y";
	}

###########################################################################=
#####
###########################################################################=
#####

sub	print_class
	{
	local( $class ) = @_;

    &PQsetdb( "$database" );
    &PQexec( "begin" );
 
    &PQexec( "retrieve portal tmp ( $class.all )" );
    &PQexec( "fetch 1 in tmp" );
 
    $p = &PQparray( "tmp" );
    $g = &PQngroups( $p );
    $t = 0;

	print SCRIPT "create $class ( ";
 
    for( $k = 0; $k < $g; $k++ )
        {
        $n = &PQntuplesGroup( $p, $k );
        $m = &PQnfieldsGroup( $p, $k );
 
        for( $i = 0; $i < $m; $i++ )
            {
			if( $i > 0 )
				{
				print SCRIPT ", ";
				}
			$field_name	= &PQfnameGroup( $p, $k, $i );

			$field_type = &PQftype( $p, $k, $i );

			print SCRIPT "$field_name = $types[$field_type]";
            }
        }

	print SCRIPT " )\n";

	print SCRIPT "copy $class from \"$PGH/dump/$database#$class\"\n";

	&PQexec( "copy $class to \"$PGH/dump/$database#$class\"" );
 
    &PQexec( "close tmp" );
    &PQexec( "end" );
    &PQfinish();
    }

###########################################################################=
#####
###########################################################################=
#####



--
    /		Sarum Wizard
 /\|/\		All Hail Discordia
 | K |		david.finch@vger.demon.co.uk
 \___/		sarum@cix.compulink.co.uk
--
  Paul M. Aoki          |  University of California at Berkeley
  aoki@CS.Berkeley.EDU  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"

   If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================