Re: [GENERAL] import sql dump with psql - language creation throws error
On 2009-01-20, 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. 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' -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import sql dump with psql - language creation throws error
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 akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import sql dump with psql - language creation throws error
-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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import sql dump with psql - language creation throws error
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 akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import sql dump with psql - language creation throws error
-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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import sql dump with psql - language creation throws error
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 akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import sql dump with psql - language creation throws error
-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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import sql dump with psql - language creation throws error
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import sql dump with psql - language creation throws error
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: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
