Show
Ignore:
Timestamp:
07/05/11 21:09:38 (3 years ago)
Author:
depesz
Message:

some cleanup, grouping, added timing information

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • trunk/tools/fast.dump.and.restore/fast.restore

    r261 r262  
    1717use Pod::Usage; 
    1818use POSIX qw( :sys_wait_h ); 
    19 use File::Spec; 
    2019use File::Temp qw( tempfile tempdir ); 
     20use POSIX qw( strftime ); 
     21use Time::HiRes qw( time ); 
    2122 
    2223our %killed_pids = (); 
     
    3839        'CLEANUP' => 1, 
    3940    ); 
     41    $self->log( 'Starting get_list_from_schema' ); 
    4042    $self->get_list_from_schema(); 
     43    $self->log( 'get_list_from_schema finished.' ); 
     44 
     45    $self->log( 'Starting load_base_schema' ); 
    4146    $self->load_base_schema(); 
     47    $self->log( 'load_base_schema finished.' ); 
     48 
     49    $self->log( 'Starting load_data' ); 
    4250    $self->load_data(); 
     51    $self->log( 'load_data finished.' ); 
     52 
     53    $self->log( 'Starting get_tables_size' ); 
    4354    $self->get_tables_size(); 
     55    $self->log( 'get_tables_size finished.' ); 
     56 
     57    $self->log( 'Starting get_indexes_size' ); 
    4458    $self->get_indexes_size(); 
     59    $self->log( 'get_indexes_size finished.' ); 
     60 
     61    $self->log( 'Starting get_fkeys_size' ); 
    4562    $self->get_fkeys_size(); 
     63    $self->log( 'get_fkeys_size finished.' ); 
     64 
     65    $self->log( 'Starting create_constraints_and_indexes' ); 
    4666    $self->create_constraints_and_indexes(); 
     67    $self->log( 'create_constraints_and_indexes finished.' ); 
     68 
     69    $self->log( 'Starting create_foreign_keys' ); 
    4770    $self->create_foreign_keys(); 
     71    $self->log( 'create_foreign_keys finished.' ); 
     72 
     73    $self->log( 'Starting finish_schema' ); 
    4874    $self->finish_schema(); 
     75    $self->log( 'finish_schema finished.' ); 
     76 
     77    return; 
     78} 
     79 
     80sub log { 
     81    my $self = shift; 
     82    my ( $format, @args ) = @_; 
     83    $format =~ s/\s*\z//; 
     84 
     85    my $time         = time(); 
     86    my $date_time    = strftime( '%Y-%m-%d %H:%M:%S', localtime $time ); 
     87    my $miliseconds = ( $time * 1000 ) % 1000; 
     88 
     89    my $time_stamp = sprintf "%s.%03u", $date_time, $miliseconds; 
     90    my $msg = sprintf $format, @args; 
     91 
     92    printf "%s : %s\n", $time_stamp, $msg; 
    4993    return; 
    5094} 
     
    61105sub get_indexes_size { 
    62106    my $self = shift; 
    63     open my $fh, '<', File::Spec->catfile( $self->{ 'input' }, 'index.sizes' ) or croak( "Cannot open index.sizes in dump: $OS_ERROR\n" ); 
     107    open my $fh, '<', 'index.sizes' or croak( "Cannot open index.sizes in dump: $OS_ERROR\n" ); 
    64108    while ( my $l = <$fh> ) { 
    65109        $l =~ s/\s*\z//; 
     
    73117sub get_fkeys_size { 
    74118    my $self = shift; 
    75     open my $fh, '<', File::Spec->catfile( $self->{ 'input' }, 'fkeys.ordering' ) or croak( "Cannot open fkeys.ordering in dump: $OS_ERROR\n" ); 
     119    open my $fh, '<', 'fkeys.ordering' or croak( "Cannot open fkeys.ordering in dump: $OS_ERROR\n" ); 
    76120    while ( my $l = <$fh> ) { 
    77121        $l =~ s/\s*\z//; 
     
    132176        'next_data' => sub { my $x = shift @{ $self->{ 'ic_list' } }; return unless defined $x; return ( $x->{ 'name' }, $x ) }, 
    133177        'show_progress' => sub { return $self->show_progress_for_ic_files(); }, 
    134         'worker'        => sub { return $self->process_ic_file( @_ ) }, 
     178        'worker'        => sub { return $self->pg_restore( \@_, 1 ) }, 
    135179    ); 
    136180    return; 
     
    190234        && ( 0 == $in_queue ) ) 
    191235    { 
    192         print "\nData loading done.\n"; 
     236        print "\n"; 
    193237        return; 
    194238    } 
     
    208252        && ( 0 == $in_queue ) ) 
    209253    { 
    210         print "\nData loading done.\n"; 
     254        print "\n"; 
    211255        return; 
    212256    } 
     
    226270        && ( 0 == $in_queue ) ) 
    227271    { 
    228         print "\nData loading done.\n"; 
     272        print "\n"; 
    229273        return; 
    230274    } 
     
    262306    my $fkey = shift; 
    263307 
    264     my ( $list_fh, $list_filename ) = tempfile( 'list.XXXXXX', 'DIR' => $self->{ 'tmpdir' } ); 
    265     print $list_fh $fkey->{ 'line' } . "\n"; 
    266     close $list_fh; 
    267  
    268     my $output = $self->run_command( 
    269         $self->{ 'pg_restore' }, 
    270         '-L', 
    271         $list_filename, 
    272         File::Spec->catfile( $self->{ 'input' }, 'schema.dump' ), 
    273     ); 
    274     unlink $list_filename; 
    275  
    276     my ( $sql_fh, $sql_filename ) = tempfile( 'sql.XXXXXX', 'DIR' => $self->{ 'tmpdir' }, ); 
    277     print $sql_fh "BEGIN;\n"; 
    278     print $sql_fh "LOCK TABLE ONLY $_ IN SHARE ROW EXCLUSIVE MODE;\n" for @{ $fkey->{ 'tables' } }; 
    279     print $sql_fh $output . "\n"; 
    280     print $sql_fh "COMMIT;\n"; 
    281     close $sql_fh; 
    282  
    283     $self->run_command( 
    284         $self->{ 'psql' }, 
    285         '-qAtX', 
    286         '-f', 
    287         $sql_filename, 
    288     ); 
    289     unlink $sql_filename; 
    290     return; 
    291 
    292  
    293 sub process_ic_file { 
    294     my $self    = shift; 
    295     my $ic_data = shift; 
    296  
    297     my ( $list_fh, $list_filename ) = tempfile( 'list.XXXXXX', 'DIR' => $self->{ 'tmpdir' }, ); 
    298     print $list_fh $ic_data->{ 'line' } . "\n"; 
    299     close $list_fh; 
    300  
    301     $self->run_command( 
    302         $self->{ 'pg_restore' }, 
    303         '-L', 
    304         $list_filename, 
    305         '-d', 
    306         $self->{ 'database' }, 
    307         File::Spec->catfile( $self->{ 'input' }, 'schema.dump' ), 
    308     ); 
    309     unlink $list_filename; 
     308    my $output = $self->pg_restore( [ $fkey ], 0 ); 
     309 
     310    my @sql_lines = (); 
     311    push @sql_lines, "BEGIN;"; 
     312    push @sql_lines, "LOCK TABLE ONLY $_ IN SHARE ROW EXCLUSIVE MODE;" for @{ $fkey->{ 'tables' } }; 
     313    push @sql_lines, $output . ""; 
     314    push @sql_lines, "COMMIT;"; 
     315 
     316    $self->psql( join("\n", @sql_lines ) ); 
     317 
    310318    return; 
    311319} 
     
    365373    my $dir; 
    366374 
    367     croak( 'Cannot opendir() on ' . $self->{ 'input' } . ": $OS_ERROR\n" ) unless opendir( $dir, $self->{ 'input' } ); 
     375    croak( 'Cannot opendir() on ' . $self->{ 'input' } . ": $OS_ERROR\n" ) unless opendir( $dir, '.' ); 
    368376    my @names = readdir $dir; 
    369377    closedir $dir; 
    370378 
    371379    my @data_files = (); 
    372     for my $filename ( @names ) { 
    373         my $file_path = File::Spec->catfile( $self->{ 'input' }, $filename ); 
    374         next unless -f $file_path; 
    375         next unless $filename =~ m{\Adata\.[A-Za-z0-9_]+\.[A-Za-z0-9_]+\.\d+\.dump\z}; 
    376         my $file_size = ( stat( $file_path ) )[ 7 ]; 
     380    for my $file_name ( @names ) { 
     381        next unless -f $file_name; 
     382        next unless $file_name =~ m{\Adata\.[A-Za-z0-9_]+\.[A-Za-z0-9_]+\.\d+\.dump\z}; 
     383        my $file_size = ( stat( $file_name ) )[ 7 ]; 
    377384        push @data_files, 
    378385            { 
    379             'file_path' => $file_path
     386            'file_path' => $file_name
    380387            'file_size' => $file_size, 
    381388            }; 
     
    389396    my $self = shift; 
    390397 
    391     my ( $list_fh, $list_filename ) = tempfile( 'list.XXXXXX', 'DIR' => $self->{ 'tmpdir' } ); 
     398    my @items = (); 
    392399 
    393400    for my $i ( @{ $self->{ 'list' } } ) { 
     
    397404        next if $i->{ 'type' } eq 'TRIGGER'; 
    398405        next if $i->{ 'type' } eq 'ACL'; 
    399         print $list_fh $i->{ 'line' } . "\n"; 
    400     } 
    401     close $list_fh; 
    402     $self->run_command( 
    403         $self->{ 'pg_restore' }, 
    404         '-L', 
    405         $list_filename, 
    406         '-d', 
    407         $self->{ 'database' }, 
    408         File::Spec->catfile( $self->{ 'input' }, 'schema.dump' ) 
     406        push @items, $i; 
     407    } 
     408 
     409    $self->pg_restore( \@items, 1 ); 
     410 
     411    $self->psql( "\\i sequences.sql" ); 
     412 
     413    return; 
     414
     415 
     416sub finish_schema { 
     417    my $self = shift; 
     418 
     419    $self->pg_restore( 
     420        [ grep { $_->{'type'} =~ m{\A (?: TRIGGER | ACL ) \z }xms } @{ $self->{'list'} } ], 
     421        1 
    409422    ); 
    410     unlink $list_filename; 
    411     $self->psql( "\\i " . File::Spec->catfile( $self->{ 'input' }, 'sequences.sql' ) ); 
    412     print "Base schema successfully loaded.\n"; 
    413 
    414  
    415 sub finish_schema { 
    416     my $self = shift; 
    417  
    418     my ( $list_fh, $list_filename ) = tempfile( 'list.XXXXXX', 'DIR' => $self->{ 'tmpdir' } ); 
    419  
    420     for my $i ( @{ $self->{ 'list' } } ) { 
    421         print $list_fh $i->{ 'line' } . "\n" if $i->{ 'type' } eq 'TRIGGER'; 
    422         print $list_fh $i->{ 'line' } . "\n" if $i->{ 'type' } eq 'ACL'; 
    423     } 
    424     close $list_fh; 
    425  
    426     $self->run_command( 
    427         $self->{ 'pg_restore' }, 
    428         '-L', 
    429         $list_filename, 
    430         '-d', 
    431         $self->{ 'database' }, 
    432         File::Spec->catfile( $self->{ 'input' }, 'schema.dump' ) 
    433     ); 
    434     unlink $list_filename; 
    435  
    436     print "Rest of schema successfully loaded.\n"; 
     423 
     424    return; 
    437425} 
    438426 
     
    440428    my $self = shift; 
    441429    my $list = $self->run_command( 
    442         $self->{ 'pg_restore' }
     430        'pg_restore'
    443431        '-l', 
    444         File::Spec->catfile( $self->{ 'input' }, 'schema.dump' ) 
     432        'schema.dump' 
    445433    ); 
    446434 
     
    506494 
    507495    my $db = $self->psql( 'SELECT current_user, current_database()' ); 
     496    $self->{ 'database' } = $db->[ 0 ]->[ 1 ]; 
    508497 
    509498    my $largest_tables = $self->psql( 
    510 "SELECT * FROM ( SELECT rpad(oid::regclass::text, 32) || ' (' || pg_size_pretty(pg_relation_size(oid)) || ')' from pg_class where relkind = 'r' and relname !~ '^(information_schema|pg_.*)\$' order by pg_relation_size(oid) desc limit 5) x order by 1" 
     499        q{ 
     500            SELECT 
     501                * 
     502            FROM 
     503                ( 
     504                    SELECT 
     505                        rpad(oid::regclass::text, 32) || ' (' || pg_size_pretty(pg_relation_size(oid)) || ')' 
     506                    FROM 
     507                        pg_class 
     508                    WHERE 
     509                        relkind = 'r' 
     510                        and relname !~ '^pg_' 
     511                    order by 
     512                        pg_relation_size(oid) desc 
     513                    limit 5 
     514                ) x 
     515            order by 
     516                1 
     517        } 
    511518    ); 
    512519 
     
    523530    printf "Sample tables : %s\n", shift @tables; 
    524531    printf "              - %s\n", $_ for @tables; 
    525     $self->{ 'database' } = $db->[ 0 ]->[ 1 ]; 
    526532    return; 
    527533} 
     
    537543        'fkey-jobs'  => 1, 
    538544    }; 
     545 
    539546    my $is_ok = GetOptions( $opts, qw( help|? input|o=s compressor|c=s jobs|j=i fkey-jobs|f=i psql|p=s pg_restore|r=s ) ); 
    540547    pod2usage( '-verbose' => 1, ) unless $is_ok; 
     
    558565    $opts->{ 'input' } = abs_path( $opts->{ 'input' } ); 
    559566    @{ $self }{ keys %{ $opts } } = values %{ $opts }; 
    560     return; 
     567    chdir $self->{ 'input' }; 
     568    return; 
     569
     570 
     571sub pg_restore { 
     572    my $self = shift; 
     573    my ( $lines, $to_db ) = @_; 
     574 
     575    my ( $list_fh, $list_filename ) = tempfile( 'list.XXXXXX', 'DIR' => $self->{ 'tmpdir' }, ); 
     576    print $list_fh $_->{ 'line' } . "\n" for @{ $lines }; 
     577    close $list_fh; 
     578 
     579    my @cmd = ( 'pg_restore', '-L', $list_filename ); 
     580    push @cmd, ( '-d', $self->{'database'} ) if $to_db; 
     581    push @cmd, 'schema.dump'; 
     582 
     583    my $response = $self->run_command( @cmd ); 
     584 
     585    unlink $list_filename; 
     586 
     587    return $response; 
    561588} 
    562589 
    563590sub psql { 
    564     my $self   = shift; 
    565     my $query  = shift; 
    566     my $output = $self->run_command( $self->{ 'psql' }, '-qAtX', '-F', "\t", '-c', $query, ); 
    567     my @rows   = grep { '' ne $_ } split /\r?\n/, $output; 
    568     my @data   = map { [ split /\t/, $_ ] } @rows; 
     591    my $self       = shift; 
     592    my $query      = shift; 
     593    my $query_file = shift; 
     594 
     595    my $remove_query_file = 1; 
     596 
     597    my $query_fh; 
     598    if ( defined $query_file ) { 
     599        $remove_query_file = 0; 
     600        open $query_fh, '>', $query_file or croak( "Cannot write to $query_file: $OS_ERROR\n" ); 
     601    } 
     602    else { 
     603        ( $query_fh, $query_file ) = tempfile( 'fast.dump.XXXXXXXX', 'TMPDIR' => 1, ); 
     604    } 
     605 
     606    print $query_fh $query; 
     607    close $query_fh; 
     608    my $output = $self->run_command( qw( psql -qAtX -F ), "\t", '-f', $query_file ); 
     609    unlink $query_file if $remove_query_file; 
     610 
     611    my @rows = grep { '' ne $_ } split /\r?\n/, $output; 
     612    my @data = map { [ split /\t/, $_ ] } @rows; 
     613 
    569614    return \@data; 
    570615} 
     
    573618    my $self = shift; 
    574619    my ( @cmd ) = @_; 
     620 
     621    # Use paths provided by user as command line options 
     622    $cmd[ 0 ] = $self->{ $cmd[ 0 ] } if $self->{ $cmd[ 0 ] }; 
    575623 
    576624    my $real_command = join( ' ', map { quotemeta } @cmd );