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?

Genital

did you try using pg_restore?.. – Echolocation

@VaoTsun my problem is I don't know how to put that dump file in Google Cloud Platform Console. – Genital

cloud.google.com/sql/docs/postgres/import-export/… states you can't use custom format for google cloud sql – Echolocation

@VaoTsun i know. again, the problem is when I run that command pg_restore -d my_new_database temp.sql where to put temp.sql in google cloud storage and how to include like s3://asdfafasfa something like – Genital

but this is totally unrelated to the posts subject?.. Please post a different question then – Echolocation

nope, problem is already mention in detail though – Genital

Answer 1

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.

Echolocation

i know. again, the problem is when I run that command pg_restore -d my_new_database temp.sql where to put temp.sql in google cloud storage and how to include like s3://asdfafasfa something like – Genital

Answer 2

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

Conidium