Обсуждение: Restore from dump file: parse error

Hi!

I am updating from 6.3.2 to 6.4.2. Obviously I started by dumping my
databases, i.e.:

pg_dump database > database.sql

After the update, I restoring my databases, i.e.:

createdb database
psql database < database.sql

(interestingly pg_dump doesn't insert create database command, I think it
should)

However, this database have big text fields. pg_dump made some text data
spanned several lines with "\" on the unfinished lines. On reading the
database.sql, psql choke on this continued lines, give "PARSER ERROR" and
print out it help page, eventually, psql crash on segmentation fault.

Anything can be done on this stage?

Thank you.

Arif

Hi,

To get the database creation you must use the -s option with pg_dump.
(Dump out only the schema, no data)

The parse error is probably due to a single quote somewhere in you text
data...

I use : pg_dump -D mydatabase > recover.sql

On Fri, 7 May 1999, darold wrote:

>To get the database creation you must use the -s option with pg_dump.
>(Dump out only the schema, no data)

Well, I like to have both.

>The parse error is probably due to a single quote somewhere in you text
>data...

Well, you are correct! Is there anyway to quote that single quote?

>I use : pg_dump -D mydatabase > recover.sql

Should it solve the single quote problem?

Thanks!

Arif

Hi,

Well to quote that single quote, you have to quote that single quote :-)
ex : s_field='suivre l'actualite'
=>  s_field='suivre l''actualite'

pg_dump -D mydatabase > recover.sql  or other options will never solve
this pg_dump feature.

You can make a little program with perl to process your entire file by
search single quote not after an = and not before a , and space and ) and (.

Regards,

Gilles Darold

S P Arif Sahari Wibowo wrote:

> On Fri, 7 May 1999, darold wrote:
>
> >To get the database creation you must use the -s option with pg_dump.
> >(Dump out only the schema, no data)
>
> Well, I like to have both.
>
> >The parse error is probably due to a single quote somewhere in you text
> >data...
>
> Well, you are correct! Is there anyway to quote that single quote?
>
> >I use : pg_dump -D mydatabase > recover.sql
>
> Should it solve the single quote problem?
>
> Thanks!
>
> Arif

At 10:37 +0300 on 10/05/1999, darold wrote:


> Hi,
>
> Well to quote that single quote, you have to quote that single quote :-)
> ex : s_field='suivre l'actualite'
> =>  s_field='suivre l''actualite'
>
> pg_dump -D mydatabase > recover.sql  or other options will never solve
> this pg_dump feature.
>
> You can make a little program with perl to process your entire file by
> search single quote not after an = and not before a , and space and ) and (.
>
> Regards,
>
> Gilles Darold

Just a minute. I tried dumping and restoring fields with and without single
quotes in them in 6.4.2, and there was no problem, and I didn't need to
change anything. Single quotes should not be a problem for COPY, because
they are not considered delimiters. The strings are not surrounded with
quotes. Thus, the strings you see in the COPY commands are the actual
strings that you have in the database, except for newlines and tabs that
have a backslash before them.

So, the question is what went wrong with the dump. Has it dumped correctly,
that is, with a single, unescaped quote where a single quote is in the
fields? In that case, the 6.3.2 psql is the culprit, and since your issue
is with upgrading to 6.4.2, you need not worry.

If, on the other hand, the dump makes the copy with single quotes doubled
or backslashed, you will have to use some sed or perl script to remove
that, because they will not be interpreted correctly. This is the 6.3.2
dump's fault.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma