Обсуждение: [GENERAL] pg_dump and insert json with copy

Hello,

i had backup my database with the following command

# schema
   PGCLIENTENCODING=utf-8 pg_dump -p 5432 -s -E UTF-8 database | gzip -c
> database_schema.sql.gz
# data
   PGCLIENTENCODING=utf-8 pg_dump -p 5432 -a -E UTF-8 database | gzip -c
> database_data.sql.gz

and try to restore it with

# create db
   PGCLIENTENCODING=utf-8 psql -p 5432 -c "CREATE DATABASE database WITH
ENCODING 'UTF8'"
# schema
   PGCLIENTENCODING=utf-8 zcat database_schema.sql.gz | psql -p 5432
database
# data
   PGCLIENTENCODING=utf-8 zcat database_data.sql.gz | psql -p 5432 database

some tables has json type and show the following error at restore time:

ERROR:  syntax error at or near "a"
LINE 1: a:1:{i:0;
        ^
ERROR:  syntax error at or near "s"
LINE 1: s:1:"1";
        ^
ERROR:  syntax error at or near "}"
LINE 1: }s:2:"x4";
        ^
ERROR:  syntax error at or near "a"
LINE 1: a:1:{^CCancel request sent
ERROR:  syntax error at or near "s"
LINE 1: s:4:"4508";
        ^
Is there a way to do a clean backup and restore with this json data?

best regards,
basti

basti <mailinglist@unix-solution.de> writes:
> some tables has json type and show the following error at restore time:

> ERROR:  syntax error at or near "a"
> LINE 1: a:1:{i:0;
>         ^

Is that really the very first error reported by the restore run?
It seems much more likely to be a failure cascading from something else.

In any case, you haven't provided nearly enough information for anyone
else to investigate this problem.  Please see
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

            regards, tom lane