База знаний » БД PostgreSQL

Wiki »

Dump-cut » История » Редакция 6

Редакция 5 (Андрей Волков, 2015-04-13 17:23) → Редакция 6/9 (Андрей Волков, 2015-04-13 19:18)

h1. Dump-cut 

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

 <pre> 
 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 
 </pre> 

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

 <pre> 
 xzcat pg_dumpall-2012-03-18.sql.xz pv -petraW blizko.backup-2014-01-30.sql.bz2 | awk -v tables=settings,wiki -f dump-cut.awk nice -n19 bunzip2 | nice -n19 sed -e -n '/^COPY products (.*) FROM stdin;$/,/^\\.$/p;/^\\.$/q' | nice -n19 gzip -3 > file.sql 
 </pre> 

 <pre> 
 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" 
 } products-2014-01-30_00-51.sql.gz 
 </pre>