[GENERAL] Pgdump error "invalid page header in block"

Yesterday, I had some problem with postgresql 9.0.2. Today i backup postgres and has error pg_dump: reading dependency data pg_dump: SQL command failed pg_dump: Error message from server: ERROR: invalid page header in block 299 of relation "pg_depend_depender_index" pg_dump: The command was: SELE

Re: [GENERAL] updating all records of a table

On Thu, 2011-03-03 at 20:03 -0700, Gauthier, Dave wrote: > Hi: > > I have to update all the records of a table. I'm worried about what > the table will look like in terms of fragmentation when this is > finished. Is there some sort of table healing/reorg/rebuild measure I > should take if I want

[GENERAL] updating all records of a table

Hi: I have to update all the records of a table. I'm worried about what the table will look like in terms of fragmentation when this is finished. Is there some sort of table healing/reorg/rebuild measure I should take if I want the resulting table to operate at optimal efficiency? What about

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

On Tue, Mar 1, 2011 at 7:24 AM, Tom Lane wrote: > Adrian Klaver writes: >> Looks like the TOAST compression is not working on the second machine. Not >> sure >> how that could come to be. Further investigation underway:) > > Somebody carelessly messed with the per-column SET STORAGE settings, >

Re: [GENERAL] I need your help to get opinions about this situation

Rayner -- <...> > I have a database of 1000 tables, 300 of theirs are of major growing > with 1 rows daily, the estimate growing for this database is of > 2,6 TB every year. In and of-itself sheer number of rows only hits you when you need to be reading most of them; in that case good hardw

Re: [GENERAL] orphaned?? tmp files

Reid Thompson writes: > Given the below information, is it reasonable to assume that files in > pgsql_tmp dated prior to 10 days ago can be safely removed? That listing doesn't show anything in pgsql_tmp ... What the listing looks like to me is a tablespace containing a few tables that haven't

[GENERAL] orphaned?? tmp files

Given the below information, is it reasonable to assume that files in pgsql_tmp dated prior to 10 days ago can be safely removed? postgres@hw-prod-repdb1> uptime 15:01:35 up 10 days, 13:50, 3 users, load average: 1.23, 1.15, 0.63 postgres@hw-prod-repdb1> pwd /mnt/iscsi/psql_tmp/tmpdata postgr

Re: [GENERAL] Tracking table modifications / table stats

On Thu, Mar 3, 2011 at 12:34 PM, Andy Colson wrote:There are stat tables you can look at: > > http://www.postgresql.org/docs/9.0/static/monitoring-stats.html > > -Andy > Aha! Thank you.

Re: [GENERAL] Tracking table modifications / table stats

On 3/3/2011 11:00 AM, Derrick Rice wrote: Hey folks, I was looking through the contrib modules with 8.4 and hoping to find something that satisfies my itch. http://www.postgresql.org/docs/8.4/static/pgstatstatements.html comes the closest. I'm inheriting a database which has mostly unknown usag

Re: [GENERAL] Tracking table modifications / table stats

On Thu, Mar 3, 2011 at 11:00 AM, Derrick Rice wrote: > Hey folks, > > I was looking through the contrib modules with 8.4 and hoping to find > something that satisfies my itch. > http://www.postgresql.org/docs/8.4/static/pgstatstatements.html comes the > closest. > > I'm inheriting a database which

Re: [GENERAL] Why count(*) doest use index?

On 03/03/2011 05:29 AM, obamaba...@e1.ru wrote: I use pgsql 9.0.3 and I know that postgresql tries to use the fields in indexes instead of the original table if it possible But when I run SELECT COUNT(id) FROM tab or SELECT COUNT(*) FROM tab where there "id" is PRIMARY KEY and there are othe

[GENERAL] Tracking table modifications / table stats

Hey folks, I was looking through the contrib modules with 8.4 and hoping to find something that satisfies my itch. http://www.postgresql.org/docs/8.4/static/pgstatstatements.html comes the closest. I'm inheriting a database which has mostly unknown usage patterns, and would like to figure them ou

[GENERAL] Why count(*) doest use index?

I use pgsql 9.0.3 and I know that postgresql tries to use the fields in indexes instead of the original table if it possible But when I run SELECT COUNT(id) FROM tab or SELECT COUNT(*) FROM tab where there "id" is PRIMARY KEY and there are other indexes there I get execution plan that d

[GENERAL] closing connection more expensive than opening connection?

A developer here accidentally flooded a server with connection opens and closes, essentially one per transaction during a multi-threaded data migration process. We were curious if this suggests that connection clean up is more expensive than creation thereby exhausting resources, or if perhaps the

Re: [GENERAL] opening connection more expensive than closing connection?

top posting my self to clarify, since the title is in fact inverted: close might be more expensive the open. On 03/03/2011 08:28 AM, Rob Sargent wrote: > A developer here accidentally flooded a server with connection opens and > closes, essentially one per transaction during a multi-threaded data

[GENERAL] invalid byte sequence

After upgrading to pg 9.0.3 (from 8.4.2) on my Mac OS 10.6.2 machine i find this in my log file (a lot): STATEMENT: SELECT pg_file_read('pg_log/postgresql-2011-03-03_00.log', 25, $ ERROR: invalid byte sequence for encoding "UTF8": 0xe3bc74 Apparently pg doesn't like the contents of t

[GENERAL] opening connection more expensive than closing connection?

A developer here accidentally flooded a server with connection opens and closes, essentially one per transaction during a multi-threaded data migration process. We were curious if this suggests that connection clean up is more expensive than creation thereby exhausting resources, or if perhaps the

Re: [GENERAL] data type

On Thu, Mar 3, 2011 at 6:41 AM, Nick Raj wrote: > Which type of data type will be used in above function (in place of ?) > that can collect more than one row(20,000) ? > Maybe the id that those 20M records have in common? hth, WBL On Thu, Mar 3, 2011 at 6:41 AM, Nick Raj wrote: > Hi, >

Re: [GENERAL] Postgresql not start during Startup

also nice: sysv-rc-conf - SysV init runlevel configuration tool for the terminal On Tue, Mar 1, 2011 at 2:09 PM, Ray Stell wrote: > On Tue, Mar 01, 2011 at 06:37:35PM +0530, Adarsh Sharma wrote: > > > > But I want to start it after booting automatically. > > > > > http://embraceubuntu.com/2005/

Re: [GENERAL] Screencasts for PostgreSQL

maybe this? http://enterprisedb.com/resources-community/webcasts-podcasts-videos cheers, WBL On Tue, Mar 1, 2011 at 3:34 PM, James B. Byrne wrote: > I recently viewed a screen-cast on PostgreSQL developed by > Peepcode.com a

Re: [GENERAL] I need your help to get opinions about this situation

I'll leave the "can/cannot" responses to those more familiar with high-load/use situations but I am curious; what reasons other than cost are leading you to discontinue using your current database engine? With that many entities any migration is likely to be quite challenging even if you restrict

Re: [GENERAL] Integrated Trigers

Hi, do you want to know how to create a trigger? http://www.postgresql.org/docs/9.0/interactive/sql-createtrigger.html do you want to know how to list the triggers in your database? - in pgAdmin they are listed in the tree-vie

[GENERAL] Integrated Trigers

Hello, I'm still at beginning regarding databases. I got a question about trigers that model the behavior of some data, usualy a relational database may contain trigers and declarative sql constraints. My question is how could I detect these trigers within a database? using postgresql would help