We had two Greenplum instances running and we needed to copy one big table to the other. There’s a variety of ways to dump and import but we were moving a large amount of data, so the thought of dropping it to a local file was not a very good option.
One of the ways to move a table from one instance to the other using COPY commands. If you are on the destination database you would use:
psql -h remotegreenplumtopulldatafrom -U me -c "COPY myschema.mytable to STDOUT" | psql -c "COPY myschema.mytable FROM STDIN"
We were going to move a lot of data though. So I came up with a quick script to do it a partition at a time
#!/usr/bin/perl
################################
# database_dump
#
# Simple program do go out and copy dated partitions of
# a greenplum db to another db where the table exists
# it starts at start_time and goes incrementally backwards
# a day at a time until it gets to end_time
#
# 2010-03-16 SHK
################################
use strict;
use warnings;
use POSIX;
use Time::Local;
# Fields are S,M,H,D,M,Y - Note month is 0-11
my $start_time = timelocal(0,0,0,1,2,2011);
my $end_time = timelocal(0,0,0,1,2,2010);
my $cmd;
my $running_date = $start_time;
until ( $running_date < $end_time ) {
my $part_date = strftime("%Y%m%d",localtime($running_date));
$running_date -= 86400
$cmd = qq~psql -h remotegreenplumtopullfrom -U skahler -c "COPY myschema.mytable_1_prt_$partdate to STDOUT" | psql -c "COPY myschema.mytable FROM STDIN"~;
print "$cmd\n";
system($cmd);
}
exit;
In our case the new db didn't have much action going on while I was moving the data in. I think if it was an active table that I was moving things into I'd create create a table on the destination target, load the data into it and then exchange that partition into the my target table.