3

I've got a large'ish (> 700GB) PostgreSQL database. I'm working on importing it into AWS RDS. I realize there are modules that will cause problems, we're dealing with that separately, so for right now, I am just working on migrating the data to RDS.

I'm migrating to and from PostgreSQL 9.4.5 using pg_dump and pg_restore 9.4.5.

Several hours into the transfer (pg_dump to pg_restore with a directory format), I checked the log and realized there is one thing (possibly a table?) owned by the Postgres user I was unaware of.

I found the line in the toc.dat file that made the call and unpacked the .dat.gz file to find it only contains "\." (sans the quotes) in it.

So, my question is, short of stopping the transfer and starting over, is there a way to simply transfer that specific command and file once the import is completed?

Here are the relevant details.

The pg_dump command:

pg_dump -h localhost -p 5432 -d DBNAME -U USERNAME -F d -f /mnt/backup -j 7

The pg_restore command:

pg_restore --dbname=DBNAME --no-tablespaces --host=HOSTNAME --port=5432 --username=USERNAME --verbose -F d -j 6 /mnt/backup

The section from the toc.dat file. I realize the directory format outputs in binary format, but I am hoping that the fact that this is identifiable means there is a way to go back and fix that one error.

^@^F^@^@^@public^A^A^@^@^@^@^H^@^@^@postgres^@^E^@^@^@false^@^C^@^@^@173^A^A^@^@^@^@    ^@^@^@10393.dat^@W<^@^@^@^A^@^@^@^@^A^@^@^@0^@^E^@^@^@26106^@^N^@^@^@search_name_66^@
^@^@^@TABLE DATA^@^C^@^@^@^@^@^@^@^@^@^@^@^@^@^@]^@^@^@COPY search_name_66 (place_id, search_rank, address_rank, name_vector, centroid) FROM stdin;

The actual error reported by pg_restore:

pg_restore: [archiver (db)] Error from TOC entry 10393; 0 16787 TABLE DATA spatial_ref_sys postgres
6

I found the line in the toc.dat file that made the call and unpacked the .dat.gz file to find it only contains "\." (sans the quotes) in it.

\. is not a command, it marks the end of data in a COPY stream. If the corresponding data file inside the dump contains only that, then the source table was empty.

Also note that you can pg_restore --list to list the contents of a dump in directory or compressed format.

You may also restore the contents of an individual table into an SQL file rather than in the target database, then modify that file as convenient, and play it into the target. See the pg_restore manpage for more.

  • Thanks! I'm assuming that table was never generated (though I will double check). If correct, how can I restore that file? Obviously, I can create that table but I'm also asking for the possibility that the data file had actual data in it. Is there a way to pg_restore that one data file? Perhaps just like a regular dump file? – Brooks Jan 11 '16 at 11:35
  • @Brooks: the contents of one table can be displayed with pg_restore --table=tablename. – Daniel Vérité Jan 11 '16 at 12:56
  • Is the last part of the answer you describe the -f filename/--file=filename flag? – lpd Jul 17 '18 at 12:54
  • @lpd: either -f file.sql or pg_restore [options] file.dump >file.sql as it's the same thing. – Daniel Vérité Jul 17 '18 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.