Re: [GENERAL] Foreign Tables
Shigeru Hanada shigeru.han...@gmail.com 7:48 AM (5 hours ago) to Eliot, pgsql-general This message may not have been sent by: shigeru.han...@gmail.com Learn morehttp://mail.google.com/support/bin/answer.py?hl=enctx=mailanswer=185812 Report phishing Why this message is popping up in my inbox ? Is there any problem with in-house gmail setting of mine. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Thu, Nov 17, 2011 at 7:48 AM, Shigeru Hanada shigeru.han...@gmail.comwrote: Hi Eliot, 2011/11/17 Eliot Gable egable+pgsql-gene...@gmail.com: snip 1a) Can the foreign tables be written to? For example, I have server1 with table foo and server2 which does 'create foreign table bar' where bar references server1.foo. Can server2 write to bar and have it show in server1.foo? Foreign tables in 9.1 are read-only, so you can't write to them. Making foreign tables writable is a TODO item, but ISTM it's difficult to implement it for even 9.2. So the answer to your question 1a) is No. BTW, I'm interested in your use case very much because I'm working on enhancement of foreign tables for 9.2. I would appreciate it if you tell me some details of your reporting system. Foreign tables may suit your reporting system. a) Where are materialized views, triggers and source tables? I guess all of them are on appliances, not on PostgreSQL server for reporting. b) Do you need to update data on appliances during making a report? If you do, how do you do it without foreign tables? (from reporting application, or using dblink or something?) If source of report are on appliances as materialized views (or ordinary tables), and you don't need to update data on appliances, I think you can use foreign tables to gather information on a PostgreSQL server. In this case, you need to define foreign tables for each materialized view (or ordinary table). Then, you can execute SELECT statement using foreign tables on the reporting server to gather information from appliances. FDW for PostgreSQL 9.1, pgsql_fdw, is provided as a third party product[1], though it seems not ready for production use. # Currently you need to extract pgsql_fdw from git repository. Incidentally, pgsql_fdw is being proposed as a contrib module of 9.2[2]. [1]https://sourceforge.net/projects/interdbconnect/ [2]http://archives.postgresql.org/pgsql-hackers/2011-10/msg01329.php Regards, -- Shigeru Hanada -- 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] how could duplicate pkey exist in psql?
Yan Chunlu wrote: recently I have found several tables has exactly the same pkey, here is the definition: diggcontent_data_account_pkey PRIMARY KEY, btree (thing_id, key) the data is like this: 159292 | funnypics_link_point | 41 | num 159292 | funnypics_link_point | 40 | num I could not even update this record. It could be a software bug or something else, but did you ever restore the database after a storage problem? This has happened to me once: http://archives.postgresql.org/pgsql-general/2010-02/msg00971.php Yours, Laurenz Albe -- 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] how could duplicate pkey exist in psql?
On 17 November 2011 06:19, Yan Chunlu springri...@gmail.com wrote: recently I have found several tables has exactly the same pkey, here is the definition: diggcontent_data_account_pkey PRIMARY KEY, btree (thing_id, key) the data is like this: 159292 | funnypics_link_point | 41 | num 159292 | funnypics_link_point | 40 | num I could not even update this record. really confused about how could this happen... thanks! Hi, could you send us result of the query: select thing_id, '|'||key||'|' from table? Maybe there are some more spaces in the key column which were hidden by table alignment in the client? regards Szymon
Re: [GENERAL] Is it ever necessary to vacuum a table that only gets inserts/updates?
Thank you Tom John. In this case, there are no updates/deleted - only inserts. For now, I have set per-table autovacuum rules in order to minimize the frequency of vacuums but to ensure the statistics are updated frequently with analyze: Table auto-vacuum VACUUM base threshold5 Table auto-vacuum VACUUM scale factor0.3 Table auto-vacuum ANALYZE base threshold5 Table auto-vacuum ANALYZE scale factor0.02 Table auto-vacuum VACUUM cost delay20 Table auto-vacuum VACUUM cost limit200 On Wed, Nov 16, 2011 at 9:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: John R Pierce pie...@hogranch.com writes: On 11/16/11 4:24 PM, Jason Buberel wrote: Just wondering if there is ever a reason to vacuum a very large table ( 1B rows) containing rows that never has rows deleted. no updates either? To clarify: in Postgres, an update means an insert and a delete. So unless you mean that this table is insert-only, you certainly still need vacuum. you still want to do a vacuum analyze every so often to update the statistics used by the planner. If it's purely an insert-only table, such as a logging table, then in principle you only need periodic ANALYZEs and not any VACUUMs. VACUUM could still be worthwhile though, because (a) it will set commit hint bits on all pages and (b) it will set visibility-map bits on all pages. An ANALYZE would only do those things for the random sample of pages that it visits. While neither of those things are critical, they do offload work from future queries that would otherwise have to do that work in-line. So if you've got a maintenance window where the database isn't answering queries anyway, it could be worthwhile to run a VACUUM just to get those bits set. regards, tom lane -- Jason L. Buberel CTO, Altos Research http://www.altosresearch.com/ 650.603.0907
Re: [GENERAL] How to lock and unlock table in postgresql
Hi Alban, Thanks for the reply. 1) I'm using PostgreSQL 8.1; So, I can't use RETURNING clause! 2) The function I gave is just to put my understanding! Thanks for spotting the error though. Regards, Siva. -Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: Thursday, November 17, 2011 1:20 PM To: Siva Palanisamy Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to lock and unlock table in postgresql On 17 Nov 2011, at 7:10, Siva Palanisamy wrote: If there is a better solution, kindly let me know. CREATE OR REPLACE FUNCTION Fun() RETURNS VOID AS ' DECLARE Id INTEGER; BEGIN INSERT INTO table1 VALUES (DEFAULT, ''Sample'', DEFAULT); SELECT MAX(id) INTO Id FROM table1; INSERT INTO table2 VALUES(DEFAULT,Id,''sampledata''); END; ' LANGUAGE 'plpgsql'; Regards, Siva. As John says, you're re-inventing the wheel that sequences solve. You could also get the id using INSERT .. RETURNING. You have another problem on your hands though. You have a naming conflict between your variable name and a column name in that second query: id and Id are the same. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. --- -- 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] how could duplicate pkey exist in psql?
Em 17-11-2011 03:19, Yan Chunlu escreveu: recently I have found several tables has exactly the same pkey, here is the definition: diggcontent_data_account_pkey PRIMARY KEY, btree (thing_id, key) the data is like this: 159292 | funnypics_link_point | 41 | num 159292 | funnypics_link_point | 40 | num I could not even update this record. really confused about how could this happen... thanks! I know one scenario this can happen on Linux. In my case, it was caused by a rsync... instead copy to a different location, script was copying pg_xlog over own pg_xlog. I did this stupidity once, and learned for a life time. Lost two hours of work to recover everything (from backup, at least I had one). Be careful with rsync and cp, since Linux does not block files from being overwriten even when they are in use. Regards, Edson. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] connection manager pgpool... help
srs, need help. I have several applications accessing my databases. My customers are divided into databases within my cluster pg905, have at least a 60/90 clients per cluster, heavy number of updates in the bank, as inserts, updates and deletes and an average 50 to 100 simultaneous connections all the time. I plan on using a connection pool (pgpool), thinking to improve performance and have better management of the connections. Based on my scenario, can anyone help me? I'm using postgresql version 9.0.5 thank you -- *Atenciosamente, Emanuel Araújo* http://eacshm.wordpress.com/ * * *Linux Certified LPIC-1*
Re: [GENERAL] Is it ever necessary to vacuum a table that only gets inserts/updates?
On Nov 17, 2011 1:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: John R Pierce pie...@hogranch.com writes: On 11/16/11 4:24 PM, Jason Buberel wrote: Just wondering if there is ever a reason to vacuum a very large table ( 1B rows) containing rows that never has rows deleted. no updates either? To clarify: in Postgres, an update means an insert and a delete. So unless you mean that this table is insert-only, you certainly still need vacuum. you still want to do a vacuum analyze every so often to update the statistics used by the planner. If it's purely an insert-only table, such as a logging table, then in principle you only need periodic ANALYZEs and not any VACUUMs. Won't a VACUUM FREEZE (or autovac equivalent) be necessary eventually, to handle xid wraparound? If so, doing it pre-emptively might help avoid a giant I/O load and work pause when its forced. Or am I just confused?
Re: [GENERAL] how could duplicate pkey exist in psql?
I am using pgpool's replication feature, it does copy pg_xlog from one server to another, was that possible cause of the problem? thanks for the help! On Thu, Nov 17, 2011 at 5:38 PM, Edson Richter rich...@simkorp.com.brwrote: Em 17-11-2011 03:19, Yan Chunlu escreveu: recently I have found several tables has exactly the same pkey, here is the definition: diggcontent_data_account_**pkey PRIMARY KEY, btree (thing_id, key) the data is like this: 159292 | funnypics_link_point | 41 | num 159292 | funnypics_link_point | 40 | num I could not even update this record. really confused about how could this happen... thanks! I know one scenario this can happen on Linux. In my case, it was caused by a rsync... instead copy to a different location, script was copying pg_xlog over own pg_xlog. I did this stupidity once, and learned for a life time. Lost two hours of work to recover everything (from backup, at least I had one). Be careful with rsync and cp, since Linux does not block files from being overwriten even when they are in use. Regards, Edson. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how could duplicate pkey exist in psql?
seems they are identical: 159292 | |funnypicscn_link_karma| 159292 | |funnypicscn_link_karma| On Thu, Nov 17, 2011 at 4:07 PM, Szymon Guz mabew...@gmail.com wrote: On 17 November 2011 06:19, Yan Chunlu springri...@gmail.com wrote: recently I have found several tables has exactly the same pkey, here is the definition: diggcontent_data_account_pkey PRIMARY KEY, btree (thing_id, key) the data is like this: 159292 | funnypics_link_point | 41 | num 159292 | funnypics_link_point | 40 | num I could not even update this record. really confused about how could this happen... thanks! Hi, could you send us result of the query: select thing_id, '|'||key||'|' from table? Maybe there are some more spaces in the key column which were hidden by table alignment in the client? regards Szymon
[GENERAL] upgrading from 8.3 to 9.0
Hi, I'm on the verge of upgrading a server (Fedora 8 ehehe) running postgresql 8.3 It also has postgis 1.3 installed. Thinking of using pgadmin3 to perform the backup and then restore it after I've upgraded the server to fedora 15/16 and thus upgrading postgresql to 9.0. I seem to remember problems with restoring from a pgadmin's .backup file in the past... :S Any pitfalls I should be aware of? Btw: it's a reasonably large DB with 30mil+ rows... Already thankful for any insight, -- Pedro Doria Meunier GSM: +351 91 581 88 23 Skype: pdoriam signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] How could I find the last modified procedure in the database?
On Wed, Nov 16, 2011 at 07:02:11PM -0500, Tom Lane wrote: I'd try looking to see which row in pg_proc has the latest xmin. Unfortunately you can't ORDER BY xmin ... order by age(xmin) ? 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
Re: [GENERAL] how could duplicate pkey exist in psql?
On Thu, Nov 17, 2011 at 01:19:30PM +0800, Yan Chunlu wrote: recently I have found several tables has exactly the same pkey, here is the definition: diggcontent_data_account_pkey PRIMARY KEY, btree (thing_id, key) please check: select thing_id, key, count(*) from diggcontent_data_account group by 1,2 having count(*) 1; this will show if you have really duplicated values. if you have - the index ( diggcontent_data_account_pkey ) is broken. Exact reason can vary, any chance this database is hot-backup restored from different system? 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] checkpoints are occurring too frequently
I have a lot of entries like this in the log file 2011-11-17 02:02:46 PYST LOG: checkpoints are occurring too frequently (13 seconds apart) 2011-11-17 02:02:46 PYST HINT: Consider increasing the configuration parameter checkpoint_segments. No, checkpoint parameters in postgres.conf are: checkpoint_segments = 32 # in logfile segments, min 1, 16MB each checkpoint_timeout = 10min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 What should be a correct value for checkpoint_segments to avoid excessive checkpoint events?
Re: [GENERAL] checkpoints are occurring too frequently
increase your checkpoint segments -- GJ -- 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] checkpoints are occurring too frequently
Hi Anibal, On Thu, 17 Nov 2011 09:48:10 -0300, Anibal David Acosta a...@devshock.com wrote: What should be a correct value for checkpoint_segments to avoid excessive checkpoint events? There is no golden rule or value that fits all scenarios. Usually 32 is a good value to start with, however it might not be perfectly tailored for your environment. To give you an idea, currently you are issuing a checkpoint every 32*16MB of WAL traffic (or every 10 minutes). Maybe you can describe us better your workload, if it is subject to usage spikes or regularly distributed throughout the day mainly in terms of inserts/updates/deletes. I would gradually try and increase checkpoint_segments by 32 and monitor the effects. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it -- 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] checkpoints are occurring too frequently
Thanks! -Mensaje original- De: Gabriele Bartolini [mailto:gabriele.bartol...@2ndquadrant.it] Enviado el: jueves, 17 de noviembre de 2011 10:14 a.m. Para: Anibal David Acosta CC: pgsql-general@postgresql.org Asunto: Re: [GENERAL] checkpoints are occurring too frequently Hi Anibal, On Thu, 17 Nov 2011 09:48:10 -0300, Anibal David Acosta a...@devshock.com wrote: What should be a correct value for checkpoint_segments to avoid excessive checkpoint events? There is no golden rule or value that fits all scenarios. Usually 32 is a good value to start with, however it might not be perfectly tailored for your environment. To give you an idea, currently you are issuing a checkpoint every 32*16MB of WAL traffic (or every 10 minutes). Maybe you can describe us better your workload, if it is subject to usage spikes or regularly distributed throughout the day mainly in terms of inserts/updates/deletes. I would gradually try and increase checkpoint_segments by 32 and monitor the effects. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction wraparound problem due to wrong datfrozenxid?
I ran into a rather unusual problem today where Postgres brought down a
database to avoid transaction wraparound in a situation where it doesn't appear
that it should have.
The error in the log is explicit enough...
Nov 16 04:00:03 SRP1 postgres[58101]: [1-1] FATAL: database is not accepting
commands to avoid wraparound data loss in database stat
Nov 16 04:00:03 SRP1 postgres[58101]: [1-2] HINT: Stop the postmaster and use
a standalone backend to vacuum database stat.
Yet, going back several days in the logs, there were none of the usual WARNING
messages in the log about this situation occurring in xxx transactions.
When I query datfrozenxid value in pg_database it certainly showed a problem.
backend SELECT datname,datfrozenxid, age(datfrozenxid) FROM pg_database;
1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid(typeid = 28, len = 4, typmod = -1, byval = t)
3: age (typeid = 23, len = 4, typmod = -1, byval = t)
1: datname = postgres(typeid = 19, len = 64, typmod = -1,
byval = f)
2: datfrozenxid = 2699851604 (typeid = 28, len = 4, typmod = -1,
byval = t)
3: age = 1269165380 (typeid = 23, len = 4, typmod = -1, byval = t)
1: datname = stat (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = 1822525199 (typeid = 28, len = 4, typmod = -1,
byval = t)
*** 3: age = 2146491785 (typeid = 23, len = 4, typmod = -1, byval =
t)
1: datname = config (typeid = 19, len = 64, typmod = -1, byval
= f)
2: datfrozenxid = 3869013990 (typeid = 28, len = 4, typmod = -1,
byval = t)
3: age = 12994 (typeid = 23, len = 4, typmod = -1, byval = t)
1: datname = template1 (typeid = 19, len = 64, typmod = -1,
byval = f)
2: datfrozenxid = 2000352260 (typeid = 28, len = 4, typmod = -1,
byval = t)
3: age = 1968664724 (typeid = 23, len = 4, typmod = -1, byval = t)
1: datname = template0 (typeid = 19, len = 64, typmod = -1,
byval = f)
2: datfrozenxid = 2000357564 (typeid = 28, len = 4, typmod = -1,
byval = t)
3: age = 1968659420 (typeid = 23, len = 4, typmod = -1, byval = t)
I then ran a query to check the age of all the relfrozenxid on each of the
tables in the DB and it indicated that they were all OK.
backend select sum(case when age(relfrozenxid) 20 then 1 else 0 end)
as gt_2billion, sum(case when age(relfrozenxid) between 15 and
20 then 1 else 0 end) as gt_1_5billion, sum(case when age(relfrozenxid)
between 10 and 15 then 1 else 0 end) as gt_1billion, sum(case
when age(relfrozenxid) between 5 and 10 then 1 else 0 end) as
gt_500million, sum(case when age(relfrozenxid) between 1 and 5
then 1 else 0 end) as gt_100million, sum(case when age(relfrozenxid)
1 then 1 else 0 end) as lt_100million from pg_class where relkind in
('r','t');
1: gt_2billion (typeid = 20, len = 8, typmod = -1, byval = f)
2: gt_1_5billion (typeid = 20, len = 8, typmod = -1, byval = f)
3: gt_1billion (typeid = 20, len = 8, typmod = -1, byval = f)
4: gt_500million (typeid = 20, len = 8, typmod = -1, byval = f)
5: gt_100million (typeid = 20, len = 8, typmod = -1, byval = f)
6: lt_100million (typeid = 20, len = 8, typmod = -1, byval = f)
1: gt_2billion = 0 (typeid = 20, len = 8, typmod = -1, byval = f)
2: gt_1_5billion = 0 (typeid = 20, len = 8, typmod = -1, byval = f)
3: gt_1billion = 0 (typeid = 20, len = 8, typmod = -1, byval = f)
4: gt_500million = 628 (typeid = 20, len = 8, typmod = -1,
byval = f)
5: gt_100million = 8928 (typeid = 20, len = 8, typmod = -1,
byval = f)
6: lt_100million = 0 (typeid = 20, len = 8, typmod = -1, byval = f)
I confirmed this with...
backend select relname,relfrozenxid,age(relfrozenxid) from pg_class where
relkind in('r','t') order by 3 desc limit 1;
1: relname (typeid = 19, len = 64, typmod = -1, byval = f)
2: relfrozenxid(typeid = 28, len = 4, typmod = -1, byval = t)
3: age (typeid = 23, len = 4, typmod = -1, byval = t)
1: relname = qoe_flowbwidth_dist_dig1_014(typeid = 19, len =
64, typmod = -1, byval = f)
2: relfrozenxid = 2970264132 (typeid = 28, len = 4, typmod = -1,
byval = t)
3: age = 998752902 (typeid = 23, len = 4, typmod = -1, byval = t)
My understanding has always been that the datfrozenxid should match this
relfrozenxid, which it evidently doesn't.
My environment:
FreeBSD 6
PG 8.2.4(Yes, I intend to upgrade, which would be helped if someone can say
that this problem is fixed in some future
[GENERAL] synchronous replication + fsync=off?
Hello, I have two servers with battery backed power supply (USV). So it is unlikely, that both will crash at the same time. Will synchronous replication work with fsync=off? That means we will commit to system cache, but not to disk. Data will not survive a system crash but the second system should still be consistent. Or: will it work with master: fsync=off and slave(s): fsync=on? In this case master is free for read-queries and slave has time to do all the heavy writing. Data on master will not survive a crash but can be restored from slave. Thanks, Joerg
Re: [GENERAL] How to lock and unlock table in postgresql
Zitat von Siva Palanisamy siv...@hcl.com: Hi Alban, Thanks for the reply. 1) I'm using PostgreSQL 8.1; So, I can't use RETURNING clause! You should Upgrade ASAP! 8.1 is 'out of lifetime'. Regards, Andreas -- 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] synchronous replication + fsync=off?
What if power supply goes ? What if someone trips on the cable, and both servers go ? -- 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] how could duplicate pkey exist in psql?
Em 17-11-2011 09:21, Yan Chunlu escreveu: I am using pgpool's replication feature, it does copy pg_xlog from one server to another, was that possible cause of the problem? I did not mean that this IS your problem, I just gave you a tip regarding a problem I had in the past, that eventually has same simptom. This scenario only happens when your script is copy data over own data... like in rsync -ar root@127.0.0.1:/var/lib/pgsql/9.0/data/* /var/lib/pgsql/9.0/data/ the command above is highly dangerous because it copies data over the network link over its own data... if you have transactions runing during the command above, you will get a crash (and, in my case, I had duplicate primary keys). Would be better to check if this could be happening to you... some script overwriting data using rsync, cp, etc... I had no other situation where Postgresql allowed duplicate keys. Hope this helps, Edson. thanks for the help! On Thu, Nov 17, 2011 at 5:38 PM, Edson Richter rich...@simkorp.com.br mailto:rich...@simkorp.com.br wrote: Em 17-11-2011 03:19, Yan Chunlu escreveu: recently I have found several tables has exactly the same pkey, here is the definition: diggcontent_data_account_pkey PRIMARY KEY, btree (thing_id, key) the data is like this: 159292 | funnypics_link_point | 41 | num 159292 | funnypics_link_point | 40 | num I could not even update this record. really confused about how could this happen... thanks! I know one scenario this can happen on Linux. In my case, it was caused by a rsync... instead copy to a different location, script was copying pg_xlog over own pg_xlog. I did this stupidity once, and learned for a life time. Lost two hours of work to recover everything (from backup, at least I had one). Be careful with rsync and cp, since Linux does not block files from being overwriten even when they are in use. Regards, Edson. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it ever necessary to vacuum a table that only gets inserts/updates?
Craig Ringer ring...@ringerc.id.au writes: On Nov 17, 2011 1:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: If it's purely an insert-only table, such as a logging table, then in principle you only need periodic ANALYZEs and not any VACUUMs. Won't a VACUUM FREEZE (or autovac equivalent) be necessary eventually, to handle xid wraparound? Sure, but if he's continually adding new rows, I don't see much point in launching extra freeze operations. 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] synchronous replication + fsync=off?
On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg jschub...@cebacus.de wrote: Hello, I have two servers with battery backed power supply (USV). So it is unlikely, that both will crash at the same time. Will synchronous replication work with fsync=off? That means we will commit to system cache, but not to disk. Data will not survive a system crash but the second system should still be consistent. you should never use fsync=off (in production at least) the appropiate parameter to use is synchronous_commit which is the one that controls synchronous replication: off = no local nor remote synchronous commit local = local synchronous commit but no remote on = both, local and remote, synchronous commit synchronous commit = flushed to disk once all that said, i guess you can use fsync on any combination (off on master and on on standby, for your case) but i haven't tried. anyway that will guarantee you will lose your master instalation on OS crash and i think to remember that even if the OS doesn't crash there is a risk (altough i can't find the mail saying that) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] synchronous replication + fsync=off?
On Thu, Nov 17, 2011 at 9:07 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg jschub...@cebacus.de wrote: Hello, I have two servers with battery backed power supply (USV). So it is unlikely, that both will crash at the same time. Will synchronous replication work with fsync=off? That means we will commit to system cache, but not to disk. Data will not survive a system crash but the second system should still be consistent. you should never use fsync=off (in production at least) That's not entirely true. for instance, session servers are fine with fsync=off because the data in them is only alive while the session is up. Corrupted database means reinit db, restore schema, put back in loop. But yeh for data that means anything, fsync off is a bad idea. -- 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] synchronous replication + fsync=off?
On 17 Listopad 2011, 17:07, Jaime Casanova wrote: On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg jschub...@cebacus.de wrote: Hello, I have two servers with battery backed power supply (USV). So it is unlikely, that both will crash at the same time. Will synchronous replication work with fsync=off? That means we will commit to system cache, but not to disk. Data will not survive a system crash but the second system should still be consistent. you should never use fsync=off (in production at least) the appropiate parameter to use is synchronous_commit which is the one that controls synchronous replication: off = no local nor remote synchronous commit local = local synchronous commit but no remote on = both, local and remote, synchronous commit synchronous commit = flushed to disk While I don't recommend it, fsync=off definitely is an option, especially with sync replication. The synchronous_commit is not a 1:1 replacement. Imagine for example a master with lot of I/O, and a sync standby. By setting fsync=off on the master and fsync=on on the slave the master does not need to wait for the fsync (so the I/O is not that stressed and can handle more requests from clients), but the slave actually does fsync. So you don't force local fsync, but you're waiting for fsync from the standby. But standby doesn't need to handle all the I/O the primary has. You can't do this with synchronous_commit - that basically forces you to do local fsync on commit, or not to wait for the commit at all. Tomas Disclaimer: I haven't actually tried this, so maybe I missed something. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] monitoring sql queries
I am in need of a tool or method to see each/every SQL query that hits the PostgreSQL database. By query I mean the query in SQL syntax with all the parameters passed. What I want to do is: 1) see the query 2) Determine how long the query takes to execute 3) Possibly log both of the above to a log file Is there any tool internal to PostgreSQL that would allow me to do this? I cannot monitor it from the code that is actually connecting sending the query. any help or pointers would be greatly appreciated. J.V. -- 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] monitoring sql queries
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote: I am in need of a tool or method to see each/every SQL query that hits the PostgreSQL database. By query I mean the query in SQL syntax with all the parameters passed. What I want to do is: 1) see the query 2) Determine how long the query takes to execute 3) Possibly log both of the above to a log file Is there any tool internal to PostgreSQL that would allow me to do this? I cannot monitor it from the code that is actually connecting sending the query. any help or pointers would be greatly appreciated. just enable logging of queries. 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
Re: [GENERAL] monitoring sql queries
On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote: On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote: I am in need of a tool or method to see each/every SQL query that hits the PostgreSQL database. By query I mean the query in SQL syntax with all the parameters passed. What I want to do is: 1) see the query 2) Determine how long the query takes to execute 3) Possibly log both of the above to a log file Is there any tool internal to PostgreSQL that would allow me to do this? I cannot monitor it from the code that is actually connecting sending the query. any help or pointers would be greatly appreciated. just enable logging of queries. As depesz mentioned, there's a log_min_duration GUC, that allows you to log queries that exceed some time interval. If you want to log all queries, you may set this to 0 but it may easily fill your log with garbage. There are two contrib modules that might help you - pg_stat_statements and auto_explain. The former one is probably more interesting in this case. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Session variables and C functions
I'm writing a custom C function and one of the things it needs to do is to be configured from the SQL-land, per user session (different users have different configurations in different sessions). I have found (and have used) the SET SESSION command and the current_setting() function for use with custom_variable_classes configuration in postgresql.conf, but I'm not sure how to achieve the same effect from C. Ideally, the C module would create its own custom variable class, named e.g. module, then define some setting, e.g. module.setting. The users would then execute an SQL command such as SET SESSION module.setting='something', and the module would need to pick this up in the C function. Any pointers to how this would be done? signature.asc Description: OpenPGP digital signature
Re: [GENERAL] monitoring sql queries
On Thu, Nov 17, 2011 at 11:46 AM, Tomas Vondra t...@fuzzy.cz wrote: On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote: On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote: I am in need of a tool or method to see each/every SQL query that hits the PostgreSQL database. By query I mean the query in SQL syntax with all the parameters passed. What I want to do is: 1) see the query 2) Determine how long the query takes to execute 3) Possibly log both of the above to a log file Is there any tool internal to PostgreSQL that would allow me to do this? I cannot monitor it from the code that is actually connecting sending the query. any help or pointers would be greatly appreciated. just enable logging of queries. As depesz mentioned, there's a log_min_duration GUC, that allows you to log queries that exceed some time interval. If you want to log all queries, you may set this to 0 but it may easily fill your log with garbage. Just as a warning, on heavily-loaded systems, this logging can have a significant impact to your performance. Not so much because it's logging, but due to the fact that your log-files may start requiring more disk I/O than the actual database. If you are going to do this under any serious load, I would recommend separating 'pg_log' on to a separate [set of] physical disk[s]. --Scott There are two contrib modules that might help you - pg_stat_statements and auto_explain. The former one is probably more interesting in this case. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Please recommend me the best bulk-delete option
Hi All, I'm using PostgreSQL 8.1.4. I've 3 tables: one being the core (table1), others are dependents (table2,table3). I inserted 7 records in table1 and appropriate related records in other 2 tables. As I'd used CASCADE, I could able to delete the related records using DELETE FROM table1; It works fine when the records are minimal in my current PostgreSQL version. When I've a huge volume of records, it tries to delete all but there is no sign of deletion progress for many hours! Whereas, bulk import, does in few minutes. I wish to do bulk-delete in reasonable minutes. I tried TRUNCATE also. Like, TRUNCATE table3, table2,table1; No change in performance though. It just takes more time, and no sign of completion! From the net, I got few options, like, deleting all constraints and then recreating the same would be fine. But, no query seems to be successfully run over 'table1' when it's loaded more data! Please recommend me the best solutions to delete all the records in minutes. CREATE TABLE table1( t1_id SERIAL PRIMARY KEY, disp_name TEXT NOT NULL DEFAULT '', last_updated TIMESTAMP NOT NULL DEFAULT current_timestamp, UNIQUE(disp_name) ) WITHOUT OIDS; CREATE UNIQUE INDEX disp_name_index on table1(upper(disp_name)); CREATE TABLE table2 ( t2_id SERIAL PRIMARY KEY, t1_id INTEGER REFERENCES table1 ON DELETE CASCADE, typeTEXT ) WITHOUT OIDS; CREATE TABLE table3 ( t3_id SERIAL PRIMARY KEY, t1_id INTEGER REFERENCES table1 ON DELETE CASCADE, config_key TEXT, config_valueTEXT ) WITHOUT OIDS; Regards, Siva. ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. --- -- 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] Session variables and C functions
Ivan Voras ivo...@freebsd.org writes: Ideally, the C module would create its own custom variable class, named e.g. module, then define some setting, e.g. module.setting. The users would then execute an SQL command such as SET SESSION module.setting='something', and the module would need to pick this up in the C function. Plenty of examples of that in contrib/ ... 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] Please recommend me the best bulk-delete option
Hi All, I'm using PostgreSQL 8.1.4. I've 3 tables: one being the core (table1), others are dependents (table2,table3). I inserted 7 records in table1 and appropriate related records in other 2 tables. As I'd used CASCADE, I could able to delete the related records using DELETE FROM table1; It works fine when the records are minimal in my current PostgreSQL version. When I've a huge volume of records, it tries to delete all but there is no sign of deletion progress for many hours! Whereas, bulk import, does in few minutes. I wish to do bulk-delete in reasonable minutes. I tried TRUNCATE also. Like, TRUNCATE table3, table2,table1; No change in performance though. It just takes more time, and no sign of completion! From the net, I got few options, like, deleting all constraints and then recreating the same would be fine. But, no query seems to be successfully run over 'table1' when it's loaded more data! Please recommend me the best solutions to delete all the records in minutes. CREATE TABLE table1( t1_id SERIAL PRIMARY KEY, disp_name TEXT NOT NULL DEFAULT '', last_updated TIMESTAMP NOT NULL DEFAULT current_timestamp, UNIQUE(disp_name) ) WITHOUT OIDS; CREATE UNIQUE INDEX disp_name_index on table1(upper(disp_name)); CREATE TABLE table2 ( t2_id SERIAL PRIMARY KEY, t1_id INTEGER REFERENCES table1 ON DELETE CASCADE, typeTEXT ) WITHOUT OIDS; CREATE TABLE table3 ( t3_id SERIAL PRIMARY KEY, t1_id INTEGER REFERENCES table1 ON DELETE CASCADE, config_key TEXT, config_valueTEXT ) WITHOUT OIDS; Regards, Siva. ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. --- -- 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] Please recommend me the best bulk-delete option
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Siva Palanisamy Sent: Thursday, November 17, 2011 1:04 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Please recommend me the best bulk-delete option Hi All, I'm using PostgreSQL 8.1.4. I've 3 tables: one being the core (table1), others are dependents (table2,table3). I inserted 7 records in table1 and appropriate related records in other 2 tables. As I'd used CASCADE, I could able to delete the related records using DELETE FROM table1; It works fine when the records are minimal in my current PostgreSQL version. When I've a huge volume of records, it tries to delete all but there is no sign of deletion progress for many hours! Whereas, bulk import, does in few minutes. I wish to do bulk-delete in reasonable minutes. I tried TRUNCATE also. Like, TRUNCATE table3, table2,table1; No change in performance though. It just takes more time, and no sign of completion! From the net, I got few options, like, deleting all constraints and then recreating the same would be fine. But, no query seems to be successfully run over 'table1' when it's loaded more data! Please recommend me the best solutions to delete all the records in minutes. CREATE TABLE table1( t1_id SERIAL PRIMARY KEY, disp_name TEXT NOT NULL DEFAULT '', last_updated TIMESTAMP NOT NULL DEFAULT current_timestamp, UNIQUE(disp_name) ) WITHOUT OIDS; CREATE UNIQUE INDEX disp_name_index on table1(upper(disp_name)); CREATE TABLE table2 ( t2_id SERIAL PRIMARY KEY, t1_id INTEGER REFERENCES table1 ON DELETE CASCADE, typeTEXT ) WITHOUT OIDS; CREATE TABLE table3 ( t3_id SERIAL PRIMARY KEY, t1_id INTEGER REFERENCES table1 ON DELETE CASCADE, config_key TEXT, config_valueTEXT ) WITHOUT OIDS; Regards, Siva. --- The fastest you can do is: TRUNCATE table3; TRUNCATE table2; TRUNCATE table1; (do them separately to avoid any possible re-ordering - I am unsure whether it is allowed but given your comments it may be) Well, not totally true since: DROP DATABASE ...; CREATE TABLE table1; CREATE TABLE table2; CREATE TABLE table3; Is possibly faster... Anyway, if you execute the three TRUNCATEs in the proper order, thus avoiding any kind of cascade, you should get maximum performance possible on your UNSUPPORTED VERSION of PostgreSQL. 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] Please recommend me the best bulk-delete option
Hi. On 17 Listopad 2011, 19:03, Siva Palanisamy wrote: Hi All, I'm using PostgreSQL 8.1.4. I've 3 tables: one being the core (table1), That's a bit old - update to 8.1.23 (or to a never version, if possible). others are dependents (table2,table3). I inserted 7 records in table1 and appropriate related records in other 2 tables. As I'd used CASCADE, I could able to delete the related records using DELETE FROM table1; It works fine when the records are minimal in my current PostgreSQL version. When I've a huge volume of records, it tries to delete all but there is no sign of deletion progress for many hours! Whereas, bulk import, does in few minutes. I wish to do bulk-delete in reasonable minutes. I tried TRUNCATE also. Like, TRUNCATE table3, table2,table1; No change in performance though. It just takes more time, and no sign of completion! From the net, I got few options, like, deleting all constraints and then recreating the same would be fine. But, no query seems to be successfully run over 'table1' when it's loaded more data! Please recommend me the best solutions to delete all the records in minutes. TRUNCATE table1 CASCADE should be quite fast. Have you analyzed the tables after loading the data? That might be one cause. Post explain analyze of the queries, or at least explain if it takes very long to finish. And post some basic system stats collected when running them (iostat, vmstat). Tomas -- 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] Please recommend me the best bulk-delete option
On 17 Listopad 2011, 19:26, David Johnston wrote: Anyway, if you execute the three TRUNCATEs in the proper order, thus avoiding any kind of cascade, you should get maximum performance possible on your UNSUPPORTED VERSION of PostgreSQL. AFAIK cascade with TRUNCATE means 'truncate the depending tables first'. That's quite different from cascade with DELETE where the db has to actually find the records, and I don't think there's a significant performance impact. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] convert text field to utf8 in sql_ascii database
Hi All. I am in the middle of a process to get all my data into utf8. As its not all converted yet, my database encoding is SQL_ASCII. I am getting external apps fixed up to write utf8 to the database, and so far so good. But, I ran across some stuff that needs a one time convert, and wanted to just write sql to update it. I cant seem to figure it out. I'm on Slackware 64, PG 9.0.4. I tried: update general set legal = convert(legal, 'windows_1250', 'utf8'); ERROR: function convert(text, unknown, unknown) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts. It looks like convert() only does bytea, and legal is defined as text. Can't seem to cast it either (as legal::bytea). The result is bytea, so again would have to cast/convert? Also, I'd like to add, I dont understand the online help: http://www.postgresql.org/docs/9.0/static/functions-string.html shows convert(string, src, dest), but then says see Table 9-7, which has conversion names, like windows_1250_to_utf8, where do I use that? The help also shows functions convert_to() and convert_from(). But I dont understand how to use them. update general set legal = convert_to(legal, 'utf8'); How does it know what source encoding to use? And it converts to bytea so how do I convert it back to text? I'm a little confused. I know I'm probably not doing this the right way (c), I'm trying to get there eventually, but for now, I cant just drop my database and recreate it as utf8. Any hints? -Andy -- 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] connection manager pgpool... help
On 11/17/11 2:34 AM, Emanuel Araújo wrote: Based on my scenario, can anyone help me? how can we help you? you didn't ask any questions (other than the above metaquestion, which is unanswerable) I might note in passing... a connection pool will only help if your applications are written to connect to the pool, execute transaction(s), disconnect from the pool.This pattern is very suitable to web applications, and interactive applications that wait for user input. -- 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
[GENERAL] Performance degradation 8.4 - 9.1
This query is taking much longer on 9.1 than it did on 8.4. Why is it using a seq scan? = explain verbose SELECT status,EXISTS(SELECT 1 FROM eventlog e WHERE e.uid = ml.uid AND e.jobid = ml.jobid AND type = 4),EXISTS(SELECT 1 FROM eventlog e WHERE e.uid = ml.uid AND e.jobid = ml.jobid AND type = 1) FROM maillog ml WHERE jobid IN(1132730); QUERY PLAN --- Index Scan using maillog_jobid_status_key on public.maillog ml (cost=0.00..120373618.25 rows=338943 width=10) Output: ml.status, (alternatives: SubPlan 1 or hashed SubPlan 2), (SubPlan 3) Index Cond: (ml.jobid = 1132730) SubPlan 1 - Index Scan using eventlog_uid_and_jobid_and_type_key on public.eventlog e (cost=0.00..176.66 rows=1 width=0) Index Cond: ((e.uid = ml.uid) AND (e.jobid = ml.jobid) AND (e.type = 4)) SubPlan 2 - Seq Scan on public.eventlog e (cost=0.00..32897949.27 rows=17535360 width=8) Output: e.uid, e.jobid Filter: (e.type = 4) SubPlan 3 - Index Scan using eventlog_uid_and_jobid_and_type_key on public.eventlog e (cost=0.00..176.66 rows=1 width=0) Index Cond: ((e.uid = ml.uid) AND (e.jobid = ml.jobid) AND (e.type = 1)) (13 rows) = select version(); version --- PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit (1 row) -- 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] Performance degradation 8.4 - 9.1
On Nov 17, 2011, at 14:24, Joseph Shraibman wrote: This query is taking much longer on 9.1 than it did on 8.4. Why is it using a seq scan? Without seeing the table definition (including indexes) as well as the output of EXPLAIN for 8.4, it's kind of hard to say. Does this formulation of the query give you a different plan? SELECT status, e4.type IS NOT NULL, e1.type IS NOT NULL FROM maillog ml LEFT JOIN eventlog e4 ON (e4.uid, e4.jobid) = (ml.uid, ml.jobid) AND e4.type = 4 LEFT JOIN eventlog e1 ON (e1.uid, e1.jobid) = (ml.uid, ml.jobid) AND e1.type = 1 WHERE jobid = 1132730; Michael Glaesemann grzm seespotcode net -- 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] Performance degradation 8.4 - 9.1
On 11/17/2011 03:30 PM, Michael Glaesemann wrote: On Nov 17, 2011, at 14:24, Joseph Shraibman wrote: This query is taking much longer on 9.1 than it did on 8.4. Why is it using a seq scan? Without seeing the table definition (including indexes) as well as the output of EXPLAIN for 8.4, it's kind of hard to say. Does this formulation of the query give you a different plan? SELECT status, e4.type IS NOT NULL, e1.type IS NOT NULL FROM maillog ml LEFT JOIN eventlog e4 ON (e4.uid, e4.jobid) = (ml.uid, ml.jobid) AND e4.type = 4 LEFT JOIN eventlog e1 ON (e1.uid, e1.jobid) = (ml.uid, ml.jobid) AND e1.type = 1 WHERE jobid = 1132730; It does, but still not the right plan. I want pg to use the plan I posted, minus the seqscan. It estimates that subplan 1 is faster than subplan 2 and they both would give the same results, so why is it running subplan 2? BTW setting enable_seqscan = false on the original doens't solve my problem, I get this instead which is still slow. = explain verbose owl- SELECT status , --dsn,servername,software,serverip,ip,pod,format, owl- EXISTS(SELECT 1 FROM eventlog e WHERE e.uid = ml.uid AND e.jobid = ml.jobid AND type = 4), owl- EXISTS(SELECT 1 FROM eventlog e WHERE e.uid = ml.uid AND e.jobid = ml.jobid AND type = 1) FROM maillog ml WHERE jobid IN(1132730); QUERY PLAN --- Index Scan using maillog_jobid_status_key on public.maillog ml (cost=0.00..120407480.20 rows=338951 width=10) Output: ml.status, (alternatives: SubPlan 1 or hashed SubPlan 2), (SubPlan 3) Index Cond: (ml.jobid = 1132730) SubPlan 1 - Index Scan using eventlog_uid_and_jobid_and_type_key on public.eventlog e (cost=0.00..176.71 rows=1 width=0) Index Cond: ((e.uid = ml.uid) AND (e.jobid = ml.jobid) AND (e.type = 4)) SubPlan 2 - Bitmap Heap Scan on public.eventlog e (cost=21708484.94..43874627.61 rows=17541300 width=8) Output: e.uid, e.jobid Recheck Cond: (e.type = 4) - Bitmap Index Scan on eventlog_jobid_type_type (cost=0.00..21704099.62 rows=17541300 width=0) Index Cond: (e.type = 4) SubPlan 3 - Index Scan using eventlog_uid_and_jobid_and_type_key on public.eventlog e (cost=0.00..176.71 rows=1 width=0) Index Cond: ((e.uid = ml.uid) AND (e.jobid = ml.jobid) AND (e.type = 1)) (15 rows) -- 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] monitoring sql queries
How is this accomplished? Is it possible to log queries to a table with additional information? 1) num rows returned (if a select) 2) time to complete the query 3) other info? How is enabling this actually done? On 11/17/2011 9:32 AM, hubert depesz lubaczewski wrote: On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote: I am in need of a tool or method to see each/every SQL query that hits the PostgreSQL database. By query I mean the query in SQL syntax with all the parameters passed. What I want to do is: 1) see the query 2) Determine how long the query takes to execute 3) Possibly log both of the above to a log file Is there any tool internal to PostgreSQL that would allow me to do this? I cannot monitor it from the code that is actually connecting sending the query. any help or pointers would be greatly appreciated. just enable logging of queries. Best regards, depesz -- 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] monitoring sql queries
What is a GUC and how do I use it? On 11/17/2011 9:46 AM, Tomas Vondra wrote: On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote: On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote: I am in need of a tool or method to see each/every SQL query that hits the PostgreSQL database. By query I mean the query in SQL syntax with all the parameters passed. What I want to do is: 1) see the query 2) Determine how long the query takes to execute 3) Possibly log both of the above to a log file Is there any tool internal to PostgreSQL that would allow me to do this? I cannot monitor it from the code that is actually connecting sending the query. any help or pointers would be greatly appreciated. just enable logging of queries. As depesz mentioned, there's a log_min_duration GUC, that allows you to log queries that exceed some time interval. If you want to log all queries, you may set this to 0 but it may easily fill your log with garbage. There are two contrib modules that might help you - pg_stat_statements and auto_explain. The former one is probably more interesting in this case. Tomas -- 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] Foreign Tables
On Thu, Nov 17, 2011 at 2:59 AM, Raghavendra raghavendra@enterprisedb.com wrote: Shigeru Hanada shigeru.han...@gmail.com 7:48 AM (5 hours ago) to Eliot, pgsql-general This message may not have been sent by: shigeru.han...@gmail.com Learn morehttp://mail.google.com/support/bin/answer.py?hl=enctx=mailanswer=185812 Report phishing Why this message is popping up in my inbox ? Is there any problem with in-house gmail setting of mine. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Thu, Nov 17, 2011 at 7:48 AM, Shigeru Hanada shigeru.han...@gmail.comwrote: Hi Eliot, 2011/11/17 Eliot Gable egable+pgsql-gene...@gmail.com: snip 1a) Can the foreign tables be written to? For example, I have server1 with table foo and server2 which does 'create foreign table bar' where bar references server1.foo. Can server2 write to bar and have it show in server1.foo? Foreign tables in 9.1 are read-only, so you can't write to them. Making foreign tables writable is a TODO item, but ISTM it's difficult to implement it for even 9.2. So the answer to your question 1a) is No. BTW, I'm interested in your use case very much because I'm working on enhancement of foreign tables for 9.2. I would appreciate it if you tell me some details of your reporting system. Foreign tables may suit your reporting system. a) Where are materialized views, triggers and source tables? I guess all of them are on appliances, not on PostgreSQL server for reporting. b) Do you need to update data on appliances during making a report? If you do, how do you do it without foreign tables? (from reporting application, or using dblink or something?) If source of report are on appliances as materialized views (or ordinary tables), and you don't need to update data on appliances, I think you can use foreign tables to gather information on a PostgreSQL server. In this case, you need to define foreign tables for each materialized view (or ordinary table). Then, you can execute SELECT statement using foreign tables on the reporting server to gather information from appliances. FDW for PostgreSQL 9.1, pgsql_fdw, is provided as a third party product[1], though it seems not ready for production use. # Currently you need to extract pgsql_fdw from git repository. Incidentally, pgsql_fdw is being proposed as a contrib module of 9.2[2]. [1]https://sourceforge.net/projects/interdbconnect/ [2]http://archives.postgresql.org/pgsql-hackers/2011-10/msg01329.php Regards, -- Shigeru Hanada -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general This message is displayed because Gmail sees the 'from' address as being at gmail.com, but the SMTP headers show that it was actually sent from the mailing list server, so it reports that the message doesn't appear to be from who it says its from. On a technical level, its right, the message didn't come from Gmail and the mailing list software spoofed the from address. -Adam
Re: [GENERAL] monitoring sql queries
On Thu, 17 Nov 2011 14:32:22 -0700 J.V. jvsr...@gmail.com wrote: How is this accomplished? The best way that I know if is to use pgFouine. The documentation for pgFouine should get you started. HTH, Bill -- 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] monitoring sql queries
On Thu, Nov 17, 2011 at 4:32 PM, J.V. jvsr...@gmail.com wrote: How is this accomplished? Is it possible to log queries to a table with additional information? 1) num rows returned (if a select) This isn't logged 2) time to complete the query This is logged 3) other info? Take a look at the log_line_prefix parameter How is enabling this actually done? You enable this by using a GUC (global unified config) variable in the postgresql.conf file called log_min_duration_statement. --Scott On 11/17/2011 9:32 AM, hubert depesz lubaczewski wrote: On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote: I am in need of a tool or method to see each/every SQL query that hits the PostgreSQL database. By query I mean the query in SQL syntax with all the parameters passed. What I want to do is: 1) see the query 2) Determine how long the query takes to execute 3) Possibly log both of the above to a log file Is there any tool internal to PostgreSQL that would allow me to do this? I cannot monitor it from the code that is actually connecting sending the query. any help or pointers would be greatly appreciated. just enable logging of queries. Best regards, depesz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] monitoring sql queries
On 17 Listopad 2011, 22:34, J.V. wrote: What is a GUC and how do I use it? It just means there's a config option log_min_duration_statement that you can set in postgresql.conf. Set it e.g. to 100, reload the configuration (e.g. by restarting the server or sending HUP signal to the process) and all queries exceeding 100ms will be logged. Tomas On 11/17/2011 9:46 AM, Tomas Vondra wrote: On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote: On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote: I am in need of a tool or method to see each/every SQL query that hits the PostgreSQL database. By query I mean the query in SQL syntax with all the parameters passed. What I want to do is: 1) see the query 2) Determine how long the query takes to execute 3) Possibly log both of the above to a log file Is there any tool internal to PostgreSQL that would allow me to do this? I cannot monitor it from the code that is actually connecting sending the query. any help or pointers would be greatly appreciated. just enable logging of queries. As depesz mentioned, there's a log_min_duration GUC, that allows you to log queries that exceed some time interval. If you want to log all queries, you may set this to 0 but it may easily fill your log with garbage. There are two contrib modules that might help you - pg_stat_statements and auto_explain. The former one is probably more interesting in this case. Tomas -- 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] Authentication configuration for local connections on Windows
Hi ! When attempting to start Postgres 9.1.1 with hba conf for local connections on Windows, I get an error. e.g. I tried adding the following line to pg_hba.conf localall user1 trust and I get: pg_ctl -D pgsql\data -w start waiting for server to startLOG: local connections are not supported by this build CONTEXT: line 92 of configuration file C:/pg/pgsql/data/pg_hba.conf FATAL: could not load pg_hba.conf stopped waiting pg_ctl: could not start server Although, it is not clear what options I have to use while building/configuring? This same configuration used to work with Postgres 9.0.3, though. Any thoughts? -- Deepak
Re: [GENERAL] Authentication configuration for local connections on Windows
On Thursday, November 17, 2011 3:41:22 pm deepak wrote: Hi ! When attempting to start Postgres 9.1.1 with hba conf for local connections on Windows, I get an error. e.g. I tried adding the following line to pg_hba.conf localall user1 trust and I get: pg_ctl -D pgsql\data -w start waiting for server to startLOG: local connections are not supported by this build CONTEXT: line 92 of configuration file C:/pg/pgsql/data/pg_hba.conf FATAL: could not load pg_hba.conf stopped waiting pg_ctl: could not start server Although, it is not clear what options I have to use while building/configuring? This same configuration used to work with Postgres 9.0.3, though Any thoughts? Local refers to Unix sockets. Windows does not have them so you will need to use localhost. For the reason why: http://www.postgresql.org/docs/9.1/interactive/release-9-1.html Reject local lines in pg_hba.conf on platforms that don't support Unix-socket connections (Magnus Hagander) Formerly, such lines were silently ignored, which could be surprising. This makes the behavior more like other unsupported cases. -- Deepak -- Adrian Klaver adrian.kla...@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] Authentication configuration for local connections on Windows
On 17/11/2011 23:41, deepak wrote: Hi ! When attempting to start Postgres 9.1.1 with hba conf for local connections on Windows, I get an error. e.g. I tried adding the following line to pg_hba.conf localall user1 trust and I get: pg_ctl -D pgsql\data -w start waiting for server to startLOG: local connections are not supported by this build CONTEXT: line 92 of configuration file C:/pg/pgsql/data/pg_hba.conf FATAL: could not load pg_hba.conf stopped waiting pg_ctl: could not start server Although, it is not clear what options I have to use while building/configuring? This same configuration used to work with Postgres 9.0.3, though. Any thoughts? On Windows you need to use a host line, as Unix sockets aren't available. Ray -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Session variables and C functions
On 17 November 2011 19:02, Tom Lane t...@sss.pgh.pa.us wrote: Ivan Voras ivo...@freebsd.org writes: Ideally, the C module would create its own custom variable class, named e.g. module, then define some setting, e.g. module.setting. The users would then execute an SQL command such as SET SESSION module.setting='something', and the module would need to pick this up in the C function. Plenty of examples of that in contrib/ ... Ok, I found DefineCustom*() and _PG_init() but there's a small problem: the functions I'm writing are for a user defined typed and apparently _PG_init() is not called until some operation with the type is done (e.g. anything with the input/output functions). This means that DefineCustom*() is not called and PG doesn't know about the variable until it's too late - I need the session variable to be settable by the user before input/output - relying on the default value is not enough. Is there any way to make _PG_init() called earlier, e.g. as soon as the session is established or at database connection time, something like that? -- 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] Session variables and C functions
Ivan Voras ivo...@freebsd.org writes: Is there any way to make _PG_init() called earlier, e.g. as soon as the session is established or at database connection time, something like that? Preload the library --- see shared/local_preload_libraries configuration settings. 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] Session variables and C functions
On 18 November 2011 01:20, Tom Lane t...@sss.pgh.pa.us wrote: Ivan Voras ivo...@freebsd.org writes: Is there any way to make _PG_init() called earlier, e.g. as soon as the session is established or at database connection time, something like that? Preload the library --- see shared/local_preload_libraries configuration settings. Ok, thanks! -- 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] Authentication configuration for local connections on Windows
On Thursday, November 17, 2011 3:41:22 pm deepak wrote: Hi ! Although, it is not clear what options I have to use while building/configuring? This same configuration used to work with Postgres 9.0.3, though. Any thoughts? Error in my previous post the setting should be host not localhost. -- Deepak -- Adrian Klaver adrian.kla...@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] Incremental backup with RSYNC or something?
On Mon, Nov 14, 2011 at 1:45 PM, Venkat Balaji venkat.bal...@verse.in wrote: Question: what can I do to rsync only the new additions in every table starting 00:00:01 until 23:59:59 for each day? A table level replication (like Slony) should help here. Slony needs more than one physical server, right? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Huge number of INSERTs
Hi. I have a massive traffic website. I keep getting FATAL: Sorry, too many clients already problems. It's a Quad core machine with dual servers, 4 SCSI disks with RAID 10, with RAM of 8GB. Server is Nginx backed by Apache for the php. Postgresql just has to do about 1000 SELECTs a minute, and about 200 INSERTs a minute. Maybe 10-20 UPDATEs. My conf file is below. My vmstat + top are below too. What else can I do? max_connections = 350 shared_buffers = 256MB effective_cache_size= 1400MB # Nov 11 2011, was 1500MB temp_buffers= 16MB # min 800kB maintenance_work_mem= 256MB# min 1MB wal_buffers = 12MB # min 32kB fsync = on # turns forced synchronization on or off checkpoint_segments = 128 # was 128 checkpoint_timeout = 1000 # was 1000 enable_indexscan= on #- LOGGING -- log_directory = 'pg_log' log_filename= 'pglog.postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age= 1d log_min_messages= 'error' log_min_error_statement = 'error' log_min_duration_statement = 5000 # In milliseconds client_min_messages = 'warning' log_duration= off #- AUTOVAC -- autovacuum = on autovacuum_max_workers = 5 # max number of autovacuum subprocesses autovacuum_vacuum_cost_delay= 10ms autovacuum_vacuum_cost_limit= 350 vmstat procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 6 1 4044 101396 84376 556959200 168 221 326 200 55 22 21 1 0 top - 19:43:49 up 7:33, 3 users, load average: 19.63, 19.61, 19.25 Tasks: 663 total, 19 running, 644 sleeping, 0 stopped, 0 zombie Cpu(s): 65.8%us, 15.5%sy, 0.0%ni, 1.7%id, 0.1%wa, 0.0%hi, 17.0%si, 0.0%st Mem: 8177444k total, 8062608k used, 114836k free,84440k buffers Swap: 2096440k total, 4044k used, 2092396k free, 5572456k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 6337 postgres 15 0 397m 100m 97m S 2.3 1.3 0:16.56 postgres: MYDB_MYDB MYDB 127.0.0.1(60118) SELECT 424 postgres 15 0 397m 101m 98m S 2.0 1.3 1:01.79 postgres: MYDB_MYDB MYDB 127.0.0.1(37036) SELECT 2887 postgres 15 0 397m 100m 98m S 2.0 1.3 0:34.55 postgres: MYDB_MYDB MYDB 127.0.0.1(57710) SELECT 3030 postgres 15 0 397m 101m 98m S 2.0 1.3 0:32.35 postgres: MYDB_MYDB MYDB 127.0.0.1(45574) SELECT 5273 postgres 15 0 397m 100m 98m S 2.0 1.3 0:22.38 postgres: MYDB_MYDB MYDB 127.0.0.1(52143) SELECT 5560 postgres 15 0 397m 100m 98m S 2.0 1.3 0:20.05 postgres: MYDB_MYDB MYDB 127.0.0.1(56767) SELECT 5613 postgres 16 0 397m 100m 98m S 2.0 1.3 0:19.51 postgres: MYDB_MYDB MYDB 127.0.0.1(57745) SELECT 5652 postgres 15 0 397m 100m 98m S 2.0 1.3 0:19.76 postgres: MYDB_MYDB MYDB 127.0.0.1(58464) SELECT 32062 postgres 15 0 397m 101m 98m S 2.0 1.3 1:55.79 postgres: MYDB_MYDB MYDB 127.0.0.1(55341) SELECT 358 postgres 15 0 397m 101m 98m S 1.6 1.3 1:04.11 postgres: MYDB_MYDB MYDB 127.0.0.1(35841) SELECT 744 postgres 15 0 397m 101m 98m S 1.6 1.3 0:53.01 postgres: MYDB_MYDB MYDB 127.0.0.1(50058) SELECT 903 postgres 15 0 397m 101m 98m S 1.6 1.3 0:50.79 postgres: MYDB_MYDB MYDB 127.0.0.1(51258) SELECT 976 postgres 15 0 397m 101m 98m S 1.6 1.3 0:48.24 postgres: MYDB_MYDB MYDB 127.0.0.1(52828) SELECT 1011 postgres 15 0 397m 101m 98m S 1.6 1.3 0:48.20 postgres: MYDB_MYDB MYDB 127.0.0.1(53503) SELECT 2446 postgres 15 0 397m 101m 98m S 1.6 1.3 0:38.97 postgres: MYDB_MYDB MYDB 127.0.0.1(51982) SELECT 2806 postgres 16 0 397m 100m 98m R 1.6 1.3 0:34.83 postgres: MYDB_MYDB MYDB 127.0.0.1(57204) SELECT 3361 postgres 15 0 397m 101m 98m R 1.6 1.3 0:30.32 postgres: MYDB_MYDB MYDB 127.0.0.1(48782) idle 3577 postgres 15 0 397m 100m 98m S 1.6 1.3 0:27.92 postgres: MYDB_MYDB MYDB 127.0.0.1(52019) SELECT 3618 postgres 15 0 397m 101m 98m S 1.6 1.3 0:27.53 postgres: MYDB_MYDB MYDB 127.0.0.1(41291) SELECT 3704 postgres 15 0 397m 100m 98m S 1.6 1.3 0:25.70 postgres: MYDB_MYDB MYDB 127.0.0.1(43642) SELECT 5073 postgres 15 0 397m 100m 98m S 1.6 1.3 0:23.92 postgres: MYDB_MYDB MYDB 127.0.0.1(47398) SELECT 5185 postgres 15 0 397m 100m 98m S 1.6 1.3 0:23.03 postgres: MYDB_MYDB MYDB 127.0.0.1(49137) SELECT 5528 postgres 15 0 397m 100m 98m S 1.6 1.3 0:20.81 postgres: MYDB_MYDB MYDB 127.0.0.1(55531) SELECT 5549 postgres 15 0 397m 100m 98m S 1.6 1.3 0:20.71 postgres: MYDB_MYDB MYDB 127.0.0.1(56391)
Re: [GENERAL] Huge number of INSERTs
On 11/17/11 4:44 PM, Phoenix Kiula wrote: I keep getting FATAL: Sorry, too many clients already problems. It's a Quad core machine with dual servers, 4 SCSI disks with RAID 10, with RAM of 8GB. Server is Nginx backed by Apache for the php. Postgresql just has to do about 1000 SELECTs a minute, and about 200 INSERTs a minute. Maybe 10-20 UPDATEs. My conf file is below. My vmstat + top are below too. are you using a connection pool? it should be, pgbouncer or something, the pooling built into php is weak sauce. your php pages should be grabbing a pool connection, doing their thing, releasing the pool connection. -- 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] upgrading from 8.3 to 9.0
I've performed a very similar upgrade including postgis upgrade at the same time, we used the following command examples ... also put some simple scripting together to dump multiple databases in parallel as downtime was critical: Dump database data: pg_dump -Fc database --compress=1 /mnt/dumps/database.dump Dump global data: pg_dumpall -g /mnt/dumps/globals..sql Parse the global file and create a script to create new directory structure for table spaces etc (also changed paths to new mount points here) Run the global sql script: psql -f /mnt/dumps/globals.sql postgres Restore databases without GIS functionality: pg_restore -j 2 -C -d postgres /mnt/dumps/database.dump Restore databases with GIS functionality (upgrade of postgis version requires this): sh /tmp/postgis_restore.pl /usr/share/postgresql/contrib/postgis-1.5/postgis.sql database_user /mnt/dumps/gisdatabase.dump -E=UTF8 Those were the basic essential steps ... there are other supporting things we did around the outside to streamline the transition, it all worked perfectly on the day. Best advise is that if its more than a scratch environment, test test test !! From: Pedro Doria Meunier pdo...@netmadeira.com To: pgsql-general@postgresql.org Sent: Friday, 18 November 2011 12:40 AM Subject: [GENERAL] upgrading from 8.3 to 9.0 Hi, I'm on the verge of upgrading a server (Fedora 8 ehehe) running postgresql 8.3 It also has postgis 1.3 installed. Thinking of using pgadmin3 to perform the backup and then restore it after I've upgraded the server to fedora 15/16 and thus upgrading postgresql to 9.0. I seem to remember problems with restoring from a pgadmin's .backup file in the past... :S Any pitfalls I should be aware of? Btw: it's a reasonably large DB with 30mil+ rows... Already thankful for any insight, -- Pedro Doria Meunier GSM: +351 91 581 88 23 Skype: pdoriam
Re: [GENERAL] Huge number of INSERTs
On 11/17/2011 04:44 PM, Phoenix Kiula wrote: Hi. I have a massive traffic website. Massive = what, exactly? I keep getting FATAL: Sorry, too many clients already problems. It's a Quad core machine with dual servers, 4 SCSI disks with RAID 10, with RAM of 8GB. Database only? Or is it also your webserver? Server is Nginx backed by Apache for the php. Postgresql just has to do about 1000 SELECTs a minute, and about 200 INSERTs a minute. Maybe 10-20 UPDATEs. My conf file is below. My vmstat + top are below too. What else can I do? Provide more info. What version of PostgreSQL? What OS? What OS tuning, if any, have you done? (Have you increased readahead? Changed swappiness, turned off atime on your mounts, made syslogging asynchronous, etc?). Does your RAID have battery-backed cache? What are the cache settings? What is the nature of the queries? Single record inserts or bulk? Same for the selects. Have you run analyze on them and optimized the queries? What is the typical duration of your queries? Are lots of queries duplicated (caching candidates)? What is the size of your database? Do you have any bandwidth bottleneck to the Internet? Is this your database server only or is it running web and/or other processes? How long does a typical web-request take to handle? At first blush, and shooting in the dark, I'll guess there are lots of things you can do. Your shared_buffers seems a bit low - a rough starting point would be closer to 25% of your available RAM. You are a prime candidate for using a connection pooler. I have had good luck with pgbouncer but there are others. If you have lots of repeated queries, you could benefit from memcached or similar. If your typical web request involves a database hit, there is not really a benefit to having so many web processes that you exhaust your database connections. At least until you fix the underlying issues, you might want to decrease the maximum number of allowed web connections. (If you server lots of static content, you may want to adjust your process count accordingly). Note: bandwidth bottlenecks can screw everything up. Your web processes stay alive dribbling the data to the client and, even though they don't have much work to do, they are still holding database connections, using memory, etc. Such cases can often benefit from a reverse proxy. Provide more data and we can provide more assistance. Cheers, Steve -- 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] Huge number of INSERTs
Hi, there's a pretty wiki page about tuning PostgreSQL databases: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server On 18 Listopad 2011, 1:44, Phoenix Kiula wrote: Hi. I have a massive traffic website. I keep getting FATAL: Sorry, too many clients already problems. That has nothing to do with the inserts, it means the number of connection requests exceeds the max_connections. You've set it to 350, and that seems too high - the processes are going to struggle for resources (CPU, I/O and memory) and the scheduling gets expensive too. A good starting point is usually 2*(number of cores + number of drives) which is 16 or 24 (not sure what a dual server is - probably dual CPU). You may increase that if the database more or less fits into memory (so less I/O is needed). But go step by step - by 10 connections or something like that. The problem is that each connection can allocate memory (work_mem), and if you have too many connections doing that at the same time you'll get OOM or a swapping system (and that's not what you want). And you should give the sessions enough memory, because otherwise they're going to do on-disk sort. So you have to keep in mind these rules (1) give each session enough memory to perform the operations in RAM (enough work_mem to sort in memory etc.), but not more (2) don't use too many connections - watch the I/O utilization and don't overload it (you won't get higher throughput, just higher latencies) BTW the same rule holds for the number of Apache workers - how many are you using? Is that on the same machine or on a dedicated one? The fact that you're receiving too many clients suggests that you have MaxClients higher than 350. Have you actually tested this to see if it gives better performance than 50? If the clients actually need to connect / query the database, there's probably no point in having more than max_connections of them. It's a Quad core machine with dual servers, 4 SCSI disks with RAID 10, with RAM of 8GB. Server is Nginx backed by Apache for the php. Postgresql just has to do about 1000 SELECTs a minute, and about 200 INSERTs a minute. Maybe 10-20 UPDATEs. That's completely information-less description. Those SELECTs may be a simple fetch by PK queries or complex queries aggregating data from 20 tables. So the fact that you need to execute 1000 of them is useless. The same for UPDATEs and INSERTs. Post an example of the queries with EXPLAIN ANALYZE for each of them (use explain.depesz.com to post it). My conf file is below. My vmstat + top are below too. A static (single line) of vmstat is not very useful - we need a few lines of vmstat 1 (say 30) collected when the application is in use. What else can I do? 1) Decrease the number of connections to a reasonable value. 2) Use a connection pooler. You may also use persistent connections in PHP too, but you have to set MaxClients in apache config to the same value (otherwise you'll get too many clients). The connection pooler can handle this for you - it will wait until a connection is available. And the most important thing - prepare a simple stress script (a few HTTP requests, performed by a typical client) and use it to stress test the application. Start with low max_connections / MaxClients (say 20), increase them gradually and watch the performance (throughput). The usual behavior is that at the beginning the throughput scales linearly (2 clients give you 2x the throughput of 1 client, with the same latency). Then this growth stops and the throughput does not grow anymore - adding more clients just increases the latency. Then the throughput usually goes down. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using the internal data dictionary
I need to assemble a complete data dictionary for project documentation and other purposes and I was wondering about the pros and cons of using the pg_catalog metadata. But I hesitate to poke around in here because I don't know why it's kept so out of sight and not much documented. But it seems like an ideal source of information to tap with a program to generate accurate, current reports of what's in the database. Is this a bad idea (everything I'm thinking of doing would be read only except for the description fields) but I'd just like to make sure that there's not some innocent looking table in there that acts as a doomsday device if you so much as read its first record, etc. I'm just not sure why this isn't more widely used or talked about. Regards, Bill Thoen GISnet http://gisnet.com 303-786-9961 -- 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] Using the internal data dictionary
On Nov 17, 2011, at 22:17, Bill Thoen bth...@gisnet.com wrote: I need to assemble a complete data dictionary for project documentation and other purposes and I was wondering about the pros and cons of using the pg_catalog metadata. But I hesitate to poke around in here because I don't know why it's kept so out of sight and not much documented. But it seems like an ideal source of information to tap with a program to generate accurate, current reports of what's in the database. Is this a bad idea (everything I'm thinking of doing would be read only except for the description fields) but I'd just like to make sure that there's not some innocent looking table in there that acts as a doomsday device if you so much as read its first record, etc. I'm just not sure why this isn't more widely used or talked about. Regards, Bill Thoen GISnet http://gisnet.com 303-786-9961 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general information_schema is the more standardized point of entry into the database meta-data, catalog is generally intended for internals use and thus has a less stable API contract. That said, you are reinventing the wheel if you are looking for a straight dump of the current reality. Various third-party tools already do this. I've used, but am not affiliated with, PostgreSQL Maestro. Also, pgAdmin, I think, provides access to this information as well (as does psql via it's various commands). You should never directly update the catalog but instead use the appropriate SQL command. For descriptions you need to use COMMENT ON. Reading it should never cause a problem. 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] Using the internal data dictionary
On 11/17/11 7:17 PM, Bill Thoen wrote: I need to assemble a complete data dictionary for project documentation and other purposes and I was wondering about the pros and cons of using the pg_catalog metadata. But I hesitate to poke around in here because I don't know why it's kept so out of sight and not much documented. But it seems like an ideal source of information to tap with a program to generate accurate, current reports of what's in the database. Is this a bad idea (everything I'm thinking of doing would be read only except for the description fields) but I'd just like to make sure that there's not some innocent looking table in there that acts as a doomsday device if you so much as read its first record, etc. I'm just not sure why this isn't more widely used or talked about. pg_catalog is whats used to extract the data displayed by all the various \ commands in psql, by pgadmin3, pg_dump, etc the sql standard information_schema is in fact implemented as views into pg_catalog -- 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
[GENERAL] Result of ORDER-BY
[PostgreSQL 8.3.9] I have a query, as follows SELECT DISTINCT ON(category) category FROM gdb_books WHERE category LIKE 'Fiction%' GROUP BY category The (partial) result is this: ... # Fiction - General (A) # Fiction - General - Anthologies # Fiction - General (B) # Fiction - General (C) # Fiction - General (D) ... I would have expected '- Anthologies' to be either at the top, or at the bottom of the result. Does anyone have an explanation why this is not so; are the special characters (parenthesis, hyphen) just ignored? If so, is there a way to force ORDER BY to include the special characters in the sort? Thank you for any reply. Pat Willener GDB Tokyo -- 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] Result of ORDER-BY
On Nov 17, 2011, at 7:14 PM, Good Day Books wrote: Does anyone have an explanation why this is not so; are the special characters (parenthesis, hyphen) just ignored? If so, is there a way to force ORDER BY to include the special characters in the sort? The query as shown does't actually have an ORDER BY clause in it; did you write GROUP BY where you meant ORDER BY? -- -- Christophe Pettus x...@thebuild.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] Result of ORDER-BY
On Fri, Nov 18, 2011 at 12:14:35PM +0900, Good Day Books wrote: [PostgreSQL 8.3.9] I have a query, as follows SELECT DISTINCT ON(category) category FROM gdb_books WHERE category LIKE 'Fiction%' GROUP BY category Does anyone have an explanation why this is not so; are the special characters (parenthesis, hyphen) just ignored? If so, is there a way to force ORDER BY to include the special characters in the sort? See the other remark in this thread about GROUP BY and ORDER BY. Note that GROUP BY used to cause ORDER BY every time, because it was always implemented with a sort. That hasn't been true for several releases, and if you're relying on that side effect it could be the cause of this, although it's pretty surprising that you still got A, B, C in that case. In any case, you definitely need an ORDER BY category here, too. Does that make a difference? You might also want to look at your collation. Sort orders are notorious for being surprising across collations. What's this one? A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Incremental backup with RSYNC or something?
On Sun, Nov 13, 2011 at 7:01 AM, Craig Ringer ring...@ringerc.id.au wrote: On Nov 13, 2011 7:39 PM, Phoenix Kiula Searching google leads to complex things like incremental WAL and whatnot, or talks of stuff like pgcluster. I'm hoping there's a more straightforward core solution without additional software or PHD degrees. Nothing really basic. You'll need to use PITR (WAL shipping), streaming replication or a row level replication solution. One method they could use would be to partition the data into old and new, and backup the new, then merge the partitions and start a new one or something like that. But that's a huge pain. I would suggest PITR backups here as the likely best match. -- 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] Incremental backup with RSYNC or something?
On Fri, Nov 18, 2011 at 6:08 AM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Mon, Nov 14, 2011 at 1:45 PM, Venkat Balaji venkat.bal...@verse.in wrote: Question: what can I do to rsync only the new additions in every table starting 00:00:01 until 23:59:59 for each day? A table level replication (like Slony) should help here. Slony needs more than one physical server, right? Not necessarily, you can replicate with-in the same host as well. It all depends on which tables you want to replicate. It does not depend on number of hosts. Thanks VB
[GENERAL] pg_dump error
Hi, I am using PostgreSQL 9.0. It is installed on Windows XP. I am trying to take a backup of the database using pg_dump. But each time I give a command Pg_dump pgdb backup.sql I am prompted for a password and I provided the database password. After this, I got an error as follows.. pg_dump: [archiver (db)] connection to database pgdb failed : FATAL: password authentication failed for user windows user_name. Can somebody please tell me what I should be doing to avoid this error? Thanks, Mamatha
Re: [GENERAL] pg_dump error
On 11/17/11 11:01 PM, mamatha_kagathi_c...@dell.com wrote: I am using PostgreSQL 9.0. It is installed on Windows XP. I am trying to take a backup of the database using pg_dump. But each time I give a command Pg_dump pgdb backup.sql I am prompted for a password and I provided the database password. After this, I got an error as follows.. pg_dump: [archiver (db)] connection to database “pgdb” failed : FATAL: password authentication failed for user “windows user_name”. add -U dbusername to the command line. -- 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
