Re: Dumping DB containing json

Michael Kovacs <kovacs@gmail.com> writes:
> I'm currently stuck on a problem with a db dump from pg_dump that contains
> a database with text columns containing json. I'm wondering if there's
> something I need to do to properly dump the data that I'm not doing or to
> restore.

It's unlikely that the specific content of the text columns has anything
to do with it.

> I get tons of errors about the nulls in the data along with the following
> syntax errors:

> psql:rm_prod-1-19-2012.sql:148863: invalid command \N
> psql:rm_prod-1-19-2012.sql:148864: invalid command \N
> ...

You've extracted a rather useless subset of the error messages here.
What appears to be happening is that psql is trying to read COPY data
as though it were SQL commands; which suggests that the initial COPY
command failed for some reason, but the message that might tell you why
is before these.

One obvious question is whether you are restoring into an empty database.
One way to produce this sort of symptom is to load into a database
that already has tables of the same names but not the same column
lists.  (Then, running the pg_dump script will yield first a "table
already exists" error on CREATE TABLE, then a "column does not exist"
error on the COPY command's column list, and then massive spewage like
what you illustrated because psql doesn't switch into COPY data mode
as the script is expecting.)

If that's not it, let's have a look at the *first* few dozen messages
you get.

            regards, tom lane