2

I'm trying to upload a pg_dump file from a postgres 9.6 database that I just built locally on my Windows machine up into Google Cloud SQL. I have followed the directions on this page as best I can on my Windows machine (I mannually commented out the "EXTENSION" statements in the dump file). I then uploaded the pg_dump file to a Google Cloud Bucket, and finally tried to import the pg_dump'ed file into Google Cloud SQL. I keep getting the following error:

Import failed: ERROR: invalid byte sequence for encoding "UTF8": 0xff Import error: exit status 3

I've looked around and on Stack Overflow and some similar posts suggested putting in a additional arguments to specify the encoding. I've tried the following 3 pg_dump commands and none of them seem to be compatible with Google Cloud's import process. Any ideas?

pg_dump -U [USER_NAME] --format=plain --no-owner --no-acl [DB_NAME] > dbexport.sql

pg_dump --encoding=ISO88591 -U [USER_NAME] --format=plain --no-owner --no-acl [DB_NAME] > dbexport.sql

pg_dump --encoding=UTF8 -U [USER_NAME] --format=plain --no-owner --no-acl [DB_NAME] > dbexport.sql

I also did a search in the .sql file using Notepad++ for "\x{0xff}" per this post and several upper/lower case variants and couldn't find that character in the file anywhere.

Jed
  • 1,823
  • 4
  • 20
  • 52
  • 1
    BOM issue? https://en.wikipedia.org/wiki/Byte_order_mark – cske Oct 23 '18 at 04:41
  • supposedly 0xFF is equal to "ÿ", but I doubt that character is in my database. – Jed Oct 24 '18 at 03:20
  • If it was a BOM issue, what should I try differently than I am currently doing? – Jed Oct 24 '18 at 03:41
  • 1
    check the first to byte, to be sure it is caused by BOM, this could happen when you open exported sql edit & save notepad automatically add BOM, if thats the case remove the first two byte and then upload to cloud. [Bom & Notepad++](https://stackoverflow.com/questions/32986445/remove-a-bom-character-in-a-file) – cske Oct 24 '18 at 17:32
  • Awesome! That did it! I just had to encode the file as UTF-8 in Notepad++ and then save it. That version loaded into Google Cloud SQL with no issues. @cske, write that up as an answer and I'll give you the credit. – Jed Oct 25 '18 at 02:43

1 Answers1

2

Problem is when dump file was modified, your editor added BOM to the file. Remove the first two bytes of file (eg. Bom & Notepad++) to be able to import it.

cske
  • 2,233
  • 4
  • 26
  • 24