Re: import sql dump with psql - language creation throws error

On 2009-01-20, Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de> 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.

easiest solution is probably to drop it before restoring,
else, seeing as you have cron you probably have sed also and can use
sed to drop the apropriate lines from the dump, or to remove the error
message.

psql -o /dev/null $DB < /var/lib/postgresql/scripts/$SQL_DUMP \
2>&1 | sed 'SED SCRIPT HERE'