Re: [ADMIN] Crash with pg_clog file not found

[Matthieu Roger]

>>2008-10-31 20:17:32 CET 10.0.0.119 exile FATAL:  connection limit
>>exceeded for non-superusers
>
> This is just a wild guess, but how many connections are there? Especially see

This link is interesting, I'm reading about it and found a good
article on the msdn blog with a tool to check the desktop heap :
http://www.microsoft.com/downloads/details.aspx?familyid=5CFC9B74-97AA-4510-B4B9-B2DC98C8ED8B&displaylang=en

I will check usage, we have a number of postgres.exe running even if
we don't have many concurrent connections.

Matthieu

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

Re: [ADMIN] Lock them out?

[Glyn Astill]

> From: Carol Walter <[EMAIL PROTECTED]>

> I'm running PostgreSQL version 8.2.10 on Solaris 10. 
> How can I lock  
> everyone out except the postgres user?
> 

Do it in pg_hba.conf




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

Re: [ADMIN] Lock them out?

[Carol Walter]

Thanks.  That will work.  I hadn't thought of that.

Carol
On Nov 3, 2008, at 10:11 AM, Glyn Astill wrote:




From: Carol Walter <[EMAIL PROTECTED]>



I'm running PostgreSQL version 8.2.10 on Solaris 10.
How can I lock
everyone out except the postgres user?



Do it in pg_hba.conf




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



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

Re: [ADMIN] Crash with pg_clog file not found

[Tom Lane]

"Matthieu Roger" <[EMAIL PROTECTED]> writes:
> 2008-10-31 20:14:27 CET 10.0.0.119 exile PANIC:  could not access
> status of transaction 0

Can you present a self-contained test case that causes that?

A stack trace from the PANIC would be mighty useful too, but I dunno
whether there's any point in asking for that from a Windows system.

regards, tom lane

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

Re: [ADMIN] Crash with pg_clog file not found

[Matthieu Roger]

2008/11/3 Tom Lane <[EMAIL PROTECTED]>:
> "Matthieu Roger" <[EMAIL PROTECTED]> writes:
>> 2008-10-31 20:14:27 CET 10.0.0.119 exile PANIC:  could not access
>> status of transaction 0
>
> Can you present a self-contained test case that causes that?

Hello Tom,

we were not able to reproduce at will :-( it seems to happen after
running for 1 week, sometimes less, always for the same base, I know
not helpful at all ...

> A stack trace from the PANIC would be mighty useful too, but I dunno
> whether there's any point in asking for that from a Windows system.

Anyway to do that under windows ?

8.3.3 was also producing the same error, prior version (8.3.1) did not
seem to exhibit it, though we've opened a new universe in the web game
which increased the number of accounts and players in september so
maybe this triggers the problem.

I know this is not much helping, if there is any debug build I could run ...

Matthieu

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

[ADMIN] Lock them out?

[Carol Walter]

Greetings,

I'm running PostgreSQL version 8.2.10 on Solaris 10.  How can I lock  
everyone out except the postgres user?


Carol

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

Re: [ADMIN] Crash with pg_clog file not found

[Rainer Bauer]

"Matthieu Roger" wrote:

>2008-10-31 20:17:32 CET 10.0.0.119 exile FATAL:  connection limit
>exceeded for non-superusers

This is just a wild guess, but how many connections are there? Especially see


Rainer

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

Re: [ADMIN] Crash with pg_clog file not found

[Scott Marlowe]

On Mon, Nov 3, 2008 at 8:49 AM, Matthieu Roger <[EMAIL PROTECTED]> wrote:
>
> 8.3.3 was also producing the same error, prior version (8.3.1) did not
> seem to exhibit it, though we've opened a new universe in the web game
> which increased the number of accounts and players in september so
> maybe this triggers the problem.

I know you probably don't want to hear this right now, but PostgreSQL
can handle a much higher load under some flavor of unix than it can
under windows.  Luckily, it's pretty easy to set up a machine running
Centos5, Ubuntu 8.x or some other flavor of linux.  Due to basic
architectural differences, the difference isn't likely to change soon.

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

[ADMIN] connect to psql without passwd

[Isabella Ghiurea]

I'm writing a shell script to run analyze and other jobs as use 
postgres, having issues passing the passwd  in psql is always asking for 
password, I'm using .pgpass file as argument in psql ( I understood 
PGPASS is depreciated). My .pgpass file contains only the passwd string  
not the full connection string9 hostname, port etc) since this is local 
connection , but is  always asking for passwd, using  :PG ver 8.3, RH 5,

The script contains something like :
Home=
PSQL=
PGDATA=
export HOME, PSQL, PGDATA etc
(cat $HOME/.pgpass
cat << EOF
REINDEX database test
;
EOF
) | $PSQL -U postgres -d test  > /var/lib/pgsql//reindex.log

thank you
Isabella



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

Re: [ADMIN] connect to psql without passwd

[Milen A. Radev]

Isabella Ghiurea написа:
> I'm writing a shell script to run analyze and other jobs as use
> postgres, having issues passing the passwd  in psql is always asking for
> password, I'm using .pgpass file as argument in psql ( I understood
> PGPASS is depreciated). My .pgpass file contains only the passwd string 
> not the full connection string9 hostname, port etc) since this is local
> connection , but is  always asking for passwd, using  :PG ver 8.3, RH 5,
> The script contains something like :
> Home=
> PSQL=
> PGDATA=
> export HOME, PSQL, PGDATA etc
> (cat $HOME/.pgpass
> cat << EOF
> REINDEX database test
> ;
> EOF
> ) | $PSQL -U postgres -d test  > /var/lib/pgsql//reindex.log


You need to re-read the relevant page from the manual
(http://www.postgresql.org/docs/current/static/libpq-pgpass.html). You don't
need to supply the content of the file - psql (and all other programmes based
on libpq) will look for it at a predefined place and use it if a few conditions
are met (correct permissions on the file, correct syntax, etc).


-- 
Milen A. Radev


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

[ADMIN] many tables vs large tables

[Kevin Neufeld]

What is the general consensus around here ... to have many smaller tables, or 
have a few large tables?

I'm looking at a db model where a client has around 5500 spatial (PostGIS) tables, where the volume of each one varies 
greatly ... from a few hundred rows to over 250,000.


Speed is of the utmost importance. I'm investigating various options, like grouping the tables based on a common 
attribute or spatial type (POINT, LINESTRING, etc) into many multi-million tuple tables.


Or, table inheritance could be my friend here, in terms of performance. Ie. Using inheritance and constraint exclusion, 
the query planner could quickly isolate the tables of interest.  It's orders of magnitude faster to perform a sequential 
scan through a relatively small table than it is to do an index scan on a large, likely unclustered table.  The question 
is, can the underlying OS handle thousands of tables in a tablespace?  Would it overwhelm the parser to perform 
constraint exclusion on 50-100 tables?  Can it be done relatively quickly?


Clearly, serious testing is in order, but I just wanted to get a feel for 
things before I dive in.

Cheers,
Kevin

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

[ADMIN] rebellious postgres process

[Laszlo Nagy]

This is what I see in "top" since two days:

last pid: 95702;  load averages:  2.48,  2.69,  
2.75 
up 3+02:08:49  10:27:58

257 processes: 3 running, 246 sleeping, 8 zombie
CPU states: 15.5% user,  0.0% nice, 22.1% system,  1.3% interrupt, 61.1% 
idle

Mem: 864M Active, 6028M Inact, 512M Wired, 254M Cache, 214M Buf, 211M Free
Swap: 10G Total, 500K Used, 10G Free

PID USERNAME   THR PRI NICE   SIZERES STATE  C   TIME   WCPU 
COMMAND
677 pgsql1 1070 22396K  5460K CPU3   6  52.4H 99.02% 
postgres


The processi is using 100% CPU for 52 hours! Here it is:

%ps axl | grep 677
 70   677   666 757 107  0 22396  5460 select Rs??  3144:50.88 
postgres: stats collector process(postgres)



Is this normal? Fortunately, we have more processors in this system and 
it is not causing slow-down. But I really need to know what is happening 
here. I believe it is not the way it should work.


Yesterday the system was restarted suddenly, and this process might be 
the reason for this. After the restart, the process is doing the same 
thing again:


 PID USERNAME   THR PRI NICE   SIZERES STATE  C   TIME   WCPU 
COMMAND
 904 pgsql1 1050 22396K  5332K CPU3   3 386:05 99.22% 
postgres



Here is some more info:

Hardware: Two Quad Xeon 2.5GHz processors, 8GB RAM, RAID with 2GB cache 
and BBU, 10 SATA2 disks in RAID 1+0 OS: FreeBSD 7.0 amd64

Postgresql: 8.3
Databases: two bigger ones, total database size on disk is about 10GB, 
used mostly read-only, but some smaller tables updated frequently.


I don't think that it needs stat collection continuously.

Thanks,

 Laszlo


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