FAQ
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.

Search Discussions

  • Adrian Klaver at Jul 28, 2011 at 1:37 pm

    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
  • Tom Lane at Jul 28, 2011 at 1:41 pm

    Adrian Klaver 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
  • Nigel Heron at Jul 29, 2011 at 2:38 am

    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.
  • Tom Lane at Jul 29, 2011 at 2:51 am

    Nigel Heron 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 28, '11 at 4:31a
activeJul 29, '11 at 2:51a
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase