Re: [GENERAL] "invalid memory alloc request size" + "Could not open file "pg_clog/XXXX"

[Albe Laurenz]

scheu_postgresql wrote:
> In my Postgresql 8.4.0 server, since this morning some tables are
unavailable, see example below :
> 
> --> pg_dump MY_DB > bkp_MY_DB.dmp
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  invalid memory alloc
request size 18446744073709551613
> pg_dump: The command was: COPY . (col1,
col2, ...).
> 
> --> vacuum analyze . ;
> WARNING:  terminating connection because of crash of
another server process
> DETAIL:  The postmaster has commanded this server process
to roll back the current
> transaction and exit, because another server process exited abnormally
and possibly corrupted shared
> memory.
> HINT:  In a moment you should be able to reconnect to the
database and repeat your
> command.
> 
> --> select * from . ;
> ERROR:  invalid memory alloc request size 18446744073709551613
> 
> --> server log file
> Feb 29 05:31:44 my_server postgres[6686]: [17-1] user=,db= LOG:
server process (PID 3887) was
> terminated by signal 11: Segmentation fault
> Feb 29 05:31:44 my_server postgres[6686]: [18-1] user=,db= LOG:
terminating any other active server
> processes
> Feb 29 05:31:44 my_server postgres[6686]: [19-1] user=,db= LOG:  all
server processes terminated;
> reinitializing
> Feb 29 05:31:44 my_server postgres[3892]: [20-1] user=,db= LOG:
database system was interrupted; last
> known up at 2012-02-29 05:22:33 CET
> Feb 29 05:31:44 my_server postgres[3892]: [21-1] user=,db= LOG:
database system was not properly shut
> down; automatic recovery in progress
> Feb 29 05:31:44 my_server postgres[3892]: [22-1] user=,db= LOG:  redo
starts at 10/67C2A3B8
> Feb 29 05:31:45 my_server postgres[3892]: [23-1] user=,db= LOG:
record with zero length at
> 10/68BCF990
> Feb 29 05:31:45 my_server postgres[3892]: [24-1] user=,db= LOG:  redo
done at 10/68BCF960
> Feb 29 05:31:45 my_server postgres[3892]: [25-1] user=,db= LOG:  last
completed transaction was at log
> time 2012-02-29 05:31:42.618352+01
> Feb 29 05:31:45 my_server postgres[6686]: [20-1] user=,db= LOG:
database system is ready to accept
> connections
> Feb 29 05:32:52 my_server postgres[4469]: [21-1]
user=[unknown],db=[unknown] LOG:  incomplete startup
> packet
> Feb 29 05:33:52 my_server postgres[6686]: [21-1] user=,db= LOG:
server process (PID 5151) was
> terminated by signal 11: Segmentation fault
> Feb 29 05:33:52 my_server postgres[6686]: [22-1] user=,db= LOG:
terminating any other active server
> processes
> Feb 29 05:33:52 my_server postgres[6686]: [23-1] user=,db= LOG:  all
server processes terminated;
> reinitializing
> Feb 29 05:33:52 my_server postgres[5152]: [24-1] user=,db= LOG:
database system was interrupted; last
> known up at 2012-02-29 05:31:45 CET
> Feb 29 05:33:52 my_server postgres[5152]: [25-1] user=,db= LOG:
database system was not properly shut
> down; automatic recovery in progress
> Feb 29 05:33:52 my_server postgres[5152]: [26-1] user=,db= LOG:
record with zero length at
> 10/68BCF9D8
> Feb 29 05:33:52 my_server postgres[5152]: [27-1] user=,db= LOG:  redo
is not required
> Feb 29 05:33:52 my_server postgres[5153]: [24-1] user=match,db=MY_DB
FATAL:  the database system is in
> recovery mode
> Feb 29 05:33:52 my_server postgres[6686]: [24-1] user=,db= LOG:
database system is ready to accept
> connections
> Feb 29 05:37:19 my_server postgres[6686]: [25-1] user=,db= LOG:
server process (PID 8065) was
> terminated by signal 11: Segmentation fault
> Feb 29 05:37:19 my_server postgres[6686]: [26-1] user=,db= LOG:
terminating any other active server
> processes
> Feb 29 05:37:19 my_server postgres[6686]: [27-1] user=,db= LOG:  all
server processes terminated;
> reinitializing
> Feb 29 05:37:19 my_server postgres[8066]: [28-1] user=,db= LOG:
database system was interrupted; last
> known up at 2012-02-29 05:33:52 CET
> Feb 29 05:37:19 my_server postgres[8066]: [29-1] user=,db= LOG:
database system was not properly shut
> down; automatic recovery in progress
> Feb 29 05:37:19 my_server postgres[8066]: [30-1] user=,db= LOG:  redo
starts at 10/68BCFA20
> Feb 29 05:37:19 my_server postgres[8066]: [31-1] user=,db= LOG:
record with zero length at
> 10/68BD5BD0
> Feb 29 05:37:19 my_server postgres[8066]: [32-1] user=,db= LOG:  redo
done at 10/68BD5BA0
> Feb 29 05:37:19 my_server postgres[8066]: [33-1] user=,db= LOG:  last
completed transaction was at log
> time 2012-02-29 05:35:44.468968+01
> Feb 29 05:37:19 my_server postgres[6686]: [28-1] user=,db= LOG:
database system is ready to accept
> connections
> Feb 29 05:38:27 my_server postgres[8639]: [29-1]
user=[unknown],db=[unknown] LOG:  incomplete startup
> packet
> Feb 29 05:38:53 my_server postgres[6686]: [29-1] user=,db= LOG:
server process (PID 8809) was
> terminated by signal 11: Segmentation fault
> 
> 
> I have tried to restart Postgresql but it did not solve these issues
> I cannot backup the full database because some tables have become
unreadable
> I have got 7 databases on this server and only 2 have got this pro

Re: [GENERAL] what Linux to run

[Vincent Veyron]

Le mercredi 29 février 2012 à 11:31 -0500, Gary Chambers a écrit :
> > Note that Ubuntu also comes in a GUI free server edition as well.  I can
> > definitely state that Ubuntu 10.04 LTS Server edition is rock solid stable
> 
> +1
> 
> I've been running 10.04 LTS Server for over three years (on a Dell PowerEdge
> 2850) using Martin Pitt's PostgreSQL 9.1 PPA.
> 

Hi,

I find that using the Dedian distribution (which Ubuntu is based on)
makes the process of building a server very simple and reliable. Below
are the notes I took for the last one; you'll have most steps outlined;
it uses a LAMP stack made of Linux+Apache+Mod_Perl+Postgresql.

The one I built before this one was up for 550 days, serving 5 users
full time. The machine is the cheapest server at online.net (dedibox, 15
€/month)), it serves 100 requests/seconds, session validation included.
I only took it down because it required a bios update. 

#
#Install Notes
#

Debian V6.0.0 (64BITS)
Date 2012 01 26

#installation initiale avec sda1,2 et 3 seulement
apt-get install parted
#après installation, création des partitions logiques 5,6,7
#et remount de /var, /home, /var/log dessus

#
#ssh
#

#edit /etc/ssh/sshd_config
# Authentication:
LoginGraceTime 60
PermitRootLogin no
StrictModes yes
#pas plus de quatre essais (message dans les logs à partir de la
troisième erreur)
MaxAuthTries 4
AllowUsers X 

#edit .ssh/config on workstation

#ssh displays funky characters
dpkg-reconfigure locales
  207. fr_FR ISO-8859-1   
  208. fr_FR.UTF-8 UTF-8  
  209. fr_FR@euro ISO-8859-15 

default : fr_FR@euro

#désactiver les programmes lancés par défaut et non utilisés
update-rc.d -f bind9 remove 
update-rc.d -f mdadm remove 
update-rc.d -f portmap remove 

#run 
apt-get update && apt-get upgrade

#utilities
apt-get install gcc rsync sqlite3 make
apt-get install git 

#
#Postgresql
#
apt-get install postgresql postgresql-client postgresql-plperl-8.4

createuser -d X

#pg_dumpall && pg_restore cluster from workstation

#
#Apache
#
apt-get install apache2-mpm-worker libapache2-request-perl
libapache2-mod-perl2 libapache2-mod-apreq2 apache2.2-common 

#configure logrotate : edit /etc/logrotate.d/apache2

#enable apache2 modules
a2enmod ssl rewrite apreq

#
#install perl modules
#

#pre-compiled binaries for DBI & DBD::Pg & sqlite3  

apt-get install libapache-dbi-perl libdbd-pg-perl libdbd-sqlite3-perl

Done.

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique


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

Re: [GENERAL] what Linux to run

[Ivan Voras]

On 28/02/2012 18:17, Rich Shepard wrote:
> On Tue, 28 Feb 2012, mgo...@isstrucksoftware.net wrote:
> 
>> If we move to Linux, what is the preferred Linux for running Postgres
>> on. This machine would be dedicated to the database only.
> 
> Michael,
> 
>   There is no 'preferred' linux distribution; the flame wars on this topic
> died out a decade or so ago.
> 
>   From what you write, I would suggest that you look at one of the Ubunutus
> . Either the KDE or Gnome versions will appear
> Microsoft-like; the Xfce version appears more like CDE. Download a bootable
> .iso (a.k.a. 'live disk) and burn it to a cdrom and you can try it without
> .installing it. If you do like it, install it from the same disk.
> 
>   The Ubuntus boot directly into the GUI and that tends to be more
> comfortable for newly defenestrated users. If you like that, but want the
> more open and readily-available equivalent, install Debian. The ubuntus are
> derivatives of debian.

One interesting thing I've discovered recently is that there is a HUGE
difference in performance between CentOS 6.0 and Ubuntu Server 10.04
(LTS) in at least the memory allocator and possibly also multithreading
libraries (in favour of CentOS). PostgreSQL shouldn't be particularly
sensitive to either of these, but it makes me wonder what else is
suboptimal in Ubuntu.




signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] what Linux to run

[Ivan Voras]

On 28/02/2012 17:57, mgo...@isstrucksoftware.net wrote:
> Our application runs on Windows, however we have been told that we can
> pick any OS to run our server on.  I'm thinking Linux because from
> everything I've read, it appears to be a better on performance and there
> are other features like tablespaces which we could take advantage of. 
> On our hosted solution, the application runs in a Software as a Service
> model and being able to keep each companies tables in their own table
> space would be nice.  Additionally it appears that there are a lot more
> ways to tune the engine if we need to than under windows, plus the
> capability to hold more connections.
> 
> If we move to Linux, what is the preferred Linux for running Postgres
> on.  This machine would be dedicated to the database only. 
> 
> I'd like a recommendation for both a GUI hosted version and a non-GUI
> version.  I haven't used Linux in the past but did spend several year s
> in a mixed Unix and IBM mainframe environment at the console level.

Hi,

PostgreSQL administration would not benefit much from a GUI, as it is
basically centered around editing and tuning configuration files (either
its or the OS's).

For Linux, if you want stability and decent performance, you should
probably choose either CentOS, or if you want commercial support, Red
Hat Enterprise Linux (which is basically the same thing, only commercial).

Personally, I'd recommend FreeBSD (it's not a Linux, it's more
Unix-like) but I'm probably biased ;)




signature.asc
Description: OpenPGP digital signature

[GENERAL] Allowed DML on replicas?

[François Beausoleil]

Hi!  

I'm getting ready to build a reporting server, one where long-running queries 
and backups will be taken from. This new server will be a slave from the master 
where all changes are done. Some reports are better expressed with extracting a 
subset of the data and leaving it in a table to be reused, until the report set 
is done.

In my specific case, I have a table with ~30M rows representing Twitter users. 
When I JOIN this table with the interactions I have on hand, it takes a long 
time, because PostgreSQL ends up doing a full table scan of the personas table. 
To make subsequent reporting steps easier, I do the JOIN only once, and write 
the results to a table.

My question is:

* Can a new schema be created on a replica?
* Will this impact replication in any way?
* If I can't, what would you advise? dump / reload in a separate database 
without dropping the table, to keep the extra schemas around?

Thanks!
François Beausoleil



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

[GENERAL] 2 x duplicate key value violation: which exception comes first ?

[Marc Mamin]

Hello,

I've a table with 1 primary key and 1 unique index.

I would like to handle differently the duplicate key violations
depending on which constraints are affected.
(Within a plpgsql CATCH block, using SQLERRM).

It seems, that when both constraints would be violated, the exception is
always about the primary key.

Can I rely on that or should I expect the exception order being random ?

Thanks,

Marc Mamin

Re: [GENERAL] Privilege on schema 'public' not revokable

[Vincent de Phily]

On Wednesday 29 February 2012 14:14:19 Tom Lane wrote:
> "David Johnston"  writes:
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Vincent de Phily
> > 
> >> [ this doesn't do anything: ]
> >> db_foo=# REVOKE CREATE ON SCHEMA public FROM foouser;
> > 
> > "foouser" is obtaining its permission to "CREATE" on the "public " schema
> > via global/PUBLIC permissions (i.e., via inheritance).  Revoking only
> > removes an explicitly granted permission but does not institute a block
> > for
> > an inherited permission.  You would need to revoke the global permission
> > to
> > CREATE on "public" and then only GRANT it back to those users/roles that
> > you wish to have it - all others will then effectively lose that ability.
> Correct.  Note where it says in the GRANT manual page that a user's
> effective rights are the sum of those granted to PUBLIC, those granted
> directly to him, and those granted to roles he is a member of.  Rights
> granted to PUBLIC are available to everybody, full stop, and can't be
> selectively blocked.
> 
>   regards, tom lane

Ah thanks, that's what I had been thinking during the night, but you confirmed 
it. Leaves me wondering how I lost the public permission in the first place, 
but hey :p

Also, how do I see the privileges granted to public on schema ?

-- 
Vincent de Phily

[GENERAL] "Compressed data is corrupt"

[Matthias Leisi]

I have a behaviour of Postgres which I do not understand (and thus can
not fix...). When inserting into a log-like table, I get the error
message "compressed data is corrupt" for certain (rare) combination of
values. When I re-create the same table structure from scratch and
insert the same data in to that table, the error does not appear.

Situation which triggers the error:

dnswl=# \d+ requesthistory
 Table "public.requesthistory"
 Column  |  Type  | Modifiers  |
Storage  | Description
-+++--+-
 requestip   | inet   | not null   | main |
 requesthostname | character varying(255) | not null   | extended |
 requestdate | date   | not null   | plain|
 requestcount| integer| not null default 0 | plain|
Indexes:
"requesthistory_pkey" PRIMARY KEY, btree (requestip, requestdate)
Has OIDs: no
dnswl=# insert into requesthistory values ('209.166.168.6',
'myhostname', '2012-02-29', 23);
ERROR:  compressed data is corrupt

Situation which does not lead to the error:

dnswl=# \d+ testip
  Table "public.testip"
  Column  |  Type  | Modifiers  | Storage  | Description
--+++--+-
 ip   | inet   | not null   | main |
 hostname | character varying(255) | not null   | extended |
 mydate   | date   | not null   | plain|
 count| integer| not null default 0 | plain|
Indexes:
"testip_pkey" PRIMARY KEY, btree (ip, mydate)
Has OIDs: no
dnswl=# insert into testip values ('209.166.168.6', 'myhostname',
'2012-02-29', 23);
INSERT 0 1

Changing the hostname, date or count fields does not change the
situation. Changing the IP address slightly (eg from "..6" to "..5")
makes the error disappear.

Any clue what may be going on? Any more things I should try and test?

Running Postgresql 8.4.7 on an openSuSE machine (64bit).

-- Matthias

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

Re: [GENERAL] Allowed DML on replicas?

[Guillaume Lelarge]

On Thu, 2012-03-01 at 08:24 -0500, François Beausoleil wrote:
> Hi!  
> 
> I'm getting ready to build a reporting server, one where long-running queries 
> and backups will be taken from. This new server will be a slave from the 
> master where all changes are done. Some reports are better expressed with 
> extracting a subset of the data and leaving it in a table to be reused, until 
> the report set is done.
> 
> In my specific case, I have a table with ~30M rows representing Twitter 
> users. When I JOIN this table with the interactions I have on hand, it takes 
> a long time, because PostgreSQL ends up doing a full table scan of the 
> personas table. To make subsequent reporting steps easier, I do the JOIN only 
> once, and write the results to a table.
> 
> My question is:
> 
> * Can a new schema be created on a replica?

No if you use a HotStandby. Yes if you use another kind of replication
(Slony for example).

> * Will this impact replication in any way?

No, because you can't with a HotStandby. No if you use another kind of
replication (Slony for example).

> * If I can't, what would you advise? dump / reload in a separate database 
> without dropping the table, to keep the extra schemas around?
> 

It depends. Using Slony is one way to do it.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com


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

Re: [GENERAL] what Linux to run

[Scott Marlowe]

On Thu, Mar 1, 2012 at 5:25 AM, Ivan Voras  wrote:
>
> One interesting thing I've discovered recently is that there is a HUGE
> difference in performance between CentOS 6.0 and Ubuntu Server 10.04
> (LTS) in at least the memory allocator and possibly also multithreading
> libraries (in favour of CentOS). PostgreSQL shouldn't be particularly
> sensitive to either of these, but it makes me wonder what else is
> suboptimal in Ubuntu.

To be fair, RHEL6 was released 7 months after Ubuntu 10.04.  But
Redhat is pretty good at kernel patching for optimizations ertc. I'd
be more interested in comparisons with ubuntu 12.04, due out next
month.

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

[GENERAL] 100% cpu usage on some postmaster processes kill the complete database

[Paul Dunkler]

Hi List,

we are currently running a rather large postgresql-installation with 
approximately 4k Transactions and 50k index scans per second.

In the last days on some times of the day (irregular - 3-4 times a day), some 
of the postmaster processes are running with 100% cpu usage. That leads to a 
totally breakdown of the query execution. We see tons of statements which are 
correctly automatically aborted by our statement_timeout set to 15 seconds. I 
tried to search, but do not really recognize what the problem could be there...

Some things i have checked:
- We are not running any bulk jobs or maintenance scripts at this time
- No system errors in any logs during that slowdowns
- I/O Performance seems fine. No high IO Wait amount... But IO Write totally 
drops in that times because it seems that no postgres process can perform any 
update

I just installed a script, which prints me out the top and ps axf information 
for facing out the problem. I will post a snippet of the top here:

> top - 15:55:02 up 59 days, 37 min,  1 user,  load average: 35.95, 14.04, 7.32
> Tasks: 2417 total,  54 running, 2363 sleeping,   0 stopped,   0 zombie
> Cpu(s):  6.3%us,  1.0%sy,  0.0%ni, 90.2%id,  1.9%wa,  0.0%hi,  0.6%si,  0.0%st
> Mem:  264523700k total, 250145228k used, 14378472k free,   207032k buffers
> Swap:  2097144k total,   553624k used,  1543520k free, 166905748k cached
> 
>   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND  
>   
> 29852 postgres  20   0  131g  59m  35m R 100.0  0.0   1:27.71 postmaster  
>   
> 29854 postgres  20   0  131g  70m  45m R 100.0  0.0   1:35.43 postmaster  
>   
> 17449 postgres  20   0  131g 1.2g 1.2g R 100.0  0.5   1:52.62 postmaster  
>   
> 29868 postgres  20   0  131g 1.1g 1.0g R 100.0  0.4   1:58.93 postmaster  
>   
> 30136 postgres  20   0  131g  77m  52m R 100.0  0.0   1:34.33 postmaster  
>   
> 30294 postgres  20   0  131g  66m  41m R 100.0  0.0   1:33.33 postmaster  
>   
> 30864 postgres  20   0  131g  66m  41m R 100.0  0.0   1:36.17 postmaster  
>   
> 30872 postgres  20   0  131g  61m  36m R 100.0  0.0   1:26.81 postmaster  
>   
> 30876 postgres  20   0  131g  68m  43m R 100.0  0.0   1:33.97 postmaster  
>   
> 30899 postgres  20   0  131g  68m  44m R 100.0  0.0   1:38.95 postmaster  
>   
> 30906 postgres  20   0  131g  67m  42m R 100.0  0.0   1:27.82 postmaster  
>   
> 31173 postgres  20   0  131g  68m  44m R 100.0  0.0   1:28.49 postmaster  
>   
> 31239 postgres  20   0  131g  71m  46m R 100.0  0.0   1:31.42 postmaster  
>   
> 31248 postgres  20   0  131g  90m  65m R 100.0  0.0   1:26.20 postmaster  
>   
> 34934 postgres  20   0  131g 5580 3456 R 100.0  0.0   1:23.96 postmaster  
>   
> 47945 postgres  20   0  131g 3.0g 3.0g R 100.0  1.2   6:08.41 postmaster  
>   
> 16116 postgres  20   0  131g  84m  59m R 100.0  0.0   1:30.60 postmaster  
>   
> 16304 postgres  20   0  131g  85m  60m R 100.0  0.0   1:38.89 postmaster  
>   
> 17104 postgres  20   0  131g  96m  72m R 100.0  0.0   1:27.54 postmaster  
>   
> 17111 postgres  20   0  131g  98m  73m R 100.0  0.0   1:38.23 postmaster  
>   
> 17320 postgres  20   0  131g  98m  74m R 100.0  0.0   1:38.51 postmaster  
>   
> 31221 postgres  20   0  131g  63m  38m R 100.0  0.0   1:33.63 postmaster  
>   
> 31272 postgres  20   0  131g 1.0g 1.0g R 100.0  0.4   1:32.71 postmaster  
>   
>  3290 postgres  20   0  131g  99m  74m R 100.0  0.0   1:32.76 postmaster  
>   
>  3459 postgres  20   0  131g 2.1g 2.0g R 100.0  0.8   1:44.92 postmaster  
>   
> 16492 postgres  20   0  131g 100m  75m R 100.0  0.0   1:33.36 postmaster  
>   
> 16562 postgres  20   0  131g 114m  89m R 100.0  0.0   1:35.14 postmaster  
>   
> 17146 postgres  20   0  131g  91m  66m R 100.0  0.0   1:37.39 postmaster  
>   
> 17403 postgres  20   0  131g  98m  73m R 100.0  0.0   1:32.13 postmaster  
>   
> 31100 postgres  20   0  131g  62m  38m R 100.0  0.0   1:29.06 postmaster  
>   
>  2019 postgres  20   0  131g 1.2g 1.2g R 98.7  0.5   1:40.91 postmaster   
>   
>  2150 postgres  20   0  131g 1.3g 1.3g R 98.7  0.5   2:53.14 postmaster   
>   
> 16048 postgres  20   0  131g  71m  46m R 98.7  0.0   1:29.75 postmaster   
>   
> 30190 postgres  20   0  131g 1.4g 1.3g R 98.7  0.5   0:55.98 postmaster   
>   
> 16112 postgres  20   0  131g 862m 827m R 97.1  0.3   0:48.00 postmaster   
>   
> 31202 postgres  20   0  131g  74m  49m R 97.1  0.0   1:34.62 postmaster   
>   
> 35658 postgres  20   0  131g 5948 3788 R 97.1  0.0   0:12.29 postmaster   
>   
> 16134 postgres  20   0  131g 1.9g 1.9g R 95.4  0.8   1:47.27 postmaster   
>   
> 31034 postgres  20   0  131g  69m  44m R 95.4  0.0   1:26.35 postmaster   
>   
> 16120 postgres  20   0  131g 1.2g 1.2g R 93.8  0.5   2:04.02 postmaster   
>   
> 30891 postgres  20   0  131g  57m  33m R 93.8  0.0   1:23.08 postmaster   

Re: [GENERAL] 100% cpu usage on some postmaster processes kill the complete database

[Richard Huxton]

On 01/03/12 16:41, Paul Dunkler wrote:

Hi List,

we are currently running a rather large postgresql-installation with 
approximately 4k Transactions and 50k index scans per second.

In the last days on some times of the day (irregular - 3-4 times a day), some 
of the postmaster processes are running with 100% cpu usage. That leads to a 
totally breakdown of the query execution. We see tons of statements which are 
correctly automatically aborted by our statement_timeout set to 15 seconds. I 
tried to search, but do not really recognize what the problem could be there...

Some things i have checked:
- We are not running any bulk jobs or maintenance scripts at this time
- No system errors in any logs during that slowdowns
- I/O Performance seems fine. No high IO Wait amount... But IO Write totally 
drops in that times because it seems that no postgres process can perform any 
update

I just installed a script, which prints me out the top and ps axf information 
for facing out the problem. I will post a snippet of the top here:


Combine that with this:
  SELECT * FROM pg_stat_activity;

That will let you line up pids from top with active queries.

--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] Ident authentication failed for user

[Ruben Blanco]

Hi:

I cannot connect to my Postgres database from my PHP scripts. I get the error:

   PHP Warning:  pg_connect(): Unable to connect to PostgreSQL server:
FATAL:  Ident authentication failed for user "postgres" in ...


I have tried many combinations for "host" TYPE in "pg_hba.conf"
(restarting postmaster) without success:

# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# "local" is for Unix domain socket connections only
local   all all   ident

# IPv4 local connections:
#20120301 rbz [1291-1292]
#hostall all 127.0.0.1/32     ident

# IPv6 local connections:
#20120301 rbz [1291-1292]
#hostall all     ::1/128  ident

#20120301 rbz [1291-1292]
#hostall all 127.0.0.1/32 trust
#hostall all 0.0.0.0/0trust
#hostall all *trust
hostall all 127.0.0.1 255.255.255.255   trust
#hostall all localhosttrust
host allall ::1/128   trust


Isn't the las record the less restrictive configuration for "host" connections?

I can connect to psql witout any problem.

Thanks in advance for any help.

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

Re: [GENERAL] accumulating handles problem on machine running postgresql

[Adam Bruss]

After doing some more work with Process Monitor I found the leaks to be 
stemming from a driver associated with a License Manager from SafeNet Inc. 
which runs on the machine. 

Adam Bruss
Senior Development Engineer
AWR Corporation
11520 N. Port Washington Rd., Suite 201
Mequon, WI  53092  USA
P: 1.262.240.0291 x104
F: 1.262.240.0294
E: abr...@awrcorp.com
W: http://www.awrcorp.com


-Original Message-
From: dennis jenkins [mailto:dennis.jenkins...@gmail.com] 
Sent: Wednesday, February 29, 2012 12:25 PM
To: Adam Bruss
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] accumulating handles problem on machine running 
postgresql

On Wed, Feb 29, 2012 at 8:54 AM, Adam Bruss  wrote:
> I ran process explorer and looked at the handles for the System process. The 
> vast majority of the handles are of type "Key". I can find them in the 
> registry. I took two at random from process explorer and exported the 
> registry branch for them below.
>
> ## EXAMPLE  1: ##
>
> Key Name:          
> HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}
> Class Name:        
> Last Write Time:   2/28/2012 - 1:26 AM
> Value 0
>  Name:            
>  Type:            REG_SZ
>  Data:            HwTextInsertion Class
>
>
> Key Name:          
> HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}\InprocServer32
> Class Name:        
> Last Write Time:   2/29/2012 - 4:05 AM
> Value 0
>  Name:            
>  Type:            REG_EXPAND_SZ
>  Data:            %CommonProgramFiles%\microsoft shared\ink\tiptsf.dll
>
> Value 1
>  Name:            ThreadingModel
>  Type:            REG_SZ
>  Data:            Apartment

Seems like your web server is leaking registry keys used when loading
COM objects.  The sample that you posted is for the "Tablet PC Input
Panel Text Services Framework" [1].  However, I find it strange that
a) IIS needs this and b) that it would leak it.

Are you able to obtain a large statistical sample of the leaked
registry keys?  2 out of 130,000 seems like a small sample.

Try the command line "handle.exe" tool [2].  It can dump to a text
file that you can then analyze with perl, python, grep, etc... or your
own eyeballs. :)  See if the handle list is dominated by a specific
set of registry keys.

[1] http://systemexplorer.net/filereviews.php?fid=515344
[2] http://technet.microsoft.com/en-us/sysinternals/bb896655

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

Re: [GENERAL] Ident authentication failed for user

[John R Pierce]

On 03/01/12 9:02 AM, Ruben Blanco wrote:

I cannot connect to my Postgres database from my PHP scripts. I get the error:

PHP Warning:  pg_connect(): Unable to connect to PostgreSQL server:
FATAL:  Ident authentication failed for user "postgres" in ...


I have tried many combinations for "host" TYPE in "pg_hba.conf"
(restarting postmaster) without success:

# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# "local" is for Unix domain socket connections only
local   all all   ident

...
#hostall all *trust
hostall all 127.0.0.1 255.255.255.255   trust
#hostall all localhosttrust
host allall ::1/128   trust


Isn't the las record the less restrictive configuration for "host" connections?


specify host=localhost, otherwise its using the first 'local' line, 
which specifies 'ident' authentication, as the error implies.


you do realize, trust lets any process on the localhost authenticate as 
any user, including the postgres DBA account?



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] Problem with initdb and two versions on one server?

[Chris McCormick]

Thank for the good input.

I found my problem. I compiled initially *with* the --disable-rpath
option. When I realised my mistake, I did a make uninstall,
reconfigured, rebuilt, and reinstalled. My post was made when I had
done this and thought I had the settings as stated. However, the
uninstall didn't remove everything (chalk this up to my lack of
linux/build-from-source experience). So I think some of the
not-removed files were still using a disabled rpath and going to the
old 7.4.30 paths.

When I uninstalled and rm'd the directories before restarting the
whole process, it worked.

For the record, I was using bash on CentOS 4.9.

Thanks again,
Chris

On Tue, Feb 28, 2012 at 3:32 PM, Tom Lane  wrote:
> Chris McCormick  writes:
>>     Because of issues with dump/restore, I am instead setting up a
>> second cluster under a newer version so I can slowly migrate data (I
>> have 7.4.30, and am adding 8.3.18 on the same box). The problem is
>> that when I try to start the new postmaster it complains:
>
>> "FATAL:  database files are incompatible with server"
>> "DETAIL: The data directory was initialised by PostgreSQL version 7.4,
>> which is not compatible with this version 8.3.18."
>
> You are starting the 8.3 postmaster, but giving it a -D setting that
> points at the 7.4 data directory.  The commands you're showing look
> reasonable offhand, but clearly there's something wrong in detail.
>
> One thought that occurs to me is that you might have a PGDATA
> environment variable that points at the old data directory ... the
> explicit -D switches *should* override that, but maybe are failing to?
>
> Also, the documented syntax for pg_ctl is pg_ctl start [switches],
> not what you wrote.  You did not say what the platform is, but some
> versions of getopt() try (with varying degrees of success) to rearrange
> such commands to meet expectations.  Maybe the -D switch is getting
> dropped on the floor somewhere in there.
>
> Another thing worth doing is to examine the PG_VERSION file in each
> data directory, just to make sure it contains what you think.
>
>                        regards, tom lane

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

Re: [GENERAL] Ident authentication failed for user

[Ruben Blanco]

Hi John. Thanks a lot for your reply:

El día 1 de marzo de 2012 17:25, John R Pierce  escribió:
> On 03/01/12 9:02 AM, Ruben Blanco wrote:

> specify host=localhost, otherwise its using the first 'local' line, which
> specifies 'ident' authentication, as the error implies.

Where should I specify "host=localhost"? in the $connection_string for
pg_connect()? It doesn't work either.

I was understanding "host" TYPE was for TCP/IP connections only (my
scripts run through Apache web server). That's why I didn't expect the
"local" record to do anything with my connections. If I include this
record in the "pg_hba.conf" file I can connect to the database:

local   all all   trust


> you do realize, trust lets any process on the localhost authenticate as any
> user, including the postgres DBA account?

Yes, I just want to make it run, then will tighten security measures.

Thanks.

> --
> john r pierce                            N 37, W 122
> santa cruz ca                         mid-left coast
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

Re: [GENERAL] Ident authentication failed for user

[John R Pierce]

On 03/01/12 10:03 AM, Ruben Blanco wrote:

El día 1 de marzo de 2012 17:25, John R Pierce  escribió:

>  On 03/01/12 9:02 AM, Ruben Blanco wrote:
>  specify host=localhost, otherwise its using the first 'local' line, which
>  specifies 'ident' authentication, as the error implies.

Where should I specify "host=localhost"? in the $connection_string for
pg_connect()? It doesn't work either.


I don''t do much PHP, so I might have the syntax wrong, but yes  it 
would go in the connection string passed to pg_Connection...  however 
you specify a host.  if you don't give a host at all, it uses the 
'domain' socket, which corresponds to the LOCAL line in pg_hba.conf...  
if you specify host is localhost, then it uses the host  127.0.0.1 
(or ::1) lines.


--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

Re: [GENERAL] 100% cpu usage on some postmaster processes kill the complete database

[Paul Dunkler]

I did that now - and analyzed the situation a bit. There are only queries running which will process very fast under high load (only index scans, very low rates of sequential scans). I found a remarkable number of Insert statements...And sometimes when that happens, the CPU Utilization is going up to nearby 100% too and 98% is system usage...Am 01.03.2012 um 18:02 schrieb Richard Huxton:On 01/03/12 16:41, Paul Dunkler wrote:Hi List,we are currently running a rather large postgresql-installation with approximately 4k Transactions and 50k index scans per second.In the last days on some times of the day (irregular - 3-4 times a day), some of the postmaster processes are running with 100% cpu usage. That leads to a totally breakdown of the query execution. We see tons of statements which are correctly automatically aborted by our statement_timeout set to 15 seconds. I tried to search, but do not really recognize what the problem could be there...Some things i have checked:- We are not running any bulk jobs or maintenance scripts at this time- No system errors in any logs during that slowdowns- I/O Performance seems fine. No high IO Wait amount... But IO Write totally drops in that times because it seems that no postgres process can perform any updateI just installed a script, which prints me out the top and ps axf information for facing out the problem. I will post a snippet of the top here:Combine that with this:  SELECT * FROM pg_stat_activity;That will let you line up pids from top with active queries.--   Richard Huxton  Archonet Ltd-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
--Mit freundlichen GrüßenPaul Dunkler-XYRALITY GmbH • Lerchenstraße 28a • 22767 HamburgPaul Dunkler • SoftwareentwicklerMail: paul.dunk...@xyrality.com	Tel: +49 (0) 40 23 51 78 97Mobil: +49 (0) 151 252 228 42Fax: +49 (0) 40 23 51 78 98Web: http://www.xyrality.com/Registergericht: Hamburg HRB 115332Geschäftsführer: Sven Ossenbrüggen & Alexander Spohr-

Re: [GENERAL] 100% cpu usage on some postmaster processes kill the complete database

[Richard Huxton]

On 01/03/12 19:41, Paul Dunkler wrote:

I did that now - and analyzed the situation a bit. There are only queries
running which will process very fast under high load (only index scans, very low
rates of sequential scans). I found a remarkable number of Insert statements...

And sometimes when that happens, the CPU Utilization is going up to nearby 100%
too and 98% is system usage...


You're running on a box larger than I'm used to, so this is only 
speculation. I'm wondering whether you're hitting problems with lock 
contention or some such. It looks like you've got 48 cores there all at 
about 100% possibly none of them getting much chance to do any work.


Oddly, the totals you posted in your top output show 6.3% user cpu 
usage, which I can't make match with 50-odd processes all approaching 
100% cpu.


Perhaps have a look at vmstat output too - see if context-switches spike 
unusually high during these periods (sorry - no idea what an unusually 
high number would be on a machine like yours).


Reducing the number of concurrent backends might help, but that rather 
depends on whether my guess is right.


If no-one more experienced than me comes along shortly, try reposting to 
the performance list. There are people there who are used to machines of 
this size.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] 100% cpu usage on some postmaster processes kill the complete database

[Paul Dunkler]

Hi,You're running on a box larger than I'm used to, so this is only speculation. I'm wondering whether you're hitting problems with lock contention or some such. It looks like you've got 48 cores there all at about 100% possibly none of them getting much chance to do any work.Yes. That is what i see too...Oddly, the totals you posted in your top output show 6.3% user cpu usage, which I can't make match with 50-odd processes all approaching 100% cpu.Sometimes the Cpu is only 7% used in this times but at other peak times, the cpu is used 100% (97% system load) as i posted before.Perhaps have a look at vmstat output too - see if context-switches spike unusually high during these periods (sorry - no idea what an unusually high number would be on a machine like yours).Thanks. i will have a look at it.Reducing the number of concurrent backends might help, but that rather depends on whether my guess is right.Yes... already thought about setting up a connection pool.If no-one more experienced than me comes along shortly, try reposting to the performance list. There are people there who are used to machines of this size.Thanks. I will wait a time and consider re-posting it to the perfornance list.--   Richard Huxton  Archonet Ltd-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
--Mit freundlichen GrüßenPaul Dunkler-XYRALITY GmbH • Lerchenstraße 28a • 22767 HamburgPaul Dunkler • SoftwareentwicklerMail: paul.dunk...@xyrality.com	Tel: +49 (0) 40 23 51 78 97Mobil: +49 (0) 151 252 228 42Fax: +49 (0) 40 23 51 78 98Web: http://www.xyrality.com/Registergericht: Hamburg HRB 115332Geschäftsführer: Sven Ossenbrüggen & Alexander Spohr-

Re: [GENERAL] 100% cpu usage on some postmaster processes kill the complete database

[Scott Marlowe]

On Thu, Mar 1, 2012 at 1:52 PM, Paul Dunkler  wrote:
>
> Hi,
>
> You're running on a box larger than I'm used to, so this is only speculation. 
> I'm wondering whether you're hitting problems with lock contention or some 
> such. It looks like you've got 48 cores there all at about 100% possibly none 
> of them getting much chance to do any work.
>
>
> Yes. That is what i see too...
>
> Oddly, the totals you posted in your top output show 6.3% user cpu usage, 
> which I can't make match with 50-odd processes all approaching 100% cpu.
>
>
> Sometimes the Cpu is only 7% used in this times but at other peak times, the 
> cpu is used 100% (97% system load) as i posted before.
>
> Perhaps have a look at vmstat output too - see if context-switches spike 
> unusually high during these periods (sorry - no idea what an unusually high 
> number would be on a machine like yours).
>
>
> Thanks. i will have a look at it.
>
> Reducing the number of concurrent backends might help, but that rather 
> depends on whether my guess is right.
>
>
> Yes... already thought about setting up a connection pool.
>
> If no-one more experienced than me comes along shortly, try reposting to the 
> performance list. There are people there who are used to machines of this 
> size.
>
>
> Thanks. I will wait a time and consider re-posting it to the perfornance list.
>

I'd look at vmstat and iostat output (vmstat 10, iostat -xd 10) for a
few minutes.  In vmstat look for high (>100k) ints or cs numbers, in
iostat look at io utilization.

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

Re: [GENERAL] 2 x duplicate key value violation: which exception comes first ?

[Tom Lane]

"Marc Mamin"  writes:
> I've a table with 1 primary key and 1 unique index.

> I would like to handle differently the duplicate key violations
> depending on which constraints are affected.
> (Within a plpgsql CATCH block, using SQLERRM).

> It seems, that when both constraints would be violated, the exception is
> always about the primary key.

> Can I rely on that or should I expect the exception order being random ?

Offhand I think the indexes will be inserted into in OID order, which
would typically be creation order, except after a wraparound.  If you
rely on this you can expect your code to break sooner or later.

regards, tom lane

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

Re: [GENERAL] "Compressed data is corrupt"

[Tom Lane]

Matthias Leisi  writes:
> I have a behaviour of Postgres which I do not understand (and thus can
> not fix...). When inserting into a log-like table, I get the error
> message "compressed data is corrupt" for certain (rare) combination of
> values. When I re-create the same table structure from scratch and
> insert the same data in to that table, the error does not appear.

The most obvious theory for this is a corrupted index entry in the pkey
index.  Does the issue go away if you REINDEX?

regards, tom lane

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

[GENERAL] pg_upgrade + streaming replication ?

[Lonni J Friedman]

I've got a 3 node cluster (1 master/2 slaves) running 9.0.x with
streaming replication.  I'm in the planning stages of upgrading to
9.1.x, and am looking into the most efficient way to do the upgrade
with the goal of minimizing downtime & risk.  After googling, the only
discussion that I've found of using pg_upgrade with a streaming
replication setup seems to be this (nearly) year old thread:
http://web.archiveorange.com/archive/v/9FNVlDWGQtpyWVL54jlK

In summary, there is no way to use both pg_upgrade and streaming
replication simultaneously.  I'd have to either use pg_upgrade and
then effectively rebuild/redeploy the slaves, or not use pg_upgrade,
and reimport all of the data.  Is that still the latest status, or are
there other options?

thanks

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

[GENERAL] Split read/write queries between 2 servers (one master and one slave with streaming replication)?

[Benjamin Henrion]

Hi,

I am trying pgpool2 to split read and write queries, where reads
should go to the slave server (streaming replication) and writes to
the master server.

Anybody has a config file that works for pgpool2?

Best,

-- 
Benjamin Henrion 
FFII Brussels - +32-484-566109 - +32-2-3500762
"In July 2005, after several failed attempts to legalise software
patents in Europe, the patent establishment changed its strategy.
Instead of explicitly seeking to sanction the patentability of
software, they are now seeking to create a central European patent
court, which would establish and enforce patentability rules in their
favor, without any possibility of correction by competing courts or
democratically elected legislators."

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

Re: [GENERAL] Split read/write queries between 2 servers (one master and one slave with streaming replication)?

[Adam Cornett]

On Thu, Mar 1, 2012 at 5:06 PM, Benjamin Henrion  wrote:

> Hi,
>
> I am trying pgpool2 to split read and write queries, where reads
> should go to the slave server (streaming replication) and writes to
> the master server.
>
> Anybody has a config file that works for pgpool2?
>
> Best,
>
> --
> Benjamin Henrion 
> FFII Brussels - +32-484-566109 - +32-2-3500762
> "In July 2005, after several failed attempts to legalise software
> patents in Europe, the patent establishment changed its strategy.
> Instead of explicitly seeking to sanction the patentability of
> software, they are now seeking to create a central European patent
> court, which would establish and enforce patentability rules in their
> favor, without any possibility of correction by competing courts or
> democratically elected legislators."
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I've attached my pgpool conf.
All pgpool is doing here is the load balancing, replication is handled by
streaming replication (9.0+)
PGPool can be setup to do auto failover as well, although that isn't shown
in the attached config.

I'm sure some pgpool people will jump on and let me know how I'm doing it
wrong, but this is working quite well in production for us.
-Adam


pgpool.conf
Description: Binary data

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