The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database

Question

I've encountered following error message when I'm trying to import a postgres dump file to Google Cloud SQL.

The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database. This may take a few minutes. While this operation is running, you may continue to view information about the instance.

Please let me know how to import a postgres dump file (which is hosted on Google Cloud Storage) to Google Cloud SQL?

Answer 1

I got the same error below:

The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.

Because I exported and archived apple database to backup.sql in -Fc(custom format) with pg_dump as shown below:

pg_dump -U john -Fc apple > backup.sql

Then, I tried to import archive backup.sql to orange database with psql which must be used to import non-archive files:

psql -U john -f backup.sql orange

So, I did it with pg_restore which must be used to import archive files, then I could solve the error. *My answer explains how to export and archive a database with -Fc and -Ft and my answer explains how to import a database:

pg_restore -U john -d orange < backup.sql

Answer 2

https://cloud.google.com/sql/docs/postgres/import-export/creating-sqldump-csv

Important: you must ensure that your SQL dump file is created with the following flags:

• --no-owner Ownership change commands must not be included in the SQL dump file.
• --format=plain Only plain SQL format is currently supported by Cloud SQL.

and from error you see I assume you used custom format, thus the import sql you prepared won't work (at least by documentation)

https://www.postgresql.org/docs/current/static/app-pgdump.html

--format=format Selects the format of the output. format can be one of the following:

p plain Output a plain-text SQL script file (the default).

c custom Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.