PostgresSQL 每天将分区表加入集群环境

1.编写脚本AddTableToPartition.sh
该脚本每天自动生成两个文件，一个是数据库建表以及规则的DDL脚本
一个是调用slony集群命令slonik的脚本

#!/usr/bin/perl -w
#
#
use strict;
use POSIX qw(strftime);

$| = 1;


my $tk = time;
my $date = strftime "%Y%m%d", localtime($tk);
my $date_2 = strftime "%Y-%m-%d", localtime($tk);

my $sql_file = 'CreateTable.sql';
my $cmd_file = 'slonik.sh';

my $SQL_TMPL = <<EOF;
------Create table
CREATE TABLE views_$date
(
   CHECK (crdate >= '$date_2'::date AND crdate < ('$date_2'::date + 1))
) INHERITS (views)
WITHOUT OIDS;
ALTER TABLE views_$date ADD PRIMARY KEY (id);
ALTER TABLE views_$date OWNER TO slony;
-------Create Rule
CREATE OR REPLACE RULE view_rule_$date AS
    ON INSERT TO views
   WHERE new.crdate >= '$date_2'::date AND new.crdate < ('$date_2'::date + 1) DO INSTEAD  INSERT INTO views_$date (crdate, ad_id, client_ip, fb_uid, bebo_uid, earn_id, referer, agent, ms_uid, hi5_uid)
  VALUES (new.crdate, new.ad_id, new.client_ip, new.fb_uid, new.bebo_uid, new.earn_id, new.referer, new.agent, new.ms_uid, new.hi5_uid);
select "_BeboFace".replicate_partition((SELECT COALESCE(MAX(tab_id), 0) + 1 FROM "_BeboFace".sl_table), 'public'::text, 'views_$date'::text, NULL::text, 'Views Partition for $date'::text);

EOF

my $CMD_TMPL = <<EOF;
#!/bin/sh
slonik  <<_EOF_
include <data.nfo>
execute script ( set id = 1, event node = 1, filename='$sql_file');
_EOF_

EOF

open F,">$sql_file" or die "Can't open file $sql_file: $!";
print F $SQL_TMPL;
close F;

open F,">$cmd_file" or die "Can't open file $cmd_file: $!";
print F $CMD_TMPL;
close F;

#my $res = `$cmd_file`;
#print "$res";

2，生成脚本后，将执行slonik脚本的命令加入crontab，定时调用。