Thread: pg_restore peculiarities

Maybe it's just me, but I can't seem to get pg_restore to restore a
database...

I am running 8.0 beta 2 (using the dev3 installer) on Windows XP.

I created a very simple database with one table and one function and
dumped it out using:

pg_dump -U postgres -F c -f test.dump test

This worked fine, although the compressed file was 120 K (ish).
Although this might have something to do with the database having 258
functions and a number of operators and operator classes (visible
through pgAdmin III).  Following the examples in the docs, I created a
new database and tried to restore into that using

pg_restore -U postgres -d test_restored test.dump

But it gives about 117K of error messages starting with:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC Entry 17; 1255 17228 FUNCTION
plpgsql_call_handler() postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  function
"plpgsql_call_handler" already exists with same argument types
    Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS
language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAG...
pg_restore: [archiver (db)] Error from TOC Entry 18; 1255 17229 FUNCTION
plpgsql_validator(oid) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  function
"plpgsql_validator" already exists with same argument types
    Command was: CREATE FUNCTION plpgsql_validator(oid) RETURNS void
    AS '$libdir/plpgsql', 'plpgsql_validator'
    LANGUAGE c;
pg_restore: [archiver (db)] Error from TOC Entry 535; 16402 17230
PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  language
"plpgsql" already exists
    Command was: CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER
plpgsql_call_handler VALIDATOR plpgsql_validator;

Even trying to restore data only gives duplicate key constraint errors.

Now this might seem simple, but all I want to be able to do is to backup
all the data in about 20 or so tables (all the non-system tables in a
DB, and their associated sequences), and be able to restore the data in
the tables back to that state at a later date.  Now I assumed that
pg_dump and pg_restore would be the easiest ways to do this, but I can't
even seem to get a simple case working.

Does anyone know the magic word to get this working?

Thanks
Tim

Tim Penhey <tim@penhey.net> writes:
> Maybe it's just me, but I can't seem to get pg_restore to restore a
> database...

It looks to me like you have a bunch of stuff in template1, which is
being duplicated into your test_restored DB and then is interfering
with the restoration of similarly-named objects.

Create the empty database using template0 as template, instead, and
then the restore will work.

            regards, tom lane

Tom Lane wrote:

Tim Penhey <tim@penhey.net> writes: 

Maybe it's just me, but I can't seem to get pg_restore to restore a 
database...   

Ah, that appears to be it.

As long as I tell the restore command to clean as it goes it seems OK.

How does pg_dump handle referential integrity?  Particularly with the restoring of tables where the integrity is needed?

Tim

Tim Penhey wrote:

> Tom Lane wrote:
>
>>Tim Penhey <tim@penhey.net> writes:
>>
>>
>>>Maybe it's just me, but I can't seem to get pg_restore to restore a
>>>database...
>>>
>>>
> Ah, that appears to be it.
>
> As long as I tell the restore command to clean as it goes it seems OK.
>
> How does pg_dump handle referential integrity?  Particularly with the
> restoring of tables where the integrity is needed?
>
> Tim

Oops, cut wrong bit out ;-)

Tom Lane wrote:

>It looks to me like you have a bunch of stuff in template1, which is
>being duplicated into your test_restored DB and then is interfering
>with the restoration of similarly-named objects.
>
>Create the empty database using template0 as template, instead, and
>then the restore will work.
>
>            regards, tom lane
>
>
Which does bring another question forward:

What is all the stuff that has been added to template1 given it is a
clean install of version 8 beta 2 dev 3?

The only thing it asked in the installer was what languages to add to
the template, and I chose plpgsql only.

Thanks,
Tim

Tim Penhey wrote:

> Which does bring another question forward:
>
> What is all the stuff that has been added to template1 given it is a
> clean install of version 8 beta 2 dev 3?
>
> The only thing it asked in the installer was what languages to add to
> the template, and I chose plpgsql only.

I also thought so, but it's not the case. The installer puts all contrib
modules into template1 that are included with the package and selected for
install (at the module selection screen)... the default is to install all.

I would rather like the installer to only install the dlls etc. and not
throw everything into template1... perhaps add a feature request at
pgFoundry?

Best Regards,
Michael Paesold

"Michael Paesold" <mpaesold@gmx.at> writes:
> Tim Penhey wrote:
>> The only thing it asked in the installer was what languages to add to
>> the template, and I chose plpgsql only.

> I also thought so, but it's not the case. The installer puts all contrib
> modules into template1 that are included with the package and selected for
> install (at the module selection screen)... the default is to install all.

> I would rather like the installer to only install the dlls etc. and not
> throw everything into template1... perhaps add a feature request at
> pgFoundry?

Without taking a position on what the Windows installer ought to do ...
*whenever* you are restoring a pg_dump dump, you ought to do so into
an empty database cloned from template0.  If the documentation doesn't
say that in enough places, suggest some more.

            regards, tom lane

Tom Lane wrote:

>Without taking a position on what the Windows installer ought to do ...
>*whenever* you are restoring a pg_dump dump, you ought to do so into
>an empty database cloned from template0.  If the documentation doesn't
>say that in enough places, suggest some more.
>
>
Right.  If, as Tom says, that restores should be into an empty database
cloned form template0, what then is the best way to provide a data
restore facility to a user though a back end system (Tomcat & JDBC) that
will have connections open to the database that you are trying to
restore to?

Tim