Re: [ADMIN]

[scott.marlowe]

On Mon, 23 Feb 2004 [EMAIL PROTECTED] wrote:

> Hi,
> 
> We are running Postgresql 7.3.4.   Does anyone know how to make pg_dump
> write
> 
> dumps larger than 2 GB??  Compilation with D_FILE_OFFSET_BITS=64 gave
> no results...

Hi, what OS and such are you running on?  I've had no problems on RH7.2 
doing this.  And I just use a default ./configure source file made pgsql 
7.2.x




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] IDE and write cache

[scott.marlowe]

On Wed, 11 Feb 2004, Mark Lubratt wrote:

> Interesting discussions on IDE drives and their write caches.
> 
> I have a question...
> 
> You mentioned that you'd see the problem during a large number of 
> concurrent transactions.  My question is, is this a necessary condition 
> for the database crashing when the plug was pulled, or did you need use 
> a large number of concurrent transactions to "guarantee" that when you 
> pulled the plug, that it would be at an inopportune time?  In other 
> words, is an IDE drive still "more" susceptible to a power outage 
> problem even under light load?

Basically, if the data has been written to WAL, and an fsync issued, and 
the drive has it in cache, but hasn't written it to the platters, and you 
lose power, the database will likely be corrupted and will refuse to 
startup when the machine boots up.  Also, of course, some data will be 
lost that was supposedly committed in a transaction.

So, yeah, the reason for having hundreds of open transactions is that it 
makes the window of opportunity for a lying drive to corrupt the database.

So, yes, even under light load, you could have a corrupted database if you 
lose power while a write is happening.  Of course, if the database is 
sitting idle at the time of the power outage then you're ok.

-

Funny little story.  We had an electrician working above our main power 
switch (the big box that switches us from line power, to UPS, to the 
diesel generator) and said electrician clipped a piece of wire that fell 
into the switch, shorting it out, and taking down our entire hosting 
center (think $1,000 a minute...)

As I was walking down a hallway, one of the winders / fox pro guys asked 
me if my machine would come back up when the power came on (it runs on 
dial 36 gig 10krpm SCSI drives under an LSI megaraid with battery backed 
cache, and I've tested it pulling the plug before going production.)  I'd 
been bragging to him about the power plug pull tests it had passed, so of 
course, he's just teasing me.

I told him that as long as the power cut hadn't spiked the box and fried 
anything we were gold.

An hour later when they got the switch fixed and everything came back up, 
my machine came up fine, but the NAS machines that provide the web storage 
behind it (not the database, that's local) took about 10 minutes to fsck 
or mount or whatever it is they do.

So I'm walking by foxpro guy's desk and I casually say "Well, looks like 
my box had some problems coming back up."  He smiles, thinking he's got 
me, the bragging postgresql guy, by the short ones.  "yeah, seems it boots 
faster than the network storage it sits on.  Just CTRL-ALT-DEL and it was 
up and running fine."  He laughed along with me.  I trust Postgresql.  On 
SCSI or RAID with battery backed cache.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] [PERFORM] Quad processor options

[scott.marlowe]

On Wed, 12 May 2004, Grega Bremec wrote:

> ...and on Tue, May 11, 2004 at 03:02:24PM -0600, scott.marlowe used the keyboard:
> > 
> > If you get the LSI megaraid, make sure you're running the latest megaraid 
> > 2 driver, not the older, slower 1.18 series.  If you are running linux, 
> > look for the dkms packaged version.  dkms, (Dynamic Kernel Module System) 
> > automagically compiles and installs source rpms for drivers when you 
> > install them, and configures the machine to use them to boot up.  Most 
> > drivers seem to be slowly headed that way in the linux universe, and I 
> > really like the simplicity and power of dkms.
> > 
> 
> Hi,
> 
> Given the fact LSI MegaRAID seems to be a popular solution around here, and
> many of you folx use Linux as well, I thought sharing this piece of info
> might be of use.
> 
> Running v2 megaraid driver on a 2.4 kernel is actually not a good idea _at_
> _all_, as it will silently corrupt your data in the event of a disk failure.
> 
> Sorry to have to say so, but we tested it (on kernels up to 2.4.25, not sure
> about 2.4.26 yet) and it comes out it doesn't do hotswap the way it should.
> 
> Somehow the replaced disk drives are not _really_ added to the array, which
> continues to work in degraded mode for a while and (even worse than that)
> then starts to think the replaced disk is in order without actually having
> resynced it, thus beginning to issue writes to non-existant areas of it.
> 
> The 2.6 megaraid driver indeed seems to be a merged version of the above
> driver and the old one, giving both improved performance and correct
> functionality in the event of a hotswap taking place.

This doesn't make any sense to me, since the hot swapping is handled by 
the card autonomously.  I also tested it with a hot spare and pulled one 
drive and it worked fine during our acceptance testing.

However, I've got a hot spare machine I can test on, so I'll try it again 
and see if I can make it fail.

when testing it, was the problem present in certain RAID configurations or 
only one type or what?  I'm curious to try and reproduce this problem, 
since I've never heard of it before.

Also, what firmware version were those megaraid cards, ours is fairly 
new, as we got it at the beginning of this year, and I'm wondering if it 
is a firmware issue.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] [PERFORM] Quad processor options

[scott.marlowe]

On Tue, 11 May 2004, Bjoern Metzdorf wrote:

> scott.marlowe wrote:
> 
> > Well, from what I've read elsewhere on the internet, it would seem the 
> > Opterons scale better to 4 CPUs than the basic Xeons do.  Of course, the 
> > exception to this is SGI's altix, which uses their own chipset and runs 
> > the itanium with very good memory bandwidth.
> 
> This is basically what I read too. But I cannot spent money on a quad 
> opteron just for testing purposes :)

Wouldn't it be nice to just have a lab full of these things?

> > If your I/O is saturated, then the answer may well be a better RAID 
> > array, with many more drives plugged into it.  Do you have any spare 
> > drives you can toss on the machine to see if that helps?  Sometimes going 
> > from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in 
> > performance.
> 
> Next drives I'll buy will certainly be 15k scsi drives.

Better to buy more 10k drives than fewer 15k drives.  Other than slightly 
faster select times, the 15ks aren't really any faster.

> > In short, don't expect 4 CPUs to solve the problem if the problem isn't 
> > really the CPUs being maxed out.
> > 
> > Also, what type of load are you running?  Mostly read, mostly written, few 
> > connections handling lots of data, lots of connections each handling a 
> > little data, lots of transactions, etc...
> 
> In peak times we can get up to 700-800 connections at the same time. 
> There are quite some updates involved, without having exact numbers I'll 
> think that we have about 70% selects and 30% updates/inserts.

Wow, a lot of writes then.

> > If you are doing lots of writing, make SURE you have a controller that 
> > supports battery backed cache and is configured to write-back, not 
> > write-through.
> 
> Could you recommend a certain controller type? The only battery backed 
> one that I found on the net is the newest model from icp-vortex.com.

Sure, adaptec makes one, so does lsi megaraid.  Dell resells both of 
these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I 
believe.  We run the lsi megaraid with 64 megs battery backed cache.

Intel also makes one, but I've heard nothing about it.

If you get the LSI megaraid, make sure you're running the latest megaraid 
2 driver, not the older, slower 1.18 series.  If you are running linux, 
look for the dkms packaged version.  dkms, (Dynamic Kernel Module System) 
automagically compiles and installs source rpms for drivers when you 
install them, and configures the machine to use them to boot up.  Most 
drivers seem to be slowly headed that way in the linux universe, and I 
really like the simplicity and power of dkms.

I haven't directly tested anything but the adaptec and the lsi megaraid.  
Here at work we've had massive issues trying to get the adaptec cards 
configured and installed on, while the megaraid was a snap.  Installed RH, 
installed the dkms rpm, installed the dkms enabled megaraid driver and 
rebooted.  Literally, that's all it took.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] [PERFORM] Quad processor options

[scott.marlowe]

On Tue, 11 May 2004, Bjoern Metzdorf wrote:

> scott.marlowe wrote:
> > Sure, adaptec makes one, so does lsi megaraid.  Dell resells both of 
> > these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I 
> > believe.  We run the lsi megaraid with 64 megs battery backed cache.
> 
> The LSI sounds good.
> 
> > Intel also makes one, but I've heard nothing about it.
> 
> It could well be the ICP Vortex one, ICP was bought by Intel some time ago..

Also, there are bigger, faster external RAID boxes as well, that make the 
internal cards seem puny.  They're nice because all you need in your main 
box is a good U320 controller to plug into the external RAID array.

That URL I mentioned earlier that had prices has some of the external 
boxes listed.  No price, not for sale on the web, get out the checkbook 
and write a blank check is my guess.  I.e. they're not cheap.

The other nice thing about the LSI cards is that you can install >1 and 
the act like one big RAID array.  i.e. install two cards with a 20 drive 
RAID0 then make a RAID1 across them, and if one or the other cards itself 
fails, you've still got 100% of your data sitting there.  Nice to know you 
can survive the complete failure of one half of your chain.

> > I haven't directly tested anything but the adaptec and the lsi megaraid.  
> > Here at work we've had massive issues trying to get the adaptec cards 
> > configured and installed on, while the megaraid was a snap.  Installed RH, 
> > installed the dkms rpm, installed the dkms enabled megaraid driver and 
> > rebooted.  Literally, that's all it took.
> 
> I didn't hear anything about dkms for debian, so I will be hand-patching 
> as usual :)

Yeah, it seems to be an RPM kinda thing.  But, I'm thinking the 2.0 
drivers got included in the latest 2.6 kernels, so no biggie. I was 
looking around in google, and it definitely appears the 2.x and 1.x 
megaraid drivers were merged into "unified" driver in 2.6 kernel.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] download problems

[scott.marlowe]

ftp3.us.postgresql.org is working for me.

On Tue, 11 May 2004, Thomas Burns wrote:

> Hi,
> 
> I'm having a really stupid problem -- it seems to be impossible to
> download postgre.  All of the US mirrors timeout.  Is this normal?
> There are no apparent problems with my connection.
> 
> Thomas E. Burns
> Founder, jGuru and knowspam.net
> http://www.knowspam.net -- Enjoy Email with knowspam.net
> http://www.jguru.com -- FAQs, Forums and News for Java developers
> [EMAIL PROTECTED]
> 415.255.7285
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] [PERFORM] Quad processor options

[scott.marlowe]

On Tue, 11 May 2004, Bjoern Metzdorf wrote:

> Hi,
> 
> I am curious if there are any real life production quad processor setups 
> running postgresql out there. Since postgresql lacks a proper 
> replication/cluster solution, we have to buy a bigger machine.
> 
> Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI 
> hardware-raid 10.
> 
> Has anyone experiences with quad Xeon or quad Opteron setups? I am 
> looking at the appropriate boards from Tyan, which would be the only 
> option for us to buy such a beast. The 30k+ setups from Dell etc. don't 
> fit our budget.
> 
> I am thinking of the following:
> 
> Quad processor (xeon or opteron)
> 5 x SCSI 15K RPM for Raid 10 + spare drive
> 2 x IDE for system
> ICP-Vortex battery backed U320 Hardware Raid
> 4-8 GB Ram

Well, from what I've read elsewhere on the internet, it would seem the 
Opterons scale better to 4 CPUs than the basic Xeons do.  Of course, the 
exception to this is SGI's altix, which uses their own chipset and runs 
the itanium with very good memory bandwidth.

But, do you really need more CPU horsepower?

Are you I/O or CPU or memory or memory bandwidth bound?  If you're sitting 
at 99% idle, and iostat says your drives are only running at some small 
percentage of what you know they could, you might be memory or memory 
bandwidth limited.  Adding two more CPUs will not help with that 
situation.

If your I/O is saturated, then the answer may well be a better RAID 
array, with many more drives plugged into it.  Do you have any spare 
drives you can toss on the machine to see if that helps?  Sometimes going 
from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in 
performance.

In short, don't expect 4 CPUs to solve the problem if the problem isn't 
really the CPUs being maxed out.

Also, what type of load are you running?  Mostly read, mostly written, few 
connections handling lots of data, lots of connections each handling a 
little data, lots of transactions, etc...

If you are doing lots of writing, make SURE you have a controller that 
supports battery backed cache and is configured to write-back, not 
write-through.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Postmaster hogs CPU

[scott.marlowe]

On Thu, 6 May 2004, Gaetano Mendola wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Tom Lane wrote:
> 
> | Gaetano Mendola <[EMAIL PROTECTED]> writes:
> |
> |>You can basically renice the process that is performing the query.
> |
> |
> | However, that's unlikely to do anything very pleasant, since you'll have
> | priority-inversion problems.  "nice" has no idea when the process is
> | holding a lock that someone else wants ...
> 
> That can be true, however in order to have a priority-inversion problem
> I think are necessary 3 different level of priority, you have carefully
> choose the postmaster and good value of nice in order to have it happen.
> 
> I was wandering about do the same work done with vacuum ( the sleep
> trick each n records) in order to slow some expensive but not crucial
> queries:
> 
> test> set query_delay = 10;  <-- 10 ms
> test> select * from ;

I like that idea.  Make it more like a query_priority and let the system 
figure out delays though.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Postmaster hogs CPU

[scott.marlowe]

On Wed, 5 May 2004, Chris Gamache wrote:

> When I run an query (that I have identified as expensive, and non-critical), I
> would like to be able to take its priority down a few notches to allow the
> standard every-day tasks of the DB to run unhindered. As it stands, postmasters
> elbow each other out for processor time, and it seems like one expensive query
> can bring other DB functions to a crawl. 
> 
> Hardware : Dual P4 Xeon 2.8GHz; 1 GB RAM; ULTRA320 RAID 10 with an ext3
> filesystem
> PostgreSQL 7.4.2
> 
> What can be done to allow for smarter preemption? Could I do anything at the OS
> level to throttle that particular postmaster's process? I'm running (IMO) a 
> balanced config, but there's always room for improvement. Its that oddball
> query that comes around once every so often that causes the problem.

It is inadvisable to change priority of backends as that could lead to 
deadlocks in certain situations I believe.

Have you read:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

yet?  It may just be that you don't have enough shared_buffers or whatnot.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Where to submit a bug report?

[scott.marlowe]

On Tue, 4 May 2004, Bruno Wolff III wrote:

> On Tue, May 04, 2004 at 18:50:09 +,
>   Bradley Kieser <[EMAIL PROTECTED]> wrote:
> > Hi All,
> > 
> > Is this the correct place to submit a bug report or should I do it 
> > somewhere else?
> 
> Bugs go to pgsql-bugs, how I don't think this is really a bug, but
> a "feature".
> 
> > 
> > BUG DETAILS:
> > 
> > PG version 7.4.2
> > Platform: Linux
> > 
> > BUG DESC:
> > 
> > Using aliases in the "select" part of a select clause isn't strongly 
> > checked against the alias definitions in the FROM part of the SQL. 
> > Specifically, if the alias in the "select" clause matches another table 
> > name (not in the FROM list), then the select is applied against that 
> > other table whereas the correct action would be to chuck and error.
> > 
> > EXAMPLE:
> > 
> > One table: acct_dets
> > One view: acct_dets_view created as select * from acct_dets_table where 
> > 
> > Select acct_dets.aaa, acct_dets.bbb from acct_dets_view;
> > 
> > The above select is accepted and processed when it SHOULD throw an error.
> 
> Postgres has a feature where tables not listed in FROM clauses get silently
> included. There is something that turns this off, but I think it is
> only available in the development version. This feature is really only
> useful for delete where there isn't a syntax to specify addition tables
> and you need to use a subquery instead. A number of people didn't like the
> way it works now as that usually when you end up using this feature
> it is a mistake in the query rather than intentional use.

It's in 7.4 and it's called:

#add_missing_from = true

uncomment that and set it to false and it should error out as expected. 


---(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] need libraries to complete PostgreSQLinstall

[scott.marlowe]

On Fri, 30 Apr 2004, kandiah ratnavale wrote:

> unable to complete install of PosrtgreSQL without undernoted libraries
> 
>   libcrypto.so.2
>   libssl.so.2
> 
> need assistance to locate these libraries for download??
> 
> earlier request of few days ago not responded to!!

Those libs are generally available as packages for bsd or linux, as part 
of your distribution.

What OS / version are you running?  Have you tried searching in google to 
see if you can find them?  libssl.so.2 is part of openssl, libcrypto is 
too.  so, it looks like you don't have openssl installed.  If you're 
running a linux package, look through your rpms to find openssl and 
install that.  Note that you should look for any security updates to 
openssl, as there have been a few nasty security bugs found in it 
recently.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] Multiple disks

[scott.marlowe]

On Thu, 15 Apr 2004, Cris Carampa wrote:

> scott.marlowe wrote:
> 
> > The current king of putting postgresql on multiple platters and getting 
> > best overall performance is a battery backed caching raid controller run 
> > one or more many disk RAID-5 arrays.
> 
> I thought the best choice were RAID-10 (1+0) (when available). There 
> isn't a write penalty with RAID-5?

there is for software RAID-5 or for non-battery backed cached RAID-5, but 
for a battery backed controller, the penalty is basically gotten rid of by 
being hidden in a lazy write.  Plus the controller can "gang" writes to 
individual drives to achieve a very good throughput.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] PGfs Codebase? even old version????bb at wv dot com

[scott.marlowe]

A couple of points, 

1:  One might want to look at the global file system, which i think is 
still in some kind of active development.

2:  Remember when the whole firebirdSQL thing went down and we said no 
would ever use the same name as our database for another project???  haha.  
The universe proves to be more strange and twisted than we had at first 
thought.

On Sun, 18 Apr 2004, Adam Alkins wrote:

> IIRC, the pgfs project was abandoned quite some time ago. wvfs was it's 
> replacement, but both have disappeared completely from wv's site. Why 
> not try emailing the author? bb at wv dot com
> 
> Something of interest is http://sourceforge.net/projects/sqlfs/ , the 
> code is in CVS.
> 
> Regards,
> Adam
> 
> Matt Dent wrote:
> 
> >Does ANYBODY know where I can get my hands on the GPL'd PGfs (Postgres
> >File System) code?  I saw a note in the archives from 2002 that nobody
> >has seen it in 'years'... and I've just spent over 2 1/2 hours scouring
> >the net to the best of my ability and can't find it -- nor an
> >explanation as to why it's no longer available ANYWHERE!
> >
> >Anybody know of a similar project out there -- or why this fell off the
> >face of the earth?  (Patents maybe?)
> >
> >Matt D.
> >
> >---(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
> >
> >
> >.
> >
> >  
> >
> 
> 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] Any Tips or Tricks?

[scott.marlowe]

On Sat, 17 Apr 2004, Joseph A. Nagy, Jr. wrote:

> I'm new to PostgreSQL and I was wondering if there were any tips or tricks
> to admining a postgre db and if there are any tools like phpmyadmin for
> postgre.

My biggest tips would be:

Get to know psql.  The postgresql monitor, with history, tab completion, 
and syntax diagrams built right in is often the most overlooked tool in 
the box because it isn't a GUI.  It might not be point and click but it is 
rich in capability, and pretty easy to use.

The second would be to search the archives of the mailing lists, as the pg 
community is one of the best online mailing list communities I've ever 
dealt with.  most questions are already answered somewhere in here, if you 
just use the right keywords to find it.

The last tip would be "don't be afraid to ask questions."   People here 
hardly ever bite.  :-)




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] varchar to text

[scott.marlowe]

On Tue, 13 Apr 2004, Stefan Holzheu wrote:

> I'd like to alter all columns from type varchar to text. Could I do this by:
> 
> UPDATE pg_attribute SET atttypid = 25, atttypmod=-1 where attrelid 
> =(select oid from pg_class where relname='table_name') and atttypid=1043;
> 
> I just tried on a test database. It worked fine with one exception: 
> Views depending on an altered column did not work anymore. After 
> recreating the views it was ok.
> 
> I know the procedure of "rename column - add column - delete column" but 
> it's laborious for a large number of columns. There was also a 
> discussion on the list maybe one year ago. Unfortunately I couldn't find 
> the thread in the archive.
> 
> We are running postgres 7.4.1

The old fashioned way of doing this was to dump the database, change the 
appropriate fields in the dump, and reload.

It's certainly faster than the rename add drop column boogie, and cleans 
up your data store at the same time.  Back in the days of transaction wrap 
around and index bloat, it wasn't such a bad thing to do every few months 
anyway.  :-)

Speaking of which, I just checked, and it appears I've got growing system 
index on stats problems in my older 7.2 database, so I'm off dump and 
reload it...  


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] sequence value of the record just inserted.

[scott.marlowe]

On Fri, 9 Apr 2004, Ben Kim wrote:

> 
> Dear admins,
> 
> I have a table whose primary key is a record_id with serial type. 
> 
> I would like to know, when I insert a new row, what was the value of the
> record_id that I just inserted. Since this is a multi user application, I
> cannot simply select max of the record_id or currval of the sequence.
> 
> I would appreciate an advice.

You want the functions for sequences:

nextval, currval, and setval:

http://www.postgresql.org/docs/7.4/static/functions-sequence.html

nextval and currval are transactionally safe.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] Multiple disks

[scott.marlowe]

On Thu, 8 Apr 2004, hal wrote:

> What is the best/simplest way to split:
>   a database
>   multiple databases
>   a table
>   multiple tables
> across more than one disk drive?
> 
> I know that this has come up before but I can't find
> any info.  A pointer to a HOWTO or other info would
> be wonderful.

The current king of putting postgresql on multiple platters and getting 
best overall performance is a battery backed caching raid controller run 
one or more many disk RAID-5 arrays.  The more disks, the better up to a 
dozen or two.

The red headed step child is the alternate location stuff, discussed in 
the docs here: 
http://www.postgresql.org/docs/7.4/static/manage-ag-alternate-locs.html
which will allow you to put individual databases in different locations.  
It's a hackish kludge that should be replaced by the upcoming tablespaces 
patches.  I'm not sure if those will be done by 7.5 release or not.

The next way is to individually link thinks like indexes onto other 
volumes.  The procedure is basically, create the index, figure out which 
file in $PGDATA/base/oidofyourdbhere is the index, shut down postgresql, 
copy to file elsewhere, softlink it, restart apache.  This setup will not 
survive reindexing or dropping / recreating the index.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [Fwd: Re: [ADMIN] Location of a new column]

[scott.marlowe]

I replied, and finally got him unsubscribed from the list.

He was just tired of receiving our admin list all the time and couldn't 
figure out how to turn it off, so I walked him through the web interface 
to get off the list.

On Thu, 8 Apr 2004, Terry Hampton wrote:

> 
>   Anyone know this list member,
>   who was kind enought to reply?
> 
> 
> 
>  Original Message 
> Subject: Re: [ADMIN] Location of a new column
> Date: Thu, 8 Apr 2004 06:58:56 -0700
> From: "scott" <[EMAIL PROTECTED]>
> To: "Terry Hampton" <[EMAIL PROTECTED]>
> References: <[EMAIL PROTECTED]>
> 
> FUCK U
> - Original Message -
> From: "Terry Hampton" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, April 08, 2004 5:36 AM
> Subject: [ADMIN] Location of a new column
> 
> 
>  >
>  > All,
>  >
>  > I thought this was answered before, but I
>  > could not find anything in my saved email.
>  >
>  > I need to ALTER a table to add a new column.
>  > I want the new column to be between two existing
>  > columns,  however. Didn't I see mention of
>  > an "AFTER" parameter that specified the location
>  > in the table where the new column should be
>  > placed ?Maybe not .. How then,
>  > can I specify the location within the table,   of
>  > new column ?
>  >
>  > 7.3  &  RH Linux
>  >
>  >
>  >
>  > Many thanks !
>  >
>  > Terry
>  >
>  >
>  > --
>  > Terry L. Hampton
>  > Project Manager
>  > LimaCorp, LLC   www.limacorp.com
>  > 513.587.1874
>  >
>  >
>  >
>  > ---(end of broadcast)---
>  > TIP 6: Have you searched our list archives?
>  >
>  >http://archives.postgresql.org
> 
> 
> 
> 



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] Raw devices vs. Filesystems

[scott.marlowe]

Note that the innefficiency could well lie with Informix's file system 
interfacing as easily as it could lie with the operating system.  Do they 
charge extra for being able to access raw devices or somehow make more 
money by supporting them?  If so, there could be a clear business case for 
lots of uwaits() in the code path that handles file systems.

I'm just saying it's a possibility.

On Tue, 6 Apr 2004, Gregory S. Williamson wrote:

> Remarkable, perhaps, to you. Not in the Informix world. But irrelevant to postgres, 
> no ?
> 
> -Original Message-
> From: Chris Browne [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 06, 2004 1:57 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [ADMIN] Raw devices vs. Filesystems
> 
> 
> [EMAIL PROTECTED] ("Gregory S. Williamson") writes:
> > No point to beating a dead horse (other than the sheer joy of the
> > thing) since postgres does not have raw device support, but ...  raw
> > devices, at least on solaris, are about 10 times as fast as cooked
> > file systems for Informix. This might still be a gain for postgres'
> > performance, but the portability issues remain.
> 
> That claim seems really rather remarkable.
> 
> It implies an entirely stunning degree of inefficiency in the
> implementation of filesystems on Solaris.
> 
> The amount of indirection involved in walking through i-nodes and such
> is something I would expect to introduce some percentage of
> performance loss, but for it to introduce overhead of over 900%
> presumably implies that Sun (and/or Veritas) got something really
> horribly wrong.
> 


---(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] code editing

[scott.marlowe]

On Fri, 2 Apr 2004, Mark Bross wrote:

> What is your recommendations for code editin in Postgresql?
> I'm a student at Regis university in Denver co.

Do you mean for editing the backend code itself, stylewise, or do you mean 
for editing your own code, like plpgsql functions?

I'll assume you mean an editor for plpgsql or queries or scripts.

I myself am an old schooler who prefers simple editors like vi or pico.

But almost any editor you like will work.

Are you on unix or windows?


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] Migrating from a slave disk

[scott.marlowe]

On Mon, 5 Apr 2004, Gastón Simone wrote:

> Hi all,
> 
> I have a slave disk with an old PostgreSQL installation. Now I want to
> migrate its information to my new primary disk with a new PGSQL
> installation. I have to do it this way because de old disk does not boot as
> primary any more. It prints a kernel error.
> Can somebody help me? Thanks a lot!

Is this a linux box running kernel level mirroring?  If so you can just 
mount the drive to access it.  If you've got a boot disk for the old drive 
you just change the jumpers to move the old one in place of the new one 
and boot from the boot floppy.


---(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] Do Petabyte storage solutions exist?

[scott.marlowe]

For quite some time.  I believe the max table size of 32 TB was in effect 
as far back as 6.5 or so.  It's not some new thing.  Now, the 8k row 
barrier was broken with 7.1.  I personally found the 8k row size barrier 
to be a bigger problem back then.  And 7.1 broke that in 2001, almost 
exactly four years ago.  6.5 came out in 1999-06-09, so the limit to table 
sizes was gone a very long time ago.

On Fri, 2 Apr 2004, Bradley Kieser wrote:

> Ah! It's been updated then! Coolio! You just can't beat OpenSource!
> ;-)
> Thx for the update!
> 
> Brad
> 
> Tony and Bryn Reina wrote:
> 
> > let alone the storate limit of 2GB per
> >  
> >
> >>table. So sadly, PG would have to bow out of this IMHO unless someone
> >>else nukes me on this!
> >>
> >>
> >>
> >
> >I just checked the PostgreSQL website and it says that tables are limited to
> >16 TB not 2 GB.
> >
> >-Tony
> >
> >  
> >
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] Do Petabyte storage solutions exist?

[scott.marlowe]

On Thu, 1 Apr 2004, Tony and Bryn Reina wrote:

>  let alone the storate limit of 2GB per
> > table. So sadly, PG would have to bow out of this IMHO unless someone
> > else nukes me on this!
> >
> 
> I just checked the PostgreSQL website and it says that tables are limited to
> 16 TB not 2 GB.

Actually, it's 32 TB, which can be quadrupled by increasing the block size 
to 32k, the maximum allowed, which would make the maximum table size 128 
TB.

I just saw your response before firing off my previous messages.  
Apologies if I came off harsh, but I've heard people at my office saying 
similar things because they "heard it on the mailing lists" so it much be 
true.



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] Do Petabyte storage solutions exist?

[scott.marlowe]

On Thu, 1 Apr 2004, Bradley Kieser wrote:

> I think as far as PG storage goes you're really on a losing streak here 
> because PG clustering really isn't going to support this across multiple 
> servers. We're not even close to the mark as far as clustered servers 
> and replication management goes, let alone the storate limit of 2GB per 
> table. So sadly, PG would have to bow out of this IMHO unless someone 
> else nukes me on this!

Hold on, there are instances of running postgresql on SANs that are many 
terabytes in size.  It will work fine, as long as you only need the one 
image of the server running at a time.  With FC-AL or more modern 
technology you can put ~256 devices on a single fibre loop, and most boxes 
can handle four of those controllers, so you have the possibility for 1024 
drives.  Of course, most kernels are not gonna handle that many drives 
well, so you're much better off aggregating the drives on a storage box, 
then mounting that from your database server.

HOWEVER, this isn't my biggest gripe, it is the misinformation you're 
spreading about a 2g table limit.  That's the individual FIELD limit on 
postgresql.  Tables can be significantly larger than 2g.

If you're not sure ask first, don't spread such misinformation, it makes 
both the community and the database look bad.


> 
> Brad
> 
> Tony Reina wrote:
> 
> >I have a database that will hold massive amounts of scientific data.
> >Potentially, some estimates are that we could get into needing
> >Petabytes (1,000 Terabytes) of storage.
> >
> >1. Do off-the-shelf servers exist that will do Petabyte storage?
> >
> >2. Is it possible for PostgreSQL to segment a database between
> >multiple servers? (I was looking at a commercial vendor who had a
> >product that took rarely used data in Oracle databases and migrated
> >them to another server to keep frequently accessed data more readily
> >available.)
> >
> >Thanks.
> >-Tony
> >
> >---(end of broadcast)---
> >TIP 4: Don't 'kill -9' the postmaster
> >
> >  
> >
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Best Platform for postgres.

[scott.marlowe]

On Thu, 1 Apr 2004, Hemapriya wrote:

> Hi,
> 
> Does anyone know postgres performance in Linux vs Mac
> Os.. Pls suggest the best platform to go for..

Generally, linux on X86, dollar for dollar, offers better performance than 
the Mac.  Even a relatively cheap single CPU ~2GHz machine with 1 gig ram 
will run quite fast.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Spontaneous PostgreSQL Server Reboot?

[scott.marlowe]

On Tue, 30 Mar 2004, Andrew Biagioni wrote:

> Alex,
> 
> the answer is "no" to all of these.  We are a tiny start-up (2 guys, and 
> we do our own cleaning);  ambient temperature varies significantly but 
> is not related to the failure, and one machine starts beeping when it 
> gets too hot (then we added an extra case fan);  no fancy watchdogs 
> (maybe someday...  One can only dream :-> );  three different cases, 
> power supplies, motherboards, etc., etc. (one power supply is 
> extra-large, and that's the machine that started failing first!).
> 
> We originally blamed the problem on hardware failure (first machine); 
> then on OS version/configuration (second machine);  now we're out of 
> things to blame, except maybe unusually bad luck...

What did memtest86 say?

Did the same person build all the machines?  I've seen plenty of folks 
build machines and zap the memory when installing it.  >95% of all ESD 
failures are partial / delayed failures, so just because a computer boots 
up doesn't mean proper ESD procedures were followed, and if not, and if 
you're in a dry environment like I am (I live in Denver) then it's quite 
possible all three have bad CPU/mobo/memory or something like that.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Deleting a database

[scott.marlowe]

On Tue, 30 Mar 2004, Juan Miguel wrote:

> This is a little question :
> 
> In postgres, "delete database dbsample", frees the disk space used for 
> this DB?, or I have to drop the files manually ?
> I wondered this question, because I read that when you delete records, 
> you must run vacuum for freeing the space.

the whole database instance is dropped, no need to clean up. Same is true 
for dropped tables, indexes, or any other complete object like that.

The only thing vacuum recovers is space within an object, like deleted 
tuples or index entries.


---(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] License for PostgreSQL for commercial purpose

[scott.marlowe]

On Fri, 26 Mar 2004, Radu-Adrian Popescu wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Doug Quale wrote:
> | Chris Browne <[EMAIL PROTECTED]> writes:
> |
> |
> |>The FSF characterizes the PostgreSQL license as being "an X11 style
> |>license."  They felt a need to distinguish between different
> |>variations of licenses that are called 'BSD licenses.'
> |>
> |>The FSF web site then compares various variations on "BSD licenses,"
> |>considering that there are some that they deem to be "free" (in their
> |>terms), and that there are others that they deem to _NOT_ be "free"
> |>(again in their terms).
> |
> |
> | No, that's not what the FSF says.  All the BSD licenses are considered
> | free by the FSF. (Look at the web page yourself.)  Most BSD licenses
> | are compatible with the GPL, but the original BSD license contains a
> | problematic advertising clause that makes it incompatible with the
> | GPL.
> |
> | The Postgres license is a free software license that is GPL
> | compatible.
> 
> Where GPL compatible means (possibly among other things) that I can get
> a BSD-licensed Postgresql and turn it into a GPL-licensed MyPostgresql ?
> 
> Not that I would, just curious. And even if I did, it would be a severly
> castrated postgresql, as the history of the "My" particle suggests :))
> ~ - sorry I couldn't resist.

No, it means you can distribute the two together like on a redhat CD 
without worrying about conflicting licenses.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] License for PostgreSQL for commercial purpose

[scott.marlowe]

On Thu, 25 Mar 2004, Eric Yum wrote:

> Dear Sir
>  
> I am a developer of one commercial organization. We are going to develop
> some applications with PostgreSQL 7.3.3. I learn from some websites that
> it cost no charge for developing software with PostgreSQL in commercial
> environment. However, I saw the PostgreSQL is under two type of
> licenses, namely, X11-style license
>   and BSD license for the
> following websites, 
>
> http://www.postgresql.org/licence.html
> http://www.gnu.org/directory/database/servers/postgresql.html

Postgresql falls under an X11 LIKE license, which happens to be the BSD 
license.  Of the links listed, the only definitive one is the one from 
www.postgresql.org

Postgresql is free for use in all instances, commercial or otherwise.  
The only requirements are the ones listed on the licence page you listed. 
I.e. you have to include the copyright notice.

So, it's free as in beer (no cost) and free as in freedom (no restrictions 
on use.)


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] date style

[scott.marlowe]

On Fri, 19 Mar 2004 [EMAIL PROTECTED] wrote:

>  Hi,
> Can any body explain me how to set the date style exactly as oracle date
> style  like dd-mon-.

http://www.postgresql.org/docs/7.4/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT

should cover it.




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] Re-Init database cluster.

[scott.marlowe]

On Wed, 17 Mar 2004, Louie Kwan wrote:

> Hi All,
> 
> I did a pg_dumpall and psql the output and created a db. Basically, I would
> like to clean up  everything including users and groups.
> 
> I tried to do initdb, but it didn't work. 
> 
> My question is that can I just delete the directory where the database
> cluster stored before.
> 
> I.e. rm -rf /var/lib/pgsql/data
> 
> Or I should do something else to re-init the database cluster.

You might wanna back up your pg_hba.conf and postgresql.conf files first, 
if you've got any settings in there you'd like to keep.  but yeah, that's 
the first step.  If you've got the space to back up the whole directory 
I'd advise you do that as well first.

Then initdb --locale=youlocalehere

and you're off.  but like Tom said, what made you wanna do this?  Just 
wanting a fresh start?  Corruption?  etc...?


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] Row data corruption under 7.3.5

[scott.marlowe]

On Wed, 17 Mar 2004, Marc Mitchell wrote:

> This is follow-up to a problem first reported on 3/1/04.  The problem
> has continued to occur intermittently and recently we experienced the
> first occurrence where the first column of a table was the column where
> the corrupted and thus we could not recover it.
> Google groups searching have found numerous hits for people reporting
> the same symptoms.  While we've seen some instructions to get things
> back, we've seen nothing about correcting the root cause.


Have you tested your hardware and proven to yourself that both your memory 
and your drive subsystem have no bad blocks or bits?

Postgresql is good, but it can't make up for broken hardware.

If you haven't actually tested your hardware, then you don't know if it's 
truly reliable or not.  and if you put a server into production without 
testing the drives and memory, you can't just expect it to be good.  I've 
seen many a brand new server, both intel and Sparc based, with bad memory 
or drives right from the factory.

> We've observed nothing that would lead us to believe there are any
> hardware problems.

What you are seeing from Postgresql IS a sign that you have hardware 
problems.  Please test your hardware immediately.


---(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: [PERFORM] [ADMIN] syslog slowing the database?

[scott.marlowe]

On Mon, 15 Mar 2004, Bruce Momjian wrote:

> scott.marlowe wrote:
> > On Thu, 11 Mar 2004, Christopher Kings-Lynne wrote:
> > 
> > > > You could also consider not using syslog at all: let the postmaster
> > > > output to its stderr, and pipe that into a log-rotation program.
> > > > I believe some people use Apache's log rotator for this with good
> > > > results.
> > > 
> > > Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
> > > the first rotation...
> > > 
> > > I've read in the docs that syslog logging is the only "production" 
> > > solution...
> > 
> > Can you use the apache log rotator?  It's known to work in my environment 
> > (redhat 7.2, postgresql 7.2 and 7.4) with this command to start it in my 
> > rc.local file:
> > 
> > su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 2>1&'
> 
> Sure, our documentation specifically mentions using rotatelogs.


hehe.  What I meant was can Christopher use it, or does he have a 
limitation in his environment where he can't get ahold of the apache log 
rotater... :-)  


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Hardware for a database server

[scott.marlowe]

On Wed, 10 Mar 2004, Erwin Brandstetter wrote:

> Hi List!

Howdy!

> Short version:
> It is for a medium sized database. ~ 50 users, < 5GB, biggest table < 1 
> million tupples, 60 tables, lots of indices, triggers, rules, and other 
> objects.

SNIP!

> What will I purchase?
> 
> CPU:
> Single AMD Opteron.
> Opteron, because i plan to migrate to amd64 debian as soon as Debian 
> has a stable release.
> Single, because multi-CPU would only make sense if the CPU could ever 
> get the bottleneck. But I don't have to fear that, right? No need for a 
> dual-cpu setup?
> Should I consider Athlon 64 FX or Athlon 64? I guess socket 940 has 
> more future than socket 754, right?

I would recommend going with a dual CPU machine.  while having lotsa CPUs 
isn't like to be necessary for what you're doing, dual CPUs are not quite 
the same thing.  In most cases having dual CPUs allows the OS to run on 
one CPU while the database runs on the other, so to speak.  I.e. there's 
enough going on to use a second processor a fair bit.  After that, it 
really depends on how CPU intensive your database use is.

Generally I've found dual CPU machines to be more responsive than single 
CPU machines under heavy load.

> Controller / Hard Discs:
> RAID 5 with 4+ discs including a hot spare. But SCSI or SATA?
> I am undecided on this. Until about a year ago, I would have said SCSI, 
> period. But I have read of SATA RAIDs for entry-level-servers doing 
> quite well and Linux dealing with it ever more smoothly.

Standard IDE drives have an issue that all the ones I've tested so far, 
and presumably, most of the rest lie about fsync, and therefore are not 
guaranteed to have a coherent database on them should you lose power 
during a transaction.  If SATA drives in fact have proper fsyncing with 
write caching, then they're a great choice.  You might want to test one or 
two before commiting to a rack of them.

SCSI drives seem to pass with flying colors.  With a battery backed 
caching RAID controller you can get very good results.

RAID5 with a small number of drives is fine for good read performance, but 
not as good under a heavily written environment like RAID 1+0 is.  For >6 
drives, RAID5 starts to catch up in a heavily written environment as the 
number of platters available to spread the writes out on goes up.

That said, we get great performance from RAID1 on our LSI megaraid, nearly 
as good as a 5 drive RAID5 for reads, and better for writes.

> ([1], [2])
> So I wonder if it is still a good decission to spend 3 times the money 
> per gigabyte on SCSI?

Only if your data is important.  In this instance, it sounds like most of 
what you're holding is coming from other sources, and losing a days worth 
of data is no big deal, since you can get it back.  If that's the case, 
back up every night, turn off fsync, and run on a rack of IDE or SATA 
drives, whichever are cheaper per meg, and spend your money on memory for 
the server.

> And do 3ware Controllers still have the best driver support under 
> Linux?

LSI/Megaraid's megaraid2 driver is very fast and very stable.  the adaptec 
drive seems to be working pretty well nowadays as well.  Installation on 
our Dell boxes with the adaptec were much more difficult than the 
megaraid2 driver, which uses dkms (dynamic kernel module system) which is 
a very cool system.  you install the dkms rpm, then when you install a 
source rpm for a drive, the dkms package kicks in, compiles it, puts it in 
the right directory, and you're ready to use it.

I've not used the 3ware controller before.

> Any harddisks known to be especially apt for databases (hi I/O load 
> ..)?

I really like the performance my Seagate Barracuda 36 gig 10krpm drives 
give me.  If it's mostly read, just throw as many drives at it as you can 
on fast busses.  Aggregate bandwidth is almost always the real key to fast 
performance.


> Power supply:
> Secured with UPS, auto-shutdown before power fails, so do I need my 
> RAID controller battery-backed still?

Yep.  Power supplies fail, motherboards fry and take out the power rail 
every so often.  Idiots trip over power cords.  hehe.  been there, done 
that, got the TShirt.

> RAM:
> As much as the motherboard will bear. 4 GB probably. This seems the 
> easyest point to decide on. Correct? DDR SDRAM PC333 or PC400?

If you're looking at 64 bit machines, most of those can hold >4 gig, at 
least 8 gig nowadays.  Don't buy tons today, but do buy enough to 
interleave access if you have >1 CPU.  Opterons can interleave access, and 
theoretically each CPU could get max memory bandwidth if you have enough 
banks to allow interleaving.  So it's theoretically possible for an 
SMP machine with 8 sticks totalling 1 gig could outrun the same machine 
with 2 sticks totalling 2 gigs, since there'd be a 75% chance that the 
second CPU accessing memory would not be in contention with the first CPU.

If you're looking at 32 bit machines, stick to 2 gigs unless you will 
regularly

Re: [ADMIN] HELP - 7.4.1 tcp connects

[scott.marlowe]

On Mon, 8 Mar 2004, gerold kathan wrote:

> hi,
> i built/installed a pgsql 7.4.1 on a redhat ES3 system. everything works
> fine - but connections via tcp from outside are NOT working (as i was
> used to in 7.3) 
> * configured postgresql.conf and pg_hba stuff
> 
> => is there a way to check locally whether the server can handle
> requests via tcp on port 5432 ?

Is there a firewall on the box?  Most modern Linux systems, ES3 included, 
can do their own firewalling, so you might wanna fire up whatever lets you 
edit the firewall on ES3 and see if you can poke a hole in it for port 
5432.  You can test if the server is responding on that port by using nmap

nmap -p 5432 youriphere




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] postgres on apple OS X server

[scott.marlowe]

On Tue, 9 Mar 2004, Mira Kaloper wrote:

> Hi,
> 
> I have a postgres database that is VERY slow - in one table we have 50 
> mil records in it.
> I have tried to play with postgres.conf file and shared memory but was 
> not able to speed it up to something that we can work with. We have 
> apple RAID and I am planning to take database to the raid. I was 
> wondering if someone has any hints about improving the speed on postgres.
> 
> Currently we have:
> shmall = 134217728
> shmmax = 134217728
> 
> and in postgresql.conf  I was setting up values to:
> shared_buffers = 15200
> sort_mem = 32168

There are a few areas here.

One, is database tuning:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

The next is query tuning.  Use explain analyze select ... 
to see what is taking the longest, and if any of the estimates for rows 
are way off from actual.  Further, check for things like mismatched FK 
relationships (i.e. a text field fking to an int field).   Lastly, make 
sure you're analyzing your data, and that you've vacuumed the database 
lately.  You may need a vacuum full if you've waited a long time since the 
last vacuum.

The next issue is your hardware.  There has been another report here on 
OSX performing pretty poorly, especially disappointing were the results 
with apple's XRAID product, which was running about 2/3 as fast as was 
advertised.

You might want to post the output of explain analyze of one of the slow 
queries.  The best list for this stuff is pgsql-perform..


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] Problem with data format

[scott.marlowe]

What version of Postgresql are you running.  Versions 7.2 and before would 
gladly swap around day / month when they needed to be to fit, and this 
caused me a few headaches where I had data feeds that were partially 
failing and couldn't figure out why.

On Sat, 6 Mar 2004, Dario Ottaviano wrote:

> That's ok, but how can i do that?
> I tried something like "Set datestyle='European', but nothing changed!
> 
> Please, can you help me!
> Thank you,
> Dario Ottaviano
> 
> 
> "Tom Lane" <[EMAIL PROTECTED]> ha scritto nel messaggio
> news:[EMAIL PROTECTED]
> > "Dario Ottaviano" <[EMAIL PROTECTED]> writes:
> > > So, for istance, if i write the date: 04/03/2004 (4 Mar 2004), it writes
> the
> > > date 03/04/2004 (3 Apr 2004) into the table .
> >
> > You need to change the DateStyle setting to agree with your desired
> > format.
> >
> > regards, tom lane
> >
> > ---(end of broadcast)---
> > TIP 6: Have you searched our list archives?
> >
> >http://archives.postgresql.org
> >
> 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 


---(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] Postgres and multiprocessor?

[scott.marlowe]

On Mon, 8 Mar 2004, mlists wrote:

> Can PostgreSQL use multiple processors if they are present in a 
> computer, and if so, how much will it speed up database server? Tnx in 
> advance.

Yes, Postgresql can use >1 CPU.  Each process can use one, and only one 
CPU by itself.  however, the OS can use another CPU, and another user can 
use another CPU.

The difference it makes depends greatly on your load.  If your database 
application is CPU intensive, and currently CPU bound, then yes, of 
course, the extra CPUS will help.  If your application is I/O / memory BW 
bound, then no, extra CPUs won't help much.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] Upgrading

[scott.marlowe]

On Thu, 4 Mar 2004, Indibil wrote:

> Hi:
> 
> I have a PostgreSQL 7.2.2 running in a Linux Mandrake 8.2 Server.
> I want to upgrade to PostgreSQL 7.3
> I would like to accomplish this by installing  fit rpm packages, because my
> linux skills are limited.
> However, there aren't any packages of PostgreSQL 7.3 for Mandrake 8.2
> I must be careful because the database is in a production environment.
> What would be the best way to upgrade?

Unless there is a known problem for you with 7.4, I'd recommend upgrading 
to that.  7.4.2 is due out soon (today or something like it) and it is 
a much easier upgrade than 7.2 to 7.3 was.  

You might want to upgrade to 7.2.5 first though, as there were some issues 
with early 7.2s upgrading to 7.4 I remember running into.

I don';t use RPMs for postgresql, so I'm not much help there.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] Problem with data format

[scott.marlowe]

On Sat, 6 Mar 2004, Dario Ottaviano wrote:

> Hi, i use Postgresql 7.2 on a windows server (Win 2000 Server).
> The problem is that when i write a date into a timestamp field of a table,
> it writes it in to No-Europe format.

7.2 had some issues with accepting the wrong date formats and just 
mangling them until they fit, whether it made sense or not (i.e. you could 
insert a date of the format 2003-22-03, which should be illegal, and it 
would swap it around and insert 2003-03-22)  The fixes are in 7.3 and 
later, I'd suggest upgrading to 7.4.x if you want proper bounds checking 
on dates.

> So, for istance, if i write the date: 04/03/2004 (4 Mar 2004), it writes the
> date 03/04/2004 (3 Apr 2004) into the table .

while 7.2 allows you to set a european or US date style, it will gladly 
swap months and days to get a date it can insert.  Better, in my opinion, 
to use 7.3/7.4 which enforce the date style you set.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Database Encryption (now required by law in Italy)

[scott.marlowe]

On Fri, 5 Mar 2004, Silvana Di Martino wrote:

> Alle 19:38, venerdì 5 marzo 2004, scott.marlowe ha scritto:
> > > Unfortunately, the new Italian law forces us to take seriously into
> > > account this catastrophic scenario and another one that is almost as
> > > worring: an unfaithful SysAdmin that copies your data and sells them to
> > > KGB. So, database encryption (and not disk encryption) is the _only_
> > > answer.
> >
> > the only way for this to work is for it to be a "two key system" like the
> > military uses for missile launch.
> >
> > One sysadmin as the "key" to the database box, but the data is encrypted
> > before being sent to the database box on another system with another admin
> > with another "key".  Preferably these two would never interact or know
> > each other.
> 
> Well, this is not necessarly true. Data maintainers and SysAdmin performs 
> different tasks (accordingly to italian law):
> - SysAdmins take care of the hardware and of the software. They should never 
> need to access data. They just need to access the RDBMS software and its 
> configuration.
> - Just Data Maintainers need to access data.
> This should allow us to have two password for two different tasks. So, there 
> is not any need to use the military scheme to enforce data security.

Sorry, but that's the wrong answer.  Once someone has root on a unix box 
her can do ANYTHING he wants.  and he can cover his tracks.  If the 
encryption takes place on his box, he can attach to the process doing the 
encryption and /or replace it with a trojan copy of his own and get your 
data.  The ONLY way to keep the data secure is  for it to be encrypted 
elsewhere before it gets to the storage box.  If the box that stores it 
encrypts, it, the root user on that box can impersonate anyone and any 
process on that box to get to the data in mid stream.


---(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] Database Encryption (now required by law in Italy)

[scott.marlowe]

On Fri, 5 Mar 2004, Silvana Di Martino wrote:

> Alle 15:11, venerdì 5 marzo 2004, Alex Page ha scritto:
> > If you're trying to protect against somebody taking down your server
> > room door with a sledgehammer, lifting your server out of the rack,
> > driving it away and booting off an alternative medium to avoid needing
> > to know your root password, then a loopback encrypted partition (or data
> > encrypted in GPG where the decryption key is not stored on the database
> > server) is a sensible precaution.
> 
> Unfortunately, the new Italian law forces us to take seriously into account 
> this catastrophic scenario and another one that is almost as worring: an 
> unfaithful SysAdmin that copies your data and sells them to KGB. So, database 
> encryption (and not disk encryption) is the _only_ answer.

the only way for this to work is for it to be a "two key system" like the 
military uses for missile launch.

One sysadmin as the "key" to the database box, but the data is encrypted 
before being sent to the database box on another system with another admin 
with another "key".  Preferably these two would never interact or know 
each other.

If the encryption and decryption happen on the same box that runs that 
database, then it's simply more work for the sysadmin to get at the data, 
not an impossibility.  Anything outside of two seperate systems, one with 
storage, the other doing encrypting without any form long term storage is 
just a charade of security.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] SYSDATE in PostgreSQL !?

[scott.marlowe]

On Mon, 1 Mar 2004, Bjoern Metzdorf wrote:

> Louie Kwan wrote:
> 
> > If there is no SYSDATE defined in PostgreSQL , what can I do ?
>  >
>  > createTimeStamp   DATE default (SYSDATE)
> 
> Perhaps NOW() ist what you are looking for?
> 
> createTimeStamp   DATE default 'NOW()'

I don't think you want the quotes around now(), otherwise it will always 
insert the same date / time each time.


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] Running Multiple Postmasters

[scott.marlowe]

On Thu, 26 Feb 2004, John Allgood wrote:

> Hello
> 
> I am building a server to run 9 databases. Can I run a seperate 
> postmaster for each of the 9 databases. I would be setting the port 
> number to a different value for each db of course. The reason I wish to 
> do this is so that I can install about three databases on a seperate 
> disk and put the other on seperate disk as well.

Up to here, there's no need for seperate postmasters, as you can easily 
install different databases onto different disk spaces.  

If this is your only reason, look into:

http://www.postgresql.org/docs/7.4/static/manage-ag-alternate-locs.html

Plus it gives you the advantage that you have one database cluster to 
manage and your tuning only needs to take into account a server handling 
one database.

That said, it's pretty easy to put multiple instances on one machine.

The way I do it, is to create a seperate account for each instance to run 
in, so that by looking at the prompt I know which database I'm 
administering, and no matter how much I might fat finger things, if I'm 
logged in as pgsql01 and try to delete the files for pgsql02 I can't do 
it, I don't have permission to.

Then you just have a line like this in rc.local for each database to get 
it started.

su - pgsql1 -c -- 'pg_ctl start -l $PGDATA/pgsql.log'

I'm not sure anymore (it's been a while since I did it last) if you have 
to include a port number anywhere in there.  I think the pg_ctl / 
postgresql.conf file handle all that though.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] PosgreSQL hogging resources?

[scott.marlowe]

Are you getting problems with crashing backends in postgresql and such 
showing up?  I'm wondering if you have bad memory or something like that.

In my experience, Linux/apache/php/postgresql never crashes, it just goes 
unresponsive when you get into severe overload.

Is your database vacuum / analyzed often?

Do you have indexes that are being used?

On Fri, 20 Feb 2004, Jeremy Smith wrote:

> I agree that my site is a bit bloated, it has more than 2500 total queries,
> but it is a bit more complex of an application that might be readily
> apparent.  For the curious, this is my site: http://www.xpertleagues.com.
> But the issue is that with mysql, at my peak levels last year I had a server
> load of 30+ (I know this is horrendous, I am looking into either upgrading
> my P4 2.4gig 1gig ram server this year, or distributing across more than one
> server) but the site itself never performed as slowly as it is now.  And
> amazingly considering the server load last year, the server never crashed.
> But now I am actually getting complaints on the lagtime, and I only have one
> league actively drafting, last year I had 70+ at peak.
> 
> I will look into some of the suggestions you have made, the problem is that
> I can't do large scale optimization at the moment because I am still adding
> features to the site.  I just wonder if the best mode of attack would be
> switching back to mysql until I have added all of the necessary features,
> optimizing the queries and code there, and then switching back to pg at a
> later date.
> 
> Jeremy
> 
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Mitch Pirtle
> Sent: Friday, February 20, 2004 1:57 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: [ADMIN] PosgreSQL hogging resources?
> 
> 
> Jeremy Smith wrote:
> 
> >I have newly installed PostgreSQL onto my server, the server's main
> function
> >is to serve up a fantasy football site that has a tremendous number of
> >queries per page.  Right now with very low traffic I am seeing a server
> load
> >of 2.0+.  That got me a little concerned, so I looked at "top" and noticed
> >that postgres is taking anywhere from 60 - 100 percent of my CPU at any
> >given time.  There are also 116 sleeping processes out of 123.  This all
> >seems very bad, do you guys have any idea what might be causing it or how
> it
> >can be addressed?  How do I go about cleaning out the sleeping processes?
> >
> I agree with Lamar's comments, as well as wondering if it is really
> needed to run a 'tremendous number of queries' for each page view...
> Some quick solutions could be to determine if you could:
> 
> 1) make changes to your design to require fewer hits to the database per
> page,
> 2) make a view that provided the information without running so many
> separate queries, and/or
> 3) consider using a caching library like ADOdb to limit the number of
> trips to your database
> 
> Any combination of these three could significantly reduce the load on
> your DB box, as well as provide some huge performance gains.  How hard
> is your webserver working?  Are they running on the same box?
> 
> -- Mitch
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html
> 
> 
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 


---(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] PosgreSQL hogging resources?

[scott.marlowe]

On Fri, 20 Feb 2004, Lamar Owen wrote:

> How fast does the page load?  That would be the big question.  Run apache 
> bench (ab) against the page and see how many pages per second yu can get.  A 
> load of 2.0, an average CPU of 60-100%, and 7 running processes is not bad at 
> all.  It just means your server is working.

That really depends on the server.  If it's a PII-266 then it's about 
right, if it's a dual AMD Athlon 2800 with 2 gigs of ram something's 
horribly wrong.  So, what kind of hardware is this jer?


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] PosgreSQL hogging resources?

[scott.marlowe]

On Fri, 20 Feb 2004, Jeremy Smith wrote:

> 
> I have newly installed PostgreSQL onto my server, the server's main function
> is to serve up a fantasy football site that has a tremendous number of
> queries per page.  Right now with very low traffic I am seeing a server load
> of 2.0+.  That got me a little concerned, so I looked at "top" and noticed
> that postgres is taking anywhere from 60 - 100 percent of my CPU at any
> given time.  There are also 116 sleeping processes out of 123.  This all
> seems very bad, do you guys have any idea what might be causing it or how it
> can be addressed?  How do I go about cleaning out the sleeping processes?

Don't worry about sleeping processes, you should have a good hundred 
sleeping on any unix box.  My workstation has 152, my server has 173, and 
the response time on both is way sub second.

Now, about postgresql, what is it doing when it's chewing up 100% cpu? 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Installing Postres support in PHP

[scott.marlowe]

But then you wouldn't need a .h file, as it's all rpms, it should just 
need a lib (.so, etc...)  I'm not sure what "build apache" means...

I always install apache/postgresql/php from source files, due to the 
issues I've had finding suitable rpms in the past for all three that work 
together.

On Fri, 13 Feb 2004, Jeremy Smith wrote:

> 
> It is a third party utility that installs it by RPMs.  It should work the
> same way as if I were installing the RPMs command line.
> 
> Jeremy
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of scott.marlowe
> Sent: Friday, February 13, 2004 3:39 PM
> To: Jeremy Smith
> Cc: [EMAIL PROTECTED]
> Subject: Re: [ADMIN] Installing Postres support in PHP
> 
> 
> On Fri, 13 Feb 2004, Jeremy Smith wrote:
> 
> > Hi,
> >
> > my php is not set up for postgres yet.  When I try to install it using
> Build
> > Apache, I get an error that file "libpq-fe.h" is not found.  Does anyone
> > know where to get this file and where it should be placed?
> 
> It this all installed from source, or RPMs?
> 
> 
> ---(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
> 
> 
> 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] Installing Postres support in PHP

[scott.marlowe]

On Fri, 13 Feb 2004, Jeremy Smith wrote:

> Hi,
> 
> my php is not set up for postgres yet.  When I try to install it using Build
> Apache, I get an error that file "libpq-fe.h" is not found.  Does anyone
> know where to get this file and where it should be placed?

It this all installed from source, or RPMs?


---(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] hanging for 30sec when checkpointing

[scott.marlowe]

On Thu, 12 Feb 2004, Steve Crawford wrote:

> > > I'm running a reasonable sized (~30Gb) 7.3.4 database on Linux
> > > and I'm getting some weird performance at times.
> 
> > I am having a similar problem and this is what I've found so far:
> >
> > During the checkpoint the volume of data that's written isn't very
> > high and it goes on for a fairly long time (up to 20 seconds) at a
> > rate that appears to be well below our disk array's potential.  The
> > volume of data written is usually 1-5 MB/sec on an array that we've
> > tested to sustain over 50 MB/sec  (sequential writes, of course).
> >
> > It turns out that what's going on is that the command queue for the
> > RAID array (3Ware RAID card) is filling up during the checkpoint
> > and is staying at the max (254 commands) for most of the
> > checkpoint.  The odd lucky insert appears to work, but is extremely
> > slow.  In our case, the WAL files are on the same array as the data
> > files, so everything grinds to a halt.
> 
> I spoke with some 3Ware reps at a trade show and they recommended 
> adding the following to /etc/sysctl.conf:
> vm.max-readahead = 256
> vm.min-readahead = 128
> 
> These settings take effect at boot. To change on a running system:
> echo 256 > /proc/sys/vm/max-readahead
> echo 128 > /proc/sys/vm/min-readahead

Note that if you edit /etc/sysctl.conf and want the changes to take 
effect, you can do so with:

sysctl -p




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7

[scott.marlowe]

On Thu, 12 Feb 2004, Jeremy Smith wrote:

> Scott,
> 
> I kept plugging away at it and I now have 7.4.0 installed, and I'm very
> happy about it :)
> 
> There were a couple of mistakes that I made, one was in not looking at the
> order that I was removing the 7.3.3 RPMs.  When I would try to remove the
> wrong one, it would tell me that I couldn't remove it because there were
> certain files that other RPMs were dependant upon.  Once I removed those
> files, and went back to remove the original, it worked.  Once I had 7.3.3
> removed, installing 7.4.0 was pretty easy since I realized that the order of
> my installation of the different files was important.  Sorry if I cluttered
> up the list for the last few days with lots of RPM related problems, but I
> finally have the whole system figured out to the point that I can at least
> get by.

Hey, that's why the lists are here.  FYI, if you have more than one RPM 
to remove and they're all interdependent, you can just list them all at 
once for removal:

rpm -e unixODBC-devel-2.2.5-9 unixODBC-2.2.5-9 unixODBC-kde-2.2.5-9

so that if the devel and kde ones are dependent on the main one, they all 
just go away at once.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7

[scott.marlowe]

On Wed, 11 Feb 2004, Jeremy Smith wrote:

> Thanks Lamar
> 
> I will try that link later, for some reason it's not coming up now.  I tried
> one of the FTPs off of the postgresql.org site, and the folder for 7.4.1 and
> Redhat 7.3 was empty.
> 
> Btw, I have removed my RPM installation of 7.4.0, and my failed attempt at
> installing 7.4.1 and am now left with a working version of 7.3.3.  I am
> almost tempted to just go ahead and use this verion and give up on all the
> installing and uninstalling.  Is there alot I would be missing out on by not
> having 7.4.1?  I plan on leasing a new server in June that would likely have
> Red Hat Enterprise on it, and hopefully it would either have an up to date
> version of postgrese, or no version at all for a clean installation.  I have
> so much work to do on my site that I hate spending valuable time on this..

7.4 is an incremental improvement over 7.3.  If you stick to 7.3, you 
should at least seek out the latest 7.3 version in RPM format and do a 

rpm -Uvh postgresql-7.3.5.rpm

Well, dangit, I just looked, and it appears the latest 7.3 version 
available on the postgresql ftp site(s) is 7.3.4, not 7.3.5.  Anyone have 
a link to a 7.3.5 rpm for rh 7.x?

7.4.x has a lot of improvements.  If you deinstall the 7.3, 7.4.x should 
install just fine.  but, there's another issue.

If the server you'll be migrating to in July will only have 7.3, it is 
MUCH harder to go backwards on data dumps / imports than it is to go 
forwards.  I.e. if you develop on 7.4, you may find it a bit difficult 
to export import from 7.4 to 7.3 (or not, I haven't tried going from 7.4 
to 7.3)  So either make sure your new server can / will have 7.4, or stick 
to 7.3.  I'd recommend upgrading to 7.3.4 for now, and when a set of 7.3.5 
rpms comes out upgrade to them.  These are "in place" upgrades so you can 
do them without a dump / restore problem.





---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] psql not starting up..

[scott.marlowe]

You need to rpm -e 'oldpostgresqlpackagenamehere' to get rid of 7.3 first.

On Wed, 11 Feb 2004, Jeremy Smith wrote:

> To follow up on this, I just did try deleting those files and running the
> RPM process again, and received the same errors.
> 
> rpm -ih postgresql-libs-7.4-0.3PGDG.i386.rpm
> ### [100%]
> file /usr/lib/libpq.so.3 from install of postgresql-libs-7.4-0.3PGDG
> conflicts with file from package postgresql-libs-7.3.3-1PGDG
> .
> .
> .
> 
> Jeremy
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Jeremy Smith
> Sent: Wednesday, February 11, 2004 11:44 AM
> To: [EMAIL PROTECTED]
> Subject: [ADMIN] psql not starting up..
> 
> 
> Hi again,
> 
> I have started working on migrating my DBs from mysql to postgres today and
> ran into a problem.  When I type in psql, I get this error:
> 
> Welcome to psql 7.3.3, the PostgreSQL interactive terminal.
> 
> Type:  \copyright for distribution terms
>\h for help with SQL commands
>\? for help on internal slash commands
>\g or terminate with semicolon to execute query
>\q to quit
> 
> psql: relocation error: psql: undefined symbol: PQgetssl
> 
> It is showing psql as being version 7.3.3 even though I upgraded to 7.4.0
> last night.  But when I installed the RPM for the libraries (among other
> things) I got errors like this:
> 
> file /usr/share/locale/zh_CN/LC_MESSAGES/libpq.mo from install of
> postgresql-libs-7.4-0.3PGDG conflicts with file from package
> postgresql-libs-7.3.3-1PGDG
> 
> should I just go through one by one and delete the files that it is telling
> me I have a conflict with?
> 
> Thanks alot!
> Jeremy
> 
> 
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 
> 
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
> 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] psql not starting up..

[scott.marlowe]

Or maybe rpm -Uvh postgresql-7.4.rpm

would work too.


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] constraints and performance

[scott.marlowe]

On Wed, 11 Feb 2004, Jodi Kanter wrote:

> Do constraints effect performance significantly?

That depends.

Foreign key constraints on int4 columns or int8 columns (matching of 
course) are usually quite fast.

FK constraints on non-int (numeric, text, etc...) tend to be slower.

check constraints can be a killer if they are complex or use functions, 
but simple check constraints are usually alright.

Do you have a specific use case you're looking at?


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] hanging for 30sec when checkpointing

[scott.marlowe]

On Wed, 11 Feb 2004, Peter Galbavy wrote:

> scott.marlowe wrote:
> > Oh, spreading misinformation isn't lying?  You live in a different
> > world than I do.
> 
> Again, I apologise if you took my comment so strongly. I can understand when
> something that someone works on so hard is critisised. OTOH however, your
> original post that I replied to, was very presumptive and over generalised.
> If you say 100% of all IDE drives with write caches from 100% of
> manufacturers are broken, then I simply do not believe you.

Find me one that doesn't lie.  No one I know has found one yet.  And we've 
looked around.  I'd guess though, that with the current state of IDE 
drivers in the two common free unixes / clones, that if fsync was obeyed, 
the throughput on writes would drop quite a bit, since those drivers are 
pretty much one thing at a time oriented.

Your beliefs won't change the fact that no one has shown a single IDE 
drive that doesn't lie.  The fact that no one has shown a drive that 
doesn't lie doesn't prove they all do either.  But until I see one that 
behaves properly, I'll err on the side of caution, and assume they all do.

And the above paragraph reads like part of the SQL 92 spec... :-)

> > Last year, I and several others on the pgsql lists ran a series of
> > tests to determine which drive subsystems could survive power off
> > tests.  We ran the tests by initiating dozens or hundreds of
> > simultaneous transactions against a postgresql machine, then pulling
> > the plug in the middle.
> 
> Thanks for that and the subsequent detail. What is still missing for me is
> the simple question; "What OSes were tested ?" and more specifically was the
> OS driver code compared for the SCSI and IDE subsystems ? It is highly
> possible and probable that underlying OS drivers for IDE and SCSI were
> written by different people and different attention to following standards
> documentation.

I tested linux, someone else tested on BSD.  I do not know if any other 
flavors of Unix were tested.  It's in the archives, so you can search them 
if you want to see.

We looked into it fairly closely, and if you read the comments in the IDE 
code for both the BSD kernel and Linux kernel, you will see comments to 
the fact that IDE drives basically all lie about fsync.  And stuff about 
how to get the manufacturers to make drives that don't.

Basically, the single threaded design of earlier IDE interfaces is still 
pretty much what's implemented today, and the latest IDE interface specs 
seem to allow some kind of command queueing, but no one has a driver to 
take advantage of it for either BSD or linux.  I'm a bit rusty on details, 
it's been about 6 months or so.  Searching for fsync and IDE on the BSD 
and linux kernel mailing lists should bring up some interesting results.  
Who knows, the 2.6 linux kernel or latest BSD kernels may finally be 
addressing these issues.

> Is any of this writtent up anywhere with more details ?It would make very
> interesting reading.

Other than in the archives, I don't think so.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7

[scott.marlowe]

Log in as root, then "su - postgres" and type in "pg_autovacuum --help" 
and see if you get a response from that.  If so, the the autovacuum 
daemon is there, and all you have to do to get it running it its basic 
format is to run it with 'pg_autovacuum -D'.

On Tue, 10 Feb 2004, Jeremy Smith wrote:

> I actually had to install version 7.4.0 as there weren't any RPMs for 7.4.1
> on Redhat 7.3.  I am setting up some tables using phpPGadmin right now, and
> there is a link for "vacuum" so maybe that's it..
> 
> Jeremy
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of scott.marlowe
> Sent: Tuesday, February 10, 2004 7:15 PM
> To: Jeremy Smith
> Cc: [EMAIL PROTECTED]
> Subject: Re: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7
> 
> 
> You're welcome.  Glad it all worked for you.  If it's any consolation,
> that's probably the hardest stuff you'll have to deal with in postgresql.
> do the 7.4.1 rpms include the autovacuum daemon?  If so, get it set up to
> run and you shouldn't have to worry too much about holding postgresql's
> hand, so to speak.
> 
> On Tue, 10 Feb 2004, Jeremy Smith wrote:
> 
> > Thanks so much Scott,
> >
> > I followed this tutorial (linking it in case anyone has a similar troubles
> > in the future) on installing the RPMs:
> > http://www.lyris.com/lm_help/7.8/installing_postgresql_usin.html and it
> > worked out great!
> >
> > Jeremy
> >
> > -Original Message-
> > From: scott.marlowe [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, February 10, 2004 5:50 PM
> > To: Jeremy Smith
> > Subject: RE: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7
> >
> >
> > On Tue, 10 Feb 2004, Jeremy Smith wrote:
> >
> > > Hello,
> > >
> > > thank you for this link.  But I wonder, do I need to do this if postgres
> > has
> > > never actually been used by any user of this server in the past?
> >
> > Oh, then just uninstall the RPMs, rm -Rf the
> > /var/lib/whereeverpostgresqlputsdata directory and install the new rpms
> > and you're gold.
> >
> >
> >
> >
> >
> 
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 
> 
> 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7

[scott.marlowe]

You're welcome.  Glad it all worked for you.  If it's any consolation, 
that's probably the hardest stuff you'll have to deal with in postgresql.  
do the 7.4.1 rpms include the autovacuum daemon?  If so, get it set up to 
run and you shouldn't have to worry too much about holding postgresql's 
hand, so to speak.

On Tue, 10 Feb 2004, Jeremy Smith wrote:

> Thanks so much Scott,
> 
> I followed this tutorial (linking it in case anyone has a similar troubles
> in the future) on installing the RPMs:
> http://www.lyris.com/lm_help/7.8/installing_postgresql_usin.html and it
> worked out great!
> 
> Jeremy
> 
> -Original Message-
> From: scott.marlowe [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 10, 2004 5:50 PM
> To: Jeremy Smith
> Subject: RE: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7
> 
> 
> On Tue, 10 Feb 2004, Jeremy Smith wrote:
> 
> > Hello,
> >
> > thank you for this link.  But I wonder, do I need to do this if postgres
> has
> > never actually been used by any user of this server in the past?
> 
> Oh, then just uninstall the RPMs, rm -Rf the
> /var/lib/whereeverpostgresqlputsdata directory and install the new rpms
> and you're gold.
> 
> 
> 
> 
> 


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] hanging for 30sec when checkpointing

[scott.marlowe]

On Tue, 10 Feb 2004, Peter Galbavy wrote:

> scott.marlowe wrote:
> > I don't know who you think you are, but I've physically tested the
> > stuff I'm talking about.  Care to qualify what you mean?
> 
> I would genuinely be interested in seeing the results and the methodology.
> 
> > IDE drives (all the ones I've ever tested) LIE about their write
> > caches and fsync.  don't believe me?  Simple, hook one up, initiate
> > 100 parallel transactions, pull the power plug, watch your database
> > fail to come back up due to the corruption caused by the LYING IDE
> > drives.
> 
> See my comment/question below.
> 
> > Do the same with SCSI.  watch the database come right back to life.
> >
> > If you're gonna accuse me of lying, you damned well better have the
> > balls AND evidence to back it up.
> 
> I am NOT accussing anyone of lying, least of all people I don't personally
> know, and certainly not you. What I am referring to is over-generalisation.
> You made a long and detailed generalisation, without detailing anything.

Oh, spreading misinformation isn't lying?  You live in a different world 
than I do.

>From www.dictionary.com:

misinformation

\Mis*in`for*ma"tion\, n. Untrue or incorrect information. --Bacon.

Source: Webster's Revised Unabridged Dictionary, © 1996, 1998 MICRA, Inc.

> My primary question, without seeing the way you did it, is can you comment
> on whether you wrote your own testbed or did you rely on potentially flawed
> OS interfaces ? Did you use a signal analyser ?

Last year, I and several others on the pgsql lists ran a series of tests 
to determine which drive subsystems could survive power off tests.  We 
ran the tests by initiating dozens or hundreds of simultaneous 
transactions against a postgresql machine, then pulling the plug in the 
middle.

Due to the nature of postgresql, if a drive reports an fsync before it has 
actually written out its cache, the database will be corrupted and refuse 
to startup when the machine is powered back up.  Signal analyzers are 
nice, but if the database doesn't work, it doesn't really matter what the 
sig an says.  If you'd like to set one up and test that way be my guess, 
but, the "rubber hitting the road" is when you simulate the real thing, 
losing power during transactions.

Here's what we found:

SCSI drives, (at least all the ones we tested, I tested Seagate 18 
gig 10krpm barracudas, many others were tested) as a group, passed the 
test with flying colors.  No one at that time found a single SCSI drive 
that failed it.

IDE drives, with write cache enabled, failed 100% of the time.

IDE drives, with write cache disabled, passed 100% of the time.

SCSI RAID controllers with battery backed cache set to write back passed.

The IDE RAID controller from Escalade passed.  I don't recall if we ever 
found out if it had battery backed cache, or if it disabled the cache on 
the drives.

Performance wise, the IDEs were neck and neck with the SCSI drives when 
they had their write caches enabled.  When the write cache was disabled, 
their performance was about 1/4 to 1/3 as fast as the SCSI drives.

The SCSI RAID card (lsi megaraid is what I tested, someone else tested 
the adaptec) with battery backed cache as well as the escalade were 
great performers.

> Now, I have *not* done the tests - hence my real interest, but I have had at
> least as many problems with SCSI sub-systems as with IDE over the years.
> Probably more actually. Ever since using IBM EIDE drives (the 75GXP
> included, I am a lucky one) I have had very little, knock on wood, to worry
> about even during power failures.

I've built servers with both IDE and SCSI in the past 5 years, and my 
experience has been that while IDE is fine for file / print servers, it's 
a disaster waiting to happen under postgresql.

Keep in mind, we're not talking drive failure rates, or cabling / 
termination issues here, we're talking about the fact that with IDE drives 
(without the escalade controller) you have two choices, fast, or safe.

With SCSI, you get both.  With the RAID controllers mentioned you have 
both.

While my post may have seemed like simple uninformed opinion to you at the 
time you read it, it was, in fact, backed up by weeks of research by both 
myself and a handful of other people on the postgresql mailing lists.  
Your extremely flippant remark could just as easily have been a request 
for more information on how I had reached those conclusions, but no.  It 
had to be an accusation of lying.  And that IS what it was.  No amount of 
hand waving by you can change the fact that you accused me of 
dissemenating misinformation, which is dissemenating untruths, which is 
lying.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] hanging for 30sec when checkpointing

[scott.marlowe]

On Mon, 9 Feb 2004, Tom Lane wrote:

> "scott.marlowe" <[EMAIL PROTECTED]> writes:
> > That said we have a really HUGE (~200 drive) IDE storage array my web / 
> > app server sits on top of.  No clue if that thing will reliably work under 
> > a database, and I'm in no hurry to find out.  
> 
> > But since the fsync on WAL is all that seems important, I could always 
> > initlocation a big chunk of it and keep the WAL local and I should be ok.
> 
> Unfortunately not --- at checkpoint time, the constraint goes the other
> way.  We have to be sure all the data file updates are down to disk
> before we write a checkpoint record to the WAL log.  So you can still
> get screwed if the data-file drive lies about write completion.

Hmmm.  OK.  Would the transaction size be an issue here?  I.e. would small 
transactions likely be safer against corruption than large transactions?

I ask because most of the testing I did was with pgbench running 100+ 
simos (on a -s 100 pgbench database) and as long as the WAL drive was 
fsyncing correctly, the database survived.




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] hanging for 30sec when checkpointing

[scott.marlowe]

On Tue, 10 Feb 2004, Tom Lane wrote:

> "scott.marlowe" <[EMAIL PROTECTED]> writes:
> >> Unfortunately not --- at checkpoint time, the constraint goes the other
> >> way.  We have to be sure all the data file updates are down to disk
> >> before we write a checkpoint record to the WAL log.  So you can still
> >> get screwed if the data-file drive lies about write completion.
> 
> > Hmmm.  OK.  Would the transaction size be an issue here?  I.e. would small 
> > transactions likely be safer against corruption than large transactions?
> 
> Transaction size would make no difference AFAICS.  Reducing the interval
> between checkpoints might make things safer in such a case.
> 
> > I ask because most of the testing I did was with pgbench running 100+ 
> > simos (on a -s 100 pgbench database) and as long as the WAL drive was 
> > fsyncing correctly, the database survived.
> 
> Did you try pulling the plug immediately after a CHECKPOINT command
> completes?  You could test by manually issuing a CHECKPOINT while
> pgbench runs, and yanking power as soon as the prompt comes back.

I will try that.  Thanks for the tip.  I'll let you know how it works 
out.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] Upgrading from 7.2 to 7.4.1 on Redhat 7

[scott.marlowe]

Hi Jeremy.  Updating major versions (i.e. 7.2 to 7.3 or 7.4) requires you 
to dump and restore your database.

This page explains it:

http://www.postgresql.org/docs/7.4/static/install-upgrading.html

If you still need some more help, feel free to ask, we're a pretty 
responsive community.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] hanging for 30sec when checkpointing

[scott.marlowe]

On Mon, 9 Feb 2004, Goulet, Dick wrote:

> Scott,
> 
>   If you feel it is necessary to apologize for such a minor 
> infraction of polite etiquette please come on over to Oracle-L.  We have 
> harshness 10 times greater.  Probably because there are so many 
> practioners and so many different points of view.  We call them "Holy 
> Wars".  The current blazing one is on RAID, the good, the bad, and the 
> ugly. 

Hehe, I grew up on FIDO net, so I know all about the flammage... :-)

I can still remember the amiga versus atari ST holy wars of old.

>   BTW: From a Holy War on Oracle-L of similar topic.  There is a 
> difference on how bad that lying IDE drive is depending on who the 
> vendor is, what system it's plugged into, and what OS is being used.  
> Some do a better job than others of "covering up" the lies.  The other 
> chap may have one of those better systems, so from his point of view 
> it's "old fashioned misinformation".  Doesn't mean it's not true, just 
> covered up better.  Kind of like "Air Freshener".

Well, it's interesting that during all the testing I and many others were 
doing last year, it appeared the escalade IDE RAID controllers were doing 
SOMETHING (no is quite sure if it was disabling write cache or not, but we 
guessed that was so) that made the IDE drives under them safe from the 
power off data loss issue that IDE drives seem to suffer from.

As for the OS, my guess is that some OSes probably just insert some delay 
between receiving an fsync notification from an IDE drive and reporting it 
back to the application or something like that, that makes them appear 
safe.  Such situations often result in systems that only fail under very 
heavy concurrent load, but pass the test under light to medium 
concurrency.

That said we have a really HUGE (~200 drive) IDE storage array my web / 
app server sits on top of.  No clue if that thing will reliably work under 
a database, and I'm in no hurry to find out.  

But since the fsync on WAL is all that seems important, I could always 
initlocation a big chunk of it and keep the WAL local and I should be ok.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] hanging for 30sec when checkpointing

[scott.marlowe]

On Mon, 9 Feb 2004 [EMAIL PROTECTED] wrote:

> >
> > If you're gonna accuse me of lying, you damned well better have the balls
> > AND evidence to back it up.
> >
> 
> Wow.  Scott, all traffic to the admin list has ceased since you posted
> this, we are shocked!
> 
> You put a lot of effort into finding the root of the 'mysteriously good
> IDE performance' issue last year, and did so with great professionalism
> and thoroughness, so it's understandable that this chap's comments would
> upset you.
> 
> If he doesn't have the wit to respond then he's just a troll anyway.

Well, I still feel I should apologize for my harshness on a public list.  
I could have at least taken that one private.  But you are right, I did 
spend a lot of my time chasing down the IDE issues, so to be so flippantly 
accused of spreading misinformation rankled me abit.

So, to anyone on the admin list that was offended, I apologize...


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] hanging for 30sec when checkpointing

[scott.marlowe]

On Sat, 7 Feb 2004, Peter Galbavy wrote:

> scott.marlowe wrote:
> > Also, running on SCSI drives will be much faster than running on IDE
> > drives if the IDE drives have their caches disabled like they should,
> > since they lie otherwise.  Since SCSI disks don't usually lie, and are
> > designed to handle multiple requests in parallel, they are much
> > faster as parallel load increases.  If you're writing a lot, you
> > should either have a great number of IDE drives with the write cache
> > turned off, like some of the newer storage devices made of ~100 IDE
> > drives, or you should have SCSI.  SCSI's advantage won't be as great
> > as the number of drives approaches infinity.  But for 1 to 10 drives
> > my guess is that SCSI is gonna be a clear winner under parallel load.
> 
> Nice to see old fashioned misinformation being spread around the place...

I don't know who you think you are, but I've physically tested the stuff 
I'm talking about.  Care to qualify what you mean?

IDE drives (all the ones I've ever tested) LIE about their write caches 
and fsync.  don't believe me?  Simple, hook one up, initiate 100 parallel 
transactions, pull the power plug, watch your database fail to come back 
up due to the corruption caused by the LYING IDE drives.

Do the same with SCSI.  watch the database come right back to life.

If you're gonna accuse me of lying, you damned well better have the balls 
AND evidence to back it up.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] Windows Installer with Configuration Instructions

[scott.marlowe]

On Wed, 4 Feb 2004, Harley Milne wrote:

> I urgently need to a working windows installer that can successfully install
> and configure a version of postgresql.
> 
> It's been a frustrating experience trying to locate and use anything I have
> found out there.
> 
> I've tried installing a couple installers that I've located, but the server
> never seems to start.
> 
> I'm running Win 2k SP 4
> 
> If somebody has an installer and instructions that work, please can you send
> them to me.
> I need to convert a 2 table Access dB to gresql and and update the tomcat
> app to use JDBC or OBDC to connect to the new dB.

Have you tried going to http://www.cygwin.com/ ?

You can download cygwin, and postgresql is one of the optional packages 
available during install.  Just select it and go.

It's not the fastest but it's pretty reliable, and it works well.  If you 
want a native version, it would be advisable to wait until the windows 
port is done, as most of the windows ports out there are based on older 
versions, and probably have other issues as well, guessing based on all 
the corner cases that the official win32 porters are running into right 
now.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] FATAL 2: (pg_clog ... no such file or directory)

[scott.marlowe]

On Fri, 6 Feb 2004, Tom Lane wrote:

> kaolin fire <[EMAIL PROTECTED]> writes:
> > Where would I go to start tracking down recurring error messages of the 
> > sort:
> 
> > FATAL 2:  open of /usr/local/pgsql/data/pg_clog/06F7 failed: No such 
> > file or directory
> > FATAL 2:  open of /usr/local/pgsql/data/pg_clog/0707 failed: No such 
> > file or directory
> 
> > 06F7 and 0707 do not exist.  Currently just looks like it goes from 
> >  (May 14 2002) to 004F (Feb 6 2004, and counting).
> 
> Given those facts, you have corrupt data --- specifically, a wildly
> out-of-range transaction number in some tuple header, causing the tuple
> validity checker to try to fetch a nonexistent page of the CLOG.  The
> odds are good that the corruption extends further than just the one
> field; that just happens to be the one that gets checked first.
> 
> There are discussions in the mailing list archives about how to locate
> and clean up corrupted data.  It's a pretty messy process but you can
> usually get back everything except the rows on the particular corrupted
> page (I'm optimistically assuming there's only one).  Looking for
> threads mentioning pg_filedump might be the quickest way to find info.

And don't forget, if you've gotten corrupt data, you need to check all 
your hardware thoroughly.  A single bad bit in memory can play more havoc 
with your system than almost anything else, because it will fail just 
often enough to cause problems, but seldom enough to be very hard to find.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] talking to port 5432

[scott.marlowe]

On Fri, 6 Feb 2004, Ernst Roebbers wrote:

> Dear Sirs,
> 
> I would like to communicate with the postgres backend
> using port 5432 from a small remote machine where only 
> a socket is available - no application layer.
> 
> Do You have any hints where I can find some information
> what kind of input the postmaster needs?

There are libs to handle that for you that can be compiled into almost any 
language, so it's kinda like reinventing the wheel to do it by hand.

What language are you gonna use?


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Problem with too short column

[scott.marlowe]

On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote:

> Hi,
> 
> I actually try to insert varchar which length is higher than 32 in a column
> which type is varchar(32). I can't change the type of the column, and I
> want to trunc the data. I know it's possible ! I have seen this !
> 
> Example : "j'aime les saucisses avec du beurre dedans" will be "j'aime les
> saucisses avec du beu". For the moment it produces a SQL Error !
> 
> Is it an option in the config files ?

Note that in my previous posting I think I used substring with a 0 start, 
it should start at 1:

select substring('abcdefgh',1,4);
SELECT
substring

abcd


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] Problem with too short column

[scott.marlowe]

On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote:

> Hi,
> 
> I actually try to insert varchar which length is higher than 32 in a column
> which type is varchar(32). I can't change the type of the column, and I
> want to trunc the data. I know it's possible ! I have seen this !
> 
> Example : "j'aime les saucisses avec du beurre dedans" will be "j'aime les
> saucisses avec du beu". For the moment it produces a SQL Error !
> 
> Is it an option in the config files ?

No, it's your job to truncate it.  You could use substring in your insert 
statement like this:

insert into tablename (fieldname) values 
(substring('inputdatathatstoolonghere',0 for x);

where x is the length you want it chopped off at.

Further, you could create an insert (i.e. before) trigger to do the same 
thing.

Note that the SQL spec is very clear, truncating automatically is an 
error, and if you want things truncated, it's not the databases job to do 
it by any kind of "default" setting.  Luckily, it's pretty easy to do it 
either "by hand" in SQL with substring, or by trigger.

> I'm using Postgresql 7.2.2.

Upgrade.  There are bugs in the early 7.2 series you don't want to have to 
deal with.  I'd recommend going to 7.4.1 if you can, but if not, at least 
upgrade to the latest version of 7.2.  As long as you compile it the same 
as your earlier 7.2 install, it's an in place (i.e. no dump restore 
required) upgrade.



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] slow seqscan after vacuum analize

[scott.marlowe]

On Wed, 4 Feb 2004, Edoardo Ceccarelli wrote:

> I have a simple query that scans each record, like this: select * from utente where
> luogorilasciodoc='ciao'
> The execution time BEFORE vacuum is: 1203ms The execution time AFTER vacuum is: 
> 6656ms
> !!!
> What is going on? Thought that one vaccum the db to get better performance!
> 
> PostgreSQL Ver. 7.3.4 on linux

Can you show us the output of "explain analyze select * from ..." for both 
before and after a vacuum?   i.e. with the difference in performance.  If 
not, just an explain analyze of the way it runs now might be enough.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] hanging for 30sec when checkpointing

[scott.marlowe]

On Wed, 4 Feb 2004, Iain wrote:

> If I understand checkpoints correctly, data that is already written to the
> WAL (and flushed to disk) is being written to the DB (flushing to disk).
> Meanwhile, other writer transactions are continuing to busily write to the
> WAL. In which case a disk bandwidth problem (other than kernal config
> issues) may be helped by placing the WAL files on a disk (and maybe even
> controller) seperate from the DB.

Also, running on SCSI drives will be much faster than running on IDE 
drives if the IDE drives have their caches disabled like they should, 
since they lie otherwise.  Since SCSI disks don't usually lie, and are 
designed to handle multiple requests in parallel, they are much faster as 
parallel load increases.  If you're writing a lot, you should either have 
a great number of IDE drives with the write cache turned off, like some of 
the newer storage devices made of ~100 IDE drives, or you should have 
SCSI.  SCSI's advantage won't be as great as the number of drives 
approaches infinity.  But for 1 to 10 drives my guess is that SCSI is 
gonna be a clear winner under parallel load.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] Postgresql 7.3 And Redhat Enterprise 3

[scott.marlowe]

Is the file postgres IN the directory /etc/init.d?  If so, is it set 
executable???

On 2 Feb 2004, Remi wrote:

> Hello, 
> 
> I've installed redhat enterprise 3 on a system to use as a db server.
> Manually I can install and run postgresql 7.3 (the one on the extras
> CD). The problem is when you try to add postgres as a service, it
> might add it, but it will not start it. To do this, we got a
> postgresql script, placed it in the init.d folder with all the other
> service shell scripts, and then went into services, add service, and
> typed postgres. The specific error received is:
> 
> env: /etc/init.d/postgres no directory or file found
> 
> Is there some environment variable that needs to be set in some file
> somewhere. I read a lot of documentation online, and modified whatever
> files that people mentioned, but maybe I'm missing something?
> 
> Just to clarify, the server pgsql components are installed. I've done
> several fresh installs with all kinds of options either selected or
> de-selected. For example, I've installed all client, and server
> components at one point.
> 
> What baffles me as well is that on Redhat 9.0 it sets up postgres for
> you on an install, and automatically adds, and configures it under
> services so that it auto starts postgres on a server reboot.
> 
> Any help is appreciated. Thanks.
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html
> 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] Forcing connections closed

[scott.marlowe]

On Thu, 29 Jan 2004, Juan Miguel wrote:

> You can kill the procs (connections) using kill -9 PID

Just use kill PID first!

'kill -9' PID will force all backends to flush cache for no good reason.

Note that on most unixes, a plain kill PID will send the term signal, 
which tells the process to poltely shutdown and release resources.  kill 
-9 is like using a sledge hammer to swat a fly for this.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] popstgresql query

[scott.marlowe]

On Mon, 19 Jan 2004, Arun Gananathan wrote:

> Hi,
> Could anyone please let me know how to perform the following in 
> postgresql
> I have two tables called P , Q  with the same attributes and data 
> tpyes. I want to insert a record ( for example called A) into table P, 
> when deleting the same record( for example  called A which is already in 
> the other table)  from the other table Q.

Pretty straight ahead.  You can either use a fancy trigger setup, or just 
do it in a transaction to ensure integrity:

begin;
insert into P (select * from Q where recid='A';
delete from Q where recid='A';
commit;

If the insert fails, the delete won't happen either.

That close to what you want?


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] Trouble connecting

[scott.marlowe]

You likely need this line in postgresql.conf:

tcpip_socket = true

On Thu, 15 Jan 2004, Norbert Beckers wrote:

> I'm a novice when it comes to PostgreSQL and PostgreSql Admin. I having 
> truoble connecting to PostgreSQl Server running on the localhost. Every 
> time I try to connect I get the following response:
> 
> ¨Error connecting to the server: could not connect to server:
> Connection refused
> is server running on 127.0.0.1 and accepting TCP/IP connections on 5432?
> ¨
> The connection information:
> Server = localhost
> Port = 5432 SSL = 
> Initial database: template1
> Username = postgres
> Trusted = off
> Password = 
> 
> Futher Information:
> PostgreSQL version  = 7.3.4-11
> PostgreSQl Admin = 3.1.1.0-cvs200312111
> OS = Fedora Core 1 (2.4.22-1.2149)  Arch = athlon
> 
> Can anyone help me out here?
> 
> Norbert Beckers, Heerlen, NL
> 
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
> 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] check for null value

[scott.marlowe]

On 16 Jan 2004, Ashok Chauhan wrote:

> 
> hello
> 
> i am selecting ten fields form a table and i want to replace the null
> values with any other value.

Look up coalesce(), it should do exactly what you need.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] pg_dump vs pg_dumpall - small database cluster,

[scott.marlowe]

On Sat, 20 Dec 2003, C. Bensend wrote:

> 
> >>   pg_dumpall seems to do this for me, without any command-line args
> >> needed.  Am I correct in saying that 'pg_dumpall > filename' will
> >> produce
> >> a PostgreSQL dump that includes _everything_ I need to go from a clean
> >> PG install to accepting connections again with data intact?  Users,
> >> passwords, etc?
> >>
> >
> > and the owner of pg_dumpall process must be a database superuser.
> 
> Absolutely...  I'm running this directly on the database server, as the
> PostgreSQL superuser, so permissions aren't an issue.  I just want to be
> very sure that pg_dumpall is getting absolutely everything I need,
> because I don't have the resources at this site to do full filesystem
> backups of this host.

Note that in the past, occasional dependency issues have cause pg_dumpall 
to fail for certain users.  I myself had it happen with a customer 
sequence a little while ago on 7.2.4.  Unfortunately, I'm unable to 
reproduce the sequence of events that caused it, so I can't really report 
it as a bug.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Help needed Please !!!!!incremental backups possible in

[scott.marlowe]

On Fri, 19 Dec 2003, Priya G wrote:

> Hi all,
>  
> Is Incremental backups possible in Postgres 7.3 or 7.4

Nope.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Postgresql on software RAID

[scott.marlowe]

On Wed, 17 Dec 2003, Adam Witney wrote:

> On 17/12/03 3:45 pm, "scott.marlowe" <[EMAIL PROTECTED]> wrote:
> 
> > On Tue, 16 Dec 2003, Robert Creager wrote:
> > 
> >> 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...
> > 
> > Just wondering, was that on hardware or software RAID5, and if hardware
> > did it have battery backed cache controllers?  Makes a huge difference.  I
> > would never use SW RAID5 for heavily written databases.
> 
> Hi Scott,
> 
> What level of activity would you call "heavily written"?

More than a couple inserts a second.  Or more specifically, depending on 
the machine, when the CPU usage starts to climb, which really can vary a 
lot from machine to machine.  A machine with 4 2.8GHz CPUs could probably 
handle a much higher write load than a single <1GHz machine.

Things like a data warehouse where you feed in streams slowly or at night 
and then do huge selects work well on RAID5 sw.  Transactional systems, 
like billing or reservation systems should probably not be on sw RAID.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Postgresql on software RAID

[scott.marlowe]

On Tue, 16 Dec 2003, Robert Creager wrote:

> 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...

Just wondering, was that on hardware or software RAID5, and if hardware 
did it have battery backed cache controllers?  Makes a huge difference.  I 
would never use SW RAID5 for heavily written databases.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] Postgresql on software RAID

[scott.marlowe]

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] Postgresql on software RAID

[scott.marlowe]

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])

Re: [ADMIN] ODBC Driver generates a too big "windows swap file" and

[scott.marlowe]

On Fri, 12 Dec 2003, Rhaoni Chiu Pereira wrote:


Hi, is there a switch in your pgsql/odbc connector to enable cursors?  If 
so, try turning that on.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Reindex database

[scott.marlowe]

On Fri, 12 Dec 2003, Greg Spiegelberg wrote:

> Any rule of thumb for REINDEX DATABASE?  Once per month?
> Per x transactions?

You shouldn't need to reindex databases for the most part.  If there's 
lots of catalog updates / delete, it might be occasionally useful to down 
the database and reindex those catalogs in single user mode since you 
can't reindex them on a live database if I remember correctly.

If you are running 7.3.5 or before, you might need to schedule daily /
weekly / monthly etc reindexes of indexes that show unreasonable growth 
problems.  This should be mostly fixed for 7.4, and reindexes should not 
really be needed anymore, we hope :-).

generally speaking, reindex is for fixing broken indexes, not generaly 
maintenance.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] easy way to copy all DBs and users on a server

[scott.marlowe]

On Wed, 12 Nov 2003, exciteworks hosting wrote:

> Is there an easy way to copy all DBs and users on a server to another 
> server?
> 
> I need to get an exact duplicate.

pg_dump -h source_server|psql -h dest_server



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Taking database offline

[scott.marlowe]

On Wed, 12 Nov 2003, ow wrote:

> Hi,
> 
> Is there a way to take the database offline to keep users out?
> Is there a way to take the database offline but still allowing pg_restore to
> run against it?

Yes, as of 7.3 you can create entries in pg_hba.conf that control who can 
connect to what database.  Just make a backup of the original pg_hba.conf, 
and set up your copy to have only the postgres (or other) superuser be 
allowed to connect.  Then, swap the two hba files and restart the server.




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] Upgrading to Solaris 9

[scott.marlowe]

On Tue, 11 Nov 2003, Gaetano Mendola wrote:

> Danielle Cossette wrote:
> 
> > Good morning,
> > 
> > Could you please let me know if Postgres 7.1.3 will run on Solaris 9.
> > If it does, are you aware of any issues.
> 
> Seems that solaris is the worst choice for run Postgres.
> 
> Am I completely wrong ?

I thought .org and .info were being run on postgresql/solaris?

As another poster pointed out, the Solaris version was building with no 
optimzation, and it was kinda figured to be in the sysadmin's realm to fix 
that at build time.

You're likely using Solaris' qsort, which was slow in the olden days but I 
thought it had been sped up recently.  If it's been fixed, no worries, as 
I'm pretty sure Solaris 9 has a qsort that sorts fast with many identical 
keys.

Either way, upgrading to 7.3.4 or 7.4RC2 (for testing, in a couple weeks 
release...) should provide a very noticable speed increase over 7.1 due 
to many improvements made since that version.

The upgrade to 7.4 might be easier than the upgrade to 7.3 followed by 
7.4, as 7.4's pg_dump and pd_dumpall are version aware and can dump a live 
7.1.3 database.  So, if you wanna try it, just build a test server and:

pgsql7.4rc2: pg_dumpall -h production_machine_running_7.1.3 |psql -h 
testbox_running_7.4.rc2

and see what happens.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] performance problem - 10.000 databases

[scott.marlowe]

On Thu, 6 Nov 2003, William Yu wrote:

> scott.marlowe wrote:
> > Note that if you're on an IDE drive and you haven't disabled the write 
> > cache, you may as well turn off fsync as well, as it's just getting in the 
> > way and doing nothing, i.e. the IDE drives are already lying about fsync 
> > so why bother.
> 
> What about Serial ATA?

I haven't gotten my hands on one yet to test.  We might be getting some in 
in the next few months where I work and I'll test them and report back 
here then.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] performance problem - 10.000 databases

[scott.marlowe]

On 6 Nov 2003, Marek Florianczyk wrote:

> 
> ... And my management says, that there is no good support for Open
> Source, heh... ;)))

That's because your "support" needs are different.  A developer wants 
answers and solutions, a manager often wants someone to blame. :-)


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] performance problem - 10.000 databases

[scott.marlowe]

On Thu, 6 Nov 2003, Jeff wrote:

> On 06 Nov 2003 15:21:03 +0100
> Marek Florianczyk <[EMAIL PROTECTED]> wrote:
> 
> 
> > fsync = false   
> 
> HOLD THE BOAT THERE BATMAN!
> 
> I would *STRONGLY* advise not running with fsync=false in production as
> PG _CANNOT_ guaruntee data consistancy in the event of a hardware
> failure.  It would sure suck to have a power failure screw up your nice
> db for the users!

Note that if you're on an IDE drive and you haven't disabled the write 
cache, you may as well turn off fsync as well, as it's just getting in the 
way and doing nothing, i.e. the IDE drives are already lying about fsync 
so why bother.

Step the first, get on SCSI / or a good IDE RAID controller, then step the 
second, turn fsync back on.  Without reliable storage, fsync is a dunsel.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] performance problem - 10.000 databases

[scott.marlowe]

On 5 Nov 2003, Marek Florianczyk wrote:

> W li¶cie z ¶ro, 05-11-2003, godz. 19:52, Tom Lane pisze: 
> > Marek Florianczyk <[EMAIL PROTECTED]> writes:
> > > Maybe reconnect is to often, but how to explain that reular queries like
> > > select * from table1 ale much faster than \d's ? ( my post to Jeff )
> > 
> > [ further experimentation... ]  Ah-hah, I see the problem in 7.3, though
> > not in 7.4 which is what I was testing to begin with.  7.4 is smarter
> > about optimizing the LEFT JOINs that are used in \d's queries.
> > 
> 
> So how do you think
> sould I try v7.4 on production machine and wait for stable version? 
> Or better use v7.3 with 3000 databases?
> I have to do that till december.
> Will this optimization solve problem "\d" queries, or just speed it up
> few seconds ?

7.4 just went RC1 and is looking good for release in <2 weeks.  I'd test 
it to see if it works for you.  I've found the .0 releases in postgresql 
to be good enough for production, even if they do have one or two minor 
bugs that pop up.  Take frequent backups (you should anyway) and you'll 
probably be alright. 

Heck, you're already pushing the performance envelope with 3,000 users, 
might as well go for the faster of the two and you'll have one less 
scheduled upgrade ahead of you.

When do you need to go live?  If it's >1 month, then I'd definitely 
recommend 7.4.


---(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] performance problem - 10.000 databases

[scott.marlowe]

On 31 Oct 2003, Marek Florianczyk wrote:

> Hi all
> 
> We are building hosting with apache + php ( our own mod_virtual module )
> with about 10.000 wirtul domains + PostgreSQL.
> PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM
> scsi raid 1+0 )

Tom's right, you need more memory, period, and probably want a very large 
RAID1+0 (with like 10 or more disks).


> Has any one idea how to tune postgres, to accept connection faster?

Postgresql will take the amount of time it needs.  Connections, especially 
in a contentious environment, aren't cheap.

> Maybe some others settings to speed up server ?
> My settings:
> PostgreSQL:
> max_connections = 512
> shared_buffers = 8192
> max_fsm_relations = 1   
> max_fsm_pages = 10  
> max_locks_per_transaction = 512
> wal_buffers = 32
> sort_mem = 327681  
-^^-- THIS IS WAY TOO HIGH. That's ~320Meg!  PER SORT.  
Drop this down to something reasonable like 8192 or something. (i.e. 8 
meg)  If there were lots of big sorts going on by all 300 users, then 
that's 300*320 Meg memory that could get used up.  I.e. swap storm.

Have you adjusted random_page_cost to reflect your I/O setup?  While the 
default of 4 is a good number for a single drive server, it's kinda high 
for a machine with 4 or more drives in an array.  Figures from 1.2 to 2.0 
seem common.  My database under 7.2.4 run best with about 1.4 
random_page_cost


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] PostgreSQL Metadata

[scott.marlowe]

On Thu, 30 Oct 2003, David Wagoner wrote:

> In Oracle, you can "select * from dictionary" to see the data dictionary
> table names and descriptions.  Is there something similar in PostgreSQL?

Old way (still supported, not going away):

\d from a psql session

New way:  select * from information_schema.[table]

where table is one of the following:

role_table_grants
applicable_roles 
role_usage_grants
check_constraints
routine_privileges
column_domain_usage  
routines
column_privileges
schemata
columns  
sql_features
column_udt_usage 
sql_implementation_info
constraint_column_usage  
sql_languages
constraint_table_usage   
sql_packages
data_type_privileges 
sql_sizing
domain_constraints   
sql_sizing_profiles
domains  
table_constraints
domain_udt_usage 
table_privileges
element_types
tables
enabled_roles
triggered_update_columns
information_schema_catalog_name  
triggers
key_column_usage 
usage_privileges
parameters   
view_column_usage
referential_constraints  
views
role_column_grants   
view_table_usage
role_routine_grants

This is new for 7.4 and implements the SQL specced information_schema.

> 
> Also, in Oracle you can store comments on tables and columns which provides
> valuable metadata.  Is this also possible in PostgreSQL?  Here is an example
> create table statement in Oracle with comments:
> 
>   CREATE TABLE lock_date
>(
> lock_idNUMBER(9) NOT NULL,
> )
> TABLESPACE data
>   /
>   -- Comments for LOCK_DATE
>   COMMENT ON TABLE lock_date IS 'Locks out data that arrives after
> Billing and Reporting periods.'
>   /
>   -- Column Comments for LOCK_DATE
>   COMMENT ON COLUMN lock_date.lock_id IS 'Surrogate Primary Key for
> the LOCK_DATE table.'
>   /
> 
> 
> Best regards,
> 
> David B. Wagoner
> Database Administrator
> Arsenal Digital Solutions
> Web: http://www.arsenaldigital.com
> 
>  <<...OLE_Obj...>> 
> 
> 
> The contents of this e-mail message may be privileged and/or confidential.
> If you are not the intended recipient, any review, dissemination, copying,
> distribution or other use of the contents of this message or any attachment
> by you is strictly prohibited. If you receive this communication in error,
> please notify us immediately by return e-mail or by telephone
> (919-466-6700), and please delete this message and all attachments from your
> system. 
> Thank you.
> 
> 


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] Authentication Question

[scott.marlowe]

And you've restarted the server and all, right?

Just for troubleshooting edit the 127.0.0.1 line for trust and restart and 
see if the problem goes away.  I wonder if php changed behaviour on unix 
sockets versus TCP/IP somewhere along the way.  We only use explicit host 
namd / TCP/IP nowadays where I work...

On Tue, 28 Oct 2003, Epps, Aaron M. wrote:

>   Here's the PHP code that's being used to create the connection string.  It 
> looks like it isn't inserting anything if the host name isn't specified...
> 
>  // Build the connection string
> $conn_str  = "user=".$this->user;
> $conn_str .= !empty($this->pass) ? " password='".$this->pass."'" : "";
> $conn_str .= !empty($this->host) ? " host=".$this->host : "";
> $conn_str .= !empty($this->port) ? " port=".$this->port : "";
> $conn_str .= " dbname=".$this->name;
> 
> $this->link = pg_connect($conn_str)
>   or $this->logger("Database connection failed!","DBI");
> break;
>   default:
> $this->logger("Unknown database type in init()","DBI");
> break;
> 
> -Original Message-
> From: scott.marlowe [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 28, 2003 2:58 PM
> To: Epps, Aaron M.
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: [ADMIN] Authentication Question 
> 
> I bet your connect class is distilling your $db array down into a connect string 
> that looks like:
> 
> pg_connect("host= dbname=db user=username");
> 
> And the presence of a host= in there is goofing things up.  Can you edit the class 
> to make sure it isn't inserting a host= part before connecting?
> 
> On Tue, 28 Oct 2003, Epps, Aaron M. wrote:
> 
> > There is no hostname specified in the PHP code when it connects to 
> > PostgreSQL, so it's using a socket connections then correct?  Also, I 
> > did SIGHUP the PostgreSQL server after the configuration changes.  
> > Here's what the PHP config file looks like that's used to connect to 
> > PostgreSQL
> > 
> > $db = array(
> > "type"  =>  "pgsql",
> > "host"  =>  "",
> > "port"  =>  "",
> > "name"  =>  "issue-tracker",
> > "user"  =>  "webuser",
> > "pass"  =>  "password"
> > );
> > 
> > 
> > I wonder if  it's trying to authenticate because there's a username and 
> > password specified in the pg_connect() function.  However, when I remove the 
> > username and password I get the following error...
> > 
> > Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT 
> > authentication failed for user "dbname=issue-tracker" . in 
> > /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> > 
> > Thoughts?
> > 
> > -Original Message-
> > From: scott.marlowe [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, October 28, 2003 2:37 PM
> > To: Tom Lane
> > Cc: Epps, Aaron M.; '[EMAIL PROTECTED]'
> > Subject: Re: [ADMIN] Authentication Question
> > 
> > On Tue, 28 Oct 2003, Tom Lane wrote:
> > 
> > > "Epps, Aaron M." <[EMAIL PROTECTED]> writes:
> > > > I've got an authentication questions.  I've just setup pg_hba.conf to use 
> > > > "localallalltrust" for testing purposes, but when the application 
> > > > I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the 
> > > > following error.
> > >  
> > > > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT 
> > > > authentication failed for user "webuser" . in 
> > > > /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> > >  
> > > > To me, it looks like PostgreSQL is still trying to authenticate, 
> > > > even
> > > though it should be trusting all local socket connections.
> > > 
> > > (1) Did you SIGHUP the postmaster after editing the config file?
> > > ("pg_ctl reload" is the easy way to do this.)  If not, it's still 
> > > using whatever setup you had before.
> > > 
> > > (2) It's fairly likely that PHP will try to connect via TCP/IP even 
> > > for a local server.  If so, the relevant pg_hba.conf line will be 
> > > the one for 127.0.0.1, not the "local" line.  "local" is for 
> > > Unix-socket connections.
> > 
> > This is correct if you specify a host name:
> > 
> > pg_connect("host=local dbname=db")  <- TCP/IP
> > pg_connect("dbname=db") <- local unix sockets (i.e. no host=)
> > 
> > ---(end of 
> > broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to 
> > [EMAIL PROTECTED]
> > 
> > 
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Authentication Question

[scott.marlowe]

I bet your connect class is distilling your $db array down into a connect 
string that looks like:

pg_connect("host= dbname=db user=username");

And the presence of a host= in there is goofing things up.  Can you edit 
the class to make sure it isn't inserting a host= part before connecting?

On Tue, 28 Oct 2003, Epps, Aaron M. wrote:

>   There is no hostname specified in the PHP code when it connects to PostgreSQL, 
> so it's using a socket connections then correct?  Also, I did SIGHUP the PostgreSQL 
> server after the configuration changes.  Here's what the PHP config file looks like 
> that's used to connect to PostgreSQL
> 
> $db = array(
>   "type"  =>  "pgsql",
>   "host"  =>  "",
>   "port"  =>  "",
>   "name"  =>  "issue-tracker",
>   "user"  =>  "webuser",
>   "pass"  =>  "password"
> );
> 
> 
>   I wonder if  it's trying to authenticate because there's a username and 
> password specified in the pg_connect() function.  However, when I remove the 
> username and password I get the following error...
> 
> Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT 
> authentication failed for user "dbname=issue-tracker" . in 
> /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> 
> Thoughts?
> 
> -Original Message-
> From: scott.marlowe [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 28, 2003 2:37 PM
> To: Tom Lane
> Cc: Epps, Aaron M.; '[EMAIL PROTECTED]'
> Subject: Re: [ADMIN] Authentication Question 
> 
> On Tue, 28 Oct 2003, Tom Lane wrote:
> 
> > "Epps, Aaron M." <[EMAIL PROTECTED]> writes:
> > > I've got an authentication questions.  I've just setup pg_hba.conf to use 
> > > "localallalltrust" for testing purposes, but when the application 
> > > I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the following 
> > > error.
> >  
> > > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT 
> > > authentication failed for user "webuser" . in 
> > > /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> >  
> > > To me, it looks like PostgreSQL is still trying to authenticate, 
> > > even
> > though it should be trusting all local socket connections.
> > 
> > (1) Did you SIGHUP the postmaster after editing the config file?
> > ("pg_ctl reload" is the easy way to do this.)  If not, it's still 
> > using whatever setup you had before.
> > 
> > (2) It's fairly likely that PHP will try to connect via TCP/IP even 
> > for a local server.  If so, the relevant pg_hba.conf line will be the 
> > one for 127.0.0.1, not the "local" line.  "local" is for Unix-socket 
> > connections.
> 
> This is correct if you specify a host name:
> 
> pg_connect("host=local dbname=db")  <- TCP/IP
> pg_connect("dbname=db") <- local unix sockets (i.e. no host=)
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 
> 


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] Authentication Question

[scott.marlowe]

On Tue, 28 Oct 2003, Tom Lane wrote:

> "Epps, Aaron M." <[EMAIL PROTECTED]> writes:
> > I've got an authentication questions.  I've just setup pg_hba.conf to use 
> > "localallalltrust" for testing purposes, but when the application I'm 
> > using tries to connect to the PostgreSQL Db (Using PHP) I get the following error.
>  
> > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT 
> > authentication failed for user "webuser" . in 
> > /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
>  
> > To me, it looks like PostgreSQL is still trying to authenticate, even
> though it should be trusting all local socket connections.
> 
> (1) Did you SIGHUP the postmaster after editing the config file?
> ("pg_ctl reload" is the easy way to do this.)  If not, it's still using
> whatever setup you had before.
> 
> (2) It's fairly likely that PHP will try to connect via TCP/IP even for
> a local server.  If so, the relevant pg_hba.conf line will be the one
> for 127.0.0.1, not the "local" line.  "local" is for Unix-socket
> connections.

This is correct if you specify a host name:

pg_connect("host=local dbname=db")  <- TCP/IP
pg_connect("dbname=db") <- local unix sockets (i.e. no host=)


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] Authentication Question

[scott.marlowe]

On Tue, 28 Oct 2003, Epps, Aaron M. wrote:

> I've got an authentication questions.  I've just setup pg_hba.conf 
> to use "localallalltrust" for testing purposes, but when the 
> application I'm using tries to connect to the PostgreSQL Db (Using PHP) 
> I get the following error.
>  
> > " Warning: pg_connect(): Unable to connect to PostgreSQL server: 
> FATAL: IDENT authentication failed for user "webuser" . in 
> /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
>  
> To me, it looks like PostgreSQL is still trying to authenticate, even 
> though it should be trusting all local socket connections.  BTW, the web 
> server and PostgreSQL server are on the same machine, so it's a local 
> connection, not host.  Do I need to modify anything in pg_ident.conf?  
> I'd appreciate any help you can offer.  Thx.

Two possibilities.

1:  You have another line that says something like:

127.0.0.1 all all ident

and you're connecting like so:

pg_connect("host=hostname dbname=...");

OR

2:  You need to restart your server:

pg_ctl restart

OR

pg_ctl reload

There are a handful of options, like shared buffers, that can only be 
changed by restart, not reload.  I'm pretty sure pg_hba.conf can be reset 
by either restart or reload.


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] device full ---> postgres will not start

[scott.marlowe]

A quick note, anytime you're gonna upgrade, you need to first shut down 
the postmaster...  

On Mon, 27 Oct 2003, scott.marlowe wrote:

> Assuming you'll be upgrading from 7.2 to 7.2.4, then yes, all you need to 
> do is upgrage the rpms.  you can do tham all at once, assuming they're in 
> the same directory and named postgresql-something or other:
> 
> rpm -Uvh postgresql*
> 
> should do it.
> 
> If you want to upgrade to 7.3.4, then you'll need to do a pg_dumpall first 
> and save the output of that file like so:
> 
> pg_dumpall >mydata.sql
> 
> so you can restore it.  Then, after installing 7.3.4, you'll need to set 
> up a data directory and restore there.  If you've got the space, do that 
> somewhere other than the old 7.2.x data directory, as you may have some 
> issues and need to go back to 7.2.x and tweak the backup procedure etc...
> 
> On 27 Oct 2003, kbd wrote:
> 
> > i will take the advice and upgrade to 7.2.4 ASAP as in now.
> > 
> > this what rpm reports on the server currently:
> > [EMAIL PROTECTED] root]# rpm -qa | grep postgres
> > postgresql-jdbc-7.2.1-5
> > postgresql-libs-7.2.1-5
> > postgresql-7.2.1-5
> > postgresql-server-7.2.1-5
> > postgresql-contrib-7.2.1-5
> > postgresql-devel-7.2.1-5
> > [EMAIL PROTECTED] root]#
> > 
> > i downloaded the following:
> > [EMAIL PROTECTED] download]# ls
> > postgresql-7.2.4-1PGDG.i386.rpm  postgresql-libs-7.2.4-1PGDG.i386.rpm
> > postgresql-contrib-7.2.4-1PGDG.i386.rpm  postgresql-perl-7.2.4-1PGDG.i386.rpm
> > postgresql-devel-7.2.4-1PGDG.i386.rpmpostgresql-server-7.2.4-1PGDG.i386.rpm
> > postgresql-docs-7.2.4-1PGDG.i386.rpm postgresql-tcl-7.2.4-1PGDG.i386.rpm
> > postgresql-jdbc-7.2.4-1PGDG.i386.rpm
> > [EMAIL PROTECTED] download]#
> > 
> > 
> > i have done new installs using RPMs, but not an upgrade.
> > silly question time:  
> > are these the correct RPMs?
> > do I just upgrade them one at a time as follows:
> >   rpm -Uvh  postgresql...rpm
> > 
> > 
> > many thanks 
> > 
> > kd
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > [EMAIL PROTECTED] ("scott.marlowe") wrote in message news:<[EMAIL PROTECTED]>...
> > > On Fri, 24 Oct 2003, Duffy House wrote:
> > > 
> > > > Hello:
> > > > 
> > > > It looks like I got caught sleeping.
> > > > 
> > > > I am running Postgresql 7.2 under Red Hat 7.3.
> > > > Postgresql failed today.  When I tried to restart it, I got a message
> > > > stating that the device is full.
> > > > I used df to confirm that /var where postgres reside is full.  /var is on a
> > > > 4 Gig device.
> > > > Postgresql is the only application on this machine.
> > > > 
> > > > Please note, the backup of the database from the prior day, is 45 Meg
> > > > zipped.
> > > > I do not know how the database could be soaking up a 4 Gig device.
> > > > 
> > > > Questions:
> > > > 
> > > > How can I recover from this situation?
> > > > 
> > > > If postgres will not start how can I restore the database from the prior
> > > > day?
> > > > 
> > > > When I run a backup does postgres truncate its logs?
> > > > 
> > > > please keep it simple, I have about 1 hours admin experience with postgres.
> > > > 
> > > > thanks in advance.
> > > 
> > > It sounds like either you've never run vacuum, or you've had a few threads 
> > > holding transactions open for a very long time.
> > > 
> > > Easiest way to recover:  Make a bigger partition, cp everything there, and 
> > > point the postmaster at that partition to startup.  Then vacuum etc... and 
> > > copy it all back over the original partition.  Then make sure you're 
> > > running the latest and greatest (at least 7.2.4, prefereable 7.3.4) and 
> > > look up the autovacuum daemon to make sure this never happens again.  If 
> > > you installed from source, it's in the contrib/pg_autovacuum directory, 
> > > just cd in there as root, and 'make;make install' and it's installed.
> > > 
> > > 
> > > 
> > > 
> > > ---(end of broadcast)---
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > >   subscribe-nomail command to [EMAIL PROTECTED] so that your
> > >   message can get through to the mailing list cleanly
> > 
> > ---(end of broadcast)---
> > TIP 7: don't forget to increase your free space map settings
> > 
> > 
> 
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 
> 


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] device full ---> postgres will not start

[scott.marlowe]

Assuming you'll be upgrading from 7.2 to 7.2.4, then yes, all you need to 
do is upgrage the rpms.  you can do tham all at once, assuming they're in 
the same directory and named postgresql-something or other:

rpm -Uvh postgresql*

should do it.

If you want to upgrade to 7.3.4, then you'll need to do a pg_dumpall first 
and save the output of that file like so:

pg_dumpall >mydata.sql

so you can restore it.  Then, after installing 7.3.4, you'll need to set 
up a data directory and restore there.  If you've got the space, do that 
somewhere other than the old 7.2.x data directory, as you may have some 
issues and need to go back to 7.2.x and tweak the backup procedure etc...

On 27 Oct 2003, kbd wrote:

> i will take the advice and upgrade to 7.2.4 ASAP as in now.
> 
> this what rpm reports on the server currently:
> [EMAIL PROTECTED] root]# rpm -qa | grep postgres
> postgresql-jdbc-7.2.1-5
> postgresql-libs-7.2.1-5
> postgresql-7.2.1-5
> postgresql-server-7.2.1-5
> postgresql-contrib-7.2.1-5
> postgresql-devel-7.2.1-5
> [EMAIL PROTECTED] root]#
> 
> i downloaded the following:
> [EMAIL PROTECTED] download]# ls
> postgresql-7.2.4-1PGDG.i386.rpm  postgresql-libs-7.2.4-1PGDG.i386.rpm
> postgresql-contrib-7.2.4-1PGDG.i386.rpm  postgresql-perl-7.2.4-1PGDG.i386.rpm
> postgresql-devel-7.2.4-1PGDG.i386.rpmpostgresql-server-7.2.4-1PGDG.i386.rpm
> postgresql-docs-7.2.4-1PGDG.i386.rpm postgresql-tcl-7.2.4-1PGDG.i386.rpm
> postgresql-jdbc-7.2.4-1PGDG.i386.rpm
> [EMAIL PROTECTED] download]#
> 
> 
> i have done new installs using RPMs, but not an upgrade.
> silly question time:  
> are these the correct RPMs?
> do I just upgrade them one at a time as follows:
>   rpm -Uvh  postgresql...rpm
> 
> 
> many thanks 
> 
> kd
> 
> 
> 
> 
> 
> 
> 
> [EMAIL PROTECTED] ("scott.marlowe") wrote in message news:<[EMAIL PROTECTED]>...
> > On Fri, 24 Oct 2003, Duffy House wrote:
> > 
> > > Hello:
> > > 
> > > It looks like I got caught sleeping.
> > > 
> > > I am running Postgresql 7.2 under Red Hat 7.3.
> > > Postgresql failed today.  When I tried to restart it, I got a message
> > > stating that the device is full.
> > > I used df to confirm that /var where postgres reside is full.  /var is on a
> > > 4 Gig device.
> > > Postgresql is the only application on this machine.
> > > 
> > > Please note, the backup of the database from the prior day, is 45 Meg
> > > zipped.
> > > I do not know how the database could be soaking up a 4 Gig device.
> > > 
> > > Questions:
> > > 
> > > How can I recover from this situation?
> > > 
> > > If postgres will not start how can I restore the database from the prior
> > > day?
> > > 
> > > When I run a backup does postgres truncate its logs?
> > > 
> > > please keep it simple, I have about 1 hours admin experience with postgres.
> > > 
> > > thanks in advance.
> > 
> > It sounds like either you've never run vacuum, or you've had a few threads 
> > holding transactions open for a very long time.
> > 
> > Easiest way to recover:  Make a bigger partition, cp everything there, and 
> > point the postmaster at that partition to startup.  Then vacuum etc... and 
> > copy it all back over the original partition.  Then make sure you're 
> > running the latest and greatest (at least 7.2.4, prefereable 7.3.4) and 
> > look up the autovacuum daemon to make sure this never happens again.  If 
> > you installed from source, it's in the contrib/pg_autovacuum directory, 
> > just cd in there as root, and 'make;make install' and it's installed.
> > 
> > 
> > 
> > 
> > ---(end of broadcast)---
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >   subscribe-nomail command to [EMAIL PROTECTED] so that your
> >   message can get through to the mailing list cleanly
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 
> 


---(end of broadcast)---
TIP 8: explain analyze is your friend