I ran an export on my database with
gcloud sql export sql "${INSTANCE_NAME}" "${GCS_BUCKET_URI}/${INSTANCE_NAME}_${TIMESTAMP}" --database=postgres --offload --async --clean --parallel --format="value(name)"
This operation created a directory archive export for use with pg_restore
in my bucket. I downloaded that bucket with
gcloud storage cp --recursive "${GCS_BUCKET_URI}/${INSTANCE_NAME}_${TIMESTAMP}"
Then tried to restore it with:
docker run --rm -it --volumes-from playgroundgatewayv2-postgres-1 --network host -v "$(pwd)":/backup postgres:${POSTGRES_VERSION} sh -c "pg_restore --jobs=${JOBS} --dbname='${CONNECTION_URI}' --no-acl /backup/${INSTANCE_NAME}_${TIMESTAMP}"
This gave me an error for each table with data in it:
pg_restore: error: COPY failed for table "core_revision": ERROR: invalid byte sequence for encoding "UTF8": 0x8b
CONTEXT: COPY core_revision, line 1
pg_restore: error: COPY failed for table "core_project": ERROR: invalid byte sequence for encoding "UTF8": 0x8b
CONTEXT: COPY core_project, line 1
pg_restore: error: COPY failed for table "core_message": ERROR: invalid byte sequence for encoding "UTF8": 0x8b
CONTEXT: COPY core_message, line 1
Are CloudSQL for PostgreSQL in a different format? I've been able to export the database as SQL, restore it, then do a similar pg_dump --format=directory
on my local DB, and then restore from that successfully.