CODEWITHSUNDEEP
databasepostgresqlgoogle-app-enginegcloudcustom-formatting
Pyae Phyoe Shein
Pyae Phyoe Shein

Reputation: 13827

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

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?

Upvotes: 14

Views: 21530

Answers (3)

Rez.Net
Rez.Net

Reputation: 1462

This bugged me for a few days until I figured what I had done wrong. It might be the cause of the confusion here.

When dumping with custom format, you have to specify -Fc in pg_restore as well to tell it what type of file to expect (which is custom format here).

There is no easy way for pg_restore to tell the type of the input file so it needs a hint.

Upvotes: 0

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 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

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51609

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.

Upvotes: 8

Related Questions