Thread: Dumping DB containing json
Hi there,
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.
This is how I'm doing each:
pg_dump -U postgres mydb -f myfile.sql
and to restore:
psql -U postgres mydb -f myfile.sql
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
psql:rm_prod-1-19-2012.sql:148865: invalid command \N
psql:rm_prod-1-19-2012.sql:148866: invalid command \N
psql:rm_prod-1-19-2012.sql:148867: invalid command \N
psql:rm_prod-1-19-2012.sql:148868: invalid command \
psql:rm_prod-1-19-2012.sql:148870: invalid command \N
psql:rm_prod-1-19-2012.sql:148871: invalid command \N
psql:rm_prod-1-19-2012.sql:148877: invalid command \N
psql:rm_prod-1-19-2012.sql:148879: invalid command \N
psql:rm_prod-1-19-2012.sql:148882: ERROR: syntax error at or near "",""
LINE 1: ","text":"RT @GSElevator: #1: Congress is allowed to trade o...
^
psql:rm_prod-1-19-2012.sql:148882: ERROR: syntax error at or near "a"
LINE 1: a href="
^
psql:rm_prod-1-19-2012.sql:148882: ERROR: syntax error at or near "http"
LINE 1: http://blackberry.com/twitter"
^
psql:rm_prod-1-19-2012.sql:148882: ERROR: syntax error at or near "rel"
LINE 1: rel="
^
psql:rm_prod-1-19-2012.sql:148882: ERROR: syntax error at or near "nofollow"
LINE 1: nofollow"
^
psql:rm_prod-1-19-2012.sql:148882: ERROR: syntax error at or near "&"
LINE 1: >
^
psql:rm_prod-1-19-2012.sql:148882: invalid command \
psql:rm_prod-1-19-2012.sql:148892: invalid command \N
Any guidance on what I'm doing wrong would be appreciated.
Thanks,
-Michael
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
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@sss.pgh.pa.us> wrote:
Michael Kovacs <kovacs@gmail.com> writes:It's unlikely that the specific content of the text columns has anything
> 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.
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