Postgresql – database not restoring from dump file

Just from the words in your question I suspect the following: You most likely have innodb_file_per_table disabled.

NOTE : The following info is based on innodb_file_per_table being disabled

When insert data into InnoDB tables, everything and its grandmother lands in the system tablespace file, better known as ibdata1. What does ibdata1 actually contain?

• Table Data
• Table Indexes
• Metadata
• MVCC Info

Table Data and Indexes while initially bloat ibdata1. The metadata is just simply the data dictionary + the list of tablespace_ids assign on a per-table basis.

What about MVCC (Multiversioning Concurrency Control)? This represents the systrem objects designed to support transaction isolation, rollbacks, undo logs, insert buffers for secondayr indexes, and the double- write buffer.

You need to clean up the InnoDB infrastructure. I already wrote StackExchange posts on how and why to do this:

• Why does InnoDB store all databases in one file?
• Is innodb_file_per_table advisable?
• https://stackoverflow.com/a/4056261/491757
• https://serverfault.com/a/231400/69271

Getting back to your original question, the only way to estimate the size of the ibdata1 on reload would have been to run this query before the mysqldump:

SELECT
    data_length/power(1024,3) InnoDBData,
    index_length/power(1024,3) InnoDBIndexes,
    (data_length+index_length)/power(1024,3) InnoDBSize
FROM
    information_schema.tables
WHERE
    engine='InnoDB';

This will report the size of the data in GB. Upon a fresh reload of the ibdata1 (with innodb_file_per_table disabled in your case), this would have been the rule of thumb for the size estimation.

From the dump file size, it is hard to judge because the combined total size of data pages and index pages maybe far less that the size of ibdata1 the dump was created from. That difference would be have leftover space from the bloating of MVCC system objects (rollback segments, undo logs, double-write buffer, secondary index insert buffer). From another perspective, data pages could outnumber index pages, and vice versa. This could be due to too many indexes, bad design, or just a judicious amount of data.

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.