Generally, when I go through setting up replication, my work flow looks 
something like this:

1. Modify the config file to list the tables that should be replicated.

2. Run create_set.  Get error saying that public.table_name doesn't exist.

3. Go back and add the schema to the tables in the config file.

4. Run create_set.

5. Run subscribe_set.

6. Some time later, notice that Slony is in an error loop because I didn't 
create the schema on the destination first.

7. Run pg_dump a bunch of times, copying the relevant portions over to the 
destination.

8. Run subscribe_set.


In order to combine steps 5-8, I've just finished adding a switch to 
altperl/subscribe_set.pl that copies the schema over to the destination 
before creating the slonik script.

Before I commit this, I would love to know if this is something others would 
like, and if there are any major problems with it.

Things to note:

- Since there's no way to specify the order of tables being subscribed to 
the set, this creates some dependency problems when establishing foreign 
keys.

- In order to solve this, I take the pg_dump output from the set's origin 
and split it up into four parts:

     1. Everything before the CREATE TABLE statement
     2. CREATE TABLE
     3. ALTER TABLE ... ADD PRIMARY KEY
     4. Everything else after CREATE TABLE except #3

   I then remove the Slony-specific stuff, and have psql run 1,2 for every 
table, 1,3 for every table, and 1,4 for every table on the destination node. 
This made everything work in my test case.

- This is rather dependent on pg_dump's output remaining consistent, and 
people not doing strange things like embedding ";" symbols in their relation 
or field names.

- I'm operating under the assumption that all sequences in a given set are 
going to be created by the other tables.  This is something that could be 
solved pretty easily, I think (though pg_dump won't dump just a sequence, by 
the look of it).


I've attached the modified script.  Is this something that would be useful? 
Are there any major problems that I'm overlooking?  If so, is this still 
useful enough to be included in CVS?

-- 
Steve Simms <steve at deefs.net>
http://www.deefs.net
-------------- next part --------------
#!@@PERL@@
# $Id: subscribe_set.pl,v 1.10 2005/02/23 20:30:51 smsimms Exp $
# Author: Christopher Browne
# Copyright 2004 Afilias Canada

use Getopt::Long;

# Defaults
$CONFIG_FILE = '@@SYSCONFDIR@@/slon_tools.conf';
$SHOW_USAGE  = 0;
$SCHEMA = 0;

# Read command-line options
GetOptions("config=s" => \$CONFIG_FILE,
	   "help"     => \$SHOW_USAGE,
	   "s|schema" => \$SCHEMA);

my $USAGE =
"Usage: subscribe_set [--config file] [-s|--schema] set# node#

    Begins replicating a set to the specified node.

    -s
    --schema  Create the schema on the destination node first.

";

if ($SHOW_USAGE) {
  print $USAGE;
  exit 0;
}

require '@@PGLIBDIR@@/slon-tools.pm';
require $CONFIG_FILE;

my ($set, $node) = @ARGV;
if ($node =~ /^(?:node)?(\d+)$/) {
  $node = $1;
} else {
  print "Need to specify node!\n";
  die $USAGE;
}

if ($set =~ /^(?:set)?(\d+)$/) {
  $set = $1;
} else {
  print "Need to specify set!\n";
  die $USAGE;
}

get_set($set) or die "Non-existent set specified.\n";

if ($SCHEMA) {
    my $sql = "";
    my %table_sql;
    my $src_args = "-h $HOST[$SET_ORIGIN] -U $USER[$SET_ORIGIN] -d $DBNAME[$SET_ORIGIN] -p $PORT[$SET_ORIGIN]";
    my $dst_args = "-h $HOST[$node] -U $USER[$node] -d $DBNAME[$node] -p $PORT[$node]";
    foreach my $table (@SERIALTABLES, @PKEYEDTABLES, keys %KEYEDTABLES) {
	$table = ensure_namespace($table);
	my $schema;
	($schema, $table) = split(/\./, $table);
	($table_sql{"$schema.$table"}{"sql_header"},
	 $table_sql{"$schema.$table"}{"sql_create"},
	 $table_sql{"$schema.$table"}{"sql_footer"}) =
	    `@@PGBINDIR@@/pg_dump $src_args -s -n \"$schema\" -t \"$table\"` =~ /\A(.*)^(CREATE TABLE[^\;]+^\)\;)(.*)\z/sm;
    }

    # Create all of the tables
    foreach my $table (keys %table_sql) {
	$sql .= $table_sql{$table}{"sql_header"} . $table_sql{$table}{"sql_create"} . "\n";
    }

    # Separate out the primary keys from everything else
    foreach my $table (keys %table_sql) {
	# Is there a primary key?
	if ($table_sql{$table}{"sql_footer"} =~ /(ALTER TABLE ONLY \S+\s+ADD CONSTRAINT \S+ PRIMARY KEY[^\;]+\;)/) {
	    my $sql_pk = $1;
	    $sql .= $table_sql{$table}{"sql_header"} . $sql_pk . "\n";
	    $table_sql{$table}{"sql_footer"} =~ s/ALTER TABLE ONLY \S+\s+ADD CONSTRAINT \S+ PRIMARY KEY[^\;]+\;//;
	}
    }
    
    # Add all indexes and constraints
    foreach my $table (keys %table_sql) {
	$sql .= $table_sql{$table}{"sql_header"} . $table_sql{$table}{"sql_footer"};
    }

    # Remove the Slony stuff
    $sql =~ s/CREATE TRIGGER _${CLUSTER_NAME}_logtrigger_\d+[^\;]+\;//g;

    # Implement on the destination server
    my $psql_file = "/tmp/slonik-subscribe-psql.$$";
    my $fh;
    open($fh, ">", $psql_file);
    print $fh $sql;
    close $fh;
    `@@PGBINDIR@@/psql $dst_args -f $psql_file`;
}

$FILE="/tmp/slonik-subscribe.$$";
open(SLONIK, ">$FILE");
print SLONIK genheader();
print SLONIK "  try {\n";

if ($DSN[$node]) {
  my $provider = $SET_ORIGIN;
  my $forward;
  if ($PARENT[$node]) {
    $provider = $PARENT[$node];
  }
  if ($NOFORWARD[$node] eq "yes") {
    $forward = "no";
  } else {
    $forward = "yes";
  }
  print SLONIK "    subscribe set (id = $set, provider = $provider, receiver = $node, forward = $forward);\n";
} else {
  die "Node $node not found\n";
}

print SLONIK "  }\n";
print SLONIK "  on error {\n";
print SLONIK "    exit 1;\n";
print SLONIK "  }\n";
print SLONIK "  echo 'Subscribed nodes to set $set';\n";
close SLONIK;
run_slonik_script($FILE);

### If object hasn't a namespace specified, assume it's in "public", and make it so...
sub ensure_namespace {
    my ($object) = @_;
    if ($object =~ /^(.*\..*)$/) {
	# Table has a namespace specified
    } else {
	$object = "public.$object";
    }
    return $object;
}