Выкусывание из дампа¶

Перобразование дампа в схему:¶

nice -n19 bzcat db.backup-2011-07-15.sql.bz2 \
|nice -n19 awk '
BEGIN{
 search_comment=1;
 search_data=0;
 write_out=1
}
{
 if ((search_comment==0)&&(search_data==1)&&($0 ~ /^COPY /)) {
  write_out=0;
  printf ("\n%s",$2) >"/dev/stderr" 
 }
 if ((search_comment==1)&&($0 ~ /^-- Data for Name: /)) {
  search_data=1;
  search_comment=0;
 };
 if (write_out==1) print $0;
 if ((write_out==0)&&($0 ~ /^\\\.$/)) {
  write_out=1;
  search_data=0;
  search_comment=1
 };
 if (NR%1000==0) printf "." > "/dev/stderr" 
}
END{
 printf "\nDONE\n" > "/dev/stderr" 
}' \
|nice -n19 bzip2 -9 > db-cut.backup-2011-07-15-schema.sql.bz2

Выкусывание таблиц из дампа¶

(Скрипт долго работает, но он рабочий)

xzcat pg_dumpall-2012-03-18.sql.xz | awk -v tables=settings,wiki -f dump-cut.awk > file.sql

BEGIN{
    search_comment=1
    search_schema=0;
    write_out=0
#    tables="settings,wikis" 
    split(tables,table_index,",")
    for (table in table_index) {
        table_data[table]=0
        table_schema[table]=0
        if (index(table_index[table],".")) {
            split(table_index[table],a,".")
            schema_index[table]=a[1]
            table_index[table]=a[2]
        }
        else
        {
            schema_index[table]="public" 
        }
    }
}
{
    if ((search_comment==1)&&($0 ~ /^-- Name: [^ ]+; Type: TABLE; /)) {
        for (table in table_index) {
            if ((table_schema[table]==0)&&($3==table_index[table]";")&&($7==schema_index[table]";")) {
                printf ("\nschema: %s",table_index[table]) >"/dev/stderr" 
                search_schema=2;
                search_comment=0;
                write_out=1
                table_schema[table]=1
            }
        }
    };

    if ((search_comment==1)&&($0 ~ /^-- Data for Name: [^ ]+; Type: TABLE DATA; /)) {
        for (table in table_index) {
            if ((table_data[table]==0)&&($5==table_index[table]";")&&($10==schema_index[table]";")) {
                printf ("\ndata: %s",table_index[table]) >"/dev/stderr" 
                search_comment=0;
                write_out=1
                table_data[table]=1
            }
        }
    };
    if ((write_out==1)&&(search_comment==0)&&(search_schema>0)&&($0 ~ /^-- /)) {
        search_schema--
        if (search_schema==0) {
            write_out=0;
            search_comment=1
        }
    }
    if (write_out==1) print $0;
    if ((write_out==1)&&($0 ~ /^\\\.$/)) {
        write_out=0;
        search_comment=1
        do_quit=1
        for (table in table_index) {
            if ((table_data[table]==0)||(table_schema[table]==0)) do_quit=0
        }
        if (do_quit==1) {
            printf "\nDONE\n" > "/dev/stderr" 
            exit
        }
    };
    if (NR%1000==0) printf "." > "/dev/stderr" 
}
END{
    no_schema="" 
    no_data="" 
    for (table in table_index) {
        if (table_schema[table]==0) no_schema=no_schema","schema_index[table]"."table_index[table]
        if (table_data[table]==0) no_data=no_data","schema_index[table]"."table_index[table]
    }
    if ((no_schema != "")||(no_data != "")) printf "\nINCOPLETE:\n" > "/dev/stderr" 
    if (no_schema != "") printf "No schema for: %s\n", no_schema > "/dev/stderr" 
    if (no_data != "") printf "No data for: %s\n", no_data > "/dev/stderr" 
}