Re: [GENERAL] Problem creating index
Interesting . while trying to restore the database on the same machine as different database I get this error message: ERROR: date/time field value out of range: 20016009:50:37.927936 Since I get this data from a database dump obtained with pg_dump on the same hardware I suppose that can to be two possibility: - postgresql bug somewhere - hardware problem that caused data corruption Since the dump file is 11G is not so easy to handle I think that this is not related with create index problem since this field is not used by this index and increase maintenance memory had worked. I'll fix it and go ahead in maintenance_work_mem test for index creating. Best Regards 2013/8/27 Torello Querci tque...@gmail.com 2013/8/26 Jeff Janes jeff.ja...@gmail.com On Mon, Aug 26, 2013 at 10:01 AM, Torello Querci tque...@gmail.com wrote: Ok, now create index is finished using maintenance_work_mem=100MB. Thanks to all. I suppose that an error message more clear can help. Unfortunately, since no one knows what the real problem is, we can't make the message more clear. Something that is never supposed to happen has happened. One thing you could do is set log_error_verbosity to verbose. It seems like the most likely cause is flaky hardware, either memory or hard-drive. In which case, your database is in serious danger of irrecoverable corruption. Is it reproducible that if you lower the maintenance_work_mem you get the error again, and if you raise it the error does not occur? I'll try to restore the database on the same hw but different DB using differente maintenance_work_mem end verbosity and I'll posted the result here, if can help to improve the error message. Cheers, Torello
[GENERAL] virtualxid,relation lock
Hii, I have an issue with of idle transaction and one select statement in backend. what i noticed when i look the pg_lock, all are idle trans and one particlular select statement with virtualxid,relation lock. the lock are held with diffrend objects.it utilise the whole cpu.How can fix the issue. lock informations pid | vxid | lock_type |lock_mode| granted | xid_lock | relname | page | tuple | classid | objid | objsubid ---+--+---+-+-+--++--+---+-+---+-- 38423 | 4/334285 | relation | AccessShareLock | t | | admin | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | admin_pkey | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | cert_data | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | cert_data_pkey | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | client_admin | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | client_admin_creater_client_id_inx | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | client_admin_creds | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | client_admin_creds_pkey| | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | client_admin_customer_id_idx | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | client_admin_pkey | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | cust_indx_name | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | cust_indx_uri | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | customer | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | customer_pkey | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | dom_org| | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | dom_org_approver | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | dom_org_approver_idx | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | dom_org_approver_pkey | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | dom_org_pkey | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | domain | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | domain_name_customer_idx | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | domain_pkey| | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | domain_settings| | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | idp| | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | idp_pkey | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | notification | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | notification_customer_id_idx | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | notification_orgs | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | notification_pkey | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | notification_roles | | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | notify_task_seq| | | | | 38423 | 4/334285 | relation | AccessShareLock | t | | organization | | | | | 38423 | 4/334285 | relation | AccessShareLock | t
Re: [GENERAL] Problem creating index
2013/8/28 Dan Langille dan.langi...@gmail.com Same version of DB for dump restore? If not, was the dump done via the pg_dump from the newer version. If not, please do that. I'm using the same version. I make this test on the same machine. Moreover I try to remove this line using pgadmin and I get the same error (this field is part of primary key). To remove this line I need to not use primary key but give a where condition that return only this tuple. Again, is very strange that this data is in the database moreover this data came from a import procedure and this data is not present in the source import file. Really, I think that I get some kind of data corruption Best Regards
Re: [GENERAL] Problem creating index
On Wed, Aug 28, 2013 at 1:08 PM, Torello Querci tque...@gmail.com wrote: Again, is very strange that this data is in the database moreover this data came from a import procedure and this data is not present in the source import file. Really, I think that I get some kind of data corruption I'm sure you got some kind of data corruption because the date is invalid and it was in a primary key (if I get it right). Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem creating index
2013/8/28 Luca Ferrari fluca1...@infinito.it On Wed, Aug 28, 2013 at 1:08 PM, Torello Querci tque...@gmail.com wrote: Again, is very strange that this data is in the database moreover this data came from a import procedure and this data is not present in the source import file. Really, I think that I get some kind of data corruption I'm sure you got some kind of data corruption because the date is invalid and it was in a primary key (if I get it right). You get it right. At this point I think that a full server check is needed
[GENERAL] Problems with adding IP to pg_hba.conf
Hi all, I need to let a server access our postgreSQL database but I´m having problems with the configuration. I´m getting the error message below, psql: FATAL: no pg_hba.conf entry for host 10.24.17.22, user k175, database k175, SSL off I´ve added the section below to the pg_hba.conf # TYPE DATABASEUSERCIDR-ADDRESS METHOD # IPv4 local connections: hostall all 10.24.17.0/24 md5 Still resulting in the above error. Can somebody guide me to what is wrong? Thanks Christian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problems-with-adding-IP-to-pg-hba-conf-tp5768809.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with adding IP to pg_hba.conf
On 2013-08-28 2:00 PM, Chrishelring wrote: Hi all, I need to let a server access our postgreSQL database but I´m having problems with the configuration. I´m getting the error message below, psql: FATAL: no pg_hba.conf entry for host 10.24.17.22, user k175, database k175, SSL off I´ve added the section below to the pg_hba.conf # TYPE DATABASEUSERCIDR-ADDRESS METHOD # IPv4 local connections: hostall all 10.24.17.0/24 md5 Still resulting in the above error. Can somebody guide me to what is wrong? Thanks Christian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problems-with-adding-IP-to-pg-hba-conf-tp5768809.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. Are you sure you reloaded the config after making the above change? (happened to me couple times before, so worth a try :) Ziggy -- 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] Problems with adding IP to pg_hba.conf
On 08/28/2013 07:00 AM, Chrishelring wrote: psql: FATAL: no pg_hba.conf entry for host 10.24.17.22, user k175, database k175, SSL off Did you reload the configuration files after changing pg_hba.conf? If not, you need to use the init script, or pg_ctl and send a reload command. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres 9.2
Hi all, I recently ran a couple of tests where I took one of my production systems and did a drop-in replacement of postgres 8.4 with 9.2.4. I was expecting to see some performance improvement given the release notes describing 9.2 as a largely performance related release. At least for my application, which is an embedded postgresql install with a relatively small number of client connections, I'm not seeing much of a measurable difference at all. I'm just wondering if others have had a similar experience where upgrading from 8.x to 9.x has or has not improved overall performance? Thanks.
Re: [GENERAL] Pgbouncer help
Thanks Jeff, As I understand from your point, instead of connecting Postgresql port, try to use PgBouncer port. I am using libpq library functions connect postgreql and code changes would be like this. Previous code : sprintf(conninfo, user=%s password=%s dbname=%s hostaddr=%s port=%d, PG_USER, PG_PASS, PG_DB, PG_HOST, PG_PORT); conn = PQconnectdb(conninfo); new code: sprintf(conninfo, user=%s password=%s dbname=%s hostaddr=%s port=%d, PG_USER, PG_PASS, PG_DB, PG_HOST, PG_BOUNCER_PORT); conn = PQconnectdb(conninfo); -Original Message- From: Jeff Janes [mailto:jeff.ja...@gmail.com] Sent: Tuesday, August 27, 2013 11:10 PM To: Yelai, Ramkumar IN BLR STS Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Pgbouncer help On Tue, Aug 27, 2013 at 1:34 AM, Yelai, Ramkumar IN BLR STS ramkumar.ye...@siemens.com wrote: HI In our current project, we are opening several postgresql connection. Few connections are frequently used and few are occasionally used. Hence we plan to adapt connection pool method to avoid more connection to open. We plan to use Pgbouncer. Most of the pgbouncer example shows how to configure, but they are not explaining how to use in C++. Please provide me a example, how to use it in C++. pgbouncer is designed to look (to the client) just like a normal postgresql server. If you want all connections to the database to go through pgbouncer, you can move the real server to a different port, and then start up pgbouncer on that vacated port. In this case, the clients do not need to make any changes at all to their configuration. If you want to keep the real server on the same port as it currently is and to use a special port to go through pgbouncer, then you need to change the clients to use that new port number. You do this the same way you would change the client to use a different port if that different port were a regular postgresql server. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem creating index
Same version of DB for dump restore? If not, was the dump done via the pg_dump from the newer version. If not, please do that. -- Dan Langille http://langille.org/ On Aug 28, 2013, at 2:56 AM, Torello Querci tque...@gmail.com wrote: Interesting . while trying to restore the database on the same machine as different database I get this error message: ERROR: date/time field value out of range: 20016009:50:37.927936 Since I get this data from a database dump obtained with pg_dump on the same hardware I suppose that can to be two possibility: - postgresql bug somewhere - hardware problem that caused data corruption Since the dump file is 11G is not so easy to handle I think that this is not related with create index problem since this field is not used by this index and increase maintenance memory had worked. I'll fix it and go ahead in maintenance_work_mem test for index creating. Best Regards 2013/8/27 Torello Querci tque...@gmail.com 2013/8/26 Jeff Janes jeff.ja...@gmail.com On Mon, Aug 26, 2013 at 10:01 AM, Torello Querci tque...@gmail.com wrote: Ok, now create index is finished using maintenance_work_mem=100MB. Thanks to all. I suppose that an error message more clear can help. Unfortunately, since no one knows what the real problem is, we can't make the message more clear. Something that is never supposed to happen has happened. One thing you could do is set log_error_verbosity to verbose. It seems like the most likely cause is flaky hardware, either memory or hard-drive. In which case, your database is in serious danger of irrecoverable corruption. Is it reproducible that if you lower the maintenance_work_mem you get the error again, and if you raise it the error does not occur? I'll try to restore the database on the same hw but different DB using differente maintenance_work_mem end verbosity and I'll posted the result here, if can help to improve the error message. Cheers, Torello
Re: [GENERAL] Pgbouncer help
Thanks for your great inputs. Let me see, how to handle these situations in our project. Regards, Ramkumar -Original Message- From: Steve Crawford [mailto:scrawf...@pinpointresearch.com] Sent: Wednesday, August 28, 2013 1:09 AM To: Jeff Janes Cc: Yelai, Ramkumar IN BLR STS; pgsql-general@postgresql.org Subject: Re: [GENERAL] Pgbouncer help On 08/27/2013 10:40 AM, Jeff Janes wrote: On Tue, Aug 27, 2013 at 1:34 AM, Yelai, Ramkumar IN BLR STS ramkumar.ye...@siemens.com wrote: HI In our current project, we are opening several postgresql connection. Few connections are frequently used and few are occasionally used. Hence we plan to adapt connection pool method to avoid more connection to open. We plan to use Pgbouncer. Most of the pgbouncer example shows how to configure, but they are not explaining how to use in C++. Please provide me a example, how to use it in C++. pgbouncer is designed to look (to the client) just like a normal postgresql server However... Since clients are reusing previously accessed server sessions, be sure to consider the implication of the different pool types and reset options. For example, if you have multi-statement transactions you cannot, of course, use statement-level pooling since the server connection is released after the statement. And if you set any runtime parameters (set time zone to..., set statement timeout..., etc.) then you will probably need to use session-level pooling and you will need to set server_reset_query appropriately otherwise you risk ending up either having parameters set to values you did not expect by a previously connected client or having parameters you set disappear when your next statement is assigned to a different server connection. A similar issue exists if you use temporary tables as you need to be sure to stick with the same server connection while your processing needs the temporary table and you need to clean it up when you release the connection so it doesn't use extra resources and doesn't interfere with statements issued a subsequent client. For more, see the following if you haven't read them already: http://pgbouncer.projects.pgfoundry.org/doc/config.html http://wiki.postgresql.org/wiki/PgBouncer 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] pg_extension_config_dump() with a sequence
Thank you very much, Tom. That was it. Our other server is running 9.1.9 and that's why it worked there. On Tue, Aug 27, 2013 at 10:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Moshe Jacobson mo...@neadwerx.com writes: On Tue, Aug 20, 2013 at 7:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, I think you did it wrong, or else you're using a PG version that predates some necessary fix, because it works for me. Sorry for the delayed response. I am using postgres 9.1.4 with pg_dump of the same version. Ah. I think that you are missing this 9.1.7 fix: commit 5110a96992e508b220a7a6ab303b0501c4237b4a Author: Tom Lane t...@sss.pgh.pa.us Date: Fri Oct 26 12:12:53 2012 -0400 In pg_dump, dump SEQUENCE SET items in the data not pre-data section. Represent a sequence's current value as a separate TableDataInfo dumpable object, so that it can be dumped within the data section of the archive rather than in pre-data. This fixes an undesirable inconsistency between the meanings of --data-only and --section=data, and also fixes dumping of sequences that are marked as extension configuration tables, as per a report from Marko Kreen back in July. The main cost is that we do one more SQL query per sequence, but that's probably not very meaningful in most databases. Back-patch to 9.1, since it has the extension configuration issue even though not the --section switch. regards, tom lane -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] regexp idea
AI Rumman escribió: Thanks. That's awesome. Do you have any good guide where I may get more knowledge on REGEXP? This book is awesome: http://regex.info/book.html -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] postgres 9.2
On 08/27/2013 02:26 PM, pg noob wrote: Hi all, I recently ran a couple of tests where I took one of my production systems and did a drop-in replacement of postgres 8.4 with 9.2.4. I was expecting to see some performance improvement given the release notes describing 9.2 as a largely performance related release. At least for my application, which is an embedded postgresql install with a relatively small number of client connections, I'm not seeing much of a measurable difference at all. I'm just wondering if others have had a similar experience where upgrading from 8.x to 9.x has or has not improved overall performance? It would be easier to answer if you gave some information on what performance you are measuring and what the results are? Also remember there is an overhead incurred for all operations and for small installations it is a bigger part of the total cost, so you will not really gain on that. Thanks. -- 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] postgres 9.2
On Tue, Aug 27, 2013 at 2:26 PM, pg noob pgn...@gmail.com wrote: Hi all, I recently ran a couple of tests where I took one of my production systems and did a drop-in replacement of postgres 8.4 with 9.2.4. I was expecting to see some performance improvement given the release notes describing 9.2 as a largely performance related release. At least for my application, which is an embedded postgresql install with a relatively small number of client connections, I'm not seeing much of a measurable difference at all. There were a bunch of different, specific, performance improvements each with a focused area. Many of them related to reducing contention in many-CPU systems. If your system wasn't having problems in the specific areas that were improved, you wouldn't see an improvement. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.0 hot standby, consistent recovery state question
Hi. I have Pg 9.0 wal shipping hot standby secondary server. The primary is under constant stream of updates (avg 20 TXID/s). There are many lengthy COPY FROM operations in the primary. After every restart of secondary postgres I observe that it takes a fair amount of time (sometimes few minutes, sometimes much more) to replay new WAL logs achieve consistent state and start serving R/O queries. Even when R/O queries were served directly before the restart. Why does postgres take so longto reach consistent state? Can I ever try to impose a time limit on this? How? What prevents postgres from using last consistent restart point from before the cluster restart? This way I would not have to wait so long after restart to serve R/O traffic. Is this issue any different in 9.2? Is this issue mitigated in any way if I switch on streaming replication? thanks for any answers suggestions. Filip -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump question (exclude schemas)
I want to backup a database but exclude certain schemas with a patter. I have 100 schemas with the pattern: 'sch_000', 'sch_001', and so on. Will this work? $pg_dump other_options --exclude-schema='sch_*' this does not seem to exclude all schemas with this pattern ( 'sch_*' ), anything wrong here? thanks J.V.
Re: [GENERAL] pg_dump question (exclude schemas)
On 08/28/2013 12:30 PM, Jay Vee wrote: I want to backup a database but exclude certain schemas with a patter. I have 100 schemas with the pattern: 'sch_000', 'sch_001', and so on. Will this work? $pg_dump other_options --exclude-schema='sch_*' this does not seem to exclude all schemas with this pattern ( 'sch_*' ), anything wrong here? What version of Postgres? What is the complete command line? Does it exclude any of the schemas? thanks J.V. -- 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
