Re: [GENERAL] invalid memory alloc request size + Could not open file pg_clog/XXXX
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 schema.unavailable_table (col1, col2, ...). -- vacuum analyze schema.unavailable_table ; 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 schema.unavailable_table ; 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 problem What could
Re: [GENERAL] what Linux to run
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
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 http://www.ubuntu.org/. 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
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?
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 ?
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
On Wednesday 29 February 2012 14:14:19 Tom Lane wrote: David Johnston pol...@yahoo.com 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
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?
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
On Thu, Mar 1, 2012 at 5:25 AM, Ivan Voras ivo...@freebsd.org 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
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 31261 postgres 20 0 131g 81m 56m R 93.8 0.0 1:24.51 postmaster
Re: [GENERAL] 100% cpu usage on some postmaster processes kill the complete database
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
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
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 abr...@awrcorp.com 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: NO CLASS
Last Write Time: 2/28/2012 - 1:26 AM
Value 0
Name: NO NAME
Type: REG_SZ
Data: HwTextInsertion Class
Key Name:
HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}\InprocServer32
Class Name: NO CLASS
Last Write Time: 2/29/2012 - 4:05 AM
Value 0
Name: NO 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
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?
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 t...@sss.pgh.pa.us wrote: Chris McCormick mccormi...@gmail.com 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
Hi John. Thanks a lot for your reply: El día 1 de marzo de 2012 17:25, John R Pierce pie...@hogranch.com 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
On 03/01/12 10:03 AM, Ruben Blanco wrote: El día 1 de marzo de 2012 17:25, John R Piercepie...@hogranch.com 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
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
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
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
On Thu, Mar 1, 2012 at 1:52 PM, Paul Dunkler paul.dunk...@xyrality.com 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 ?
Marc Mamin m.ma...@intershop.de 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
Matthias Leisi matth...@leisi.net 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 ?
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)?
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 bhenrion at ffii.org 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)?
On Thu, Mar 1, 2012 at 5:06 PM, Benjamin Henrion b...@udev.org 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 bhenrion at ffii.org 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
