Re: [ADMIN] connecting to server

[Vishal Arora]

Hi Reddy, 
 
Have you done the entries in pg_hba.conf file and in postgresql.conf file - 
listen address. ?
 
- Vishal



Date: Tue, 16 Sep 2008 00:53:22 -0400From: [EMAIL PROTECTED]: [EMAIL 
PROTECTED]: [ADMIN] connecting to serverCC: pgsql-admin@postgresql.org
 Hi gb   Thanks for your reply,  Ya the sever is up and running, i don't know 
how to check weather the server is accepting TCP/IP connections.  The sever is 
in different box.   i read the manual pages I am not getting..RegardsHanumantha 
Reddy
_
Search for videos of Bollywood, Hollywood, Mollywood and every other wood, only 
on Live.com 
http://www.live.com/?scope=video&form=MICOAL

Re: [ADMIN] Heavy postgres process

[Guido Barosio]

Well, the answer is shor Vivekt:

Upgrade that postgresql ASAP, it's too way old.

gb.-

On Wed, Sep 17, 2008 at 9:29 AM, Vivek_Sharan <[EMAIL PROTECTED]> wrote:
> I'm using postgres 7.4.5
>
> Regards,
> Vivek
>
>
>
> -Original Message-
> From: Guido Barosio [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 16, 2008 8:08 PM
> To: Vivek_Sharan
> Cc: Scott Marlowe; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Heavy postgres process
>
> On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <[EMAIL PROTECTED]> wrote:
>> Thanks for the information so far
>> My Application runs on FreeBSd box and main technological component are 
>> Apache and mod Perl, database is postgres. I have already scanned 
>> pg_stat_activity and pg_listener table but could get any clue. 
>> Pg_stat_activity shows list of all idle processes but command 
>> (current_query) column is empty. So I cannot make out what these processes 
>> are doing.
>> TOP on this server doesn't have any option available to further break down 
>> processes. And hitting 'M; did change anything because this is not available 
>> with top on this server. Following is the output of top if filtered for only 
>> postgres user
>>
>> *
>> last pid: 92308;  load averages:  0.00,  0.03,  0.05
>> 78 processes:  2 running, 76 sleeping
>> CPU states:  1.6% user,  0.0% nice,  3.4% system,  0.0% interrupt, 94.9% idle
>> Mem: 413M Active, 2122M Inact, 534M Wired, 140M Cache, 199M Buf, 533M Free
>> Swap: 4096M Total, 3880K Used, 4092M Free
>>
>>  PID USERNAME  PRI NICE  SIZERES STATE  C   TIME   WCPUCPU COMMAND
>> 90976 postgres2   0 83568K 76016K sbwait 2   0:32  2.83%  2.83% postgres
>> 90963 postgres2   0 83396K 75876K sbwait 2   0:25  1.37%  1.37% postgres
>> 90919 postgres2   0 83808K 76244K sbwait 1   0:32  0.39%  0.39% postgres
>> 87341 postgres2   0  6388K   756K select 3   2:35  0.00%  0.00% postgres
>> 87340 postgres2   0  7200K  1224K select 0   1:41  0.00%  0.00% postgres
>> 90961 postgres2   0 83580K 76008K sbwait 0   0:30  0.00%  0.00% postgres
>> 90920 postgres2   0 83636K 76068K sbwait 0   0:29  0.00%  0.00% postgres
>> 90934 postgres2   0 83664K 76012K sbwait 0   0:27  0.00%  0.00% postgres
>> 90924 postgres2   0 83408K 75872K sbwait 0   0:25  0.00%  0.00% postgres
>> 90915 postgres2   0 79292K 72664K sbwait 0   0:23  0.00%  0.00% postgres
>> 90955 postgres2   0 79644K 73040K sbwait 0   0:22  0.00%  0.00% postgres
>> 90979 postgres2   0 78904K 72260K sbwait 0   0:17  0.00%  0.00% postgres
>> 87339 postgres2   0 74756K   672K select 1   0:12  0.00%  0.00% postgres
>> 90921 postgres2   0 75504K 59848K sbwait 3   0:01  0.00%  0.00% postgres
>> 90927 postgres2   0 75540K 59296K sbwait 3   0:01  0.00%  0.00% postgres
>> 90962 postgres2   0 75524K 56960K sbwait 0   0:01  0.00%  0.00% postgres
>> 90923 postgres2   0 75540K 57584K sbwait 1   0:01  0.00%  0.00% postgres
>> 90914 postgres2   0 75552K 57776K sbwait 1   0:01  0.00%  0.00% postgres
>> 90917 postgres2   0 75524K 57256K sbwait 3   0:01  0.00%  0.00% postgres
>> 90922 postgres2   0 75504K 57352K sbwait 1   0:01  0.00%  0.00% postgres
>> 90918 postgres2   0 75508K 57748K sbwait 3   0:01  0.00%  0.00% postgres
>> 90933 postgres2   0 75540K 53728K sbwait 2   0:01  0.00%  0.00% postgres
>> 90926 postgres2   0 75484K 54928K sbwait 3   0:01  0.00%  0.00% postgres
>> 90931 postgres2   0 75512K 20880K sbwait 3   0:00  0.00%  0.00% postgres
>> 90977 postgres2   0 75512K 20584K sbwait 0   0:00  0.00%  0.00% postgres
>> 91005 postgres2   0 75512K 19956K sbwait 0   0:00  0.00%  0.00% postgres
>> 90966 postgres2   0 75488K 19056K sbwait 1   0:00  0.00%  0.00% postgres
>> 90986 postgres2   0 75512K 19348K sbwait 1   0:00  0.00%  0.00% postgres
>> 90973 postgres2   0 75512K 18140K sbwait 1   0:00  0.00%  0.00% postgres
>> 90989 postgres2   0 75512K 18668K sbwait 2   0:00  0.00%  0.00% postgres
>> 90956 postgres2   0 75488K 18320K sbwait 2   0:00  0.00%  0.00% postgres
>> 90998 postgres2   0 75512K 17564K sbwait 3   0:00  0.00%  0.00% postgres
>> 90925 postgres2   0 75488K 17412K sbwait 1   0:00  0.00%  0.00% postgres
>> 1 postgres2   0 75528K  7920K sbwait 0   0:00  0.00%  0.00% postgres
>> *
>>
>> Output of vmstat command
>>
>> procs  memory  pagedisks faults  cpu
>>  r b w avmfre  flt  re  pi  po  fr  sr da0 da1   in   sy  cs us sy id
>>  0 0 0  423492 688492   40   0   0   0  52  57   0   0   50   11  50 53 47 -0
>>
>> *
>> Output of systat command
>>
>>> systat
>>
>>
>>/0   /1   /2   /3   /4   /5   /6   /7   /8   /9   /10
>> Load Average   |
>>
>>/0   /10 

[ADMIN] unable to backup database -- psql not up to date

[dbchristopher]

I upgraded to postgresql 8.3.3 a few weeks ago, and it seems that while the
server was upgraded, but for some reason none of the accompanying software
was (pg_dump, psql, etc)

I get this error when I try to back up the database:
pg_dump: server version: 8.3.3; pg_dump version: 8.2.6
pg_dump: aborting because of version mismatch (Use the -i option to proceed
anyway.)
pg_dump: *** aborted because of error

and likewise when I log into the psql client:
WARNING: You are connected to a server with major version 8.3,
but your psql client is major version 8.2. Some backslash commands,
such as \d, might not work properly.

How could this have happened? I really need to be able to back up my
information. I reinstalled the software and it didn't change anything. What
do I do to upgrade pg_dump to the newest version?
-- 
View this message in context: 
http://www.nabble.com/unable-to-backup-databasepsql-not-up-to-date-tp19520466p19520466.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


-- 
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] unable to backup database -- psql not up to date

[Jonathan Nalley]

On Tue, Sep 16, 2008 at 5:53 PM, dbchristopher <[EMAIL PROTECTED]> wrote:
>
> I upgraded to postgresql 8.3.3 a few weeks ago, and it seems that while the
> server was upgraded, but for some reason none of the accompanying software
> was (pg_dump, psql, etc)
>
> I get this error when I try to back up the database:
> pg_dump: server version: 8.3.3; pg_dump version: 8.2.6
> pg_dump: aborting because of version mismatch (Use the -i option to proceed
> anyway.)
> pg_dump: *** aborted because of error
>
> and likewise when I log into the psql client:
> WARNING: You are connected to a server with major version 8.3,
> but your psql client is major version 8.2. Some backslash commands,
> such as \d, might not work properly.
>
> How could this have happened? I really need to be able to back up my
> information. I reinstalled the software and it didn't change anything. What
> do I do to upgrade pg_dump to the newest version?

What OS are you running on?

How did you perform this upgrade?  In a packaged form like rpm?  or
did you compile from source?

-- 
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] Heavy postgres process

[Scott Marlowe]

On Wed, Sep 17, 2008 at 7:18 AM, Vivek_Sharan <[EMAIL PROTECTED]> wrote:
> Yes that's true and that's planned. We will migrate to Oracle. But as of now 
> need some pointers on solving the problem in hand.

Well, updating to the latest 7.4 version would be a very very good
move.  There are some very real and nasty data loss bugs in 7.4.5.
It's an in place update that requires only a few minutes of downtime.

I would guess you've got something like sort_mem set too high.

P.s. If you think PostgreSQL is hard to troubleshoot, I can't wait to
see you running Oracle.

-- 
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] Heavy postgres process

[Vivek_Sharan]

I'm using postgres 7.4.5

Regards,
Vivek



-Original Message-
From: Guido Barosio [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 16, 2008 8:08 PM
To: Vivek_Sharan
Cc: Scott Marlowe; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Heavy postgres process

On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <[EMAIL PROTECTED]> wrote:
> Thanks for the information so far
> My Application runs on FreeBSd box and main technological component are 
> Apache and mod Perl, database is postgres. I have already scanned 
> pg_stat_activity and pg_listener table but could get any clue. 
> Pg_stat_activity shows list of all idle processes but command (current_query) 
> column is empty. So I cannot make out what these processes are doing.
> TOP on this server doesn't have any option available to further break down 
> processes. And hitting 'M; did change anything because this is not available 
> with top on this server. Following is the output of top if filtered for only 
> postgres user
>
> *
> last pid: 92308;  load averages:  0.00,  0.03,  0.05
> 78 processes:  2 running, 76 sleeping
> CPU states:  1.6% user,  0.0% nice,  3.4% system,  0.0% interrupt, 94.9% idle
> Mem: 413M Active, 2122M Inact, 534M Wired, 140M Cache, 199M Buf, 533M Free
> Swap: 4096M Total, 3880K Used, 4092M Free
>
>  PID USERNAME  PRI NICE  SIZERES STATE  C   TIME   WCPUCPU COMMAND
> 90976 postgres2   0 83568K 76016K sbwait 2   0:32  2.83%  2.83% postgres
> 90963 postgres2   0 83396K 75876K sbwait 2   0:25  1.37%  1.37% postgres
> 90919 postgres2   0 83808K 76244K sbwait 1   0:32  0.39%  0.39% postgres
> 87341 postgres2   0  6388K   756K select 3   2:35  0.00%  0.00% postgres
> 87340 postgres2   0  7200K  1224K select 0   1:41  0.00%  0.00% postgres
> 90961 postgres2   0 83580K 76008K sbwait 0   0:30  0.00%  0.00% postgres
> 90920 postgres2   0 83636K 76068K sbwait 0   0:29  0.00%  0.00% postgres
> 90934 postgres2   0 83664K 76012K sbwait 0   0:27  0.00%  0.00% postgres
> 90924 postgres2   0 83408K 75872K sbwait 0   0:25  0.00%  0.00% postgres
> 90915 postgres2   0 79292K 72664K sbwait 0   0:23  0.00%  0.00% postgres
> 90955 postgres2   0 79644K 73040K sbwait 0   0:22  0.00%  0.00% postgres
> 90979 postgres2   0 78904K 72260K sbwait 0   0:17  0.00%  0.00% postgres
> 87339 postgres2   0 74756K   672K select 1   0:12  0.00%  0.00% postgres
> 90921 postgres2   0 75504K 59848K sbwait 3   0:01  0.00%  0.00% postgres
> 90927 postgres2   0 75540K 59296K sbwait 3   0:01  0.00%  0.00% postgres
> 90962 postgres2   0 75524K 56960K sbwait 0   0:01  0.00%  0.00% postgres
> 90923 postgres2   0 75540K 57584K sbwait 1   0:01  0.00%  0.00% postgres
> 90914 postgres2   0 75552K 57776K sbwait 1   0:01  0.00%  0.00% postgres
> 90917 postgres2   0 75524K 57256K sbwait 3   0:01  0.00%  0.00% postgres
> 90922 postgres2   0 75504K 57352K sbwait 1   0:01  0.00%  0.00% postgres
> 90918 postgres2   0 75508K 57748K sbwait 3   0:01  0.00%  0.00% postgres
> 90933 postgres2   0 75540K 53728K sbwait 2   0:01  0.00%  0.00% postgres
> 90926 postgres2   0 75484K 54928K sbwait 3   0:01  0.00%  0.00% postgres
> 90931 postgres2   0 75512K 20880K sbwait 3   0:00  0.00%  0.00% postgres
> 90977 postgres2   0 75512K 20584K sbwait 0   0:00  0.00%  0.00% postgres
> 91005 postgres2   0 75512K 19956K sbwait 0   0:00  0.00%  0.00% postgres
> 90966 postgres2   0 75488K 19056K sbwait 1   0:00  0.00%  0.00% postgres
> 90986 postgres2   0 75512K 19348K sbwait 1   0:00  0.00%  0.00% postgres
> 90973 postgres2   0 75512K 18140K sbwait 1   0:00  0.00%  0.00% postgres
> 90989 postgres2   0 75512K 18668K sbwait 2   0:00  0.00%  0.00% postgres
> 90956 postgres2   0 75488K 18320K sbwait 2   0:00  0.00%  0.00% postgres
> 90998 postgres2   0 75512K 17564K sbwait 3   0:00  0.00%  0.00% postgres
> 90925 postgres2   0 75488K 17412K sbwait 1   0:00  0.00%  0.00% postgres
> 1 postgres2   0 75528K  7920K sbwait 0   0:00  0.00%  0.00% postgres
> *
>
> Output of vmstat command
>
> procs  memory  pagedisks faults  cpu
>  r b w avmfre  flt  re  pi  po  fr  sr da0 da1   in   sy  cs us sy id
>  0 0 0  423492 688492   40   0   0   0  52  57   0   0   50   11  50 53 47 -0
>
> *
> Output of systat command
>
>> systat
>
>
>/0   /1   /2   /3   /4   /5   /6   /7   /8   /9   /10
> Load Average   |
>
>/0   /10  /20  /30  /40  /50  /60  /70  /80  /90  /100
> postgres   postgres X
> *
> entries in pg_stat_activities
>
> datid | datname | procpid | usesysid | usename  | current_query | query_star

Re: [ADMIN] Heavy postgres process

[Vivek_Sharan]

Yes that's true and that's planned. We will migrate to Oracle. But as of now 
need some pointers on solving the problem in hand.

Regards,
Vivek



-Original Message-
From: Guido Barosio [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 17, 2008 6:39 PM
To: Vivek_Sharan
Cc: Scott Marlowe; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Heavy postgres process

Well, the answer is shor Vivekt:

Upgrade that postgresql ASAP, it's too way old.

gb.-

On Wed, Sep 17, 2008 at 9:29 AM, Vivek_Sharan <[EMAIL PROTECTED]> wrote:
> I'm using postgres 7.4.5
>
> Regards,
> Vivek
>
>
>
> -Original Message-
> From: Guido Barosio [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 16, 2008 8:08 PM
> To: Vivek_Sharan
> Cc: Scott Marlowe; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Heavy postgres process
>
> On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <[EMAIL PROTECTED]> wrote:
>> Thanks for the information so far
>> My Application runs on FreeBSd box and main technological component are 
>> Apache and mod Perl, database is postgres. I have already scanned 
>> pg_stat_activity and pg_listener table but could get any clue. 
>> Pg_stat_activity shows list of all idle processes but command 
>> (current_query) column is empty. So I cannot make out what these processes 
>> are doing.
>> TOP on this server doesn't have any option available to further break down 
>> processes. And hitting 'M; did change anything because this is not available 
>> with top on this server. Following is the output of top if filtered for only 
>> postgres user
>>
>> *
>> last pid: 92308;  load averages:  0.00,  0.03,  0.05
>> 78 processes:  2 running, 76 sleeping
>> CPU states:  1.6% user,  0.0% nice,  3.4% system,  0.0% interrupt, 94.9% idle
>> Mem: 413M Active, 2122M Inact, 534M Wired, 140M Cache, 199M Buf, 533M Free
>> Swap: 4096M Total, 3880K Used, 4092M Free
>>
>>  PID USERNAME  PRI NICE  SIZERES STATE  C   TIME   WCPUCPU COMMAND
>> 90976 postgres2   0 83568K 76016K sbwait 2   0:32  2.83%  2.83% postgres
>> 90963 postgres2   0 83396K 75876K sbwait 2   0:25  1.37%  1.37% postgres
>> 90919 postgres2   0 83808K 76244K sbwait 1   0:32  0.39%  0.39% postgres
>> 87341 postgres2   0  6388K   756K select 3   2:35  0.00%  0.00% postgres
>> 87340 postgres2   0  7200K  1224K select 0   1:41  0.00%  0.00% postgres
>> 90961 postgres2   0 83580K 76008K sbwait 0   0:30  0.00%  0.00% postgres
>> 90920 postgres2   0 83636K 76068K sbwait 0   0:29  0.00%  0.00% postgres
>> 90934 postgres2   0 83664K 76012K sbwait 0   0:27  0.00%  0.00% postgres
>> 90924 postgres2   0 83408K 75872K sbwait 0   0:25  0.00%  0.00% postgres
>> 90915 postgres2   0 79292K 72664K sbwait 0   0:23  0.00%  0.00% postgres
>> 90955 postgres2   0 79644K 73040K sbwait 0   0:22  0.00%  0.00% postgres
>> 90979 postgres2   0 78904K 72260K sbwait 0   0:17  0.00%  0.00% postgres
>> 87339 postgres2   0 74756K   672K select 1   0:12  0.00%  0.00% postgres
>> 90921 postgres2   0 75504K 59848K sbwait 3   0:01  0.00%  0.00% postgres
>> 90927 postgres2   0 75540K 59296K sbwait 3   0:01  0.00%  0.00% postgres
>> 90962 postgres2   0 75524K 56960K sbwait 0   0:01  0.00%  0.00% postgres
>> 90923 postgres2   0 75540K 57584K sbwait 1   0:01  0.00%  0.00% postgres
>> 90914 postgres2   0 75552K 57776K sbwait 1   0:01  0.00%  0.00% postgres
>> 90917 postgres2   0 75524K 57256K sbwait 3   0:01  0.00%  0.00% postgres
>> 90922 postgres2   0 75504K 57352K sbwait 1   0:01  0.00%  0.00% postgres
>> 90918 postgres2   0 75508K 57748K sbwait 3   0:01  0.00%  0.00% postgres
>> 90933 postgres2   0 75540K 53728K sbwait 2   0:01  0.00%  0.00% postgres
>> 90926 postgres2   0 75484K 54928K sbwait 3   0:01  0.00%  0.00% postgres
>> 90931 postgres2   0 75512K 20880K sbwait 3   0:00  0.00%  0.00% postgres
>> 90977 postgres2   0 75512K 20584K sbwait 0   0:00  0.00%  0.00% postgres
>> 91005 postgres2   0 75512K 19956K sbwait 0   0:00  0.00%  0.00% postgres
>> 90966 postgres2   0 75488K 19056K sbwait 1   0:00  0.00%  0.00% postgres
>> 90986 postgres2   0 75512K 19348K sbwait 1   0:00  0.00%  0.00% postgres
>> 90973 postgres2   0 75512K 18140K sbwait 1   0:00  0.00%  0.00% postgres
>> 90989 postgres2   0 75512K 18668K sbwait 2   0:00  0.00%  0.00% postgres
>> 90956 postgres2   0 75488K 18320K sbwait 2   0:00  0.00%  0.00% postgres
>> 90998 postgres2   0 75512K 17564K sbwait 3   0:00  0.00%  0.00% postgres
>> 90925 postgres2   0 75488K 17412K sbwait 1   0:00  0.00%  0.00% postgres
>> 1 postgres2   0 75528K  7920K sbwait 0   0:00  0.00%  0.00% postgres
>> *
>>
>> Output of vmstat command
>>
>> procs  memory  pagedisks faults  cpu
>>  r b w avmfre  flt  re  pi  po  fr  sr da0 d

Re: [ADMIN] unable to backup database -- psql not up to date

[Valentin Bogdanov]

--- On Wed, 17/9/08, Jonathan Nalley <[EMAIL PROTECTED]> wrote:

> From: Jonathan Nalley <[EMAIL PROTECTED]>
> Subject: Re: [ADMIN] unable to backup database -- psql not up to date
> To: "dbchristopher" <[EMAIL PROTECTED]>, pgsql-admin@postgresql.org
> Date: Wednesday, 17 September, 2008, 3:23 PM
> On Tue, Sep 16, 2008 at 5:53 PM, dbchristopher
> <[EMAIL PROTECTED]> wrote:
> >
> > I upgraded to postgresql 8.3.3 a few weeks ago, and it
> seems that while the
> > server was upgraded, but for some reason none of the
> accompanying software
> > was (pg_dump, psql, etc)
> >
> > I get this error when I try to back up the database:
> > pg_dump: server version: 8.3.3; pg_dump version: 8.2.6
> > pg_dump: aborting because of version mismatch (Use the
> -i option to proceed
> > anyway.)
> > pg_dump: *** aborted because of error
> >
> > and likewise when I log into the psql client:
> > WARNING: You are connected to a server with major
> version 8.3,
> > but your psql client is major version 8.2. Some
> backslash commands,
> > such as \d, might not work properly.
> >
> > How could this have happened? I really need to be able
> to back up my
> > information. I reinstalled the software and it
> didn't change anything. What
> > do I do to upgrade pg_dump to the newest version?
> 
> What OS are you running on?
> 
> How did you perform this upgrade?  In a packaged form like
> rpm?  or
> did you compile from source?

yup, the postgres clients are usually shipped in their own packages, separate 
from the server package. I do have to manually upgrade the client packages on a 
Debian system.






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

[ADMIN] Recommended number of connections

[M C]

What are the "maximum" amount of "connections" allowed to "postgres"? and what 
would be a resonable amount of connection/processes to see based on user 
connections?



  

Re: [ADMIN] Heavy postgres process

[Scott Marlowe]

On Wed, Sep 17, 2008 at 12:31 PM, Kenneth Marshall <[EMAIL PROTECTED]> wrote:
> Gee,
>
> Going to Oracle does seem a bit like throwing the baby out with
> the bath water.

Especially considering the performance increase from pgsql 7.4 to 8.3
is humongous.  I'd say most operations are 2 to 4 times as fast and
some operations are many 10 to 100 times faster.

But for Vivek the real, first priority is to get his 7.4.5 server
updated to the latest 7.4 version to make sure his data stays safe.

-- 
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] Recommended number of connections

[Tomeh, Husam]

The maximum number of Postgres connections you can have may be
controlled by adjusting the max_connections PG parameter based on your
anticipated connections needs and requirements.  

 

Make sure that your Kernel's related parameters are also configured
properly to accommodate your max_connections settings (e.g, in Linux,
SEM* kernel's parameters) when increasing the max_connections parameter.

 

http://www.postgresql.org/docs/8.2/static/runtime-config-connection.html
 

http://www.postgresql.org/docs/8.2/static/kernel-resources.html#SYSVIPC
 

 

Regards,

  Husam 



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of M C
Sent: Wednesday, September 17, 2008 10:01 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Recommended number of connections

 

What are the "maximum" amount of "connections" allowed to "postgres"?
and what would be a resonable amount of connection/processes to see
based on user connections?

 


**
This message contains confidential information intended only for the use of the 
addressee(s) named above and may contain information that is legally 
privileged.  If you are not the addressee, or the person responsible for 
delivering it to the addressee, you are hereby notified that reading, 
disseminating, distributing or copying this message is strictly prohibited.  If 
you have received this message by mistake, please immediately notify us by 
replying to the message and delete the original message immediately thereafter.

Thank you.

   FADLD Tag
**

Re: [ADMIN] unable to backup database -- psql not up to date

[Daniel Christopher]

What OS are you running on?

How did you perform this upgrade?  In a packaged form like rpm?  or
did you compile from source?

  

I am running FreeBSD 6.1

I compiled from source, and followed the instructions in the 
documentation about upgrading to a new major release (pg_dumpall, 
shutdown, mv old version, install new version, restore)


--
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] Heavy postgres process

[Guido Barosio]

Getting to the latest 7.4 server involves the same as going to the
latest 8.x server, AFAIK, if we take in consideration that 7.4.8
requires a dump & restore (meaning downtime).

I would rather go after the latest 8.x server

2 cents.

(It seems that Vivek works for Infosys, and that may explain the
reason for an Oracle migration in the future. They are prolly plenty
of Oracle DBA's working there, cheaper and reusable for sure if you
think that often the kind of clients they handle are already
oracle'ized )

gb.-

On Wed, Sep 17, 2008 at 3:58 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 17, 2008 at 12:31 PM, Kenneth Marshall <[EMAIL PROTECTED]> wrote:
>> Gee,
>>
>> Going to Oracle does seem a bit like throwing the baby out with
>> the bath water.
>
> Especially considering the performance increase from pgsql 7.4 to 8.3
> is humongous.  I'd say most operations are 2 to 4 times as fast and
> some operations are many 10 to 100 times faster.
>
> But for Vivek the real, first priority is to get his 7.4.5 server
> updated to the latest 7.4 version to make sure his data stays safe.
>



-- 
http://www.linkedin.com/in/gbarosio

-- 
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] Heavy postgres process

[Kenneth Marshall]

Gee,

Going to Oracle does seem a bit like throwing the baby out with
the bath water. For pretty much any use, we found that Oracle requires
many more hardware and management resources than PostgreSQL needs for
the same performance. Make certain that you load test your Oracle "upgrade"
to ensure that you can meet your service requirements.

On the performance problem, I think that the 83MB is the shared_buffers
for postgres and is shared between all backends. According to the FreeBSD
site, sbwait happens when a thread is trying to send or receive data on
a blocking socket. I would try a couple of sample queries that your app
generates, to time them, but it may be your Apache process that is using
the lion's share of your memory.

Cheers,
Ken

On Wed, Sep 17, 2008 at 11:18:24PM +1000, Vivek_Sharan wrote:
> Yes that's true and that's planned. We will migrate to Oracle. But as of now 
> need some pointers on solving the problem in hand.
> 
> Regards,
> Vivek
> 
> 
> 
> -Original Message-
> From: Guido Barosio [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 17, 2008 6:39 PM
> To: Vivek_Sharan
> Cc: Scott Marlowe; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Heavy postgres process
> 
> Well, the answer is shor Vivekt:
> 
> Upgrade that postgresql ASAP, it's too way old.
> 
> gb.-
> 
> On Wed, Sep 17, 2008 at 9:29 AM, Vivek_Sharan <[EMAIL PROTECTED]> wrote:
> > I'm using postgres 7.4.5
> >
> > Regards,
> > Vivek
> >
> >
> >
> > -Original Message-
> > From: Guido Barosio [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, September 16, 2008 8:08 PM
> > To: Vivek_Sharan
> > Cc: Scott Marlowe; pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Heavy postgres process
> >
> > On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <[EMAIL PROTECTED]> wrote:
> >> Thanks for the information so far
> >> My Application runs on FreeBSd box and main technological component are 
> >> Apache and mod Perl, database is postgres. I have already scanned 
> >> pg_stat_activity and pg_listener table but could get any clue. 
> >> Pg_stat_activity shows list of all idle processes but command 
> >> (current_query) column is empty. So I cannot make out what these processes 
> >> are doing.
> >> TOP on this server doesn't have any option available to further break down 
> >> processes. And hitting 'M; did change anything because this is not 
> >> available with top on this server. Following is the output of top if 
> >> filtered for only postgres user
> >>
> >> *
> >> last pid: 92308;  load averages:  0.00,  0.03,  0.05
> >> 78 processes:  2 running, 76 sleeping
> >> CPU states:  1.6% user,  0.0% nice,  3.4% system,  0.0% interrupt, 94.9% 
> >> idle
> >> Mem: 413M Active, 2122M Inact, 534M Wired, 140M Cache, 199M Buf, 533M Free
> >> Swap: 4096M Total, 3880K Used, 4092M Free
> >>
> >>  PID USERNAME  PRI NICE  SIZERES STATE  C   TIME   WCPUCPU COMMAND
> >> 90976 postgres2   0 83568K 76016K sbwait 2   0:32  2.83%  2.83% 
> >> postgres
> >> 90963 postgres2   0 83396K 75876K sbwait 2   0:25  1.37%  1.37% 
> >> postgres
> >> 90919 postgres2   0 83808K 76244K sbwait 1   0:32  0.39%  0.39% 
> >> postgres
> >> 87341 postgres2   0  6388K   756K select 3   2:35  0.00%  0.00% 
> >> postgres
> >> 87340 postgres2   0  7200K  1224K select 0   1:41  0.00%  0.00% 
> >> postgres
> >> 90961 postgres2   0 83580K 76008K sbwait 0   0:30  0.00%  0.00% 
> >> postgres
> >> 90920 postgres2   0 83636K 76068K sbwait 0   0:29  0.00%  0.00% 
> >> postgres
> >> 90934 postgres2   0 83664K 76012K sbwait 0   0:27  0.00%  0.00% 
> >> postgres
> >> 90924 postgres2   0 83408K 75872K sbwait 0   0:25  0.00%  0.00% 
> >> postgres
> >> 90915 postgres2   0 79292K 72664K sbwait 0   0:23  0.00%  0.00% 
> >> postgres
> >> 90955 postgres2   0 79644K 73040K sbwait 0   0:22  0.00%  0.00% 
> >> postgres
> >> 90979 postgres2   0 78904K 72260K sbwait 0   0:17  0.00%  0.00% 
> >> postgres
> >> 87339 postgres2   0 74756K   672K select 1   0:12  0.00%  0.00% 
> >> postgres
> >> 90921 postgres2   0 75504K 59848K sbwait 3   0:01  0.00%  0.00% 
> >> postgres
> >> 90927 postgres2   0 75540K 59296K sbwait 3   0:01  0.00%  0.00% 
> >> postgres
> >> 90962 postgres2   0 75524K 56960K sbwait 0   0:01  0.00%  0.00% 
> >> postgres
> >> 90923 postgres2   0 75540K 57584K sbwait 1   0:01  0.00%  0.00% 
> >> postgres
> >> 90914 postgres2   0 75552K 57776K sbwait 1   0:01  0.00%  0.00% 
> >> postgres
> >> 90917 postgres2   0 75524K 57256K sbwait 3   0:01  0.00%  0.00% 
> >> postgres
> >> 90922 postgres2   0 75504K 57352K sbwait 1   0:01  0.00%  0.00% 
> >> postgres
> >> 90918 postgres2   0 75508K 57748K sbwait 3   0:01  0.00%  0.00% 
> >> postgres
> >> 90933 postgres2   0 75540K 53728K sbwait 2   0:01  0.00%  0.00% 
> >> postgres
> >> 90926 postgres2   0 75484K 54928K sbwait 3   0:01  0.00%  0.00% 
> >> postgres
> >> 90

Re: [ADMIN] Recommended number of connections

[Scott Marlowe]

On Wed, Sep 17, 2008 at 11:01 AM, M C <[EMAIL PROTECTED]> wrote:
> What are the "maximum" amount of "connections" allowed to "postgres"? and
> what would be a resonable amount of connection/processes to see based on
> user connections?

That really depends on what you're doing with it.  If each connection
is doing a simple transaction on a small indexed set, then you can
probably have hundreds of connections open on an even modest machine.
OTOH, if you're running reports across hundreds of megs with a large
work_mem setting then you might be best with only a few dozen or so
connections.

So, what are ya doing and how many users are you supporting at once?

-- 
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] Heavy postgres process

[Scott Marlowe]

On Wed, Sep 17, 2008 at 1:43 PM, Guido Barosio <[EMAIL PROTECTED]> wrote:
> Getting to the latest 7.4 server involves the same as going to the
> latest 8.x server, AFAIK, if we take in consideration that 7.4.8
> requires a dump & restore (meaning downtime).

7.4.7 to 7.4.8 does NOT require a complete dump and restore.  Been
there, done it and got the tshirt.  There's a minor fix in the
catalogs that you can get by a single SQL command.

> I would rather go after the latest 8.x server
>
> 2 cents.

Me too.  But the update to 7.4.21 or whatever the latest version is
does NOT require a dump reload, and unless you need that one line sql
fix, you don't even have to do that.

I quote the 7.4.8 release notes:

A dump/restore is not required for those running 7.4.X. However, it is
one possible way of handling two significant security problems that
have been found in the initial contents of 7.4.X system catalogs. A
dump/initdb/reload sequence using 7.4.8's initdb will automatically
correct these problems.

and further down

 If you wish not to do an initdb, perform the following procedures
instead. As the database superuser, do:

BEGIN;
UPDATE pg_proc SET proargtypes[3] = 'internal'::regtype
WHERE pronamespace = 11 AND pronargs = 5
 AND proargtypes[2] = 'cstring'::regtype;
-- The command should report having updated 90 rows;
-- if not, rollback and investigate instead of committing!
COMMIT;

Next, if you have installed contrib/tsearch2, do:

and so on.

> (It seems that Vivek works for Infosys, and that may explain the
> reason for an Oracle migration in the future. They are prolly plenty
> of Oracle DBA's working there, cheaper and reusable for sure if you
> think that often the kind of clients they handle are already
> oracle'ized )

especially if you can piggy back on someone else's oracle server
that's already running.

-- 
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] Heavy postgres process

[Guido Barosio]

Where can I buy that t-shirt? :)

Hmm, you are right. My intention was to explain that the case for a
7.4.21 (wich AFAIK is the latest one of the 7.4 series) is the same as
for the v8 series of the server. 7.4.8 seems to be the latest one
before a dump & restore would be a good idea. That was my point.

Apologies for the noise!

Gb.-

On Wed, Sep 17, 2008 at 5:07 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 17, 2008 at 1:43 PM, Guido Barosio <[EMAIL PROTECTED]> wrote:
>> Getting to the latest 7.4 server involves the same as going to the
>> latest 8.x server, AFAIK, if we take in consideration that 7.4.8
>> requires a dump & restore (meaning downtime).
>
> 7.4.7 to 7.4.8 does NOT require a complete dump and restore.  Been
> there, done it and got the tshirt.  There's a minor fix in the
> catalogs that you can get by a single SQL command.
>
>> I would rather go after the latest 8.x server
>>
>> 2 cents.
>
> Me too.  But the update to 7.4.21 or whatever the latest version is
> does NOT require a dump reload, and unless you need that one line sql
> fix, you don't even have to do that.
>
> I quote the 7.4.8 release notes:
>
> A dump/restore is not required for those running 7.4.X. However, it is
> one possible way of handling two significant security problems that
> have been found in the initial contents of 7.4.X system catalogs. A
> dump/initdb/reload sequence using 7.4.8's initdb will automatically
> correct these problems.
>
> and further down
>
>  If you wish not to do an initdb, perform the following procedures
> instead. As the database superuser, do:
>
> BEGIN;
> UPDATE pg_proc SET proargtypes[3] = 'internal'::regtype
> WHERE pronamespace = 11 AND pronargs = 5
> AND proargtypes[2] = 'cstring'::regtype;
> -- The command should report having updated 90 rows;
> -- if not, rollback and investigate instead of committing!
> COMMIT;
>
> Next, if you have installed contrib/tsearch2, do:
>
> and so on.
>
>> (It seems that Vivek works for Infosys, and that may explain the
>> reason for an Oracle migration in the future. They are prolly plenty
>> of Oracle DBA's working there, cheaper and reusable for sure if you
>> think that often the kind of clients they handle are already
>> oracle'ized )
>
> especially if you can piggy back on someone else's oracle server
> that's already running.
>



-- 
http://www.linkedin.com/in/gbarosio

-- 
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] Heavy postgres process

[Scott Marlowe]

On Wed, Sep 17, 2008 at 2:23 PM, Guido Barosio <[EMAIL PROTECTED]> wrote:
> Where can I buy that t-shirt? :)
>
> Hmm, you are right. My intention was to explain that the case for a
> 7.4.21 (wich AFAIK is the latest one of the 7.4 series) is the same as
> for the v8 series of the server. 7.4.8 seems to be the latest one
> before a dump & restore would be a good idea. That was my point.
>
> Apologies for the noise!

No!  it's a very valid point.  it's just that you don't have to dump
restore after 7.4.7 to get the security fixes in the catalog, there's
a sql only way to avoid that.Plus, if you don't do it, you're only
missing one small security update.

The data eating bugs in early 7.4 releases are plentiful and ugly.  I
was just scanning through the release notes and there were data eating
bugs in 7.4.6 through 7.4.17.  Lots of them.  It's more important to
upgrade to squash those and forget about the security bug than to let
the security bug hold you back on the update.

-- 
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] unable to backup database -- psql not up to date

[Lennin Caro]

> From: dbchristopher <[EMAIL PROTECTED]>

> I upgraded to postgresql 8.3.3 a few weeks ago, and it seems
> that while the
> server was upgraded, but for some reason none of the
> accompanying software
> was (pg_dump, psql, etc)
> 
> I get this error when I try to back up the database:
> pg_dump: server version: 8.3.3; pg_dump version: 8.2.6
> pg_dump: aborting because of version mismatch (Use the -i
> option to proceed
> anyway.)
> pg_dump: *** aborted because of error
> 
> and likewise when I log into the psql client:
> WARNING: You are connected to a server with major version
> 8.3,
> but your psql client is major version 8.2. Some backslash
> commands,
> such as \d, might not work properly.
> 
> How could this have happened? I really need to be able to
> back up my
> information. I reinstalled the software and it didn't
> change anything. What
> do I do to upgrade pg_dump to the newest version?
> -- 
> View this message in context:
> 
This happend wend you use a old psql client to connect a new postgresql. Maybe 
you have two o more postgresql install in your computer. check the psql file 
and the path of this. 

http://www.nabble.com/unable-to-backup-databasepsql-not-up-to-date-tp19520466p19520466.html
> Sent from the PostgreSQL - admin mailing list archive at
> Nabble.com.
> 
> 
> -- 
> 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] unable to backup database -- psql not up to date

[Tom Lane]

Daniel Christopher <[EMAIL PROTECTED]> writes:
> I compiled from source, and followed the instructions in the 
> documentation about upgrading to a new major release (pg_dumpall, 
> shutdown, mv old version, install new version, restore)

Well, at that level of detail, it should've worked.  So the problem is
somewhere in the details you left out ...

Maybe you have a PATH that contains an older PG version in some other
directory?

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] Heavy postgres process

[Tom Lane]

"Guido Barosio" <[EMAIL PROTECTED]> writes:
> Getting to the latest 7.4 server involves the same as going to the
> latest 8.x server, AFAIK, if we take in consideration that 7.4.8
> requires a dump & restore (meaning downtime).

(a) it does *not* require a dump and restore

(b) if you ignore the recommendations in the 7.4.8 notes and just
upgrade without doing anything else, then what you will have is a
7.4 system that still contains two security vulnerabilities.
Vulnerabilities that are in your 7.4.5 system today.  How are you
worse off?

This is about the lamest excuse for not updating I've ever heard.

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] unable to backup database -- psql not up to date

[Melissa Peterson]

Just a thought, but did you compile from the ports? The PostgreSQL
client and server are separate ports on FreeBSD. If I recall correctly,
the updated client typically isn't handled as a dependency when the
server is updated, so you would likely have to handle them separately.

Mel Peterson
R&D Engineer
[EMAIL PROTECTED]
GigaCrete, Inc.
6775 Speedway Boulevard, Suite M-104
Las Vegas, NV  89115
Phone 702-643-6363
Fax 702-543-7010
www.gigacrete.com
 

B U I L D  S T R O N G.  B U I L D  F O R W A R D.
 

This message and any attachments are solely for the intended recipient
and may contain confidential or privileged information.  If you are not
the intended recipient, any disclosure, copying, use, or distribution of
the information included in this message and any attachments is strictly
prohibited.  If you have received this communication in error, please
notify us by reply email and immediately and permanently delete this
message and any attachments.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Daniel
Christopher
Sent: Wednesday, September 17, 2008 11:39 AM
To: Jonathan Nalley; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] unable to backup database -- psql not up to date


> What OS are you running on?
>
> How did you perform this upgrade?  In a packaged form like rpm?  or
> did you compile from source?
>
>   
I am running FreeBSD 6.1

I compiled from source, and followed the instructions in the 
documentation about upgrading to a new major release (pg_dumpall, 
shutdown, mv old version, install new version, restore)

-- 
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] Postgres && Swap

[steve]

Is there any negative effects by doing this?

The swappiness is sitting on the default 60 at the moment.

On Wed, 17 Sep 2008 00:46:13 -0600, "Scott Marlowe"
<[EMAIL PROTECTED]> wrote:
> On Tue, Sep 16, 2008 at 11:30 PM,  <[EMAIL PROTECTED]> wrote:
>> Hi All
>>
>>
>> I recently made a change to my Postgres Server and upped the
> max_fsm_page
>> size to 6
>> Since then, Postgres has been using about 30-80MB of swap space.
>>
>> This box has 4GB of RAM. All up Postgres has not been allocated no more
>> than 3G
>>
>> Is this swapping something to be worried about?
> 
> I recommend  turning swappiness down in linux that should stop pg from
> getting prematurely swapped out like that.

-- 
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] Postgres && Swap

[Scott Marlowe]

Well, there's plenty of negatives to not doing it.  Like postgresql's
large shared_buffers getting swapped out to make more space for disk
buffers.  So, pgsql goes to grab data from shared buffers and has to
wait for the OS to swap them back in.

On Wed, Sep 17, 2008 at 4:28 PM,  <[EMAIL PROTECTED]> wrote:
> Is there any negative effects by doing this?
>
> The swappiness is sitting on the default 60 at the moment.
>
> On Wed, 17 Sep 2008 00:46:13 -0600, "Scott Marlowe"
> <[EMAIL PROTECTED]> wrote:
>> On Tue, Sep 16, 2008 at 11:30 PM,  <[EMAIL PROTECTED]> wrote:
>>> Hi All
>>>
>>>
>>> I recently made a change to my Postgres Server and upped the
>> max_fsm_page
>>> size to 6
>>> Since then, Postgres has been using about 30-80MB of swap space.
>>>
>>> This box has 4GB of RAM. All up Postgres has not been allocated no more
>>> than 3G
>>>
>>> Is this swapping something to be worried about?
>>
>> I recommend  turning swappiness down in linux that should stop pg from
>> getting prematurely swapped out like that.
>

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