Re: issue with pg_restore

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(dot)klaver(at)gmail(dot)com