[ADMIN] Dump SQL DDL of all functions in DB
Anyone know how to dump the DDL of every function in a schema into one file? Essentially, I'd like to do something like: 'functions ddl dump cmd' public functions.sql Bradley A Holbrook Sr. Web Developer Servillian Technology Ltd http://www.servillian.com/ http://www.servillian.com mailto:bholbr...@servillian.com bholbr...@servillian.com mailto:bholbr...@telus.blackberry.net bholbr...@telus.blackberry.net Cell: (250) 309 - 7408 Office: (250) 260 - 4351 ext. 13 * For up to the minute news, support notifications and other pertinant information regarding your website or related services, you can use the following resources: http://twitter.com/#!/Servillian Twitter | http://www.facebook.com/pages/Servillian-Technology-Ltd/144527612254727 Facebook | http://support.servillian.ca/ Support Website
Re: [ADMIN] Drop Schema Error
Worked like a charm, thanks Tom and Viktor. I’m using version 8.4.5 64bit CentOs Linux I renamed the schema from permissions to _old_permissions and I believe this caused the error. From: Viktor Bojović [mailto:viktor.bojo...@gmail.com] Sent: January-29-11 11:16 AM To: Tom Lane Cc: Bradley Holbrook; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Drop Schema Error On Sat, Jan 29, 2011 at 5:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bradley Holbrook operations_brad...@servillian.ca writes: DROP SCHEMA _old_permissions CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to function 17059 drop cascades to function 17060 [Err] ERROR: cache lookup failed for function 17060 What is this trying to tell me? Apparently you've got some dangling references in pg_depend, ie those functions were dropped already but the pg_depend entries for them were not cleaned up. We've heard similar reports before (usually about temp tables not functions, IIRC) but never been able to reproduce them or identify a cause. What PG version is this? Have you done anything funny to that schema or its contents, or had any crashes around the time of manipulating either? As far as cleaning up goes, the thing to do would be to manually delete the relevant pg_depend entries --- for this one, an entry with objid = 17060 is probably what you're looking for. But it would be good to try to figure out how you got into this state first. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin i had that problem when renaming schema which had already objects inside. functions inside that schema had references to objects inside that schema. some functions still had reference to old schema name. so i renamed it again to drop those objects. It was 8.3.13 or prior version @ 32bit debian linux . -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
[ADMIN] Drop Schema Error
Hello! I'm trying to: DROP SCHEMA _old_permissions; And I get this error: ERROR: cannot drop schema _old_permissions because other objects depend on it DETAIL: function 17059 depends on schema _old_permissions function 17060 depends on schema _old_permissions HINT: Use DROP . CASCADE to drop the dependant objects too. So, naturally I: DROP SCHEMA _old_permissions CASCADE; Which produces: NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to function 17059 drop cascades to function 17060 [Err] ERROR: cache lookup failed for function 17060 What is this trying to tell me?
Re: [ADMIN] Postgres Backup Utility
Thanks Scott... a couple comments. Our developers never decide what goes to where... they just happily plumb away on the development db until we're ready to take our product to testing (at regular intervals), once QA is passed, we wish to apply these to live. We have several diff tools and sync tools, but they take forever (especially the ones that only go one schema at a time). The DDL Logging sounds like a sufficient solution, can it be configured to only record create and alter commands (or create or replace commands on functions or updates on sequences, etc)? I'd likely write a script to have this emailed to me at the end of every day. I'm going to google DDL logging (never heard of it), but any good resources off the top of your head? Martin French is right though, ask your developers to write down all their SQL struct changes and they look at you funny... and being a developer myself I'd look at me funny. If you forget just once you're screwed into a day sifting through tables and code. -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: January-20-11 7:40 AM To: French, Martin Cc: Bradley Holbrook; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Postgres Backup Utility On Thu, Jan 20, 2011 at 5:00 AM, French, Martin fren...@cromwell.co.uk wrote: Having been a C/C++ developer many years before being a DBA, and having written ITIL software; How is migrating structure from a Development database to a test database whilst maintaining test data backwards? It's not. doing by running diffs on ddl / schema is. This is what the OP asked for: I need to be able to quickly apply the structure updates from a development database to a testing database, and do selective data updates (like on lookup tables, but not content tables). Besides, the OP was asking how to diff to databases and create ddl, not asking for us to comment on why he's doing it. Personally, I'd rather Re-read what he asked for. He did not ask how to run diffs of schema, he asked how to update it with changes from a test database. The best way to do this is to check in changes to the database and lookup tables so the DBA can then apply them when needed to the various other databases. But hey, feel free to answer a question the OP didn't ask with an answer he doesn't need. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres Backup Utility
Well, I can't just go dropping and recreating tables. it needs to create the correct alter statements if existing tables and or functions already exist. Secondly, when I'm finished changing the structure, I need to be able to select the list of tables that will have content updates. Using a script might be more work maintaining then it's worth. I have a backup utility that can do the job, but 3 tedious steps per schema, that only work about 10% of the time (and no batching options so that I can create a list of actions and run the list). From: French, Martin [mailto:fren...@cromwell.co.uk] Sent: January-18-11 5:47 AM To: Bradley Holbrook; pgsql-admin@postgresql.org Subject: RE: [ADMIN] Postgres Backup Utility I'm assuming that this needs to be tightly controlled and as such a replication tool is out of the question? In that case; The first thing to pop into my head here would be to use either use shell scripting, or to use the pg API and write a c program to handle it. I remember doing something very similar with Oracle a few years back. Cheers Martin From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Bradley Holbrook Sent: 18 January 2011 00:08 To: pgsql-admin@postgresql.org Subject: [ADMIN] Postgres Backup Utility Hello! First day on the new mailing list as I have need of some expert's advice. I need to be able to quickly apply the structure updates from a development database to a testing database, and do selective data updates (like on lookup tables, but not content tables). Any help would be appreciated! Brad ___ This email is intended for the named recipient. The information contained in it is confidential. You should not copy it for any purposes, nor disclose its contents to any other party. If you received this email in error, please notify the sender immediately via email, and delete it from your computer. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. PCI Compliancy: Please note, we do not send or wish to receive banking, credit or debit card information by email or any other form of communication. Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive Wigston, Leicester LE18 1AT. Tel 0116 2888000 Registered in England and Wales, Reg No 00986161 VAT GB 115 5713 87 900 __
[ADMIN] Postgres Backup Utility
Hello! First day on the new mailing list as I have need of some expert's advice. I need to be able to quickly apply the structure updates from a development database to a testing database, and do selective data updates (like on lookup tables, but not content tables). Any help would be appreciated! Brad
