Re: [GENERAL] Maximum transaction rate
On Sat, 2009-03-14 at 05:25 +0100, Marco Colombo wrote: > Scott Marlowe wrote: > Also see: > http://lkml.org/lkml/2008/2/26/41 > but it seems to me that all this discussion is under the assuption that > disks have write-back caches. > "The alternative is to disable the disk write cache." says it all. If this applies to raid based cache as well then performance is going to completely tank. For users of Linux + PostgreSQL using LVM. Joshua D. Drake > > .TM. > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] recatalog existing databases after re-build from source
On Fri, 2009-03-13 at 15:28 -0400, gnuo...@rcn.com wrote: > Going through the PLPython threads on the Planet, I realied that I needed > plpython. So I rebuilt PG with python, expecting that I could then > re-catalog the databases. But, not. initdb, wants the data directory, which > is where the database files are, and doesn't run if it's not empty. The data > directories have descriptive names like 16431, etc. > > These are only small test db's; losing them is not a big deal, but is there a > way to get them back? > > When I went to createlang I got the no "root" user message, which is why I > went to look at initdb. adduser says that postgres user exists, which makes > sense. > I am having a hard time understanding your problem. Why do you have to recatalog the databases? Why not just createlang -u postgres? Joshua D. Drake > thanks, > Robert > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Maximum transaction rate
On Fri, 2009-03-13 at 11:41 -0700, Ben Chobot wrote: > On Fri, 13 Mar 2009, Joshua D. Drake wrote: > > >> It seems to me that all you get with a BBU-enabled card is the ability to > >> get burts of writes out of the OS faster. So you still have the problem, > >> it's just less like to be encountered. > > > > A BBU controller is about more than that. It is also supposed to be > > about data integrity. The ability to have unexpected outages and have > > the drives stay consistent because the controller remembers the state > > (if that is a reasonable way to put it). > > Of course. But if you can't reliably flush the OS buffers (because, say, > you're using LVM so fsync() doesn't work), then you can't say what > actually has made it to the safety of the raid card. Wait, actually a good BBU RAID controller will disable the cache on the drives. So everything that is cached is already on the controller vs. the drives itself. Or am I missing something? Joshua D. Drake > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Maximum transaction rate
On Fri, 2009-03-13 at 11:41 -0700, Ben Chobot wrote: > On Fri, 13 Mar 2009, Joshua D. Drake wrote: > Of course. But if you can't reliably flush the OS buffers (because, say, > you're using LVM so fsync() doesn't work), then you can't say what > actually has made it to the safety of the raid card. Good point. So the next question of course is, does EVMS do it right? http://evms.sourceforge.net/ This is actually a pretty significant issue. Joshua D. Drake > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Maximum transaction rate
On Fri, 2009-03-13 at 11:17 -0700, Ben Chobot wrote: > On Fri, 13 Mar 2009, Joshua D. Drake wrote: > > > On Fri, 2009-03-13 at 14:00 -0400, Tom Lane wrote: > >> Marco Colombo writes: > >>> You mean some layer (LVM) is lying about the fsync()? > >> > >> Got it in one. > >> > > > > I wouldn't think this would be a problem with the proper battery backed > > raid controller correct? > > It seems to me that all you get with a BBU-enabled card is the ability to > get burts of writes out of the OS faster. So you still have the problem, > it's just less like to be encountered. A BBU controller is about more than that. It is also supposed to be about data integrity. The ability to have unexpected outages and have the drives stay consistent because the controller remembers the state (if that is a reasonable way to put it). Joshua D. Drake > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Maximum transaction rate
On Fri, 2009-03-13 at 14:00 -0400, Tom Lane wrote: > Marco Colombo writes: > > You mean some layer (LVM) is lying about the fsync()? > > Got it in one. > I wouldn't think this would be a problem with the proper battery backed raid controller correct? Joshua D. Drake > regards, tom lane > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] I don't want to back up index files
On Thu, 2009-03-12 at 12:59 -0700, Glen Parker wrote: > Tom Lane wrote: > > Glen Parker writes: > >> We have yet to recover from a PG disaster. We back up every night, and > >> never use the back ups for anything. To me, it seems perfectly > >> reasonable to get a quicker back up every night, with the remote > >> possibility of ever having to pay the price for it. > > > > Why don't you just switch to a less frequent full-backup schedule? > > > Paranoia. Others in the organization have pushed to keep up with the > nightly back ups, so that decision is mostly out of my hands. Why not just take a backup of the pitr slave instead? If you need to do it nightly, shut down the standby process, tar, start standby process. You never have to bother the master at all. Sincerely, Joshua D. Drake > > -Glen > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] I don't want to back up index files
On Wed, 2009-03-11 at 20:59 -0600, Scott Marlowe wrote: > On Wed, Mar 11, 2009 at 5:57 PM, Glen Parker wrote: > > Scott Marlowe wrote: > > Suggesting that a > > person who's been managing PG in a commercial setting since version 6.4 > > should just use pg_dump as an alternative to PITR is, well, rather > > insulting. > > Darn, I've only been around since 6.5.2, and have about four years on > an airline reservation system. I guess my opinions just don't count. > How could I possibly understand your advanced thought processes. > Alright guys, let's not get into a my elephant trunk is bigger than yours fight. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] I don't want to back up index files
On Wed, 2009-03-11 at 16:57 -0700, Glen Parker wrote: > Scott Marlowe wrote: > That's two people now who have called the idea "silly" without even a > hint of a supporting argument. Why would it be "silly" to improve the > performance of a highly valuable tool set without compromising its > utility? Am I missing something here? That's certainly possible, but > the idea didn't just hatch last night; I've put enough thought into this > to have reason to believe it's more than just "silly". O.k. a couple of things: 1. You could put all your indexes into a table space, this would allow you to "try" different things with the indexes. 2. Even though my peer Alvaro doesn't think the idea is silly, I still do and here is why. If you can invalidate the indexes you will have to reindex (or recreate) to make them valid (as you mentioned). That is an exclusive lock. If your database has any level of concurrency the cost to recreate/reindex those indexes right when you are attempting to get your standby into production is going to be very high. Basically you are trading 25% hard disk space for a longer, possibly excessively longer outage. Hard disk space is so darn cheap that it doesn't seem to make any sense. Creating indexes concurrently is also out because while you are creating those indexes your performance will tank because everything is sequential scanning and there is a possibility that the concurrent creation will fail. Sincerely, Joshua D. Drake > > -Glen > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] I don't want to back up index files
On Wed, 2009-03-11 at 14:25 -0700, Glen Parker wrote: > Grzegorz Jaśkiewicz wrote: > > So like JD said, if you don't want to dump indicies - just use pg_dump... > > If pg_dump were an acceptable backup tool, we wouldn't need PITR, would > we? We used pg_dump for years. There's a very good reason we no longer > do. That suggestion is silly. *shrug* you can consider it silly. It doesn't change the outcome. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] I don't want to back up index files
On Tue, 2009-03-10 at 18:54 -0700, Glen Parker wrote: > I am wondering the feasibility of having PG continue to work even if > non-essential indexes are gone or corrupt. I brought this basic concept > up at some point in the past, but now I have a different motivation, so > I want to strike up discussion about it again. This time around, I > simply don't want to back up indexes if I don't have to. Because > indexes contain essentially redundant data, losing one does not equate > to losing real data. Therefore, backing them up represents a lot of > overhead for very little benefit. Hello, I am sorry but this seems very silly. If you don't want to back up indexes use pg_dump. > > Any chance of something like this being done in the future? > I am going to go out on a limb here and say, "no". Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Connection Refused Error
On Mon, 2009-03-09 at 19:09 -0500, JohnD wrote: > Joshua D. Drake wrote: > > What does your listen_addresses say on the affected server? Also just to > > be safe do a /sbin/iptables -L and make sure you aren't blocking. > > > > Joshua, > > Thank you so much - that was it. My postgresql.conf listen_addresses > was commented out which defaulted to 'localhost'. Changing it to '*' > and restarting cleared the problem up. Not sure why this "stopped" working. > > Thanks again - I was beating myself up about this all day. :) Joshua D. Drake > > John > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Connection Refused Error
On Mon, 2009-03-09 at 18:11 -0500, JohnD wrote: > Hi, > Any idea why I am no longer able to connect? > What does your listen_addresses say on the affected server? Also just to be safe do a /sbin/iptables -L and make sure you aren't blocking. Joshua D. Drake > Thanks for any and all help. > > John > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] idle users
On Fri, 2009-03-06 at 01:09 +0530, Sathish Duraiswamy wrote: > Can we automate this process , maintained by postmaster itself No and that would be a bad idea. There has been discussion in the past of having an IDLE in TRANSACTION timeout but that is a different thing. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Postgres with phpScheduleIt
On Thu, 2009-03-05 at 14:08 -0500, Tom Lane wrote: > Stuart Luppescu writes: > > phpScheduleIt uses mySQL by default, but they claim is should work with > > postgres. The setup uses a script that starts like this: > > > # phpScheduleIt 1.2.0 # > > drop database if exists phpScheduleIt; > > create database phpScheduleIt; > > use phpScheduleIt; > > Frankly, it looks like you shouldn't believe their claim that they work > with postgres. The "use" bit is 100% mysql-specific, and we haven't > even got into the part of things that is likely to have database > dependencies. While it might not be difficult for someone who's > reasonably familiar with both DBs to fix things up, that's evidently not > you. Your best bet would probably be to either use mysql like they tell > you to, or find another scheduler package whose authors actually expend > more than zero effort on working with postgres. Doesn't pgAdmin have a scheduler? Joshua D. Drake > > regards, tom lane > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] pgsql announce now on twitter
On Wed, 2009-03-04 at 07:58 +0100, Michelle Konzack wrote: > Am 2009-03-03 21:38:39, schrieb Douglas J Hunley: > > Hi everyone: > > I really wanted to let everyone know that I've created @PGSQL_Announce on > > Twitter and setup a cron job to parse the feed and post it to Twitter. > > It's been working for a little while now and I think it's stable enough to > > announce to the world. > > For What? -- Increasing Twitter-Spam? Not sure what the complainer is talking about here. pgsql-announce is moderated so spam should be almost nil. Anyway, I think this is a great thing that you have done as it allows further exposure to our great project. Good Work. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] max_fsm_relations
On Fri, 2009-02-27 at 12:37 -0700, Gauthier, Dave wrote: > I just did a Vacuum Analyze on a DB. It worked OK, but I got... > > > > > > NOTICE: max_fsm_relations(1000) equals the number of relations checked > > HINT: You have at least 1000 relations. Consider increasing the > configuration parameter “max_fsm_relations” > > > > I browsed around and learned that this has to do with keeping track of > free disk space. > > Q: Is there a problem with leaving this alone? How serious is this if > it is a problem? > > Q: Is there a way I can shrink this number (reload the data to consume > the free space perhaps?) This is "relations" which means tables, indexes etc... So unless you start dropping things, no you can't reduce it. Just increase it a bit (say 20%) it won't hurt you. Note it does use a little shared_memory,. Joshua D. Drake > > > > Thanks > > -dave > > > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Standalone ODBC Driver
On Fri, 2009-02-27 at 19:06 +0100, Tim Tassonis wrote: > Joshua D. Drake wrote: > > On Fri, 2009-02-27 at 13:30 +0100, Tim Tassonis wrote: > >> Hi all > >> > >> I remember, a while ago somebody mentioning an odbc driver for postgres > >> that is not dependant on a working postgres client installation. > >> Unfortunately I lost the link to it, can anybody remember? > >> > > > > ODBCng? > > > > http://projects.commandprompt.com/public/odbcng > > Yes, thanks, thats the one. Already up and running! > > Sadly, the current svn versions seem to target only windows. No chance > to even compile it under linux, but the old version from 2007 works. The linux version lags behind. We are working on a unixodbc issue with newer versions then you will be able to. Joshua D. Drake > > Bye > Tim > > > > > > > >> ( > >> I tested it then and it worked fine for simple task, but then switched > >> back to the standard driver, as the datatype information stuff was much > >> more complete. > >> > >> Now I need to run a postgresql odbc program on a few ancient machines > >> whith completely outdated client libraries and no chance of upgrading. > >> So, the standalone driver would come very handy here again. > >> ) > >> > >> > >> > >> Bye > >> Tim > >> > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Standalone ODBC Driver
On Fri, 2009-02-27 at 13:30 +0100, Tim Tassonis wrote: > Hi all > > I remember, a while ago somebody mentioning an odbc driver for postgres > that is not dependant on a working postgres client installation. > Unfortunately I lost the link to it, can anybody remember? > ODBCng? http://projects.commandprompt.com/public/odbcng > ( > I tested it then and it worked fine for simple task, but then switched > back to the standard driver, as the datatype information stuff was much > more complete. > > Now I need to run a postgresql odbc program on a few ancient machines > whith completely outdated client libraries and no chance of upgrading. > So, the standalone driver would come very handy here again. > ) > > > > Bye > Tim > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] funny view/temp table problem with query
On Fri, 2009-02-27 at 01:36 +0100, Alban Hertroys wrote: > On Feb 26, 2009, at 11:02 AM, Grzegorz Jaśkiewicz wrote: > You're the one who's asking a question, it's your responsibility that > we can understand your problem. Woah... ease up cowboy. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] speaking of 8.4...
On Thu, 2009-02-26 at 15:41 -0800, Ron Mayer wrote: > Back in Jan 2008 that same page stated that 8.3 "would" come out > in July 2007: > http://archives.postgresql.org/pgsql-advocacy/2008-01/msg00235.php > > But that's not nearly as sad as the Chinese FAQs that state that > the latest version of Postgres is 8.2.1 or 8.2.3 depending on > whether you prefer traditional or simplified writing styles. > http://www.postgresql.org/docs/faqs.FAQ_chinese_simp.html > http://www.postgresql.org/docs/faqs.FAQ_chinese_trad.html > And for german speakers, their newest is 8.2.5 > http://www.postgresql.org/docs/faqs.FAQ_german.html > Basically for all the languages you get a different "lateset" > release. > I can fix the press faq but the others will have to go to the translators project. Joshua D. Drake > Surely these FAQ entries are doing more harm than good. > > Can we please just update all of these to link to some page > where the actual latest version is instead; and replace the > forward-looking expectations with something that is less > misleading? > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] speaking of 8.4...
On Thu, 2009-02-26 at 15:27 -0800, Ron Mayer wrote: > Joshua D. Drake wrote: > > On Fri, 2009-02-27 at 10:19 +1300, Tim Uckun wrote: > >> > >> [according to some page on the web site...] > >> 8.4 was scheduled to be released march 1. Do we know what the > > All schedules are subject to change within the community :) > >> tentative date of release is? > > > > When it is done of course. > > Perhaps that should be the official position communicated in > the various places on the web site. > > I do notice that the Press FAQ with it's Q4 2008 guess > is even more optimistic than the other page on the website > people seem to be finding that implies March. > > http://www.postgresql.org/about/press/faq > Q: When will 8.4 come out? > A: Historically, PostgreSQL has released approximately > every 12 months and there is no desire in the community > to change from that pattern. So expect 8.4 sometime in > the fourth quarter of 2008. > Wow that must have been written some time ago I don't think anyone thought we would hit that date in any recent (say last 6-8 months). Joshua D. Drake > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Connection refused (0x0000274D/10061).
On Thu, 2009-02-26 at 18:16 -0500, Tom Lane wrote: > Scott Marlowe writes: > > 8.0 is definitely supported. 8.0.0-rc1 is NOT supported, as it was a > > release candidate and is quite likely to have some nasty bugs in it. > > It's worse than that: he's running on Windows, which means that this > is not just any rc version, but an rc for the first native Windows port. > We no longer support 8.0.anything on Windows because of the unfixable > bugs in that release. > > If moteview is still shipping 8.0.rc1 to Windows customers in 2009, > I'd say that borders on criminal negligence. I am sure that MoteView's license says otherwise. Sincerely, Joshua D. Drake > > regards, tom lane > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] speaking of 8.4...
On Fri, 2009-02-27 at 10:19 +1300, Tim Uckun wrote: > > > 8.4 was scheduled to be released march 1. Do we know what the All schedules are subject to change within the community :) > tentative date of release is? When it is done of course. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Warm standby failover mechanism
On Wed, 2009-02-25 at 22:06 +, Thom Brown wrote: > > Looks like you didn't run cmd_archiver -C -I > > > > Ahh, okay, that did something, which I think means it created a > directory named after the slave IP in the archive directory. Right that is the queue directory. > Now when I run "./cmd_archiver -C cmd_archiver.ini" I get: > That command really shouldn't do anything but error. The whole point of the archiver is to be placed in the archive_command option in the postgresql.conf. E.g; cmd_archive -C cmd_archive.ini -F %p This really should be happening on the pitrtools list. Let's bounce over there and resolve this. Joshua D. Drake > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Warm standby failover mechanism
On Wed, 2009-02-25 at 21:39 +, Thom Brown wrote: > > As a note, all PITRTools does is wrap around all the tools > that you are > trying to make work. So you will still need pg_standby, rsync, > ssh > etc... > > I have updated the wiki to make it a bit more friendly. > > https://projects.commandprompt.com/public/pitrtools/wiki > > > > I've given PITRTools a try, but I have been unsuccessful in utilising > it. I've configured both ini files, everything owned by user > postgres, and upon running "./cmd_archiver -C cmd_archiver.ini" I get: > > NOTICE: check_config_func() > NOTICE: Performing standard archive > NOTICE: archive_func() > NOTICE: send_queue_func() > NOTICE: list_queue_func() > NOTICE: generate_slave_list_func() > NOTICE: Your slaves are: ['192.168.1.17'] > Traceback (most recent call last): > File "../pitr_tools/cmd_archiver", line 343, in > archive_func() > File "../pitr_tools/cmd_archiver", line 254, in archive_func > queue = send_queue_func() > File "../pitr_tools/cmd_archiver", line 219, in send_queue_func > for host in list_queue_func(): > File "../pitr_tools/cmd_archiver", line 202, in list_queue_func > list_archives = os.listdir(queuedir) > OSError: [Errno 2] No such file or directory: > '/var/lib/postgresql/archive/192.168.1.17' > > The only time that IP address appears in the INI file is on the slaves > line, so I'm not sure why it's trying to find a directory called that. Looks like you didn't run cmd_archiver -C -I Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Postgres SRPMs for RHEL
On Wed, 2009-02-25 at 12:19 -0600, Justin Pasher wrote: > >> If I cycle through the versions, the last version in the 8.1 branch I > >> can find with source RPMs is 8.1.14. > >> > > > > http://yum.pgsqlrpms.org/8.1/redhat/rhel-4ES-i386/ > > > > Unless I'm just looking for the wrong filename, I still can't fine the > source RPMs on the yum repo either, just the regular RPMs. Doh! Yep you are right. Sorry for the noise. Joshua D. Drake > > > -- > Justin Pasher > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Postgres SRPMs for RHEL
On Wed, 2009-02-25 at 12:10 -0600, Justin Pasher wrote: > Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't show > up here? > > http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ > > If I cycle through the versions, the last version in the 8.1 branch I > can find with source RPMs is 8.1.14. http://yum.pgsqlrpms.org/8.1/redhat/rhel-4ES-i386/ > > > -- > Justin Pasher > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Warm standby failover mechanism
On Wed, 2009-02-25 at 16:33 +, Thom Brown wrote: > You are doing this the hard way. Grab PITRTTools. > > https://projects.commandprompt.com/public/pitrtools > > > I can't really dispute a recommendation from JD. I'll have to look > into that. It's a shame because we've spent ages trying to work out > where we've been going wrong in this whole process (we still haven't > got it picking up WALs from the archive). As a note, all PITRTools does is wrap around all the tools that you are trying to make work. So you will still need pg_standby, rsync, ssh etc... I have updated the wiki to make it a bit more friendly. https://projects.commandprompt.com/public/pitrtools/wiki Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Warm standby failover mechanism
On Wed, 2009-02-25 at 13:54 +, Thom Brown wrote: > I'm still trying to work out how this file creation/deletion thing > will work. If I can tag a "&& touch /tmp/pg.trigger" command to the > end of the recovery command, how often will that be called? If I > can't, I still need to ensure that it is created and deleted before > the recovery command is called, otherwise it will see it before it is > deleted and put itself online. > > Has anyone got a practical example of what they've set up, or know > what others have set up? You are doing this the hard way. Grab PITRTTools. https://projects.commandprompt.com/public/pitrtools Just pull down the stable branch: svn co https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2 Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] High cpu usage after many inserts
On Wed, 2009-02-25 at 09:44 +0900, Jordan Tomkinson wrote: > > > On Wed, Feb 25, 2009 at 9:23 AM, Joshua D. Drake > wrote: > > > RAID5 outside of RAID 0 is the worst possible RAID level to > run with a > database. (of the commonly used raid level's that is). > > It is very, very slow on random writes which is what databases > do. > Switch to RAID 10. > > surely being (real) hardware raid with 15k rpm disks this wouldn't be > a huge issue unless a large amount of data was being written ? Tests done by Mark Wong on a 3 disk 15k scsi versus 4 disk raid 10 scsi show that RAID 10 is on average 30% faster. Sincerely, Joshua D. Drake > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] High cpu usage after many inserts
On Wed, 2009-02-25 at 09:21 +0900, Jordan Tomkinson wrote: > > On Wed, Feb 25, 2009 at 12:05 AM, Aidan Van Dyk > wrote: > * Greg Smith [090201 00:00]: > > > Shouldn't someone have ranted about RAID-5 by this point in > the thread? > > > What? Sorry, I wasn't paying attention... > > You mean someone's actually still using RAID-5? > > ;-) > > What exactly is wrong with RAID5 and what should we have gone with? RAID5 outside of RAID 0 is the worst possible RAID level to run with a database. (of the commonly used raid level's that is). It is very, very slow on random writes which is what databases do. Switch to RAID 10. Sincerely, Joshua D. Drkae > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Fixing invalid owners on pg_toast tables in 8.3.5
On Fri, 2009-02-20 at 12:01 -0700, Cott Lang wrote: > We're running 8.3.5 on RHEL4 x86_64. > > We removed a user yesterday and were greeted with warnings from pg_dump > this morning. :) > > pg_dump: WARNING: owner of data type "pg_toast_80075" appears to be > invalid > > The usual archives and google searches produced mainly 8.0 and earlier > incidents and suggested resolving this by re-creating a user with that > sysid. Since you can no longer specify a SYSID when creating a user > (despite what \h in psql says), we gave ALTER type/table a shot with no > luck. > Something isn't right: postgres=# create user foobar superuser; CREATE ROLE postgres=# set role foobar; SET postgres=# create type typetext AS (bar text); CREATE TYPE postgres=# select current_user; current_user -- foobar (1 row) postgres=# set role postgres; SET postgres=# drop type typetest; ERROR: type "typetest" does not exist postgres=# drop user foobar; ERROR: role "foobar" cannot be dropped because some objects depend on it DETAIL: owner of type typetext > The owner of the actual table and index is correct, only the type has an > invalid owner. I have thus far avoided the temptation to try a manual > update... > > Is there a recommended procedure for resolving this safely? > You can use alter type to change the owner of the type to a valid user but see above. Something is wrong. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Getting time-dependent load statistics
On Fri, 2009-02-20 at 17:11 +0100, Torsten Bronger wrote: > Hallöchen! > > Yesterday I ported a web app to PG. Every 10 minutes, a cron job > scanned the log files of MySQL and generated a plot showing the > queries/sec for the last 24h. (Admittedly queries/sec is not the > holy grail of DB statistics.) > > But I still like to have something like this. At the moment I just > do the same with PG's log file, with > > log_statement_stats = on > > But to generate these plots is costly (e.g. I don't need all the > lines starting with !), and to interpret them is equally costly. Do > you have a suggestion for a better approach? > Do you want queries, or transactions? If you want transactions you already have that in pg_stat_database. Just do this every 10 minutes: psql -U -d -c "select now() as time,sum(xact_commit) as transactions from pg_stat_Database" Joshua D. Drake > Tschö, > Torsten. > > -- > Torsten Bronger, aquisgrana, europa vetus >Jabber ID: torsten.bron...@jabber.rwth-aachen.de > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Removing a corrupt database by hand
On Wed, 2009-02-18 at 19:58 -0800, Mike Christensen wrote: > I have two questions actually.. > > First off, is there a way to remove a database if the postgres.exe > service won't start? It seems if I just delete the data\base\x > directory, then postgres will crash. Is there a way to drop a DB that > prevents postgres.exe from starting? > What happens if you start postgresql in single user mode? > Second question, if I just re-install postgres and create a fresh new > instance, is there a way to import my old data files back into the > database? Thanks! > > Mike > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Mammoth replicator
On Wed, 2009-02-18 at 13:55 -0200, Martín Marqués wrote: > I was working on a replication system (open source) for a DB we are > using and I was going to go for Slony-I for replication and pg_pool2 > for load balancing. > > Just yeasterday I found out the Mammoth replicator was released as > Open Source, and AFAICS it looks more suitable for our needs then > Slony-I (we are going to replicate the whole DB). I'm I wrong on this? Well that would depend on your needs I guess. Slony-I is a fine if complicated system. I would suggest popping over the the replicator-general list. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Killing OIDs
On Wed, 2009-02-11 at 18:01 +, Adrian Klaver wrote: > - "Joshua D. Drake" wrote: > Now I am confused. From the docs I get: > My bad. The docs are obviously correct. I think I was thinking about the postgresql.conf option. Joshua D. Drae > SET WITHOUT OIDS > > This form removes the oid column from the table. Removing OIDs from a > table does not occur immediately. The space that the OID uses will be > reclaimed when the row is updated. Without updating the row, both the space > and the value of the OID are kept indefinitely. This is semantically similar > to the DROP COLUMN process. > > > I remember from past posts, that to get rid of the OIDS you can do a 'fake' > update on the whole table to reclaim the space. The case the OP is dealing > with he does not want the OID setting to propagate via the dump/restore > cycle. The above statement would do that or am I mistaken? > > > Thanks, > Adrian Klaver > akla...@comcast.net > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Killing OIDs
On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote: > I'm about to deal with an upgrade of a server running 7.4. I have > checked with the developers and they are not using OIDs so I'd like to > remove them so they aren't carried forward to 8.3. > > My plan is to do the OID removal on 7.4. I can get a script with: > SELECT > 'ALTER TABLE ' || relname || ' SET WITHOUT OIDS;' > FROM > pg_class > WHERE > relkind='r' and > relowner != 1 and > relhasoids; > > Before I pull the trigger, I figured I'd post and find out if anyone > sees any feet in the way. That won't drop the OID columns. Joshua D. Drake > > Cheers, > Steve > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] getting column value length
On Tue, 2009-02-03 at 18:44 -0500, blackwater dev wrote: > I need to return all rows in a table where one of the columns 'name' > is 37+ characters. In postgres, is there a function to get the length > of the columns contents? > http://www.postgresql.org/docs/8.3/static/functions-string.html length() > > Thanks! -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Pet Peeves?
On Thu, 2009-01-29 at 17:43 +, Richard Huxton wrote: > David Fetter wrote: > > * Letter options in psql, pg_dump[all], pg_restore aren't consistent > > and can easily steer you very wrong. I'm looking at you, -d. > > Ah, good one - I keep doing that too. For the record "-d" is usually > database-name, but for pg_dump it's "dump with inserts". Which is a > zillion time slower than COPY for restoring. If we are listing pet peeves :) Up to 8.4, postgresql didn't accurately represent timestamps because they are stored as float by default The fact that there is: pg_dump pg_dumpall pg_restore At all... It should be pg_backup and that is it, with a nice -R flag for restore. The idea that it is "proper" to pipe a backup through psql to restore. Our date handling as a whole (extract,date_part) is wonky. There have been more than one blog post on this. Our lack of partitioning :) Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] What is the best plan to upgrade PostgreSQL from an ancient version?
On Tue, 2009-01-27 at 04:28 +0100, marcin mank wrote:
> On Mon, Jan 26, 2009 at 10:37 PM, Dann Corbit wrote:
> > My notion is to do a character mode database dump as SQL statements and
> > then load into the new version by execution of psql against the sql
> > STATEMENTS.
> >
> > What are the "gotchas" we can expect with this approach?
> >
> > When I say 'ancient' I mean v7.1.3 and the target is v8.3.5.
> >
>
> One gotcha is that some ancient versions (and I think 7.1 is one of
> these) used to silently truncate varchar values that don`t fit into
> the declared field width, while recent versions throw errors. Check if
> Your apps don`t depend on this behaviour.'
Wow that is reaching back. You can actually do this still:
postgres=# create table test_trunc(fname varchar(2)):
postgres-#
postgres=# create table test_trunc(fname varchar(2));
CREATE TABLE
postgres=# insert into test_trunc values ('fo');
INSERT 0 1
postgres=# insert into test_trunc values ('foo');
ERROR: value too long for type character varying(2)
postgres=# insert into test_trunc values ('foo'::varchar(2));
INSERT 0 1
postgres=# select * from test_trunc;
fname
---
fo
fo
(2 rows)
Sincerely,
Joshua D. Drake
> good luck
> Marcin
>
--
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
--
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] auto insert data every one minute
On Sat, 2009-01-17 at 22:40 -0800, searchelite wrote: > > > Tino Wildenhain wrote: > > > > > > I wonder what is you complete problem? It seems all the advices given > > so far are shots-in-the-dark. Could you perhaps expand a bit? > > > > Also for sophisticated solution, if you stick to windows you might > > want to consider something different then just pure CMD, say some > > scripting language to support. > > > > Tino > > > > > > I have pre-recorded gps data in .sql insert format..for simulate real-time > tracking, i want to insert the data in let say every one minute Feed the data to a loop that waits every 60 seconds. You could also pipe it to a named pipe while an injector was listening. Joshua D. Drake > > -- > View this message in context: > http://www.nabble.com/auto-insert-data-every-one-minute-tp20027811p21524797.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] executing a sql script
On Fri, 2009-01-16 at 22:09 -0800, johnf wrote:
> I'm using python and can execute standard "select,update,delete,functions".
> What I'd like to do is execute a sql script (a text file). But I don't know
> how?
You need to open the text file and pass it as an argument:
try:
file = "%s/%s" % (str(sqlpath),str("myfile.sql"))
procedures = open(file,'r').read()
dbcur.execute(procedures)
except psycopg2.DatabaseError, e:
print
print "EXCEPTION: procedures :%s" % str(e)
print
exit(1)
> Some thing like:
> import psycopg2
> import psycopg2.extensions
> conn = psycopg2.connect("host=%s dbname=%s user =%s password
> =%s "
> %(self.pgSqlHostID.Value,self.pgSqlDatabaseID.Value,self.pgSqlUserID.Value,self.msSqlPasswordID.Value))
>
> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
> tempCursor= conn.cursor()
> try:
> tempCursor.execute("run script %s " % FileNameScript)
> tempCursor.execute('commit')
> --
> John Fabiani
>
--
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
--
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] Vacuum and Reindex hangs
On Thu, 2009-01-15 at 20:39 +, Grzegorz Jaśkiewicz wrote: > and we also oppose to answering on top of message, and citing > everything underneeth. > Why? Because your words should say what you mean, not show it by its > look. Hence, plain ascii is enough for us - and should be for every > intelligent human being. Well now that we are completely off topic :). Let me just say that it is exactly the type of thinking above that makes Open Source people seem like jerks. I know many perfectly intelligent people that are better served through diagrams, pdf and color than a mailing list. Most of them make sure geeks like us, *EAT*. Does that mean they are not intelligent or perhaps that there talent set is just different? Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Vacuum and Reindex hangs
On Thu, 2009-01-15 at 14:32 -0600, Jason Long wrote: > I don't mean to be a pain either and I mean no disrespect to anyone on > this list in the following comments. > > However, this is about the most anal list ever. You haven't been to the debian list have you? :). > I see so many emails on here about people complaining regarding the > proper way to reply or post to the list. The basic gist is this. We have to be anal otherwise there is no conformity to a standard (even if the standard is arbitrary). Without that conformity it is impossible to be productive on the list. > > I used larger font to point point out my statement from the code. I > also did not realize it appeared that large to you. Right but just as a tip that is really not needed on a technical list. Your words should be able to convey your meaning without the requirement of a cosmetic modification. If it can't it would be better to post to a pastebin or static web page and link a URL. Just FYI most on this list probably never saw your HTML. They, like I said have already turned off HTML email. > > Just out of curiosity, why are you so apposed to HTML in a email? http://www.american.edu/econ/notes/htmlmail.htm http://www.georgedillon.com/web/html_email_is_evil.shtml Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Vacuum and Reindex hangs
On Thu, 2009-01-15 at 20:13 +, Raymond O'Donnell wrote: > On 15/01/2009 20:06, Jason Long wrote: > > I am attempting to vacuum...[snip] > > I don't mean to be a pain, but could you please avoid HUGE type sizes > such as the aboveor better still, avoid using HTML altogether in > your emails to this list. > > It makes it look as if you are not just shouting, but SCREAMING at the > top of your lungs! :-) The answer to this is to not allow HTML email at all to your client. It is the first thing I disable on any family/friend/church member that asks for help. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Vacuum and Reindex hangs
On Thu, 2009-01-15 at 14:06 -0600, Jason Long wrote: > I am attempting to vacuum and reindex my database. It keeps timing > out. See commands and last part of output below. The vacuum or > reindex only takes a short time to complete normally because the > database it less than 50 mb. I have the query timeout set to 2 > minutes, but I do not know if this can affect vacuumdb or reindexdb > and the time should take much less than 2 minutes anyway. > > I am using 8.3.3 on Centos 5. You have statement_timeout set. You can use SET in psql to change this SET statement_timeout TO ... Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] MD5 password issue
On Thu, 2009-01-15 at 18:05 +0100, Andreas Wenk wrote: > postgres=# SELECT rolname,rolpassword from pg_authid; > rolname | rolpassword > - ---+- > postgres | > pgadmin | plaintext > odie | md5passsorrrd > > The user odie was created with: > CREATE ROLE odie LOGIN ENCRYPTED PASSWORD 'feedme'; > > The user pgadmin was created with: > $ createuser -a -d -P -N -U postgres pgadmin > Per the help. You need to pass -E to have it be an encrypted (md5 hash) style password. What version of PostgreSQL is this as I recall all newer versions do this by default. Usage: createuser [OPTION]... [ROLENAME] Options: -s, --superuser role will be superuser -S, --no-superuserrole will not be superuser -d, --createdbrole can create new databases -D, --no-createdb role cannot create databases -r, --createrole role can create new roles -R, --no-createrole role cannot create roles -l, --login role can login (default) -L, --no-loginrole cannot login -i, --inherit role inherits privileges of roles it is a member of (default) -I, --no-inherit role does not inherit privileges -c, --connection-limit=N connection limit for role (default: no limit) -P, --pwpromptassign a password to new role -E, --encrypted encrypt stored password -N, --unencrypted do not encrypt stored password -e, --echoshow the commands being sent to the server --helpshow this help, then exit --version output version information, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as (not the one to create) -W, --passwordforce password prompt If one of -s, -S, -d, -D, -r, -R and ROLENAME is not specified, you will be prompted interactively. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Trying to create implicit casts to text in PG 8.3
On Tue, 2009-01-13 at 16:58 +, Sam Mason wrote: > In the end, any type system is just a tool. It's main job is to find > bugs in code by spotting a common class of error The purpose of the database as a whole is to preserve the integrity of your data. The type system is a key component of that. The main job of the type system is to assist in insuring that your data is correct. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] PgUS 2008 end of year summary
On Mon, 2009-01-12 at 20:18 -0300, Alvaro Herrera wrote: > Cross posting is not necessarily bad; in fact it's regarded to be less > annoying than multiposting, which is what you did. For argumentation, > see here > http://www.cs.tut.fi/~jkorpela/usenet/xpost.html#why > > (If you want it to be even more pain-free, add a Reply-To: > pgsql-advocacy header or some such.) Hmm good point. I didn't think about that idea. Sincerely, Joshua D. Drake > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] PgUS 2008 end of year summary
On Mon, 2009-01-12 at 18:34 -0300, Alvaro Herrera wrote: > Joshua D. Drake wrote: > > Hello, > > > > It is now 2009 and time for a, "Thanks for all the laughs 2008!" > > That's all great, congratulations. > > In the future please do not spam multiple lists with the same message. > Or rather, if you want the message to appear in more than one list, > please CC them all in a single message instead of sending one message to > each. If we do that, we get cross posting. That is why I didn't. Sincerely, Joshua D. Drake > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgUS 2008 end of year summary
Hello, It is now 2009 and time for a, "Thanks for all the laughs 2008!" 2008 was the year that PgUS spent getting its feet under itself. We formed our board, filed all of our legally required paperwork, paid a lot of money to attorneys, held elections and even managed to have some fun by working on parts of our mission. Many goals for 2008 were met. We have ensured that when the new board was seated the majority of our logistical infrastructure was in place. CPA, Legal, etc... Our Attorney is: Davis Wright Tremaine LLP 1300 SW Fifth Avenue Suite 2300 Portland, OR 97201 Our CPA is: Alten Sakai & Company LLP 1815 SW Marlow Ave., Suite 218 Portland, OR 97225-5187 We have retained Alten Sakai for not only standard CPA activities but also book keeping and general accounting. The goal being that those focusing on the success of PgUS are not book keepers or accountants. PgUS will be able to focus on actually educating people on PostgreSQL in the United States. Through our relationship with Alten Sakai we will be able to provide monthly financial statements to our members in a reliable manner. Expect to see the first detailed statement in March. PgUS closed out the year with ~ 10k USD. We have no outstanding payables and only one outstanding receivable. This leaves us with enough money to handle any sundry expenses through 2009 including Accounting and Legal fees. Our community efforts included: We started the process of working on our strategy with education. Part of that conversation can be found here: http://archives.postgresql.org/pgus-general/2008-08/msg00036.php Michael Brewer who is heading up that strategy will be posting an update to the website shortly. We worked with PostgreSQL Conference, U.S. to hold West: http://www.postgresqlconference.org/ We presented at Northern Arizona State. In 2009: We will seat the four elected members of the board: Richard Broersma, Jr. Andrew Dunstan Greg Sabino Mullane Robert Treat We will also work with PostgreSQL Conference, U.S. to hold East, West, and LFNW PgDay. We hope to hold several free classes and seminars on PostgreSQL as well as continue an aggressive move into education. Getting students and professors to start using PostgreSQL is a key to ensuring a strong relationship with potential new community members over the long term. We want to hear from our members. How is it that PgUS can help our members with their PostgreSQL needs? How would our members like to participate with PgUS? Are there members out there that have just been waiting for an opportunity to participate? What is that opportunity? What talents do you have and how would you like to use them? For those who want to help but are not sure exactly how, here are some ideas: * Drupal expertise The PostgreSQL.US site runs on Drupal + PostgreSQL * Speakers (PgUS will sponsor as it is able) We are actively pursuing every speaking engagement possible. We need more than one person to handle these engagements. * Tutorial writers This can be everything from how do I perform a backup with PostgreSQL to working with Pylons and PostgreSQL. * Community incursion We need guerrillas to start educating other communities on a more aggressive level and helping make sure that their postgresql support is top knotch. An very simple example is Drupal. They have an open issue list for Drupal 7 that is explicit to PostgreSQL. It can be found here: http://groups.drupal.org/node/17671 Lastly, please make note of our address change. Our new address is: United States PostgreSQL Association 1767 12th Street #149 Hood River, OR 97031 Sincerely, Joshua D. Drake President United States PostgreSQL Association (PgUS) -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Conference / PgCon.US update
Hello, In an attempt to ensure the continued positive growth of the community, PostgreSQL Conference, U.S. is going to change its current policy toward the domain PgCon.US. The current policy is that the domain would only be used in lieu of http://www.postgresqlconference.org when space was significantly to display the long URL was significantly limited. The new policy will be that PgCon.US will not be used. I would encourage any and all other communities making use of the PgCon name to change their branding as well. In an effort to help any communities who have invested resources in using the PgCon name, PostgreSQL Conference will offer sub domain pointing (and hosting if required) to their conference sites. As an example, the Brazilian community is using the brand PgCon Brazil with the URL: http://pgcon.postgresql.org.br/ . At the Brazilian's request PostgreSQL Conference would configure: http://brazil.postgresqlconference.org/ If you are a community looking for such help, please don't hesitate to ask. It is my hope that this will put an end to the PgCon debate. Sincerely, Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] compile of 8.1.15
On Sat, 2009-01-10 at 17:37 -0700, Chuck Davis wrote: > I have been trying to install 8.1.14 and 15 and it seems like there is > an infinite loop in the make file. I do not have the skills to find > it. Has anyone else had success compiling for Linux with gmake? Which linux? WHich gcc? Which make? How about a paste of the error itself? Joshua D. Drake > > Thanks. > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Renaming tables and columns to lowercase
On Sat, 2009-01-10 at 17:25 -0500, Bruno Lavoie wrote: > Hello, > > In a migration process, I've imported a bunch of tables from an old > Access database to PostgreSQL. The name of tables and fields on the > source are mixed case. When copying tables to pg via ODBC, identifiers > are copied in mixed case into pg destination db. Data is copied without > any problem. > > Due to pg folding policies, I always need to quote my identifiers when > querying data on these tables. It's something overkill in typing and time. > > --> Can I rename all tables and columns to lowercase, in a specified schema? Sure. Do this :) \o /tmp/go_to_lower select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' || lower(tablename)||';' from pg_tables where schemaname = 'public'; psql -U username database < /tmp/go_to_lower Sincerely, Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] dblink between oracle and postgres?
On Thu, 2009-01-08 at 15:12 -0500, Josh Harrison wrote: > Hi, > Is there any utility like (oracle's dblink etc) that can establish > connection between oracle and postgres database? dbi-link > Thanks > Josh -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Cannot restart postgresql when increasing max_connections
On Thu, 2009-01-08 at 12:51 -0700, Gregory Williamson wrote: > Thom Brown wrote: > <...> > > I actually mentioned pgPool II to my boss earlier, and it's > something we > > will have to seriously consider, but will have to do some research > first. > > We had an application ported from Informix which initially required > 1000+ connections to handle peak load (one of 4 servers pushed by two > application servers doing spatial queries). > > Using pgPool means we have a max limit of 100 and have almost never > gone near it -- usually we have 12-25 connections at once and load and > throughput are higher than they were before. Not to disparage pgPool, but we have also had great results with pgBouncer. Joshua D. Drake > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Out of control query
On Fri, 2008-12-19 at 16:20 -0600, Jason Long wrote: > I have an inventory system based on PostgreSQL 8.3.5, JBoss, > Hibernate.. > > I have a query builder that lets users filter data in a fairly complex > way. > > For some reason the search gets out of control and consumes all CPU. > > I set my statement timeout to 2 minutes and this keeps the system from > going down totally, but this is happening much more frequently than it > used to. > > I can find the problem with the individual query easily enough, but I > would like a more general solution. > > What seems strange to me is that one one core of my Quad core x5472 is > 100%. The others show minimal usage. > > I would think that other users would get a different core and one > query could not deadlock the whole system for everyone. > > Do you think this is due to my Postgres or JBoss AS configuration for > database connections? > > Any advice will be greatly appreciated. An explain analyze of the query would be useful. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] A bit confused about Postgres Plus
On Wed, 2008-12-17 at 23:33 +, Simon Riggs wrote: > On Wed, 2008-12-17 at 10:05 +, Dave Page wrote: > A free market is better for consumers and an even playing field is the > best way to cooperate. I'm sure it wouldn't be much use if everybody > stopped writing patches and concentrated on writing installers instead > so they can slip in some commercial advantage... As both pgAdmin and the one-click installers are fully open source, I fail to see how this is reasonable. If you don't like how pgAdmin is packaged, you can always create your own. Sincerely, Joshua D. Drake > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Fri, 2008-12-12 at 18:45 +0100, Markus Wanner wrote: > Hello Joshua, > Well, yeah, maybe Postgres-R is going to loose that sale as well. But > hey, it's not long ago since you've open sourced it. What makes you > think that you've already "lost that sale"? I for example didn't find > time to look at the Replicator sources up until now. > > However, IMO there's more to a fork than just having different source > trees. When I hear of a fork, I think of something more like SQL-Ledger > vs. LedgerSMB, where major disagreements play a role. Everything else is > just ordinary evolution of software ;-) > Heh fair enough. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Fri, 2008-12-12 at 16:31 +0100, Markus Wanner wrote: > Hi, > > Let me simply point out and clarify, that I have absolutely no intent to > fork from Postgres. Quite the opposite, I'm interested in working > together with other Postgres hackers. I think the point is that right now Postgres-R (just like Replicator) keeps its own tree that incorporates the PostgreSQL code. When open sourcing replicator I tried very hard to convince myself and others that it was merely a "branch" of PostgreSQL. I lost that sale :P Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Thu, 2008-12-11 at 19:33 +, Simon Riggs wrote: > On Thu, 2008-12-11 at 11:29 -0800, Joshua D. Drake wrote: > > > > As I said before, if you think something is missing, submit a software > > > or a doc patch and submit it to peer review. Until then, I think its > > > misleading to claim that only your magic spice makes replication work > > > correctly and to make out everybody else had to go through huge pains to > > > get it to work. Cos I get emails all the time saying how easy it was to > > > configure and easy to administrate. > > > > I didn't say only my magic does that. What I said was purely factual. > > That PostgreSQL by itself doesn't provide everything you need to get log > > shipping working and my *ONLY* point was that we should be clear when > > people ask, that they will have to roll their own to a degree. > > Software or doc patch, please, if you want them to be really clear. We can't back patch features and like I said previously, 8.4 doesn't really suffer from this assuming you get your patch committed. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Thu, 2008-12-11 at 19:24 +, Simon Riggs wrote: > > > True, we rely on the existence of rsync, scp etc.. and go to great pains > > > to provide as much choice as possible. > > > > > > If you think other things are required you are welcome to contribute > > > them so they can be verified fault free by the community. > > > > Well I already provided them, with pitrtools :P but my only point is > > that we really do need to make sure and be clear about what we provide. > > In true PostgreSQL fashion we only provide the functionality required to > > build the tools we need. > > As I said before, if you think something is missing, submit a software > or a doc patch and submit it to peer review. Until then, I think its > misleading to claim that only your magic spice makes replication work > correctly and to make out everybody else had to go through huge pains to > get it to work. Cos I get emails all the time saying how easy it was to > configure and easy to administrate. I didn't say only my magic does that. What I said was purely factual. That PostgreSQL by itself doesn't provide everything you need to get log shipping working and my *ONLY* point was that we should be clear when people ask, that they will have to roll their own to a degree. Stop being defensive. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Thu, 2008-12-11 at 17:37 +, Simon Riggs wrote: > On Thu, 2008-12-11 at 09:14 -0800, Joshua D. Drake wrote: > > > I think this statement is misleading. The only thing core contains is > > the ability to use a bunch of utilities (with the exception of > > pg_standby) that aren't in core to provide log shipping. > > True, we rely on the existence of rsync, scp etc.. and go to great pains > to provide as much choice as possible. > > If you think other things are required you are welcome to contribute > them so they can be verified fault free by the community. Well I already provided them, with pitrtools :P but my only point is that we really do need to make sure and be clear about what we provide. In true PostgreSQL fashion we only provide the functionality required to build the tools we need. I am not saying this is bad or good. I just didn't want the guy to go away thinking he could just use postgresql and it would just magically replicate which (at least until potentially 8.4) is not possible unless you run replicator or a bunch of hand rolled utilities. Sincerely, Joshua D. Drake > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Thu, 2008-12-11 at 17:09 +, Simon Riggs wrote: > On Wed, 2008-12-10 at 18:34 -0500, Rutherdale, Will wrote: > > Thanks very much, Steve. > Yes, everything you need for log shipping has been contributed to the > main project. If you read things elsewhere, please refer closely to the > docs which are the most accurate source of detail. I think this statement is misleading. The only thing core contains is the ability to use a bunch of utilities (with the exception of pg_standby) that aren't in core to provide log shipping. Sincerely, Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Wed, 2008-12-10 at 21:39 -0700, Scott Marlowe wrote: > On Wed, Dec 10, 2008 at 8:43 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > On Wed, 2008-12-10 at 20:41 -0700, Scott Marlowe wrote: > >> On Wed, Dec 10, 2008 at 7:40 PM, Tim Uckun <[EMAIL PROTECTED]> wrote: > > > >> Log shipping doesn't really lends itself to switching back and forth > >> between masters and slaves. > > > > Really? It seems to me that you can make a base backup just as fast as > > you can sync from slony (or replicator or whatever). > > sorry if my post wasn't clear. slony's not really any better. I don't > think that there's any replication for pgsql that's easy to do that > in. Is there? No probably not. I mean they are all pretty easy (especially log shipping) but it is definitely true they are slow, depending on the size of the database. Joshua D. Drake > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Wed, 2008-12-10 at 20:41 -0700, Scott Marlowe wrote: > On Wed, Dec 10, 2008 at 7:40 PM, Tim Uckun <[EMAIL PROTECTED]> wrote: > Log shipping doesn't really lends itself to switching back and forth > between masters and slaves. Really? It seems to me that you can make a base backup just as fast as you can sync from slony (or replicator or whatever). Joshua D. Drake > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Thu, 2008-12-11 at 15:21 +1300, Tim Uckun wrote: > What happens when I bring the primary back on line. I now want this to > be primary again and catch up on all the transactions that were sent > to the secondary. I want the secondary to resume it's backup status. > You have to run a new base backup and have the slave ship logs to the master. > Is there a product which enables this kind of functionality? > https://projects.commandprompt.com/public/pitrtools -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Wed, 2008-12-10 at 18:45 -0500, Rutherdale, Will wrote: > Thanks, Joshua. > > As I mentioned to Steve, warm standby / log shipping seems to be the > main feature I'm looking for. > > The PITR solution you mention: is that an improvement over regular log > shipping? Or do I misunderstand where that fits into the system? It is a supplement to the system. Warm standby / log shipping in postgresql by itself is very bare. Basically you have the ability to archive files and take a base backup but there are no utilities to actually perform any of those functions. PITR Tools gives you a nice wrapper around all the various external tools you need to get standby working. Once configured it even handles archiver monitoring etc... If you look here: https://projects.commandprompt.com/public/pitrtools/browser/trunk/cmd_standby.README It will show you how cmd_standby uses pg_standby to perform various functions (including things like failover). Sincerely, Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Wed, 2008-12-10 at 18:34 -0500, Rutherdale, Will wrote: > Thanks very much, Steve. > > The main (but not only) type of data replication activity I'm interested > in right now would be the warm standby. Thus it appears from the > documents you showed me that log shipping is one solution currently > available in PostgreSQL. I would want to make this work between > geographically separated machines that have TCP/IP connectivity between > them. > > From what I understand from the documents so far, I can set up log > shipping using pgsql without any external packages. There also seems to > be support for periodic backups from the primary to the warm standby. You can. There is a BSD open source project here: http://projects.commandprompt.com/public/pitrtools That will help you with your warm standby needs quite a bit. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Data Replication
On Wed, 2008-12-10 at 17:18 -0500, Rutherdale, Will wrote: > Hi. > > I am trying to determine what kind of data replication is currently > available in PostgreSQL. This is for purposes of examining capabilities > of PostgreSQL as compared to other RDBMSs. > > I attempted some searches in various areas and came up with a > bewildering array of results but no clear answer. > > a) Slony-I provides master/slave data replication (3rd party product > but FOSS) Correct. > b) PGCluster for Multi-master data replication, also 3rd party add-on > tool, also FOSS. Correct. Unsure of stability. > c) Postgres-R for multi-master data replication, appears to be a code > fork of PostgreSQL Not stable as far as I know. > d) Support for data replication in core PostgreSQL engine coming up, as > announced in this article in June 2008: > http://scale-out-blog.blogspot.com/2008/06/postgresql-gets-religion-abou > t.html e) PITR which is asynchronous log shipping. This is available in all versions >= 8.1 (foss) f) PostgreSQL Replicator an asynchronous replication system and a fork of the core postgresql (see https://projects.commandprompt.com/public/replicator) g) Londiste, created by Skype. Similar to Slony but easier to manage > > By contrast, when I search a similar question about MySQL, I get a clear > answer that both master/slave and multi-master data replication are > supported directly by the core engine. And broken but yes :) > > Could somebody please help me sort this out? I would like to know > > i) What is the current available support for data replication in > PostgreSQL? See above. Let me know if it is unclear. > ii) Does anybody have experience with these tools they could share? Slony is useful if a bit difficult to manage. It is extremely flexible however. We have several customers that user it. Replicator is a previously closed source product. It is easy to use and the latest version for 8.3 is in beta with 1.9 on the horizon which adds DDL replication. We have several customers that use it. Londiste I have played with a bit, it works but I have no production experience with it. > iii) Is data replication planned for an upcoming release of PostgreSQL, > and if so what are the exact features and when is the release expected? > Planned yes, guaranteed no and it is still log shipping which means read only slaves are out (as I recall). Sincerely, Joshua D. Drake > -Will > > > > > - - - - - Cisco > - - - - - > This e-mail and any attachments may contain information which is confidential, > proprietary, privileged or otherwise protected by law. The information is > solely > intended for the named addressee (or a person responsible for delivering it to > the addressee). If you are not the intended recipient of this message, you are > not authorized to read, print, retain, copy or disseminate this message or any > part of it. If you have received this e-mail in error, please notify the > sender > immediately by return e-mail and delete it from your computer. > > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] When to hold Pg East
Hello, While I am currently negotiating where to hold Pg East, it would be great if we could get some community to help us determine WHEN to hold Pg East. There is a very short survey (four or five questions) over at: http://www.postgresqlconference.org/2009/east/ If I could get responses to it that would be very helpful, thanks! Sincerely, Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Ubuntu for servers (was TurnKey PostgreSQL)
On Wed, 2008-12-10 at 15:55 -0500, Eric Schwarzenbach wrote: > Robert Treat wrote: > > On Tuesday 09 December 2008 19:43:02 Liraz Siri wrote: > > > >> Greg has a good point. Ubuntu is a bit of a moving target. In contrast, > >> Debian has a much slower release cycle than Ubuntu and is thus > >> considered by many people to be preferable for production server > >> applications. > >> > >> > > > > Another option for folks is to switch to another operating system thats a > > bit > > more stable *cough*solaris*cough*bsd*cough* > > > > :-) > > > And don't forget about BSD. BSD is dying. > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] COPY error with null date
On Thu, 2008-12-04 at 19:35 -0700, Bill Todd wrote: > Using 8.3.3 I am trying to import a CSV file using the following copy > command. > > copy billing.contact from 'c:/export/contact.csv' > with delimiter as ',' > null as '' > csv quote as '"'; > > The following record record causes an error because the third field, "", > is a null date and causes the error following the record. How can I > change the copy command above so that a null date or number will be > imported as null? I do not care if empty strings are imported as an > empty string or a null. null as IS NULL > > Bill > > "IASAcctSys","09/09/1995","",... > > > ERROR: invalid input syntax for type date: "" > CONTEXT: COPY contact, line 6, column date: "" > > ** Error ** > > ERROR: invalid input syntax for type date: "" > SQL state: 22007 > Context: COPY contact, line 6, column date: "" > > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] spanish
On Thu, 2008-12-04 at 13:44 -0300, Gustavo Rosso wrote: > Please, exists postgres forum in spanish? http://archives.postgresql.org/pgsql-es-ayuda/ > Thanks > Gustavo > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] two postgres server seeing the same data
On Wed, 2008-12-03 at 16:23 -0500, Jaime Casanova wrote: > On Wed, Dec 3, 2008 at 1:10 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: > >> You may have two servers pointed at $PGDATA but at no point can > >> postgresql be running on both at the same time. > > > > More importantly, if you do this, you will probably be able to get the > > two postmasters to start up. This will permanently corrupt the data. > > is this true even if one of the server just send SELECTs? Yes. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Switch off PITR
On Wed, 2008-12-03 at 13:16 -0500, Bill Moran wrote: > In response to "Joey K." <[EMAIL PROTECTED]>: > > > > How do I turn off PITR in the mean time? I commented archive_command and > > issued a pg_ctl reload and postgres is *still* archiving logs to the backup > > server. > > Pretty sure you're going to need a full restart -- reload won't cause > that parameter to be re-evaluated. You can change archive_command to something like /bin/true and reload. However you will have to do a full base backup to get postgresql doing log shipping again. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] two postgres server seeing the same data
On Wed, 2008-12-03 at 12:29 -0500, Jaime Casanova wrote: > Hi, > > a client of mine is obsessed with the idea of having two postgres > servers looking at the same partition of data... i think i can > configure two servers pointing to the same $PGDATADIR and let one off > and the other on but is it possible for load balancing, i mean with > the two servers active? obviously one of the two should be only for > reading... No. You may have two servers pointed at $PGDATA but at no point can postgresql be running on both at the same time. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Postgres mail list traffic over time
On Tue, 2008-12-02 at 15:47 -0500, Bruce Momjian wrote: > Magnus Hagander wrote: > > > I saw that but that only shows total lines, not the number of lines > > > changed, or commits per hour, etc. > > > > I've got a database of all our commits with info like: timestamp, > > author, number of rows added/deleted, number of files modified, which > > files modified, rows modified in each file. Basically it's data quickly > > parsed from a "git log --stat" of HEAD (because it was a whole lot > > easier to parse the git stuff). It's got about 27,500 commits in it - > > only the stuff that happened on HEAD, nothing for backbranches. > > > > So, if you can be a bit more specific in what you want :) Attached is > > for example "commits per month" and "lines per month". > > Yea, this is the graph I was looking for; unfortunately it does not > shed any insight on why things seems busier; 'old age' is starting to > look plausible. ;-) It could also be that a lot of work is happening off channel. I know that many contributors are having the first 50 replies of the email on jabber, irc or directly and then posting to various lists at any given point. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Favorite Tom Lane quotes
On Tue, 2008-12-02 at 00:37 -0500, Greg Smith wrote: > Tom on things that might ruin his tape backups: > > "Then of course there are the *other* risks, such as the place burning to > the ground, or getting drowned by a break in the city reservoir that's a > couple hundred yards up the hill...Or maybe being burgled by Oracle > employees who are specifically after my backup tapes." What is a tape? > > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Monty on MySQL 5.1: "Oops, we did it again"
On Mon, 2008-12-01 at 20:05 +, Grzegorz Jaśkiewicz wrote: > > > On Mon, Dec 1, 2008 at 8:00 PM, Steve Crawford > <[EMAIL PROTECTED]> wrote: > > http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html > > All interesting, but especially the part about half-way down > under the heading "So what went wrong with MySQL 5.1 ?" - > must-read for anyone involved in selecting a database. > > > well, at least they have replication and partitioning built in. How > reliable it is, is completely another story - but still, they are a > step ahead in that regard. Depends on your needs, a broken step is worse than a manual one. Joshua D. Drake > Now I know why Tom Lane doesn't have a blog :) > > -- > GJ -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Postgres mail list traffic over time
On Fri, 2008-11-21 at 08:18 -0800, Ron Mayer wrote: > Bruce Momjian wrote: > > Tom Lane wrote: > >> Bruce Momjian <[EMAIL PROTECTED]> writes: > >>> I also was confused by its flatness. I am finding the email traffic > >>> almost impossible to continue tracking, so something different is > >>> happening, but it seems it is not volume-related. > >> Yes, my perception also is that it's getting harder and harder to keep > >> up with the list traffic; so something is happening that a simple > >> volume count doesn't capture. > > If measured in "bytes of the gzipped mbox" it looks like there's a > *huge* increase of volume on Hackers in the past 3 months - well > over twice the historical levels; and maybe 4X 2002-2006. Its because we eliminated the -patches mailing list. Joshua D. Drake -- -- 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] Postgres mail list traffic over time
On Fri, 2008-11-21 at 10:43 -0300, Alvaro Herrera wrote: > Tom Lane wrote: > Markmail shows some graphs. The one on the "main page" gives the > traffic for all the lists: > http://pgsql.markmail.org/ > > If you search for "pgsql-general" you get a graph for that list: > http://pgsql.markmail.org/search/?q=list%3Aorg.postgresql.pgsql-general > > Same for -hackers: > http://pgsql.markmail.org/search/?q=list%3Aorg.postgresql.pgsql-hackers > The top "Who sent it" list is very telling. It says, "Paging Tom Lane... take a vacation!" :) Joshua D. Drake -- -- 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] Postgres mail list traffic over time
On Fri, 2008-11-21 at 00:06 -0500, brian wrote: > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> > >> I am finding the email traffic > >> almost impossible to continue tracking, so something different is > >> happening, but it seems it is not volume-related. > > > > Yes, my perception also is that it's getting harder and harder to keep > > up with the list traffic; so something is happening that a simple > > volume count doesn't capture. > > Perhaps it's just subjective: we're all getting older. ouch > Soon, these pesky whippersnappers will want to twitter their PG > questions to this list over YouTube. > I assume you don't realize that is already happening :P Joshua D. Drake -- -- 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] Postgres mail list traffic over time
On Thu, 2008-11-20 at 21:19 -0800, Joshua D. Drake wrote: > On Thu, 2008-11-20 at 23:46 -0500, Tom Lane wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > > I know that my email (I am pretty sure I am subscribed to at least as > > > many lists as you) has been on a steady incline, especially through > > > -general and -hackers. > > > > I would have said the same, which is why I find it noteworthy that > > my mail logs don't seem to support that impression. Have you got > > actual log data on the point? > > I purge my postgresql logs except for some specific ones (like PGFG). > however, I have the entire archives.postgresql.org. > > pgsql-hackers (since inception, 1997), first line date, second line > number of messages. > pgsql-general 1998-05 139 1998-06 337 1998-07 438 1998-08 226 1998-09 187 1998-10 283 1998-11 269 1998-12 242 1999-01 302 1999-02 356 1999-03 385 1999-04 332 1999-05 404 1999-06 470 1999-07 411 1999-08 496 1999-09 385 1999-10 606 1999-11 512 1999-12 631 2000-01 667 2000-02 477 2000-03 219 2000-04 705 2000-05 843 2000-06 803 2000-07 1180 2000-08 861 2000-09 999 2000-10 1337 2000-11 1084 2000-12 1002 2001-01 1700 2001-02 1623 2001-03 1656 2001-04 1568 2001-05 1710 2001-06 1651 2001-07 1342 2001-08 1303 2001-09 1195 2001-10 1223 2001-11 1124 2001-12 901 2002-01 1216 2002-02 1419 2002-03 1388 2002-04 1287 2002-05 1192 2002-06 1366 2002-07 1893 2002-08 1261 2002-09 1438 2002-10 1444 2002-11 1517 2002-12 1225 2003-01 1657 2003-02 1760 2003-03 1597 2003-04 1611 2003-05 1295 2003-06 1951 2003-07 1586 2003-08 1836 2003-09 1880 2003-10 1604 2003-11 1768 2003-12 1664 2004-01 1708 2004-02 1355 2004-03 1215 2004-04 1210 2004-05 965 2004-06 1236 2004-07 973 2004-08 1677 2004-09 1337 2004-10 1579 2004-11 1557 2004-12 1358 2005-01 1877 2005-02 1535 2005-03 1622 2005-04 1460 2005-05 1379 2005-06 1413 2005-07 1332 2005-08 1632 2005-09 1232 2005-10 1945 2005-11 1438 2005-12 1402 2006-01 1743 2006-02 1218 2006-03 1602 2006-04 1372 2006-05 1604 2006-06 1268 2006-07 1170 2006-08 1501 2006-09 1289 2006-10 1588 2006-11 1866 2006-12 1619 2007-01 1953 2007-02 1720 2007-03 1724 2007-04 1304 2007-05 1650 2007-06 1796 2007-07 1257 2007-08 2097 2007-09 1385 2007-10 1722 2007-11 1770 2007-12 1487 2008-01 1621 2008-02 1527 2008-03 1666 2008-04 1446 2008-05 1144 2008-06 1055 2008-07 1251 2008-08 1188 2008-09 1252 2008-10 1485 2008-11 1045 -- -- 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] Postgres mail list traffic over time
On Thu, 2008-11-20 at 23:46 -0500, Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > I know that my email (I am pretty sure I am subscribed to at least as > > many lists as you) has been on a steady incline, especially through > > -general and -hackers. > > I would have said the same, which is why I find it noteworthy that > my mail logs don't seem to support that impression. Have you got > actual log data on the point? I purge my postgresql logs except for some specific ones (like PGFG). however, I have the entire archives.postgresql.org. pgsql-hackers (since inception, 1997), first line date, second line number of messages. 1997-01 939 1997-02 300 1997-03 534 1997-04 865 1997-05 484 1997-06 601 1997-07 392 1997-08 399 1997-09 579 1997-10 594 1997-11 381 1997-12 351 1998-01 870 1998-02 1326 1998-03 1121 1998-04 707 1998-05 632 1998-06 493 1998-07 490 1998-08 867 1998-09 675 1998-10 1221 1998-11 609 1998-12 600 1999-01 769 1999-02 699 1999-03 1008 1999-04 217 1999-05 1155 1999-06 1241 1999-07 1052 1999-08 705 1999-09 945 1999-10 962 1999-11 929 1999-12 1065 2000-01 1688 2000-02 1460 2000-03 288 2000-04 187 2000-05 1686 2000-06 1283 2000-07 1477 2000-08 890 2000-09 642 2000-10 1320 2000-11 1419 2000-12 1234 2001-01 1469 2001-02 1178 2001-03 1708 2001-04 1181 2001-05 1478 2001-06 1151 2001-07 955 2001-08 1220 2001-09 921 2001-10 1165 2001-11 1318 2001-12 970 2002-01 1411 2002-02 1233 2002-03 1246 2002-04 1565 2002-05 1169 2002-06 1045 2002-07 1339 2002-08 2308 2002-09 1843 2002-10 1469 2002-11 1257 2002-12 1172 2003-01 1356 2003-02 1324 2003-03 1262 2003-04 1033 2003-05 812 2003-06 1316 2003-07 1068 2003-08 1373 2003-09 1695 2003-10 1631 2003-11 1643 2003-12 836 2004-01 878 2004-02 1017 2004-03 1352 2004-04 1177 2004-05 1495 2004-06 1025 2004-07 1430 2004-08 1620 2004-09 953 2004-10 1084 2004-11 1226 2004-12 963 2005-01 1116 2005-02 987 2005-03 1086 2005-04 1022 2005-05 1626 2005-06 1598 2005-07 1162 2005-08 1217 2005-09 1484 2005-10 1442 2005-11 1587 2005-12 1278 2006-01 1050 2006-02 1282 2006-03 1343 2006-04 1158 2006-05 1386 2006-06 1645 2006-07 1660 2006-08 2060 2006-09 2397 2006-10 1583 2006-11 1031 2006-12 1437 2007-01 1663 2007-02 1953 2007-03 1871 2007-04 1285 2007-05 1201 2007-06 1140 2007-07 1019 2007-08 1244 2007-09 1230 2007-10 1575 2007-11 1380 2007-12 1000 2008-01 1236 2008-02 1324 2008-03 1308 2008-04 1928 2008-05 1128 2008-06 1161 2008-07 1512 2008-08 1391 2008-09 1910 2008-10 1715 2008-11 1431 > > regards, tom lane > -- -- 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] Postgres mail list traffic over time
On Thu, 2008-11-20 at 22:36 -0500, Tom Lane wrote: > I got interested by Bruce's plot of PG email traffic here > http://momjian.us/main/img/pgincoming.gifto > and decided to try to extend it into the past. The data I have > available is just my own incoming mail log, but being a pack-rat by > nature I have that back to April 1998. Attached is a graph of Postgres > list messages per month since then. I should note that this covers only > the mail lists I'm subscribed to, which has been most of them since > about 1999; but the first few numbers in this chart are undercounts by > comparison. Also, the very last dot is month-to-date for November and > so is an underestimate. > > So, to a first approximation, the PG list traffic has been constant > since 2000. Not the result I expected. Am I reading your graph wrong? I show a sharp increase right before 2006 and then a small drop off but a constant after that? I know that my email (I am pretty sure I am subscribed to at least as many lists as you) has been on a steady incline, especially through -general and -hackers. Joshua D. Drake > > regards, tom lane > -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SQL] [GENERAL] date range query help
On Thu, 2008-11-20 at 15:41 +1100, novice wrote: > omg - how embarrassing. > so sorry :( > I note your name is novice. Don't sweat it. We are here to help. Joshua D. Drake -- -- 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] Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?
On Wed, 2008-11-19 at 18:40 +0900, Craig Ringer wrote: > Phoenix Kiula wrote: > > > I have googled but it looks like there's a whole variety of > > information from 2003 (when PG must have been quite different) until > > now--some people find stored functions slow for web based apps, others > > find it is worth the maintenance. > > If your web servers are very close in network terms to your database > server, issue mostly non-trivial queries, and are on a low latency link, > it probably doesn't matter *that* much. For one query no... for a dynamic website that uses 100 - 200 queries to draw a page? 15ms * 200, 3000ms = 3 secs * 2 (both ways) = 6 seconds. Joshua D. Drake -- -- 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] PostgreSQL 8.4 download?
On Wed, 2008-11-19 at 17:19 +0200, Devrim GÜNDÜZ wrote: > On Wed, 2008-11-19 at 08:35 -0500, Josh Harrison wrote: > > If so can you direct me to "the" website? Just a tip... 8.4 isn't released... it isn't even beta yet. > > Here is yesterday's snapshot: > > http://yum.pgsqlrpms.org/srpms/8.4/postgresql-8.4devel_20081118.tar.bz2 > > and its md5 sum: > > http://yum.pgsqlrpms.org/srpms/8.4/postgresql-8.4devel_20081118.tar.bz2.md5 > > ...or maybe better: > > ftp://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2 > -- -- 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] MVCC and index-only read
On Tue, 2008-11-18 at 15:28 -0500, Jonah H. Harris wrote: > On Tue, Nov 18, 2008 at 3:09 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > Oh, and citation needed. I don't remember seeing anything about > > oracle using indexes as sole storage units back in 8i > > Your memory-foo is weak. See ORGANIZATION INDEX: > > http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem3e.htm#2061671 > Off topic much? > -- > Jonah H. Harris, Senior DBA > myYearbook.com > -- -- 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] Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?
On Wed, 2008-11-19 at 02:18 +0800, Phoenix Kiula wrote: > I am looking to convert all my database access code into stored > procedures in PL/PGSQL. > But a DBA told me that it will be much better to do all of these > things in a stored procedure as it may bring some performance > benefits. He's an oracle DBA so I am not sure if the same applies to > PG? Will a "function" that takes input values with 15 column data > fields including two TEXT fields and then outputs perhaps an array of > values to a PHP program be faster than 2-3 separate SQL queries issues > from PHP? > Generally speaking, yes. A stored procedure will be faster, if nothing else you don't have to deal with TCP delay. You will also want to make sure you are utilizing a connection pool. For database design, especially when dealing with web developers I find it is always good to have them review: http://www-01.ibm.com/support/docview.wss?uid=nas191f301ccd7abae2f862565c2007cf178 Joshua D. Drake > Thanks for any input. Or please point me online to any resource that > discusses this kind of info. I could not find any. > > PK > -- -- 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] unsigned int type
On Sat, 2008-11-15 at 23:27 +0100, Enrico Pirozzi wrote: > Hi all, > Is it present on postgresql 8.3.x any implementation of an unsigned int type? No. Just use bigint with a check constraint or a domain. There also might be (I don't recall) a project over at http://www.pgfoundry.org/ that provides the functionality. Joshua D. Drake > > Regards, > Enrico > > -- > That's one small step for man; one giant leap for mankind > > www.enricopirozzi.info > [EMAIL PROTECTED] > Skype sscotty71 > -- -- 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] Web Application Engineer - Drupal, PHP, CSS, JavaScript, Postgresql
On Tue, 2008-11-11 at 06:45 -0800, NetGraviton wrote: > Web Application Engineer - Drupal, PHP, CSS, JavaScript, Postgresql > Please use the proper list... pgsql-jobs. Joshua D. Drake -- -- 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] Current log files when rotating?
On Mon, 2008-11-10 at 16:35 -0500, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > It seems that there is enough need for this feature, that it has been > > implemented multiple times -- but most of them will fail in corner > > cases. Seems an obvious candidate for an in-core function ... > > ... which will still fail in corner cases. Not to mention the race > condition when the logger has just/is about to switch. Which is still 99% better than the alternative. > > regards, tom lane > -- -- 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] Current log files when rotating?
On Mon, 2008-11-10 at 20:55 +0100, hubert depesz lubaczewski wrote:
> On Mon, Nov 10, 2008 at 11:44:31AM -0800, Joshua D. Drake wrote:
> > Hmm what about just "ls -tu"
> > Which if I am reading the man page correctly sorts by last access time.
>
> which might not be what you need. the problem is that there is no
> guarantee that the last "accessed" file is the current one.
>
> on the other hand - there is also no such guarantee for current being
> "last after sorting", so there is a room for failure here anyway.
>
> the only way to do it without having problems with "strange" names or
> dates of access:
>
> ps uw -C postgres | grep logger | awk '{print "/proc/"$2"/fd"}' | xargs ls -l
> | grep /var/log/postgresql | awk '{print $NF}' | xargs tail -f
>
> but it has its own set of problems ranging from "being totally
> unreadable" to "requires running on postgres user or as root".
O.k. so I think this whole thread screams TODO...
A function that allows a postgresql user to know which log file is
currently being written to.
Joshua D. Drake
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdel / 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
Re: [GENERAL] Current log files when rotating?
On Mon, 2008-11-10 at 20:12 +0100, hubert depesz lubaczewski wrote: > On Mon, Nov 10, 2008 at 01:46:14PM -0500, Greg Smith wrote: > > strftime would both work I guess, those just seemed a little heavy (was > > hoping for an "alias"-sized answer) to figure out something that the > > server certainly knows. > > it's not nice, but it works: > alias pgtail='/bin/ls -1 /var/log/postgresql/postgresql*log | tail -n 1 | > xargs tail -f' > > of course it has some assumptions: > 1. your logs are in /var/log/postgresql/ directory (easy to change) > 2. your logs are named in such way that sorting them alphabetically will > sort them chronologically (i.e. %Y-%m-%d or something similar) (not easy > to change) Hmm what about just "ls -tu" Which if I am reading the man page correctly sorts by last access time. Joshua D. Drake > > depesz > > -- > Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ > jid/gtalk: [EMAIL PROTECTED] / 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
Re: [GENERAL] Current log files when rotating?
On Mon, 2008-11-10 at 13:46 -0500, Greg Smith wrote: > Let's say you're using logging_collector and you've put some %-escapes > into log_filename for daily log rotation. Perhaps it's daily rotation > with this pattern: > > log_filename = 'postgresql-%Y-%m-%d.log' > > Is there any good way to ask the server what log file name it's currently > writing to? I was trying to write something that does a "tail" on the > current log, and was hoping there was a simple way to figure out which > file that goes against. Looking for the latest timestamp or running > strftime would both work I guess, those just seemed a little heavy (was > hoping for an "alias"-sized answer) to figure out something that the > server certainly knows. > Sorry my response was dumb. That would be a pain. I checked the admin functions and there is currently no easy way to do it. Sounds like an easy thing to add though. Joshua D. Drake > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > -- -- 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] Current log files when rotating?
On Mon, 2008-11-10 at 13:46 -0500, Greg Smith wrote: > Let's say you're using logging_collector and you've put some %-escapes > into log_filename for daily log rotation. Perhaps it's daily rotation > with this pattern: > > log_filename = 'postgresql-%Y-%m-%d.log' > > Is there any good way to ask the server what log file name it's currently > writing to? I was trying to write something that does a "tail" on the > current log, and was hoping there was a simple way to figure out which > file that goes against. Looking for the latest timestamp or running > strftime would both work I guess, those just seemed a little heavy (was > hoping for an "alias"-sized answer) to figure out something that the > server certainly knows. > show log_filename; ? Then just grab the strftime string? Joshua D. Drake > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > -- -- 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] Read only access, via functions only
On Wed, 2008-11-05 at 15:46 -0800, Webb Sprague wrote: > Hi all > > Is there a away to set up a schema such that a certain role has (1) > read only access to (2) all the tables, but (3) must use predefined > functions to use that access? you can set transactions read only but the user can turn them off. Use views, functions and GRANT. Joshua D. Drake -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
