Re: [GENERAL] pg_restore validation?
On 02/09/2011 07:52 AM, Vick Khera wrote: On Tue, Feb 8, 2011 at 8:06 PM, u235sentinel <mailto:u235senti...@gmail.com>> wrote: Is there a way we can validate a postgers backup? (short of restoring it somewhere) Define "validate" for your purpose. Once you do that, then you can come up with the procedure for accomplishing your validation. Hint: simply restoring it somewhere may not be sufficient... For validate what I'm looking to do is provide either some log or message provided by postgres that will alert us when/if the backup did 'not' complete successfully. So I guess it's more of a pg_dump validation I'm looking into. I've been googling and found pg_rman which doesn't sound like what I'm looking for. It has a validate function but their documentation is a little light. Thanks!
[GENERAL] pg_restore validation?
Is there a way we can validate a postgers backup? (short of restoring it somewhere) Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] beginners autovacuum question
I'm tracking a problem with our tables being bloated and was curious if someone regularly kills autovacuum jobs, will autovacuum later reattempt to vacuum the table it was killed under? I've made autovacuum more aggressive and given more worker threads. Yet for some reason we're not keeping up. The tables on this system are very active (at least many of them are). We have a large database (above 3.5 TB at this time) on a Sun Solaris Thumper (sunfire 4500 series I believe). Vacuum does complete when I run it manually on a table. But I'm suspecting a coworker working late at night be my killing autovacuum. Reading through the logs right now to see if this is the case. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore question
We're backing up our database using pg_dump with compression. We're selecting each database however when we tried running a pg_restore everything cept for the roles were restored. I'm digging through the pg_restore options, Is there an option I'm forgetting to include? Also we're restoring from an 8.3.8 database to a 9.0 database. Not sure if that's part of our problem. Digging through the docs we're doing 'pg_restore -C -d postgres data.dmp' Am I missing something? Thanks! -- 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] Dell Poweredge server and Postgres
On 06/07/2010 08:08 PM, Vick Khera wrote: Our primary DB server is a Sun X4200 M2 with 20Gb RAM with a "Dual LSILogic FC7X04X 4Gb/s FC PCI-Express Adapter" fibre channel card plugged into it, directly connected to a SurfRAID Triton 16 array from Partners Data Systems. The SurfRAID does the RAID using its internal controlled. It has 16 SATA drives and 2GB of cache. I bought the whole system configured and burn-in tested from Partners Data Systems. I highly recommend them. Our backup DB server is almost identical. The only difference is that it is a Sun X4200 and uses the PCI-X version of the LSI card. If you're looking to do ZFS you can set this disk system to be in JBOD mode and it will easily handle all the data for you; I don't think you'll need multiple controllers. Thanks for the help. Sounds like we have a few things to talk about tomorrow in our management review :-) thanks again! -- 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] Dell Poweredge server and Postgres
On 06/07/2010 08:01 PM, Scott Marlowe wrote: Where I work we use these: http://www.pc-pitstop.com/sata_enclosures/scsas16rm.asp for when we need lots of throughput (file servers). They allow four SAS connectors instead of the typical one or two. and will be using these: http://www.aberdeeninc.com/abcatg/kitjbod-1003.htm for our database servers, where IOPS is far more important than seq speed. My experience has been that the number of RAID controllers is no where near as important as the speed of said RAID controllers. I'd rather have a very fast RAID controller handling 16 disks at once, than 4 mediocre ones handling 4 disks each. The optimal is to have two RAID controllers, so you have redundancy. Most decent RAID controllers can run RAID-1 across the two and then RAID-0 over those RAID-1 pairs (either software or hardware, depending on OS and hardware performance). I appreciate it. I'll chat with management about these. Thanks for the tip -- 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] Dell Poweredge server and Postgres
On 06/07/2010 12:13 PM, Vick Khera wrote: Ditto. Of late I'm buying HPs, but I haven't yet put one into database service. Our DB servers are all currently Sun with fibre channel cards to external RAID systems. What kind of external RAID systems do you connect your Sun servers to? I've talked to Oracle/Sun and haven't been able to get a solution even similar to the 4540 systems. I'm hoping to find something that will allow a couple disk controllers to a subsystem. Even one would be an improvement. That way if I have to I can setup a ZFS pool in whatever RAID config I want across multiple controllers and disks. I'm figuring a 2TB database will choke if I only have one controller handling more than a dozen or so disks. Dell's solution doesn't sound right to me. We've looked at HP. They are more expensive with similar hardware to what Dell is offering. -- 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] Dell Poweredge server and Postgres
On 06/06/2010 02:04 AM, Greg Smith wrote: u235sentinel wrote: A Dell system running a PERC with battery-backed write controller will be faster on database writes than your 4540. Those Sun boxes are terrible at OLTP style workloads in particular, the types of writes PostgreSQL does can't be cached by the hard drives in the Sun Thumper systems. It's possible to bottleneck at ~100 write transactions/second on them given a particularly incompatible application (I wrote one once, learned the hard way). Hmmm.. thanks for the wake up. I completely spaced on this. Cache for writing may be faster but cache for reading the data? Especially with as much as we're pulling off. Their controllers can do read caching but I don't think it will make up the difference between the two. I'm guessing it will be slower even in a raid 10 configuration (the 4540 was setup with a software raid 10 with the ZFS filesystem). The flip side is that you can absolutely approach 2GB/s on reads on your Sun system, and I'd expect the Dell one will bottleneck somewhere in the 500MB-1GB/s range no matter how many controllers or drives you put into it. If your workload is so read heavy that you won't see any advantage from the write cache you're missing in your Sun box, it's absolutely possible for the Dell system to be a step backwards. A big Thumper box will chug away doing big reads against a 2TB database like it's no problem at all, as you already know. Makes sense. The database is only now 2TB and growing still. Currently we can grow up to around 5 TB. I'm leery of doing this but we'll be asking for their comparison reports. I'm very curious. Thanks! -- 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] Dell Poweredge server and Postgres
On 06/05/2010 07:20 PM, John R Pierce wrote: My manager and I are looking at replacing a Sun x4540 server with a Dell server connected to a disk subsystem (or two). We're looking at the R710 servers connected to an MD1220 I believe (I'd have to look again at the quote). why are you looking at replacing it? you go onto say its working great. The server is close to it's EOL. We're looking at extending the support contract and warranty for another 3 years. Even if we do that, we're looking at purchasing at least one more system and replicating the data to a DR site. With 2 TB of data it would take us a couple days to recover from a backup. Much longer than we want the database down in case of failure. The new Oracle/Sun company has changed the config for the 4540. The smallest drives you can get are 1TB drives which more than doubles the price of the server. So we're looking at Dell, IBM, HP and so on. I figured their sales / marketing would say they are faster than the Sun systems. I don't know a single sales geek who wouldn't say that. I have asked for comparisons and will be following up on it tomorrow. I'd like to see their numbers. I figured I'd ask for opinions. Guess I received a few :-) thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Dell Poweredge server and Postgres
I'm curious if anyone has had any experiences (good and bad) using Postgres on Dell PowerEdge servers. My manager and I are looking at replacing a Sun x4540 server with a Dell server connected to a disk subsystem (or two). We're looking at the R710 servers connected to an MD1220 I believe (I'd have to look again at the quote). The concern we have is our 4540 has a 2TB database which is working great. The server has 48 hard drives (250 gig drives) in RAID 10 across 6 disk controllers. A couple HBA controllers connected to a couple dozen disks may be slower (though dell assures us it will be faster than our Sun box). I thought I'd toss this out and see if anyone has any thoughts on this. I'm inclined to try it. The drives quoted are 15k drives and the PERC controller has cache vs. the Sun controllers have no cache AFAIK. BTW, in the next few months I believe we're be hitting the 2.5-3 TB size for our database. The tables are partitioned which helps a lot. Performance would be a problem otherwise with that much data I think :-) Thanks! -- 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] [Fwd: Put me on your white list]
ROFL!! That's funny... We're spam but he requested and validated the email address to receive the list emails. On 06/02/2010 03:37 PM, John R Pierce wrote: hmmm. the listadmin may wanna poke this guy for using lame whitelisting. sadly, the headers give no clue who the recipient was, other than th domain pricom.com.au Original Message From - Wed Jun 02 14:34:16 2010 Return-Path: Received: from prix.pricom.com.au (203-206-181-78.perm.iinet.net.au [203.206.181.78]) by hogranch.com (8.11.6/8.11.6) with SMTP id o52LW2n26519 for ; Wed, 2 Jun 2010 14:32:03 -0700 Received: (qmail 26496 invoked from network); 2 Jun 2010 21:31:56 - Message-ID: <20100602213156.26494.qm...@prix.pricom.com.au> Date: Thu Jun 3 07:31:56 EST 2010 From: postmas...@pricom.com.au To: pie...@hogranch.com Subject: Put me on your white list Status: I'm sorry, but your mail: Subject: Re: [GENERAL] Exception while accessing database was considered possible spam and was quarantined. If you are a real mailer, simply hit "Reply" to this message and send it without changing the Subject, and you will be put on the Pricom white list. Sorry for any inconvenience caused but this measure is necessary to combat the flood of unsolicited email. Postmaster. Srejector: 20100603.073156 -- 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 authentication against Windows Domain
On 06/02/2010 08:05 AM, Stephen Frost wrote: * Joshua Tolley (eggyk...@gmail.com) wrote: On Tue, Jun 01, 2010 at 11:56:19AM -0600, u235sentinel wrote: I'm still trying to figure out why you wouldn't want to use GSSAPI.. It's a heck of alot better than using LDAP wrt security.. Stephen We would have to rebuild the binaries and we're already heavily using the database. I could rebuild it again but it's like the fourth time I've been asked to add a feature. I did read that GSSAPI was the way to go but I'm being told to try using LDAP instead. I don't have a lot of experience with either but I'll be able to figure it out I think :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres authentication against Windows Domain
Is there is a way to connect postgres to authenticate against a windows domain without recompiling and using gssapi. Ldap perhaps? Thanks!
Re: [GENERAL] Postgres Triggers issue
Adrian Klaver wrote: On Thursday 11 February 2010 1:57:39 am Albe Laurenz wrote: u235sentinel wrote: I have a strange problem we noticed the other day with triggers. We're running 8.3.3 on Solaris 10 (intel) and have a feed that comes in regularly to populate a table we're working on. The feed works just fine inserting rows however the following trigger stops the feed until we remove the trigger. Any thoughts on what I'm doing wrong here? Thanks! --- CREATE OR REPLACE FUNCTION r.m_t() RETURNS trigger AS $BODY$ BEGIN INSERT INTO temp_m_t VALUES (NEW.*,1+1); RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER tafter AFTER INSERT OR UPDATE ON r.m_a FOR EACH ROW EXECUTE PROCEDURE r.m_t(); What do you mean "stops the feed"? Can you describe the behaviour in database terms? What exactly happens, and how does it differ from what you expect? Are there error messages? If yes, could you quote them? Yours, Laurenz Albe In addition to the above I am not quite sure about this: INSERT INTO temp_m_t VALUES (NEW.*,1+1) Are you trying to have an incrementing number for the last value? As it stands you are are always going to get 2 inserted into that field. Yes this was intentional for testing purposes. We were trying to see if we can do it and it worked. Now we can get into the really fun stuff :-) Thanks to all for their help! -- 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 Triggers issue
Adrian Klaver wrote: Well that would depend on any number of factors. Without information on how the feed is being done or more detailed logs it is hard to say for sure. At a guess though, I would say it is because the 'feed' is being done wrapped in a transaction and when the trigger errors it aborts the transaction. From my perspective, I only see inserts when I select * from pg_stat_activity. I'm told it's a jdbc connection (don't know much about java myself) but it has been interesting to see that it's working now. Still I did find it odd that the inserts stopped when the badly written trigger was there I appreciate the help :D -- 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 Triggers issue
Trigger function for an insert/update trigger should return "NEW", not NULL (OLD - for "on delete" trigger): It's an AFTER TRIGGER, so the RETURN-Value ignored. According the doc: The return value of a BEFORE or AFTER statement-level trigger or an AFTER row-level trigger is always ignored; it might as well be null. http://www.postgresql.org/docs/current/static/plpgsql-trigger.html Andreas We found the problem. I did some additional digging and learned the admin in question was trying to trigger on a schema.table that didn't exist! Yeah I did slap him around a bit ;-) remembering the schema part of the name can be important!! ::grinz:: One further question, so we're doing inserts from a remote source (it's a radware system feeding us data). Why would it stop the system from inserting data when it's an after statement? I noticed a bunch of 'connection time out' messages in our logs. It is working so I'm good. Still it is interesting the feed just stopped when the trigger was enabled. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres Triggers issue
I have a strange problem we noticed the other day with triggers. We're running 8.3.3 on Solaris 10 (intel) and have a feed that comes in regularly to populate a table we're working on. The feed works just fine inserting rows however the following trigger stops the feed until we remove the trigger. Any thoughts on what I'm doing wrong here? Thanks! --- CREATE OR REPLACE FUNCTION r.m_t() RETURNS trigger AS $BODY$ BEGIN INSERT INTO temp_m_t VALUES (NEW.*,1+1); RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER tafter AFTER INSERT OR UPDATE ON r.m_a FOR EACH ROW EXECUTE PROCEDURE r.m_t(); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
