Re: Dumping DB containing json

Hi Tom,

Thanks for the reply. I actually figured the problem out and part of it was
that the database did have some or all of the tables in the dump along with
another error that was fixed by commenting out the offending line in the
dump file. Apologies for the noise.

-Michael

On Thu, Jan 19, 2012 at 9:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Michael Kovacs <kovacs(at)gmail(dot)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
>