Re: [ADMIN] postgres Rules
Arun Gananathan wrote: Could any one let me know is there a way to findout the rules previously created in the database . thanks ganesh http://www.postgresql.org/docs/7.4/static/view-pg-rules.html#AEN51975 -- Ci-git une signature avortee. ** RIP ** ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Postgresql on software RAID
Hi all, I am experimenting with a few OS's for my new hardware. I plan to have a software RAID5 device for my pgsql data directory. I have been experimenting with FreeBSD and with Linux, does anyone have any thoughts on whether vinum RAID devices are better than Linux software RAID? Or vice versa... Or no difference at all? Any gotchas I should bear in mind? Thanks for any advice Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Moving a database AND changing the superuser
Rich Cullingford wrote: > Well, I was gonna use pg_dumpall to avoid the tedium of individual > dumps, but (gotcha!) pg_dumpall doesn't accept > --use-set-session-authorization (tho' it does run pg_dump!). It supplies that option automatically. > If I use > pg_dumpall, will pg_restore (with --use-set-session-authorization) be > smart enough to ignore the \connect calls in the dump? pg_dumpall output cannot be restored with pg_restore. > I would try all this stuff myself, but unfortunately my PG 7.4 > machine isn't available yet, so I'm grasping at straws... In PG 7.4, --use-set-session-authorization is the default and only option. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] postgres Rules
On Sun, Dec 14, 2003 at 01:25:11PM -, Arun Gananathan wrote: > Could any one let me know is there a way to findout the rules previously created in > the database . Try this: SELECT * FROM pg_rules; -- Michael Fuhr ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Postgresql on software RAID
On Tue, 16 Dec 2003, Adam Witney wrote: > > Hi all, > > I am experimenting with a few OS's for my new hardware. I plan to have a > software RAID5 device for my pgsql data directory. > > I have been experimenting with FreeBSD and with Linux, does anyone have any > thoughts on whether vinum RAID devices are better than Linux software RAID? > Or vice versa... Or no difference at all? Any gotchas I should bear in mind? For a lightly updated database, software RAID5 is a match for hardware RAID5. however, as the number of updates / second increase, the hardware RAID5 can easily outrun the software RAID5 while maintaining data reliably IF it has a battery backed cache. This is because the hardware RAID controller can respond immediately to fsync requests while holding the actual data in battery backed cache waiting for the right opportunity to flush said cache, and should a power outage occur, the raid5 hardware controller will write the cache when the machine powers up. Linux software RAID seems to have a parallelization issue when layering RAID 1 or 5 on top of 0 (or vice versa). I'm not familiar enough with BSD's RAID layer to comment on it in that light. They (linux and BSD) seem to be competitive in terms of performance for straight RAID5 or RAID1 though. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] character type modification
Can I alter a column from character varying(128) to text without having to create a temp table? I am running 7.3.3. Thanks -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [ADMIN] character type modification
On Tue, Dec 16, 2003 at 12:32:05PM -0500, Jodi Kanter wrote: > Can I alter a column from character varying(128) to text without having > to create a temp table? I am running 7.3.3. The FAQ has a question entitled "How do you remove a column from a table, or change its data type?": http://www.postgresql.org/docs/faqs/FAQ.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] character type modification
Existing table column data types cannot be altered in any stable version of postgres(7.4 or previous). I don't know if it is available in a beta release. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jodi KanterSent: Tuesday, December 16, 2003 11:32 AMTo: Postgres Admin ListSubject: [ADMIN] character type modificationCan I alter a column from character varying(128) to text without having to create a temp table? I am running 7.3.3.Thanks -- ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
Re: [ADMIN] character type modification
This is another discuttion about this problem. Please read these mails. >> >> I cannot write here all the syntax:I'll give an example: you have a table called "person" and you have a columncalled "name" varchar(10) and you want to change it into varchar(25).alter person add column name_temp varchar(25);update person set name_temp = name;alter person drop column name;alter person rename column name_temp to name;This is it. Now depending on you database complexity if you have a complexdatabase and a lot of indexes or triggers, you will have to drop these itemsand rebuild them.Have fun.Andy.- Original Message - From: "Chitta Ranjan Mishra" <[EMAIL PROTECTED]>To: "Andrei Bintintan" <[EMAIL PROTECTED]>Sent: Friday, December 12, 2003 5:46 AMSubject: [ADMIN] Help Urgent> Dear Sir,> Thanks for your reply. Kindly send me the syntax> for all this.>> Thanking you,> Regds> C.R.Mishra --- Andrei Bintintan <[EMAIL PROTECTED]> wrote: >> Create a new column> >> > alter add column > > varchar(25);> >> > Then copy the old column into the new column:> > update set = ;> >> > Drop the last column:> > alter drop column ;> >> > Rename the column:> > alter rename column to> > ;> >> > Take care for INDEXES if there are any.> >> > This should do it.> >> > Best regards.> >> >> > - Original Message - > > From: "Chitta Ranjan Mishra"> > <[EMAIL PROTECTED]>> > To: <[EMAIL PROTECTED]>> > Sent: Thursday, December 11, 2003 7:19 AM> > Subject: [ADMIN] Help Urgent> >> >> > > Dear Sir,> > > I wnat to alter the size of one of the column> > of a> > > table. How to do this in Postgres ? Plz help me.> > > It's very urgent...> > >> > > I tried with the following syntax :> > > but it failed> > >> > > alter table tablename modify(coumnname> > varchar(25));> > >> > > it's very urgnet...plz reply soon..> > >> > > Thanking you,> > > Regds> > > C.R.Mishra> > >> > >> > >> > >> > >> >> > > > Yahoo! India Mobile: Download the latest> > polyphonic ringtones.> > > Go to http://in.mobile.yahoo.com> > >> > > ---(end of> > broadcast)---> > > TIP 1: subscribe and unsubscribe commands go to> [EMAIL PROTECTED]>> > Yahoo! India Mobile: Download the latest polyphonic ringtones.> Go to http://in.mobile.yahoo.com - Original Message - From: Jodi Kanter To: Postgres Admin List Sent: Tuesday, December 16, 2003 7:32 PM Subject: [ADMIN] character type modification Can I alter a column from character varying(128) to text without having to create a temp table? I am running 7.3.3.Thanks -- ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
Re: [ADMIN] Postgresql on software RAID
> I am experimenting with a few OS's for my new hardware. I plan to have a > software RAID5 device for my pgsql data directory. > > I have been experimenting with FreeBSD and with Linux, does anyone have any > thoughts on whether vinum RAID devices are better than Linux software RAID? > Or vice versa... Or no difference at all? Any gotchas I should bear in mind? I use Postgres w/Vinum setup to mirror 2 9gig SCSI Cheetahs and I have no complaints. I bang on the db quite a bit (moderately busy site and tons of data analysis) and performance is great. Ultimately I am going to slap a hardware RAID card in there (need the immediate failover) but right now software wise performance is great. I'd probably only recommend doing mirroring in software though, RAID-5 is going to eat up alot more CPU and disk writes. -- "Don't 'kill -9' the SYSVMSG" [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Postgresql on software RAID
On Tue, 16 Dec 2003 [EMAIL PROTECTED] wrote: > > I am experimenting with a few OS's for my new hardware. I plan to have a > > software RAID5 device for my pgsql data directory. > > > > I have been experimenting with FreeBSD and with Linux, does anyone have any > > thoughts on whether vinum RAID devices are better than Linux software RAID? > > Or vice versa... Or no difference at all? Any gotchas I should bear in mind? > > I use Postgres w/Vinum setup to mirror 2 9gig SCSI Cheetahs and I have > no complaints. I bang on the db quite a bit (moderately busy site and > tons of data analysis) and performance is great. Ultimately I am going to > slap a hardware RAID card in there (need the immediate failover) but right > now software wise performance is great. I'd probably only recommend > doing mirroring in software though, RAID-5 is going to eat up alot more > CPU and disk writes. Just a clarification, it only costs for writes. For reads, an N disk RAID5 is exactly as fast as an N-1 disk RAID0. On a modern fast CPU machine, with a small write load, CPU usage is usually very low, say <5%. That said, my production databases usually sit on hardware RAID5 with battery backed cache, build / test servers often run on software RAID5. Money, meet mouth. :-) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] comparing with oracle
Hi Bruno, Thanks for the reply. Still my questions were unanswered. Pl could anyone give a solution. 1. Is there any way I can generate archive log in postgres 2. Is it possible to run the postgres in standby mode (like Oracle stnd by server) and apply the archive logs Thanks and regards, Sachdev On Mon, Dec 15, 2003 at 20:03:54 -0800, > [EMAIL PROTECTED] wrote: >> Hi, >> I was able to migrate the Oracle database to Postgres, including >> functions, procedures. Thanks to ora2pg script and postgres >> documentation >> on porting from oracle to Postgres. >> >> I have now two tuff task. We are running Oracle in stadby mode and apply >> archive logs at remote place to maintain our DR site. Is there any way I >> can generate archive logs.? > > You would need to look at one of the replication solutions. I don't know > lot about them. > >> I need to know postgres datacrash recovery techniques also. If the >> database crashes we have around 25Gb of data, is there any way apart >> from >> restoring the full back up? > > It depends on what kind of crash. If the machine unexpectedly shutdown, > then should be able to just start the server back up. If you lost a hard > drive then you will need to restore from backup. Note that backing up the > data directory while the postmaster is running will not give you a usable > backup. You either need to use pg_dump or you need to do a backup while > the postmaster is shutdown. There is no point in time recovery available > at this time. > - This email was sent using DACafeMail. Get Your FREE 25 MB eMail Account Now. http://cafemail.dcccafe.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Postgresql on software RAID
Adam Witney wrote: Hi all, I am experimenting with a few OS's for my new hardware. I plan to have a software RAID5 device for my pgsql data directory. I have been experimenting with FreeBSD and with Linux, does anyone have any thoughts on whether vinum RAID devices are better than Linux software RAID? Or vice versa... Or no difference at all? Any gotchas I should bear in mind? Of interest, there are a number of "old school" dba's of my aquantance who swear vociferously whenever the words RAID 5 and Database are mentioned in association with each other. According to the theory they expound, a database with any significant write activity whatsoever should never be on raid 5, but instead be on raid 0+1. I've never had a chance to benchmark a comparison between the two and draw my own conclusions, but it might be interesting to do so. -- Pat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] comparing with oracle
Your pardon, but I submit that the responder did answer your question. Unfortunately, your question may be meaningless in the context asked. Specifically, you are asking for an Oracle specific feature, which may not have any direct comparison in another system. Instead, it is better to ask how to accomplish whatever your requirement is in postgres. As an analogy, it's like asking a person who only understands windows how to grep. Their response would be 'huh' ? Instead, if you ask them how to search files for text ... Given this, you seem to be asking 'how do a keep a hot backup dbms server', and the responder said 'using database replication'. That's the same answer I'd give, with the additional tidbit 'using ha/failover'. Hope this is what you wanted ! -- Pat [EMAIL PROTECTED] wrote: Hi Bruno, Thanks for the reply. Still my questions were unanswered. Pl could anyone give a solution. 1. Is there any way I can generate archive log in postgres 2. Is it possible to run the postgres in standby mode (like Oracle stnd by server) and apply the archive logs Thanks and regards, Sachdev On Mon, Dec 15, 2003 at 20:03:54 -0800, [EMAIL PROTECTED] wrote: Hi, I was able to migrate the Oracle database to Postgres, including functions, procedures. Thanks to ora2pg script and postgres documentation on porting from oracle to Postgres. I have now two tuff task. We are running Oracle in stadby mode and apply archive logs at remote place to maintain our DR site. Is there any way I can generate archive logs.? You would need to look at one of the replication solutions. I don't know lot about them. I need to know postgres datacrash recovery techniques also. If the database crashes we have around 25Gb of data, is there any way apart from restoring the full back up? It depends on what kind of crash. If the machine unexpectedly shutdown, then should be able to just start the server back up. If you lost a hard drive then you will need to restore from backup. Note that backing up the data directory while the postmaster is running will not give you a usable backup. You either need to use pg_dump or you need to do a backup while the postmaster is shutdown. There is no point in time recovery available at this time. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Postgresql on software RAID
When grilled further on (Tue, 16 Dec 2003 22:30:04 -0600), Patrick Spinler <[EMAIL PROTECTED]> confessed: > > According to the theory they expound, a database with any significant > write activity whatsoever should never be on raid 5, but instead be on > raid 0+1. > Kind of related and a point of reference. We use ClearCase and have many multiple Gb vob's(databases). We were using RAID-5, but had to back off to RAID 0+1 because of performance reasons (which was indicated in the manual, once you read it...). This would happen around 1-2Gb's vob size. Our usage of CC provides heavy writing activity to the underlying dB. I don't know what kind of dB engine Atria->Rational->IBM has implemented underneath, or even it it would look like a dB to someone who knew the difference... Cheers, Rob -- 21:42:33 up 4 days, 13:15, 1 user, load average: 2.23, 2.10, 2.10 pgp0.pgp Description: PGP signature
Re: [ADMIN] Postgresql on software RAID
Patrick Spinler wrote: > Adam Witney wrote: > > Hi all, > > > > I am experimenting with a few OS's for my new hardware. I plan to have a > > software RAID5 device for my pgsql data directory. > > > > I have been experimenting with FreeBSD and with Linux, does anyone have any > > thoughts on whether vinum RAID devices are better than Linux software RAID? > > Or vice versa... Or no difference at all? Any gotchas I should bear in mind? > > > > Of interest, there are a number of "old school" dba's of my aquantance > who swear vociferously whenever the words RAID 5 and Database are > mentioned in association with each other. > > According to the theory they expound, a database with any significant > write activity whatsoever should never be on raid 5, but instead be on > raid 0+1. > > I've never had a chance to benchmark a comparison between the two and > draw my own conclusions, but it might be interesting to do so. I have heard you need +6 disks for RAID5 to be faster than Raid 0+1. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
