Re: import sql dump with psql - language creation throws error - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Adrian Klaver schrieb:
> On Tuesday 20 January 2009 2:15:08 am Andreas Wenk wrote:
>> Hi everybody,
>>
>> I have an automated mechanism to restore a demo database each night with an
>> SQL dump. What I do inbetween a shell script is the following:
>>
>> 1. all database access is canceled
>> 2. dropdb
>> 3. createdb
>> 4. import SQL dump: psql -o /dev/null $DB <
>> /var/lib/postgresql/scripts/$SQL_DUMP
>>
>> The last step is the issue. The shell script is run by an cronjob and if
>> one of the steps is failing, the crondaemon sends an E-Mail. The cluster
>> allready exists (for sure) and the language plpgsl also. The last point
>> (4.) always creates an error (what is clear), allthough the dump is
>> imported anyway:
>>
>> ERROR:  Language »plpgsql« allready exists
>>
>> psql -o /dev/null $DB < /var/lib/postgresql/scripts/$SQL_DUMP ||
>>          echo "Der dump konnte nicht eingespielt werden." >&2
>>
>> And because the ERROR message is the output, the crondaemon sends an email.
>>
>> Question:
>>
>> Where can I prevent bulding the language again? My idea was to do that
>> while creating the dump or while importing the dump. But as far as I
>> understand, that's not possible.
>>
>> The dump is created on another server using
>> /usr/bin/pg_dump -Ft -t sys_language garfield > sys_language.tar
>
> Could be lack of coffee, but I am somewhat confused. Is this indeed the dump
> file you are restoring? I didn't think you could restore a tar dump with psql.
> Also you are only dumping one table, so I am not sure why the whole db is being
> recreated.
>
Adrian,

no lack of coffee but my fault. You are totally right - that was a copy and paste error.
For sure the dump is *.sql.

Until now there is no onboard solution for this issue. Means, the import of the dump is
working correct but a "message" is thrown: FEHLER:  Sprache »plpgsql« existiert bereits
(means ERROR: Language »plpgsql« allready exists). What I do now - and this is not really
beautiful - is to erase that "message" before oputput is created from the shell script:

psql -o /dev/null $DB < /var/lib/postgresql/scripts/$SQL_DUMP  2>&1 | grep -v "FEHLER:
Sprache »plpgsql« existiert bereits"
exit 0

Now, no output from the script is catched by the crondaemon and no email is sent. On the
other hand, no error from the above line is catched at all ... unfortunately ...

By the way - language plpgsql was created with createlang plpgsql template1. And because
of tsearch2 it's not possible to create the db from template0.

Got another idea?

Cheers

Andy

- --
St.Pauli - Hamburg - Germany

Andreas Wenk

>> Thanks for any advice
>>
>> Andy
>>
>> --
>> Andreas Wenk
>>
>> St.Pauli - Hamburg - Germany
>
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJdegzVa7znmSP9AwRAvtbAKDNv9O3HyEe7kn1fjQpPIMRtk9PPwCfRf5a
KkatRS9OojoHBXGxQA05gKY=
=qhRy
-----END PGP SIGNATURE-----