Re: [GENERAL] specifying multiple ldapserver in pg_hba.conf
Στις Thursday 27 October 2011 11:00:10 ο/η Magnus Hagander έγραψε: On Wed, Oct 26, 2011 at 23:00, Darin Perusich darin.perus...@ctg.com wrote: Are you able to specify multiple ldapservers in pg_hba.conf and if so what is the format? I'd like to be able to build some redundancy incase one of the ldap servers goes down. This is unfortunately currently not possible. To do this, you need to set up some IP level redundancy for your LDAP. Thats true. We had the same issue, and ended up doing the redundancy via DNS and a cron job which checks all LDAP servers, choses an alive server, and modifies bind's config files accordingly. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing an Extension
Hi all, how can I install/use an extension form pgfoundry, namely this one: http://pgfoundry.org/projects/pgchem/ it does not come with any kind of installation instructions. (note: for windows) Best Regards, Thomas
Re: [GENERAL] PG_DUMP error : unexpected chunk number
Hi, Thanks for the info. I've sorted out my problem by recreating the table and re-insert back the data exclude the corrupted row into the newly create table. And went back to my old backup to get back the data before it corrupt. I was lucky only 1 row affected. Regards Louis From: Craig Ringer ring...@ringerc.id.au To: mailtolouis2020-postg...@yahoo.com mailtolouis2020-postg...@yahoo.com Cc: Postgres pgsql-general@postgresql.org Sent: Saturday, October 29, 2011 5:05 PM Subject: Re: [GENERAL] PG_DUMP error : unexpected chunk number On 10/28/2011 06:24 PM, mailtolouis2020-postg...@yahoo.com wrote: Hello, I think I got a big problem now, I'm not able to do pg_dump on one of my production database. When I do pg_dump it give me this error: pg_dump: Error message from server: ERROR: unexpected chunk number 18390760 (expected 4) for toast value 92784 in pg_toast_88487 I believe this message mean that my database is corrupted. Yup, pretty much. Check your hard drives. It's not impossible that there's a PostgreSQL bug that's caused the issue, but it's more likely going to be a hard drive, RAID array, or system memory/cpu/heat issue. For recovery: First, stop postgresql and take a file-level copy of your whole database. Keep that copy somewhere safe, in case your repair efforts make the issue worse. In this case, I'd probably try zeroing damaged pages as my first recovery effort. That's a bit of a big hammer, but might let you get a dump out. It WILL DESTROY DATA, so I'd recommend doing it by copying your backup to another directory and running a temporary postgresql instance with zero_damaged_pages enabled on it, then trying to dump from the temporary postmaster you've started. That way you don't have to mess with your original running database. See: http://www.postgresql.org/docs/current/static/runtime-config-developer.html It might help to look up which real table the pg_toast_88487 TOAST table is associated with, and see how important it is. Use pg_catalog for that; see the documentation. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Regarding the shared disk fail over cluster configuration
Dear All, I read the postgresql document for clustering. I am planning to go for the shared disk fail over clustering. But I am unable to understand propery how to achieve this. Below is my hardware setup - I have two server - Primary server and secondary server. Both the server RHEL 6 is installed. I have one storage. RHEL HA clustering is configured to have zero downtime. So if primary server is down then HeartBeat will bring secondary server online. Now i want to install the Postgres and do the shared disk failover clustering. I have couple of doubts 1. Do i need to install Postgres server in both Primary and Secondary Server? 2. According to the shared disk fail over concept the dbdata directory has to be shared. So is it like DB will be installed in both the server and dbdata directory will be in storage and both the database will be referring to the same data in storage? Any help will be appreciated? Thanks, Debasis -- View this message in context: http://postgresql.1045698.n5.nabble.com/Regarding-the-shared-disk-fail-over-cluster-configuration-tp4952316p4952316.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Why is there no 8.3.16 rpm with _id ?
On Sun, Oct 30, 2011 at 11:28:52PM +0200, Devrim GÜNDÜZ wrote: I have no intention to build the -id packages again, given the lack of request (first request since 8.3.11...). You can build your own packages quite easily, though. ok. fair enough. thanks. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] salve.pgsql-general
salve. pgsql-general Sono felicedi presentareuna buona occasioneper voi fotocamera digitale, computer,orologi,phone.gultarse sieteinteressati ai nostri prodotti,siamo in grado dioffrire il migliorprezzoper voi luogo: bodoeo .com 2011-10-31 19:48:59
Re: [GENERAL] Regarding the shared disk fail over cluster configuration
Hi, On 31 October 2011 23:33, Debasis Mishra debasis1...@gmail.com wrote: RHEL HA clustering is configured to have zero downtime. So if primary server is down then HeartBeat will bring secondary server online. By RHEL HA clustering do you mean RedHat cluster suite? RHCS uses SIGTERM and then kill -9 after 30 sec(?) so it could be tricky to have zero downtime 1. Do i need to install Postgres server in both Primary and Secondary Server? We have Postgres installation on both and SAN volume is mounted to primary only (RHCS takes care about it). (I would use hot standby and two data volumes now instead of shared disk) 2. According to the shared disk fail over concept the dbdata directory has to be shared. So is it like DB will be installed in both the server and dbdata directory will be in storage and both the database will be referring to the same data in storage? yes, but you should mount $PGDATA (or RHCS) to active instance only. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Why is there no 8.3.16 rpm with _id ?
2011/10/30 Devrim GÜNDÜZ dev...@gunduz.org: I have no intention to build the -id packages again, given the lack of request (first request since 8.3.11...). You can build your own packages quite easily, though. But... aren't integer datetimes supposed to be the default, with float datetimes quickly becoming deprecated? Or does the current package (w/o the -id suffix) already implement that default? I don't run PG on Linux, but I imagine those who do might be interested in the answer ;) -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] Why is there no 8.3.16 rpm with _id ?
On Mon, Oct 31, 2011 at 02:52:28PM +0100, Alban Hertroys wrote: 2011/10/30 Devrim GÜNDÜZ dev...@gunduz.org: I have no intention to build the -id packages again, given the lack of request (first request since 8.3.11...). You can build your own packages quite easily, though. But... aren't integer datetimes supposed to be the default, with float datetimes quickly becoming deprecated? Or does the current package (w/o the -id suffix) already implement that default? I don't run PG on Linux, but I imagine those who do might be interested in the answer ;) id is default in 8.4, and I am/was looking for 8.3. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [PL/pgSQL] function call
I have created a function log_insert(), which is simply a shorthand for
an INSERT table and which I want to call from various trigger functions.
CREATE OR REPLACE FUNCTION log_insert(vseverity text, vtrigger text,
vtriggertable text, vtriggerid text, vmessage text) RETURNS boolean AS
$BODY$
BEGIN
INSERT INTO log
(severity, trigger,triggertable, triggerid, message)
VALUES
(vseverity, vtrigger,vtriggertable, vtriggerid, vmessage);
END
$BODY$
LANGUAGE plpgsql VOLATILE;
I tried:
log_insert('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some msg');
= I get a syntax error on CREATE TRIGGER.
SELECT log_insert(...)
= passes the syntax check but throws an error when run:
function log_insert(unknown, unknown, unknown, integer, unknown) does
not exist Hint: No function matches the given name and argument types.
You might need to add explicit type casts.
Any help would be greatly appreciated.
--
Best Regards,
Tarlika Elisabeth Schmitz
--
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] [PL/pgSQL] function call
On Mon, Oct 31, 2011 at 8:31 AM, Tarlika Elisabeth Schmitz
postgres...@numerixtechnology.de wrote:
I have created a function log_insert(), which is simply a shorthand for
an INSERT table and which I want to call from various trigger functions.
CREATE OR REPLACE FUNCTION log_insert(vseverity text, vtrigger text,
vtriggertable text, vtriggerid text, vmessage text) RETURNS boolean AS
$BODY$
BEGIN
INSERT INTO log
(severity, trigger,triggertable, triggerid, message)
VALUES
(vseverity, vtrigger,vtriggertable, vtriggerid, vmessage);
END
$BODY$
LANGUAGE plpgsql VOLATILE;
I tried:
log_insert('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some msg');
= I get a syntax error on CREATE TRIGGER.
SELECT log_insert(...)
= passes the syntax check but throws an error when run:
function log_insert(unknown, unknown, unknown, integer, unknown) does
not exist Hint: No function matches the given name and argument types.
You might need to add explicit type casts.
Any help would be greatly appreciated.
There is some context you are not passing here -- the log_insert
function is being inside a trigger function which is where your error
always is. However, in pl/pgsql, you always call functions with
PERFORM or SELECT depending if you want to process the result.
also, FWIW, I don't like a simple wrapper for insert statement like
that -- the syntax brevity is outweighed by the loss of SQL features
such as being able to pass DEFAULT for columns.
merlin
--
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] [PL/pgSQL] function call
2011/10/31 Merlin Moncure mmonc...@gmail.com:
On Mon, Oct 31, 2011 at 8:31 AM, Tarlika Elisabeth Schmitz
postgres...@numerixtechnology.de wrote:
I have created a function log_insert(), which is simply a shorthand for
an INSERT table and which I want to call from various trigger functions.
CREATE OR REPLACE FUNCTION log_insert(vseverity text, vtrigger text,
vtriggertable text, vtriggerid text, vmessage text) RETURNS boolean AS
$BODY$
BEGIN
INSERT INTO log
(severity, trigger,triggertable, triggerid, message)
VALUES
(vseverity, vtrigger,vtriggertable, vtriggerid, vmessage);
END
$BODY$
LANGUAGE plpgsql VOLATILE;
I tried:
log_insert('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some msg');
= I get a syntax error on CREATE TRIGGER.
SELECT log_insert(...)
= passes the syntax check but throws an error when run:
function log_insert(unknown, unknown, unknown, integer, unknown) does
not exist Hint: No function matches the given name and argument types.
You might need to add explicit type casts.
Any help would be greatly appreciated.
There is some context you are not passing here -- the log_insert
function is being inside a trigger function which is where your error
always is. However, in pl/pgsql, you always call functions with
PERFORM or SELECT depending if you want to process the result.
also, FWIW, I don't like a simple wrapper for insert statement like
that -- the syntax brevity is outweighed by the loss of SQL features
such as being able to pass DEFAULT for columns.
you can use a PL default parameters now. And when there are lot of
parameters a named notation is useful
regards
Pavel
merlin
--
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
[GENERAL] Query planner always has exactly half of the rows in the table as plan rows with new GIST operator class
I have added a GIST operator class to a custom data type in
PostgreSQL. The index returns the correct results and the build speed
is fairly good as well. There is one problem however that is
presumably linked to the picksplit function (?) - the query planner
always returns half of all the rows in the table as (after vacuum)
Plan Rows. In a table with 1M rows, it will be exactly 500k. Since
this seems to be systematic and the gap between the plan rows and
actual rows is orders of magnitude, there must be a bug in my GIST
implementation.
On a table with 200 rows I get the following plan (slightly truncated
for visibility):
[
{
Plan: {
Node Type: Bitmap Heap Scan,
Relation Name: bar,
Schema: public,
Alias: foo,
Startup Cost: 25.03,
Total Cost: 34.53,
Plan Rows: 100,
Plan Width: 272,
Actual Startup Time: 0.097,
Actual Total Time: 0.214,
Actual Rows: 16,
Actual Loops: 1,
Output: [id, (
Recheck Cond: (foo.bar %? '(
Plans: [
{
Node Type: Bitmap Index Scan,
Parent Relationship: Outer,
Index Name: idx_fps,
Startup Cost: 0.00,
Total Cost: 25.00,
Plan Rows: 100,
Plan Width: 0,
Actual Startup Time: 0.069,
Actual Total Time: 0.069,
Actual Rows: 16,
Actual Loops: 1,
Index Cond: (
}
]
},
Triggers: [
],
Total Runtime: 0.327
}
]
This is how my picksplit function splits the entries (200 rows in table):
NOTICE: GIST split vector (n 26): 11 left, 15 right.
NOTICE: GIST split vector (n 26): 8 left, 18 right.
NOTICE: GIST split vector (n 26): 17 left, 9 right.
NOTICE: GIST split vector (n 26): 16 left, 10 right.
NOTICE: GIST split vector (n 26): 20 left, 6 right.
NOTICE: GIST split vector (n 26): 14 left, 12 right.
NOTICE: GIST split vector (n 26): 19 left, 7 right.
NOTICE: GIST split vector (n 26): 23 left, 3 right.
NOTICE: GIST split vector (n 26): 24 left, 2 right.
INFO: index idx_fps now contains 200 row versions in 11 pages
Any ideas what the cause might be?
--
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] Query planner always has exactly half of the rows in the table as plan rows with new GIST operator class
Adrian Schreyer ams...@cam.ac.uk writes: I have added a GIST operator class to a custom data type in PostgreSQL. The index returns the correct results and the build speed is fairly good as well. There is one problem however that is presumably linked to the picksplit function (?) - the query planner always returns half of all the rows in the table as (after vacuum) Plan Rows. Uh, no, picksplit is not where your problem is. You need to provide a selectivity estimation function for your indexable operator. It sounds like you don't have one at all, and restriction_selectivity() is defaulting to 0.5. 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] [PL/pgSQL] function call
On Mon, 31 Oct 2011 09:41:40 -0500
Merlin Moncure mmonc...@gmail.com wrote:
On Mon, Oct 31, 2011 at 8:31 AM, Tarlika Elisabeth Schmitz
postgres...@numerixtechnology.de wrote:
I have created a function log_insert(), which is simply a shorthand
for an INSERT table and which I want to call from various trigger
functions.
CREATE OR REPLACE FUNCTION log_insert(vseverity text, vtrigger text,
vtriggertable text, vtriggerid text, vmessage text)
[...]
SELECT log_insert('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some msg')
= [...] No function matches the given name and argument
types. You might need to add explicit type casts.
[...] in pl/pgsql, you always call functions with
PERFORM or SELECT depending if you want to process the result.
also, FWIW, I don't like a simple wrapper for insert statement like
that -- the syntax brevity is outweighed by the loss of SQL features
such as being able to pass DEFAULT for columns.
merlin
Thank you for your reply.
I don't feel entirely comfortable about phrasing an INSERT as SELECT
log_insert(). As for losing SQL features - no loss in
this particular scenario.
I simply thought my PL/pgSQL code would look a little less cluttered
with a one-line call than with a 3-line INSERT:
INSERT INTO log
(severity, trigger, triggertable, triggerid, message) VALUES
('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some message');
I had two errors:
1) I needed an explicit type cast for the integer NEW.id to ::text
2) the function was declared as RETURNS boolean but did not return a
value.
--
Best Regards,
Tarlika Elisabeth Schmitz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] does reindex need exclusive table access?
v8.3.4 on linux. Does reindex table foo require no other users accessing the foo table? Trying to understand why this seems to be stalled when I attempt this on a live DB (if runs fine/fast on a copy of the DB that no one uses). Should I run this inside a transaction? Thanks in Advance !
[GENERAL] Need Help With a A Simple Query That's Not So Simple
I think this should be easy, but I can't seem to put the SQL together correctly and would appreciate any help. (I'm using Pg 8.4 in CentOS 5.5, if that matters.) I have a table of Farms and a table of crops in a 1:M relationship of Farms : Crops. There are lots of different crops to choose form but for now I'm only interested in two crops; corn and soybeans. Some farms grow only corn and some grow only soybeans, and some grow both. What I'd like to know is, which Farms and how many are growing only corn, which and how many are growing soybeans and which and how many are growing both? I can easily get all the corn growers with: SELECT a.* FROM farms a JOIN crops b ON a.farm_id=b.farm_id WHERE crop_cd='0041' I can do the same with soybeans (crop_cd= '0081') and then I could subtract the sum of these from the total of all farms that grow either corn or soybeans to get the number of farms growing both, but having to do all those queries sounds very time consuming and inefficient. Is there a better way to get the farm counts or data by categories like farms growing only corn, farms growing only soybeans, farms growing both? I'm also interested in possibly expanding to a general case where I could select more than two crops. and get counts of the permutations. Here's a sketch of the relevant pieces of the data base. *Tables:* farms crops === === farm_id bigint (pkey) crop_id (pkey) type farm_idforeign key to farms size crop_cd0041 = corn 0081=soybeans ...year ... Any help would be much appreciated. TIA, - Bill Thoen
Re: [GENERAL] Need Help With a A Simple Query That's Not So Simple
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bill Thoen
Sent: Monday, October 31, 2011 6:51 PM
To: Postgrresql
Subject: [GENERAL] Need Help With a A Simple Query That's Not So Simple
I think this should be easy, but I can't seem to put the SQL together
correctly and would appreciate any help. (I'm using Pg 8.4 in CentOS 5.5, if
that matters.)
I have a table of Farms and a table of crops in a 1:M relationship of Farms
: Crops. There are lots of different crops to choose form but for now I'm
only interested in two crops; corn and soybeans. Some farms grow only corn
and some grow only soybeans, and some grow both. What I'd like to know is,
which Farms and how many are growing only corn, which and how many are
growing soybeans and which and how many are growing both? I can easily get
all the corn growers with:
SELECT a.*
FROM farms a
JOIN crops b
ON a.farm_id=b.farm_id
WHERE crop_cd='0041'
I can do the same with soybeans (crop_cd= '0081') and then I could subtract
the sum of these from the total of all farms that grow either corn or
soybeans to get the number of farms growing both, but having to do all those
queries sounds very time consuming and inefficient. Is there a better way to
get the farm counts or data by categories like farms growing only corn,
farms growing only soybeans, farms growing both? I'm also interested in
possibly expanding to a general case where I could select more than two
crops. and get counts of the permutations.
Here's a sketch of the relevant pieces of the data base.
Tables:
farms crops
=== ===
farm_id bigint (pkey) crop_id (pkey)
type farm_id foreign key to farms
size crop_cd 0041 = corn 0081=soybeans
... year
...
Any help would be much appreciated.
TIA,
- Bill Thoen
---
General Idea:
WITH crop_one AS (
SELECT farm_id, crop_cd AS crop_one_cd ...
), crop_two AS (
SELECT farm_id, crop_cd AS crop_two_cd
)
SELECT *
FROM crop_one
FULL OUTER JOIN crop_two USING (farm_id)
;
Records with NULL for crop_one_cd only grow crop 2, records with NULL for
crop_two_cd only grow crop 1, records where neither field is NULL grow
both.
Not sure regarding the general case. You likely want to use ARRAY_AGG to
get a result like:
Farm_id_100, { 'CROP_CD_1', 'CROP_CD_2' }
You could then probably get a query to output something like:
(crop_id, farms_exclusive, farms_shared, farms_without)
Where each of the farms_ columns is an array of farm_ids that match the
particular conditional
= ALL (exclusive); != ALL = ANY (shared); != ANY (without)
David J.
--
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] Server move using rsync
Thanks for sharing your experience and thoughts Venkat, Venkat Balaji said: We are performing backups to our production server exactly the same way. We have been through some problems while restoring and bringing up the database. If you are planning to take initial complete rsync with subsequent incremental rsyncs, then you need to make sure that you have all the WAL archives starting from the initial rsync on Day 1. Also are you doing the following? 1. pg_start_backup() - rsync - pg_stop_backup() ? 2. Please let us know your WAL Archive backup strategy. We're not doing this long-term, in order to have a backup server we can fail-over to, but rather as a one-off low impact move of our database. Consequently, instead of using pg_start_backup and pg_stop_backup, and keeping all WAL, we're stopping the database, rsync of everything, and starting the database in the new server, with it appearing to the new server (if it was capable of noticing such things) that it had simply been shutdown and restarted. The initial and repeated rsyncs while the first server is running and in use, are solely in order to reduce the time that the rsync takes while the postgresql application is stopped. Do you still think we need to do anything special with pg_start_backup, pg_stop_backup, and WAL archives? Is there any way during that week, that we can verify whether our partially completed database move process is going to result in a database that starts up ok? In general, yes, database can start up normally. Without WAL Archives, recovering to a particular time would not be possible. Without doing pg_start_backup, and with rsync not performing a snapshot backup, my assumption is that until we do an rsync with the service shutdown, whatever we've got at the location we're copying to, is not self-consistent. If we start up postgresql on it, won't it think it is recovering from a sudden crash? I think it may either appear to recover ok, or complain about various things, and not start up ok, with neither option providing us with much insight, as all that could tell us is that either some disk blocks are consistent, or some are not, which is our starting assumption anyway. Starting up postgresql would probably result in more disk block changes that will result in more work next time we rsync. I'm wondering whether it's worth doing anyway, simply to check that it doesn't do something completely unexpected, which would presumably alert us to something we hadn't considered. How badly can we screw things up, given we intend to perform a final rsync with no postgresql services running? What should we try and avoid doing, and why? We might simply compare some hashes between the two systems, of some files that haven't had their last-modified dates changed since the last rsync. Regards, Stephen. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. Please consider the environment before printing this e-mail __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- 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] Server hitting 100% CPU usage, system comes to a crawl.
On Thu, Oct 27, 2011 at 02:09:51PM -0600, Brian Fehrle wrote: - On 10/27/2011 01:48 PM, Scott Marlowe wrote: - On Thu, Oct 27, 2011 at 12:39 PM, Brian Fehrle - bri...@consistentstate.com wrote: - Looking at top, I see no SWAP usage, very little IOWait, and there are a - large number of postmaster processes at 100% cpu usage (makes sense, at - this - point there are 150 or so queries currently executing on the database). - - Tasks: 713 total, 44 running, 668 sleeping, 0 stopped, 1 zombie - Cpu(s): 4.4%us, 92.0%sy, 0.0%ni, 3.0%id, 0.0%wa, 0.0%hi, 0.3%si, - 0.2%st - Mem: 134217728k total, 131229972k used, 2987756k free, 462444k buffers - Swap: 8388600k total, 296k used, 8388304k free, 119029580k cached - OK, a few points. 1: You've got a zombie process. Find out what's - causing that, it could be a trigger of some type for this behaviour. - 2: You're 92% sys. That's bad. It means the OS is chewing up 92% of - your 32 cores doing something. what tasks are at the top of the list - in top? - - Out of the top 50 processes in top, 48 of them are postmasters, one is - syslog, and one is psql. Each of the postmasters have a high %CPU, the - top ones being 80% and higher, the rest being anywhere between 30% - - 60%. Would postmaster 'queries' that are running attribute to the sys - CPU usage, or should they be under the 'us' CPU usage? total spitball here but - I had something similar happen once and it was syslog causing the problem. Are you using regular vanilla syslog? or syslog-ng/rsyslog? my problem was vanilla syslog. When I moved to -ng/rsyslog or logging to a file my problem went away. Dave -- 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] Need Help With a A Simple Query That's Not So Simple
On 10/31/2011 5:05 PM, David Johnston wrote:
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bill Thoen
Sent: Monday, October 31, 2011 6:51 PM
To: Postgrresql
Subject: [GENERAL] Need Help With a A Simple Query That's Not So Simple
[...]
What I'd like to know is,
which Farms and how many are growing only corn, which and how many are
growing soybeans and which and how many are growing both?
[...]
Is there a better way to
get the farm counts or data by categories like farms growing only corn,
farms growing only soybeans, farms growing both? I'm also interested in
possibly expanding to a general case where I could select more than two
crops. and get counts of the permutations.
[...]
---
General Idea:
WITH crop_one AS (
SELECT farm_id, crop_cd AS crop_one_cd ...
), crop_two AS (
SELECT farm_id, crop_cd AS crop_two_cd
)
SELECT *
FROM crop_one
FULL OUTER JOIN crop_two USING (farm_id)
;
Records with NULL for crop_one_cd only grow crop 2, records with NULL for
crop_two_cd only grow crop 1, records where neither field is NULL grow
both.
Not sure regarding the general case. You likely want to use ARRAY_AGG to
get a result like:
Farm_id_100, { 'CROP_CD_1', 'CROP_CD_2' }
You could then probably get a query to output something like:
(crop_id, farms_exclusive, farms_shared, farms_without)
Where each of the farms_ columns is an array of farm_ids that match the
particular conditional
= ALL (exclusive); != ALL = ANY (shared); != ANY (without)
David J.
Thanks David! That worked great! When I filled in the the query from the
general idea in your example above like so:
WITH crop_one AS (
SELECT farm_id, crop_cd AS corn FROM gfc_inilmoidia_2007 WHERE
crop_cd ='0041'
), crop_two AS (
SELECT farm_id, crop_cd AS soybeans FROM gfc_inilmoidia_2007
WHERE crop_cd = '0081'
)
SELECT *
FROM crop_one
FULL OUTER JOIN crop_two USING (farm_id)
;
It produced the following (which is essentially the base of what I'm
looking for):
farm_id | corn | soybeans
-+--+--
1473 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1475 | 0041 |
1475 | 0041 |
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1477 | 0041 |
1478 | 0041 | 0081
1479 | 0041 |
1480 | | 0081
1480 | | 0081
Thanks so much for the quick reply. You've also just opened up a whole
new area of query possibilities for me of which I wasn't aware
- Bill Thoen
[GENERAL] Can I track DB connections through a generic acct on the basis of linux idsid
Hi: PG v8.3.4 on linux. Server is at one corporate site, half the DB users are at that site, the other half are at a different site 2 time zones away. Users from both sites access the DB through the same, generic postgres user accout that has select/insert/update/delete only. And almost all of this is through perl scripts that use DBI as a gateway into the PG DB. I want to be able to determine who ran what script and from what user site. All this info is at my fingertips during the execution of the perl script. I would like to ba able to log this info immediately before or after the DB connection, not wait for the transaction or query to complete. I would prefer not to have to insert this info to a log table because that would mean waiting for an additional network hit for simple queries from the remote site. Is there a way to pass the info (idsid, app, site) to the perl/dbi connection string and get this info reported in the deamon runlog somehow? How about raise notice? Could that be made to work somehow? OK, having said all that, a completely different tact... There are 1468 potential users (idsids) out there that can query the DB. Does it make sense to create a DB user for each of those, giving them all the same access through a role or something, and then attacking the problem from that direction? Is 1468 impractical? Thanks in advance!
[GENERAL]
Hello,everyone. I want to build a debian package from the source code.How should I do?please tell me the detailed steps. Thanks. fei
Re: [GENERAL] does reindex need exclusive table access?
On 01/11/11 02:51, Gauthier, Dave wrote: v8.3.4 on linux. Does reindex table foo require no other users accessing the foo table? Trying to understand why this seems to be stalled when I attempt this on a live DB (if runs fine/fast on a copy of the DB that no one uses). Yes, it requires an exclusive lock. It doesn't matter whether or not you run it as part of an explicit transaction. There is not currently any 'REINDEX CONCURRENTLY' command - not unless it's been added in a very recent version and I haven't noticed yet. You can CREATE INDEX CONCURRENTLY then drop the old index, though. A workaround for reindexing while live is to begin a transaction, create the new index with a new name, drop the old one, rename the new one to the old one, and commit. This only requires an exclusive lock for the period of the drop and rename. On more recent versions you can even use this for indexes that implement primary key or unique constrants by using the 'ADD CONSTRAINT ... USING INDEX ...' syntax; see: http://www.postgresql.org/docs/current/static/sql-altertable.html -- Craig Ringer -- 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] does reindex need exclusive table access?
Craig Ringer ring...@ringerc.id.au writes: On 01/11/11 02:51, Gauthier, Dave wrote: Does reindex table foo require no other users accessing the foo table? Trying to understand why this seems to be stalled when I attempt this on a live DB (if runs fine/fast on a copy of the DB that no one uses). Yes, it requires an exclusive lock. There is not currently any 'REINDEX CONCURRENTLY' command - not unless it's been added in a very recent version and I haven't noticed yet. You can CREATE INDEX CONCURRENTLY then drop the old index, though. Yeah. The hard part of that is not actually the reindex, it's the drop the old index ... because the old index might be in use by concurrent SELECTs. So dropping the old index absolutely requires an exclusive lock, to ensure there are no read-only transactions depending on that version of the index. Building a new index can be done with a much weaker lock. A straight reindex doesn't have a lot of choice here. We could have it take a lesser lock while it's rebuilding the index, and then try to upgrade to exclusive lock to move the new version into place --- but upgrading your lock is a well-known recipe for causing deadlocks. The one good thing about build-a-new-index-with-REINDEX-CONCURRENTLY- and-then-drop-the-old-index is that the DROP requires exclusive lock for only a tiny amount of time, and if the DROP does fail and roll back because of conflicts, you haven't lost the work of building the new index version. You can just try the DROP again. A workaround for reindexing while live is to begin a transaction, create the new index with a new name, drop the old one, rename the new one to the old one, and commit. This only requires an exclusive lock for the period of the drop and rename. I'd do that in two transactions, so you don't lose the rebuild work if there's a problem getting exclusive lock. 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] Can I track DB connections through a generic acct on the basis of linux idsid
On 01/11/11 09:23, Gauthier, Dave wrote: Is there a way to pass the info (idsid, app, site) to the perl/dbi connection string and get this info reported in the deamon runlog somehow? In newer versions of Pg you can use the application ID field. It's still likely going to be another network round-trip for the DBI driver to set this behind the scenes, though. How about raise notice? Could that be made to work somehow? Sure, but again you'll still have to send the data to the server, so you might as well just INSERT it into a log table. It sounds like you want to send some custom info as part of the initial connection. Honestly, I wouldn't worry too much about this. If you examine the connection setup using a protocol analyser like wireshark, you're likely to see a bit of client/server chat between the server and DBI driver already. Adding to this slightly shouldn't matter very much. Try it and see how much impact it actually has. OK, having said all that, a completely different tact... There are 1468 potential users (idsids) out there that can query the DB. Does it make sense to create a DB user for each of those, giving them all the same access through a role or something, and then attacking the problem from that direction? Is 1468 impractical? To me, it's more than you'd have to maintain them. Having server-side user identity is great for when you're doing trigger-based auditing and the like, but it can be a pain to keep up to date with other user info elsewhere. Binding Pg to an LDAP directory or the like can help, but isn't completely transparent and can be more hassle than it saves. -- Craig Ringer
Re: [GENERAL]
On 01/11/11 09:58, daflmx wrote: Hello,everyone. I want to build a debian package from the source code.How should I do?please tell me the detailed steps. Thanks. It depends on why. The usual answer is: Don't, just install from the debian repositories or backports.org. Why do you need to build your own package? Your answer affects the way you should go about making a package, so it matters. - Is it to install a new Pg on an old version of Debian? If so, why can't you use the version on backports.org? - Do you want to modify a version that's already available as a package for your Debian version to compile it with different options? - Do you need a full-featured PostgreSQL package that interacts with postgresql-common and the wrapper scripts, does initdb for you on install, creates the postgres user, etc? Or do you just need a simple package that contains the files produced by make install where you still have to do all the post-install steps yourself? - What version of debian do you want to install on, and which version of PostgreSQL do you want to install? - Any other reasons you're doing this? Any other special needs? If you just need to package up the files produced by make install, you can use checkconfig to make a simple deb. For more complex cases, what to do depends very much on what you answer above. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
