Thread: import sql dump with psql - language creation throws error
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
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
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
iD8DBQFJdaQsVa7znmSP9AwRAlE0AKCVTqsD9X8nMtGHcTsfzHVElK5ePQCeMC3c
vqOqVcx0ns26Nf8esi4xp/A=
=zDAD
-----END PGP SIGNATURE-----
On Tue, Jan 20, 2009 at 11:15:08AM +0100, Andreas Wenk wrote: > 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. instead of "createdb" use: createdb -D template0 Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
try creating whole db from scratch, do the dump with option -c (will recreate all objects automagically). you can also issue 'drop language plpgsql [cascade]' before.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi depesz, thanks a lot for the reply. I think that will not work, because this is (still) a 8.1 and tsearch2 is integreated. That means I have to use template1 otherwise tsearch2 would not be there ... Am I right? P.S.: upgrade to 8.3 is planned asap ;-) - -- St.Pauli - Hamburg - Germany Andreas Wenk hubert depesz lubaczewski schrieb: > On Tue, Jan 20, 2009 at 11:15:08AM +0100, Andreas Wenk wrote: >> 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. > > instead of "createdb" use: > createdb -D template0 > > Best regards, > > depesz > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdacrVa7znmSP9AwRAlZ/AJoDlznuIlPI/ODu9HSQfSIXlKc9FgCgjdmo 6/WJlfiUo0pNjwpaoS9XIok= =3AQA -----END PGP SIGNATURE-----
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. > > Thanks for any advice > > Andy > > -- > Andreas Wenk > > St.Pauli - Hamburg - Germany -- Adrian Klaver aklaver@comcast.net
-----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-----
On Tuesday 20 January 2009 7:05:23 am Andreas Wenk wrote: > 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 Just to point you to Grzegorz's suggestion of using the -c switch in the pg_dump command. To quote the manual: -c "Output commands to clean (drop) database objects prior to (the commands for) creating them. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore. " Also I am not sure cron sending the email is a bad thing. Serves as indicator that the process ran. -- Adrian Klaver aklaver@comcast.net
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Adrian Klaver schrieb: > On Tuesday 20 January 2009 7:05:23 am Andreas Wenk wrote: > >> 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 > > > Just to point you to Grzegorz's suggestion of using the -c switch in the > pg_dump command. To quote the manual: > > -c > "Output commands to clean (drop) database objects prior to (the commands for) > creating them. > This option is only meaningful for the plain-text format. For the archive > formats, you can specify the option when you call pg_restore. " > I allready tried -c ... it's not what I need or helps - thanks anyway ;-) > Also I am not sure cron sending the email is a bad thing. Serves as indicator > that the process ran. > Yes that's correct ... in a way - but imagine you have 20 cronjobs running - do you really want to spam your mailbox with these messages? I think the better way is to leave it running and only in case of an error inform me. I think this is the common way sysadmins are doing it ... Cheers Andy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdfLiVa7znmSP9AwRAqYlAKCONfrcirRuDzFYYs9+1Sbg46JejgCgif0V 2RMlNbRaqK7aAomCk6tzPow= =+whp -----END PGP SIGNATURE-----
On Tuesday 20 January 2009 7:50:58 am Andreas Wenk wrote: > > > > > Just to point you to Grzegorz's suggestion of using the -c switch in the > > pg_dump command. To quote the manual: > > > > -c > > "Output commands to clean (drop) database objects prior to (the commands > > for) creating them. > > This option is only meaningful for the plain-text format. For the > > archive formats, you can specify the option when you call pg_restore. " > > I allready tried -c ... it's not what I need or helps - thanks anyway ;-) I don't know enough about tsearch, but is plpgsql a dependency? If not could you use the information here, http://www.postgresql.org/docs/8.3/interactive/manage-ag-templatedbs.html to create a database template that has tsearch but not plpgsql and then create your demo db using it? > > > Also I am not sure cron sending the email is a bad thing. Serves as > > indicator that the process ran. > > Yes that's correct ... in a way - but imagine you have 20 cronjobs running > - do you really want to spam your mailbox with these messages? I think the > better way is to leave it running and only in case of an error inform me. I > think this is the common way sysadmins are doing it ... True but sometimes the error is that nothing happened. I have never found the "no news is good news" saying comforting. Making decisions on a negative can come back to bite you. My 0.02 cents. > > Cheers > > Andy -- Adrian Klaver aklaver@comcast.net
On 2009-01-20, Andreas Wenk <a.wenk@netzmeister-st-pauli.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'