Thread: 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