Postgresql – Error while restoring Postgres DB Dump

errorspostgresqlpostgresql-9.1restore

Using below command to restore the database

Restoration command:

cat my_db_dump.sql.gz | gunzip | psql -d new_database -U my_username

Note: my_db_dump.sql.gz size is 4.5 GB, (extracted size will be around 70 GB)

Getting the below exception (after executing above restoration command)

gunzip: stdin: unexpected end of file
ERROR: unterminated quoted string at or near "'\x3c3f78
(hexadecimal junks removed)
LINE 1: SELECT pg_catalog.lowrite(0, '\x3c3f786d6c2076657273696f6e3d…

Best Answer

Based on the first error message, the .gz backup file seems to be truncated.

This could be confirmed with the command:

gunzip -c my_db_dump.sql.gz >/dev/null

Presumably this will lead to the same error message: unexpected end of file which is the root cause, the other error from postgres being the consequence of the abrupt end of the data stream.

If the .gz file is a copy resulting from a transfer, you want to compare its size against the size of the original, and transfer it again if it's incomplete. If that's the only copy or if the original itself is incomplete, completing the restore will not be possible.

Related Solutions

Postgresql – Error restoring Postgres DB

The -c option of pg_restore does this:

Clean (drop) database objects before recreating them.

As you seem to only migrate a single table from here to there, you can safely omit -c from your command line. If you only need the data, you should tell it to pg_dump instead, by using the -a option. If your table structure is different as well, then you should prepare an ALTER TABLE statement manually to reflect those changes in the target database. (You could as well use a few tools for the latter, but for only one table the manual solution is usually much faster.)

Mysql – Error while restoring a Database from an SQL dump

The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction.

It doesn't sound like you're dealing with a mysqldump output file, there. Try the file utility.

shell> file dumpfile.sql
dumpfile.sql: ASCII text

If you don't get the ASCII text response, you're dealing with either something that isn't a dump file from mysqldump at all, or you're dealing with something that's been compressed (with gzip or bzip2, for example), which you'd need to uncompress before piping it into mysql.

If you see SQLite 3.x database then you definitely have your answer... it's a raw SQLite database, not a MySQL dump file.

Indeed, the first few bytes of a SQLite database are these:

53 51 4C 69 74 65 20 66  SQLite f
6F 72 6D 61 74 20 33 00  ormat 3^@

Note that the 16th octet here is 0x00, explaining the ERROR: ASCII '\0' appeared in the statement... message in this case. The suggestion that --binary-mode is appropriate is a false alarm.

Windows users: the 'file' utility is a tool from Unix, but the Windows version can be found here.

Best Answer

Related Solutions

Postgresql – Error restoring Postgres DB

Mysql – Error while restoring a Database from an SQL dump

Related Question