catalog corruption causes

I am trying to get my head around what causes catalog corruption. I
have posted before with regard to recovering from corruptions (if that
is what indeed happened to me), and was given much help.

Does anyone know why a database catalog will get corrupted? As I
mentioned previously, my db involves considerable use of temporary
tables created by php-psql connections. Other than that, I don't know
of too much that is "odd" about my use (misuse) of the database.

Thanks,
r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer@deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: catalog corruption causes

"Burgholzer, Robert (DEQ)"
wrote:

> I am trying to get my head around what causes catalog corruption.

From what I've seen many are caused by things outside of PostgreSQL
-- like bad disk, bad drivers, OS bugs, running on network drives
which aren't reliable, write caches without battery back-up,
anti-virus software, etc.

Another common cause is using faster-but-unreliable PostgreSQL
configuration settings like fsync=off or full_page_writes=off. If
that's couple with a kill -9 or a crash of hardware or OS, you can
get corruption.

Then there's the possibility of PostgreSQL bugs. Make sure you use
a recent minor release of whatever major release you're on, so you
get the benefit of bug fixes. And I recommend staying away from
VACUUM FULL -- among the many other reasons there are to avoid it,
it seems to have more than it's share of odd corner cases where
things can go wrong.

Less likely, but still possible, is that there's some malicious
element involved. Keep your security tight to minimize the risk of
that.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: catalog corruption causes

Thanks Kevin, that gives me more than enough things to pepper my
sysadmin with. :)

r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer@deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: catalog corruption causes

On Fri, Aug 6, 2010 at 12:28 PM, Burgholzer, Robert (DEQ)
wrote:
> I am trying to get my head around what causes catalog corruption. =A0I
> have posted before with regard to recovering from corruptions (if that
> is what indeed happened to me), and was given much help.
>
> Does anyone know why a database catalog will get corrupted? =A0As I
> mentioned previously, my db involves considerable use of temporary
> tables created by php-psql connections. =A0Other than that, I don't know
> of too much that is "odd" about my use (misuse) of the database.

Two most common causes are bad memory / hard drives / cpu and a
machine that doesn't fsync properly crashing and losing part of a
write to the disks.

memtest86+ will give you an idea if your hardware (cpu / mem) are
stable and reliable. SMART can tell you if your hard drives are
acting up. pgbench can tell you if your system is lying about fsync
(a single SATA drive shouldn't be able to do more than a few hundred
tps).

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: catalog corruption causes

On Fri, Aug 6, 2010 at 12:28 PM, Burgholzer, Robert (DEQ)
wrote:
> I am trying to get my head around what causes catalog corruption. =A0I
> have posted before with regard to recovering from corruptions (if that
> is what indeed happened to me), and was given much help.
>
> Does anyone know why a database catalog will get corrupted? =A0As I
> mentioned previously, my db involves considerable use of temporary
> tables created by php-psql connections. =A0Other than that, I don't know
> of too much that is "odd" about my use (misuse) of the database.

Note that it's also possible you've found some uncommon corner case
with pgsql code. If you have tested memory / cpu and can consistently
get corrupted catalogs by running a particular sequence of commands
against pgsql over and over then definitely report it and provide a
test case. Guidance on reporting problems here:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

High-water Mark for number of sessions/connections reachedin Postgres

I was wondering if we can query/obtain the high-water mark of number of ses=
sions or connections reached in a Postgres database. Is there a view or com=
mand that can provide this information. The pg_stat_database shows the cur=
rent number of connections, but not the high-water mark a database had reac=
hed.

Thanks in advance.

Husam
************************************************************ ***************=
***************=20
This message may contain confidential or proprietary information intended o=
nly for the use of the=20
addressee(s) named above or may contain information that is legally privile=
ged. If you are=20
not the intended addressee, or the person responsible for delivering it to =
the intended addressee,=20
you are hereby notified that reading, disseminating, distributing or copyin=
g this message is strictly=20
prohibited. If you have received this message by mistake, please immediatel=
y notify us by
replying to the message and delete the original message and any copies imme=
diately thereafter.=20

Thank you.=20
************************************************************ ***************=
***************=20
CLLD


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: High-water Mark for number of sessions/connections

"Tomeh, Husam" wrote:

> I was wondering if we can query/obtain the high-water mark of
> number of sessions or connections reached in a Postgres database.

I'm not aware of anything like that, although it seems as though it
would have obvious uses in database administration. If you had such
a thing, would you want the ability to reset it?

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: catalog corruption causes

Thanks Scott,
I will look into these testing programs and file a bug if I have in fact
found a special occurrence. I suspect that there might be some issues
with our postgres install, as the initial install was via a yum, and I
recall that our sysadmin had some difficulty removing it in order to do
a compile from source install. We have also experienced one or two
cases of strange hard drive behavior, so this gives us some avenues to
explore.

r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer@deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: High-water Mark for number of sessions/connections

On Fri, Aug 6, 2010 at 6:19 PM, Tomeh, Husam wrote:
> I was wondering if we can query/obtain the high-water mark of number of s=
essions or connections reached in a Postgres database. Is there a view or c=
ommand that can provide this information. =A0The pg_stat_database shows the=
current number of connections, but not the high-water mark a database had =
reached.

It's a pretty easy thing to approximate with a shell script.

while true;do ps ax|grep postgres:|grep -v grep|wc -l ;sleep 10;done >
connects.log &

then just tail the connects.log file. It's a dirty hack and it'll be
a few counts over due to counting the postmaster and a few other
processes, but it'll give you a good idea of what your system is
doing. Add a date in there if you need to know the time it was
happening.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: High-water Mark for number of sessions/connections

If you want a date stamp, you can change the ps ax stuff to look like this:

date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax|grep postgres:|grep -v grep|wc =
-l

On Mon, Aug 9, 2010 at 4:21 PM, Scott Marlowe wro=
te:
> On Fri, Aug 6, 2010 at 6:19 PM, Tomeh, Husam wrote:
>> I was wondering if we can query/obtain the high-water mark of number of =
sessions or connections reached in a Postgres database. Is there a view or =
command that can provide this information. =A0The pg_stat_database shows th=
e current number of connections, but not the high-water mark a database had=
reached.
>
> It's a pretty easy thing to approximate with a shell script.
>
> while true;do ps ax|grep postgres:|grep -v grep|wc -l ;sleep 10;done >
> connects.log &
>
> then just tail the connects.log file. =A0It's a dirty hack and it'll be
> a few counts over due to counting the postmaster and a few other
> processes, but it'll give you a good idea of what your system is
> doing. =A0Add a date in there if you need to know the time it was
> happening.
>



--=20
To understand recursion, one must first understand recursion.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: High-water Mark for number of sessions/connections reached in Postgres

Excerpts from Scott Marlowe's message of lun ago 09 18:29:58 -0400 2010:
> If you want a date stamp, you can change the ps ax stuff to look like t=
his:
>=20
> date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax|grep postgres:|grep -v grep=
|wc -l

FWIW the "grep" business is best solved by ps itself, something like=20
ps ax -C postgres

date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax -C postgres|wc -l

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: High-water Mark for number of sessions/connectionsreached in Postgres

VGhhbmtzIGZvciB0aGVzZSB3b3JrYXJvdW5kcyBJIGhhdmUgc29tZXRoaW5n
IHNpbWlsYXIgaW1wbGVtZW50ZWQuIEl0IHdvdWxkJ3ZlIGJlZW4gbmljZSB0
byBoYXZlIFBvc3RncmVzIG1haW50YWluIHRoZSBoaWdoLXdhdGVyIG1hcmsg
aW5zaWRlIHRoZSBkYXRhYmFzZSBpbiBhIHBnIHZpZXcgc3VjaCBhcyBwZ19z
dGF0X2RhdGFiYXNlLiBUaGlzIGlzIHVzZWZ1bCBmcm9tIGEgY2FwYWNpdHkg
cGVyc3BlY3RpdmUgd2hlcmUgdGhhdCBjYW4gYmUgbW9uaXRvcmVkIHRvIGFs
ZXJ0IERCQXMgd2hlbiBhIHRocmVzaG9sZCBpcyByZWFjaGVkLiBPdGhlciBE
QiBlbmdpbmVzIGhhcyBzdWNoIGZlYXR1cmUgYnVpbHQgaW4gc3VjaCBhcyBP
cmFjbGUuIFBlcmhhcHMgc29tZXRoaW5nIHRvIGJlIG9uIFBvc3RncmVzIFRP
IERPICBsaXN0IHNvb24gIDopDQoNClJlZ2FyZHMsDQogICAgICBIdXNhbQ0K
DQotLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0tLQ0KRnJvbTogcGdzcWwtYWRt
aW4tb3duZXJAcG9zdGdyZXNxbC5vcmcgW21haWx0bzpwZ3NxbC1hZG1pbi1v
d25lckBwb3N0Z3Jlc3FsLm9yZ10gT24gQmVoYWxmIE9mIEFsdmFybyBIZXJy
ZXJhDQpTZW50OiBNb25kYXksIEF1Z3VzdCAwOSwgMjAxMCAzOjM5IFBNDQpU
bzogU2NvdHQgTWFybG93ZQ0KQ2M6IFRvbWVoLCBIdXNhbTsgcGdzcWwtYWRt
aW5AcG9zdGdyZXNxbC5vcmcNClN1YmplY3Q6IFJlOiBbQURNSU5dIEhpZ2gt
d2F0ZXIgTWFyayBmb3IgbnVtYmVyIG9mIHNlc3Npb25zL2Nvbm5lY3Rpb25z
IHJlYWNoZWQgaW4gUG9zdGdyZXMNCg0KRXhjZXJwdHMgZnJvbSBTY290dCBN
YXJsb3dlJ3MgbWVzc2FnZSBvZiBsdW4gYWdvIDA5IDE4OjI5OjU4IC0wNDAw
IDIwMTA6DQo+IElmIHlvdSB3YW50IGEgZGF0ZSBzdGFtcCwgeW91IGNhbiBj
aGFuZ2UgdGhlIHBzIGF4IHN0dWZmIHRvIGxvb2sgbGlrZSB0aGlzOg0KPiAN
Cj4gZGF0ZSArIiVZLSVtLSVkICVIOiVNOiVTInx0ciAiXG4iICI6IjtwcyBh
eHxncmVwIHBvc3RncmVzOnxncmVwIC12IGdyZXB8d2MgLWwNCg0KRldJVyB0
aGUgImdyZXAiIGJ1c2luZXNzIGlzIGJlc3Qgc29sdmVkIGJ5IHBzIGl0c2Vs
Ziwgc29tZXRoaW5nIGxpa2UgDQpwcyBheCAtQyBwb3N0Z3Jlcw0KDQpkYXRl
ICsiJVktJW0tJWQgJUg6JU06JVMifHRyICJcbiIgIjoiO3BzIGF4IC1DICBw
b3N0Z3Jlc3x3YyAtbA0KDQotLSANCsOBbHZhcm8gSGVycmVyYSA8YWx2aGVy
cmVAY29tbWFuZHByb21wdC5jb20+DQpUaGUgUG9zdGdyZVNRTCBDb21wYW55
IC0gQ29tbWFuZCBQcm9tcHQsIEluYy4NClBvc3RncmVTUUwgUmVwbGljYXRp
b24sIENvbnN1bHRpbmcsIEN1c3RvbSBEZXZlbG9wbWVudCwgMjR4NyBzdXBw
b3J0DQoNCi0tIA0KU2VudCB2aWEgcGdzcWwtYWRtaW4gbWFpbGluZyBsaXN0
IChwZ3NxbC1hZG1pbkBwb3N0Z3Jlc3FsLm9yZykNClRvIG1ha2UgY2hhbmdl
cyB0byB5b3VyIHN1YnNjcmlwdGlvbjoNCmh0dHA6Ly93d3cucG9zdGdyZXNx
bC5vcmcvbWFpbHByZWYvcGdzcWwtYWRtaW4NCioqKioqKioqKioqKioqKioq
KioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioq
KioqKioqKioqKioqKioqKioqKioqKioqKioqKiAKVGhpcyBtZXNzYWdlIG1h
eSBjb250YWluIGNvbmZpZGVudGlhbCBvciBwcm9wcmlldGFyeSBpbmZvcm1h
dGlvbiBpbnRlbmRlZCBvbmx5IGZvciB0aGUgdXNlIG9mIHRoZSAKYWRkcmVz
c2VlKHMpIG5hbWVkIGFib3ZlIG9yIG1heSBjb250YWluIGluZm9ybWF0aW9u
IHRoYXQgaXMgbGVnYWxseSBwcml2aWxlZ2VkLiBJZiB5b3UgYXJlIApub3Qg
dGhlIGludGVuZGVkIGFkZHJlc3NlZSwgb3IgdGhlIHBlcnNvbiByZXNwb25z
aWJsZSBmb3IgZGVsaXZlcmluZyBpdCB0byB0aGUgaW50ZW5kZWQgYWRkcmVz
c2VlLCAKeW91IGFyZSBoZXJlYnkgbm90aWZpZWQgdGhhdCByZWFkaW5nLCBk
aXNzZW1pbmF0aW5nLCBkaXN0cmlidXRpbmcgb3IgY29weWluZyB0aGlzIG1l
c3NhZ2UgaXMgc3RyaWN0bHkgCnByb2hpYml0ZWQuIElmIHlvdSBoYXZlIHJl
Y2VpdmVkIHRoaXMgbWVzc2FnZSBieSBtaXN0YWtlLCBwbGVhc2UgaW1tZWRp
YXRlbHkgbm90aWZ5IHVzIGJ5ICAKcmVwbHlpbmcgdG8gdGhlIG1lc3NhZ2Ug
YW5kIGRlbGV0ZSB0aGUgb3JpZ2luYWwgbWVzc2FnZSBhbmQgYW55IGNvcGll
cyBpbW1lZGlhdGVseSB0aGVyZWFmdGVyLiAKClRoYW5rIHlvdS4gCioqKioq
KioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioq
KioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKiAKQ0xM
RAoKLS0gClNlbnQgdmlhIHBnc3FsLWFkbWluIG1haWxpbmcgbGlzdCAocGdz
cWwtYWRtaW5AcG9zdGdyZXNxbC5vcmcpClRvIG1ha2UgY2hhbmdlcyB0byB5
b3VyIHN1YnNjcmlwdGlvbjoKaHR0cDovL3d3dy5wb3N0Z3Jlc3FsLm9yZy9t
YWlscHJlZi9wZ3NxbC1hZG1pbgo=

Re: High-water Mark for number of sessions/connections

On Mon, Aug 9, 2010 at 5:33 PM, Tomeh, Husam wrote:
> This is useful from a capacity perspective where that can be monitored to alert DBAs when a threshold is reached.

If alerting based on connection counts is your concern, you might get
some benefit from the Bucardo project's check_postgres.pl script. One
of the tests it performs is a backend count. It works with any
monitoring package that speaks NRPE, so you can issue alerts based on
either a percentage of your max_connections setting, or on the actual
backend count, as indicated by the arguments with which it's invoked.

It would also be fairly straightforward to store whatever value the
test returns in a db table, log file, Cacti/other RRD-based tools, or
whatever else strikes your fancy, for historical and trending
analysis.



rls

--
:wq

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin