Dump-cut » История » Версия 7
Андрей Волков, 2015-04-13 19:19
| 1 | 7 | Андрей Волков | h1. Выкусывание из дампа |
|---|---|---|---|
| 2 | 1 | Андрей Волков | |
| 3 | Перобразование дампа в схему: |
||
| 4 | |||
| 5 | <pre> |
||
| 6 | 2 | Андрей Волков | nice -n19 bzcat db.backup-2011-07-15.sql.bz2 \ |
| 7 | |nice -n19 awk ' |
||
| 8 | BEGIN{ |
||
| 9 | 5 | Андрей Волков | search_comment=1; |
| 10 | search_data=0; |
||
| 11 | write_out=1 |
||
| 12 | 2 | Андрей Волков | } |
| 13 | { |
||
| 14 | 5 | Андрей Волков | if ((search_comment==0)&&(search_data==1)&&($0 ~ /^COPY /)) { |
| 15 | write_out=0; |
||
| 16 | 2 | Андрей Волков | printf ("\n%s",$2) >"/dev/stderr" |
| 17 | } |
||
| 18 | 5 | Андрей Волков | if ((search_comment==1)&&($0 ~ /^-- Data for Name: /)) { |
| 19 | search_data=1; |
||
| 20 | search_comment=0; |
||
| 21 | 2 | Андрей Волков | }; |
| 22 | 5 | Андрей Волков | if (write_out==1) print $0; |
| 23 | if ((write_out==0)&&($0 ~ /^\\\.$/)) { |
||
| 24 | write_out=1; |
||
| 25 | search_data=0; |
||
| 26 | search_comment=1 |
||
| 27 | 2 | Андрей Волков | }; |
| 28 | if (NR%1000==0) printf "." > "/dev/stderr" |
||
| 29 | } |
||
| 30 | END{ |
||
| 31 | printf "\nDONE\n" > "/dev/stderr" |
||
| 32 | }' \ |
||
| 33 | |nice -n19 bzip2 -9 > db-cut.backup-2011-07-15-schema.sql.bz2 |
||
| 34 | 1 | Андрей Волков | </pre> |
| 35 | 3 | Андрей Волков | |
| 36 | 4 | Андрей Волков | h2. Выкусывание таблиц из дампа |
| 37 | 3 | Андрей Волков | |
| 38 | <pre> |
||
| 39 | 6 | Андрей Волков | xzcat pg_dumpall-2012-03-18.sql.xz | awk -v tables=settings,wiki -f dump-cut.awk > file.sql |
| 40 | </pre> |
||
| 41 | |||
| 42 | <pre> |
||
| 43 | BEGIN{ |
||
| 44 | search_comment=1 |
||
| 45 | search_schema=0; |
||
| 46 | write_out=0 |
||
| 47 | # tables="settings,wikis" |
||
| 48 | split(tables,table_index,",") |
||
| 49 | for (table in table_index) { |
||
| 50 | table_data[table]=0 |
||
| 51 | table_schema[table]=0 |
||
| 52 | if (index(table_index[table],".")) { |
||
| 53 | split(table_index[table],a,".") |
||
| 54 | schema_index[table]=a[1] |
||
| 55 | table_index[table]=a[2] |
||
| 56 | } |
||
| 57 | else |
||
| 58 | { |
||
| 59 | schema_index[table]="public" |
||
| 60 | } |
||
| 61 | } |
||
| 62 | } |
||
| 63 | { |
||
| 64 | if ((search_comment==1)&&($0 ~ /^-- Name: [^ ]+; Type: TABLE; /)) { |
||
| 65 | for (table in table_index) { |
||
| 66 | if ((table_schema[table]==0)&&($3==table_index[table]";")&&($7==schema_index[table]";")) { |
||
| 67 | printf ("\nschema: %s",table_index[table]) >"/dev/stderr" |
||
| 68 | search_schema=2; |
||
| 69 | search_comment=0; |
||
| 70 | write_out=1 |
||
| 71 | table_schema[table]=1 |
||
| 72 | } |
||
| 73 | } |
||
| 74 | }; |
||
| 75 | |||
| 76 | if ((search_comment==1)&&($0 ~ /^-- Data for Name: [^ ]+; Type: TABLE DATA; /)) { |
||
| 77 | for (table in table_index) { |
||
| 78 | if ((table_data[table]==0)&&($5==table_index[table]";")&&($10==schema_index[table]";")) { |
||
| 79 | printf ("\ndata: %s",table_index[table]) >"/dev/stderr" |
||
| 80 | search_comment=0; |
||
| 81 | write_out=1 |
||
| 82 | table_data[table]=1 |
||
| 83 | } |
||
| 84 | } |
||
| 85 | }; |
||
| 86 | if ((write_out==1)&&(search_comment==0)&&(search_schema>0)&&($0 ~ /^-- /)) { |
||
| 87 | search_schema-- |
||
| 88 | if (search_schema==0) { |
||
| 89 | write_out=0; |
||
| 90 | search_comment=1 |
||
| 91 | } |
||
| 92 | } |
||
| 93 | if (write_out==1) print $0; |
||
| 94 | if ((write_out==1)&&($0 ~ /^\\\.$/)) { |
||
| 95 | write_out=0; |
||
| 96 | search_comment=1 |
||
| 97 | do_quit=1 |
||
| 98 | for (table in table_index) { |
||
| 99 | if ((table_data[table]==0)||(table_schema[table]==0)) do_quit=0 |
||
| 100 | } |
||
| 101 | if (do_quit==1) { |
||
| 102 | printf "\nDONE\n" > "/dev/stderr" |
||
| 103 | exit |
||
| 104 | } |
||
| 105 | }; |
||
| 106 | if (NR%1000==0) printf "." > "/dev/stderr" |
||
| 107 | } |
||
| 108 | END{ |
||
| 109 | no_schema="" |
||
| 110 | no_data="" |
||
| 111 | for (table in table_index) { |
||
| 112 | if (table_schema[table]==0) no_schema=no_schema","schema_index[table]"."table_index[table] |
||
| 113 | if (table_data[table]==0) no_data=no_data","schema_index[table]"."table_index[table] |
||
| 114 | } |
||
| 115 | if ((no_schema != "")||(no_data != "")) printf "\nINCOPLETE:\n" > "/dev/stderr" |
||
| 116 | if (no_schema != "") printf "No schema for: %s\n", no_schema > "/dev/stderr" |
||
| 117 | if (no_data != "") printf "No data for: %s\n", no_data > "/dev/stderr" |
||
| 118 | } |
||
| 119 | 3 | Андрей Волков | </pre> |