Thread: issue with pg_restore
Hi list, I'm trying to restore a backup into a database with a new name the dump was done on a 8.4 server with: pg_dump -F c -f bakfile olddb i'm trying to restore it with: createdb newdb; pg_restore -v --jobs=4 --disable-triggers --no-tablespaces --dbname=newdb bakfile or even just: createdb newdb; pg_restore -v --dbname=newdb bakfile It doesn't work .. pg_restore claims to be creating tables, indexes, etc. and there are no errors in the output. It only takes a few seconds to run (the file is ~250MB). In newdb, all the tables in the "public" schema are missing. All the functions and triggers were created though, tables in a non "public" schema were created but don't contain data. Tried on 8.4 and on 9.0 with the same result. I turned on server statement logging and don't see statements that would create the missing tables, there are alot of BEGIN/COMMIT statements with nothing in between. the only way i got it to work was to run: pg_restore bakfile | psql newdb which loads everything just fine but i was hoping to use parallel restore to speed it up. any ideas? -nigel.
On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote: > Hi list, > I'm trying to restore a backup into a database with a new name > > the dump was done on a 8.4 server with: > pg_dump -F c -f bakfile olddb > > i'm trying to restore it with: > createdb newdb; pg_restore -v --jobs=4 --disable-triggers > --no-tablespaces --dbname=newdb bakfile > or even just: > createdb newdb; pg_restore -v --dbname=newdb bakfile > > It doesn't work .. pg_restore claims to be creating tables, indexes, > etc. and there are no errors in the output. It only takes a few seconds > to run (the file is ~250MB). > In newdb, all the tables in the "public" schema are missing. All the > functions and triggers were created though, tables in a non "public" > schema were created but don't contain data. Tried on 8.4 and on 9.0 with > the same result. > I turned on server statement logging and don't see statements that would > create the missing tables, there are alot of BEGIN/COMMIT statements > with nothing in between. > > the only way i got it to work was to run: > pg_restore bakfile | psql newdb > which loads everything just fine but i was hoping to use parallel > restore to speed it up. > > any ideas? > > -nigel. You running the pg_restore as postgres user with sufficient privileges? You can do pg_restore -f bakfile.sql bakfile to have it restore to a text file instead of a database. Might help in seeing what is going on. -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes: > On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote: >> I'm trying to restore a backup into a database with a new name >> It doesn't work .. pg_restore claims to be creating tables, indexes, >> etc. and there are no errors in the output. It only takes a few seconds >> to run (the file is ~250MB). > You running the pg_restore as postgres user with sufficient privileges? I'm wondering if it could be the same bug reported two days ago: http://archives.postgresql.org/message-id/201107270042.22427.julian@mehnle.net Have you got standard_conforming_strings turned on? regards, tom lane
On 11-07-28 09:41 AM, Tom Lane wrote: > Adrian Klaver<adrian.klaver@gmail.com> writes: >> On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote: >>> I'm trying to restore a backup into a database with a new name >>> It doesn't work .. pg_restore claims to be creating tables, indexes, >>> etc. and there are no errors in the output. It only takes a few seconds >>> to run (the file is ~250MB). >> You running the pg_restore as postgres user with sufficient privileges? yes, i'm running it as the postgres superuser > I'm wondering if it could be the same bug reported two days ago: > http://archives.postgresql.org/message-id/201107270042.22427.julian@mehnle.net > Have you got standard_conforming_strings turned on? > > regards, tom lane That must be it! I do have standard_conforming_strings on. What i found is a string ending with a backslash as a default in a column definition .. so that bug must be more wide spread than just comments. eg. CREATE TABLE foo ( bar text DEFAULT '.\somepath\' ); thanks, -nigel.
Nigel Heron <nigel@psycode.com> writes: > On 11-07-28 09:41 AM, Tom Lane wrote: >> I'm wondering if it could be the same bug reported two days ago: >> http://archives.postgresql.org/message-id/201107270042.22427.julian@mehnle.net >> Have you got standard_conforming_strings turned on? > That must be it! I do have standard_conforming_strings on. What i found > is a string ending with a backslash as a default in a column definition > .. so that bug must be more wide spread than just comments. Yeah, actually it affects any situation where a string literal in the SQL dump ends in a backslash. I've committed a patch for it, but in the meantime the best workaround is to not use a direct-to-database restore, but pipe the SQL output through psql. regards, tom lane