[GENERAL] Too many postgres instances
Hi, Can anyone explain to me why my system is spawn so many postgress instances? System: FreeBSD 7.2 Stable I386 RAM: 1GB Here is the output of the top command: Mem: 457M Active, 92M Inact, 150M Wired, 23M Cache, 85M Buf, 18M Free Swap: 999M Total, 150M Used, 849M Free, 14% Inuse Here is a list of the postgres instances running in my system: pgsql 70502 0.0 0.5 10364 3800 ?? Ss2:24AM 0:28.85 postgres: stats collector process(postgres) pgsql 70797 0.0 0.4 49724 2892 ?? Is2:28AM 0:00.24 postgres: liferay lportal 127.0.0.1(54110) (postgres) pgsql 70798 0.0 0.7 49724 5060 ?? Is2:28AM 0:00.43 postgres: liferay lportal 127.0.0.1(52754) (postgres) pgsql 70799 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(49593) (postgres) pgsql 70800 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(55760) (postgres) pgsql 70801 0.0 0.6 49724 5016 ?? Is2:28AM 0:00.56 postgres: liferay lportal 127.0.0.1(56710) (postgres) pgsql 70802 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(65367) (postgres) pgsql 70803 0.0 0.6 49724 4508 ?? Is2:28AM 0:00.27 postgres: liferay lportal 127.0.0.1(55101) (postgres) pgsql 70804 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(62522) (postgres) pgsql 70805 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(57897) (postgres) pgsql 70806 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(51393) (postgres) pgsql 70894 0.0 0.8 49724 5908 ?? Is2:30AM 0:00.67 postgres: liferay lportal 127.0.0.1(65399) (postgres) pgsql 70895 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(54991) (postgres) pgsql 70896 0.0 0.7 49724 5412 ?? Is2:30AM 0:00.65 postgres: liferay lportal 127.0.0.1(63123) (postgres) pgsql 70897 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(63978) (postgres) pgsql 70898 0.0 0.8 49724 6284 ?? Is2:30AM 0:00.59 postgres: liferay lportal 127.0.0.1(65359) (postgres) pgsql 70902 0.0 0.6 49724 4264 ?? Is2:30AM 0:00.25 postgres: liferay lportal 127.0.0.1(50233) (postgres) pgsql 70903 0.0 0.6 49724 4924 ?? Is2:30AM 0:00.41 postgres: liferay lportal 127.0.0.1(65401) (postgres) pgsql 70904 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(63124) (postgres) pgsql 70905 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(54153) (postgres) pgsql 70906 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(63712) (postgres) pgsql 80297 0.0 0.7 49724 5044 ?? Is6:22AM 0:00.28 postgres: liferay lportal 127.0.0.1(58431) (postgres) pgsql 80298 0.0 0.7 48700 5288 ?? Is6:22AM 0:00.48 postgres: liferay lportal 127.0.0.1(50051) (postgres) pgsql 80299 0.0 1.0 49724 7600 ?? Is6:22AM 0:00.30 postgres: liferay lportal 127.0.0.1(49678) (postgres) pgsql 80300 0.0 0.7 48700 5380 ?? Is6:22AM 0:00.67 postgres: liferay lportal 127.0.0.1(58183) (postgres) pgsql 80301 0.0 0.6 49724 4748 ?? Is6:22AM 0:00.37 postgres: liferay lportal 127.0.0.1(55792) (postgres) pgsql 93527 0.0 0.4 49724 3464 ?? Is 11:28AM 0:00.14 postgres: liferay lportal 127.0.0.1(54222) (postgres) pgsql 93528 0.0 0.0 48700 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(52385) (postgres) pgsql 93529 0.0 0.0 48700 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(59677) (postgres) pgsql 93530 0.0 0.0 48700 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(60016) (postgres) pgsql 93531 0.0 0.0 49724 0 ?? IWs - 0:00.00 postgres: liferay lportal 127.0.0.1(56885) (postgres) pgsql 95330 0.0 0.9 49724 6812 ?? Is 12:05PM 0:00.18 postgres: liferay lportal 127.0.0.1(55050) (postgres) pgsql 95331 0.0 0.7 48700 5680 ?? Is 12:05PM 0:00.07 postgres: liferay lportal 127.0.0.1(53490) (postgres) pgsql 95332 0.0 0.8 49724 6572 ?? Is 12:05PM 0:00.47 postgres: liferay lportal 127.0.0.1(50029) (postgres) pgsql 95333 0.0 0.5 48700 3544 ?? Is 12:05PM 0:00.04 postgres: liferay lportal 127.0.0.1(65012) (postgres) pgsql 95334 0.0 0.8 48700 6236 ?? Is 12:05PM 0:00.06 postgres: liferay lportal 127.0.0.1(57130) (postgres) It seems I don't have enough memory to run my Liferay web portal, there are 3 web portal instances are running, but some of the primary keys are failed to index properly: .. 16:40:18,175 WARN [ResourceActionsUtil:564] Unable to obtain resource actions for unknown portlet SampleContactUs 17:45:17,326 WARN [PortalImpl:2948] Current URL /c/portal/login?p_l_id=10404
Re: [GENERAL] Too many postgres instances
Sam Wun swun2...@gmail.com wrote: Can anyone explain to me why my system is spawn so many postgress instances? Postgres starts a dedicated process for each connection. If you have too many processes, then either you're allowing too many connections, or you failed to size your server properly for the load you're experiencing. System: FreeBSD 7.2 Stable I386 RAM: 1GB 1G of RAM is not very much for a modern DB server. Here is the output of the top command: Mem: 457M Active, 92M Inact, 150M Wired, 23M Cache, 85M Buf, 18M Free Swap: 999M Total, 150M Used, 849M Free, 14% Inuse I see you only have 1G of swap, you should allocate more than that, 2x RAM is generally recommended. However, to address your RAM question directly, it's difficult to know where your problem is with the information you've provided. Let me take a guess, however. I'm guessing your shared buffers and other memory settings in Postgres are actually very low, and you're blaming Postgres for the memory problem simply because you see a lot of processes in top/ps. You can verify this by looking at these settings in your postgresql.conf: http://www.postgresql.org/docs/8.3/static/runtime-config-resource.html My guess, further is that it's actually Liferay that's using all the memory, it _is_ a Java app, and for all the good things about Java, it's not known for fitting in a small memory footprint. The top output will be a little more informative if you use: top -o res from the command line, which will sort the list by memory usage instead of CPU usage. Remember that whatever value you see for the postgres processes, you can subtract your shared_buffers setting from that, since all the postgres processes are sharing that RAM. Here is a list of the postgres instances running in my system: [snip] Looks like you have about 30, which isn't very many. As a result, I'd guess that you failed to properly plan your hardware for the load, or the load is higher than you expected. I'm not familiar with liferay, so I don't know if this is typical, but you might want to investigate your liferay configuration to see if you can convince it to use less connections. .. 16:40:18,175 WARN [ResourceActionsUtil:564] Unable to obtain resource actions for unknown portlet SampleContactUs 17:45:17,326 WARN [PortalImpl:2948] Current URL /c/portal/login?p_l_id=10404 generates exception: No Layout exists with the primary key 10404 20:22:57,623 WARN [PortalImpl:2948] Current URL /c/portal/login?p_l_id=11432 generates exception: No Layout exists with the primary key 11432 (END) I'm not sure that this problem is related to the other problems. It looks like your database has become corrupt or your configuration for liferay is corrupt. You'll probably get better assistance if you take this particular question to the Liferay community for help. Hope this points you in the right direction. Feel free to ask again if you hit other snags. -- Bill Moran http://www.potentialtech.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] connecting to a remote pq always require a password
Hi, I am trying to use the libpq to connect to my postgresql 8.3 server. If I use dbname = mydb, the connection made successfully because I am using a socket connection. But if I use host = 127.0.0.1 dbname = mydb, the error message is no password supplied. In the pg_hba.conf file, I even change the host access control to this: hostall all 127.0.0.1/32 trust but it still does not work. What I missed? -- Best Regards, David Shen -- 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] connecting to a remote pq always require a password
On Jun 18, 2009, at 8:11 AM, David Shen wrote: In the pg_hba.conf file, I even change the host access control to this: hostall all 127.0.0.1/32 trust but it still does not work. What I missed? Did you reload the configuration (or restart the sever) after making this change? pg_ctl reload -D /path/to/your/data John DeSoi, Ph.D. -- 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 -- monitor and suggestions
Is there a tool that can monitor my postgresql and recommend configuration changes based on actual use? For example, when I run VACUUM ANALYZE it tells me that I should consider increasing the max_fsm_relations. After monitoring for a period of time, I would like to know if my shared buffers are set optimally (for example). Or if I need more RAM in the machine to run better etc.. Thank you Sim -- 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 -- monitor and suggestions
2009/6/18 Sim Zacks s...@compulab.co.il: Is there a tool that can monitor my postgresql and recommend configuration changes based on actual use? For example, when I run VACUUM ANALYZE it tells me that I should consider increasing the max_fsm_relations. After monitoring for a period of time, I would like to know if my shared buffers are set optimally (for example). Or if I need more RAM in the machine to run better etc.. No. There's pgtune, but that's for initial configuration. EVerytnig else you have to findout yourself from logs. -- 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] connecting to a remote pq always require a password
--- On Thu, 6/18/09, David Shen davidshe...@googlemail.com wrote: From: David Shen davidshe...@googlemail.com Subject: [GENERAL] connecting to a remote pq always require a password To: pgsql-general@postgresql.org Date: Thursday, June 18, 2009, 12:11 PM Hi, I am trying to use the libpq to connect to my postgresql 8.3 server. If I use dbname = mydb, the connection made successfully because I am using a socket connection. But if I use host = 127.0.0.1 dbname = mydb, the error message is no password supplied. In the pg_hba.conf file, I even change the host access control to this: host all all 127.0.0.1/32 trust but it still does not work. What I missed? -- Best Regards, David Shen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general waht is the ip number of the host machien and the remote machine to try connect you can pass a password to the string conection host=127.0.0.1 dbname=mydb user=myuser password=mypassword maybe the problem is the net direction 127.0.0.x check this -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SET TIMEZONE doesn't affect to SELECT statement
Title: SET TIMEZONE doesn't affect to SELECT statement Hello; by tutorial I have tried the example: set timezone to 'Europe/London'; select '2008-01-01 12:00:00 GMT+2'::timestamptz; Result: 2008-01-01 15:00:00 set timezone to 'Europe/Moscow'; select '2008-01-01 12:00:00 GMT+2'::timestamptz; Result: 2008-01-01 15:00:00 Why is the result identical ? Is something wrong ? Thanks for suggestion.
Re: [GENERAL] SET TIMEZONE doesn't affect to SELECT statement
Strange, maybe there's some server setting because I get different results on mine.. set timezone to 'Europe/London'; select '2008-01-01 12:00:00 GMT+2'::timestamptz; Result: '2008-01-01 14:00:00+00' set timezone to 'Europe/Moscow'; select '2008-01-01 12:00:00 GMT+2'::timestamptz; Result: '2008-01-01 17:00:00+03' 2009/6/18 POST p...@centrum.sk Hello; by tutorial I have tried the example: set timezone to 'Europe/London'; select '2008-01-01 12:00:00 GMT+2'::timestamptz; Result: 2008-01-01 15:00:00 set timezone to 'Europe/Moscow'; select '2008-01-01 12:00:00 GMT+2'::timestamptz; Result: 2008-01-01 15:00:00 Why is the result identical ? Is something wrong ? Thanks for suggestion.
[GENERAL] Question re 2 aggregates from 1 query
Assume a simple many-to-one between 2 tables: create table docs (id int8 primary key, timestamp imported_when); create table pages (id int8 primary key, doc_id int8 not null references docs); It's easy to get a count of docs imported by date: select imported_when::date, count(1) from docs group by imported_when::date; It's easy to get a count of pages imported by date: select imported_when::date, count(1) from docs, pages where docs.id = pages.doc_id group by imported_when::date; Is there any way to get count of docs pages imported by date without resorting to selecting from a select: select dt, count(1), numpgs from ( select docs.imported_when::date as dt, count(1) as numpgs from docs, pages where docs.id = pages.doc_id group by docs.imported_when::date, docs.id ) as t1 group by dt order by dt; -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- 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] Question re 2 aggregates from 1 query
Yes, obviously that should have been sum(numpgs) in the select list of the last query... Question remains the same... -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- 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] FYI: Load times for a largish DB in 8.2 vs. 8.3 vs. 8.4
On Wed, Jun 17, 2009 at 10:50 AM, Todd A. Cooktc...@blackducksoftware.com wrote: The loads were all done on the same machine, with the DB going on a pair of SATA drives in a RAID-0 stripe. The machine has 2 non-HT Xeons and 8GB RAM. maintenance_work_mem was set to 512MB in all three cases. What if you double or triple the number of checkpoint segments? -- 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] Question re 2 aggregates from 1 query
Is there any way to get count of docs pages imported by date without resorting to selecting from a select: [Spotts, Christopher] If I understand you correctly..? create table docs (id int8 primary key, imported_when timestamp ); create table pages (id int8 primary key, doc_id int8 not null references docs); INSERT INTO docs values (1,now()),(2,now()),(3,now()),(4,now()- interval '1 day'); INSERT INTO pages values (1,1),(2,1),(3,2),(4,2),(5,3),(6,4),(7,4),(8,4); select imported_when::date, count(distinct pages.id),count(distinct docs.id) from docs, pages where docs.id = pages.doc_id group by imported_when::date; -- 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] FYI: Load times for a largish DB in 8.2 vs. 8.3 vs. 8.4
There was an interesting presentation at PG Con from a guy at Sun who did a series of load tests on 8.3 vs 8.4 http://www.pgcon.org/2009/schedule/events/124.en.html There is a link to the video from that page so you can watch it. But he found a strange corner case where 8.4 performed way worse. After he did a bit of digging he found a couple of default settings that had changed in 8.4, and when he set them back to their old 8.3 values and re-ran the tests, there was a huge difference in outcome. On Wed, Jun 17, 2009 at 10:50 AM, Todd A. Cooktc...@blackducksoftware.com wrote: Hi, First, the numbers: PG Version Load time pg_database_size autovac -- 8.2.13 179 min 92,807,992,820 on 8.3.7 180 min 84,048,744,044 on (defaults) 8.4b2 206 min 84,028,995,344 on (defaults) 8.4b2 183 min 84,028,839,696 off The bulk of the data is in 16 tables, each having about 55 million rows of the form (int, int, smallint, smallint, int, int, int). Each table has a single partial index on one of the integer columns. The dump file was 14GB compressed. The loads were all done on the same machine, with the DB going on a pair of SATA drives in a RAID-0 stripe. The machine has 2 non-HT Xeons and 8GB RAM. maintenance_work_mem was set to 512MB in all three cases. -- todd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of In Defense of Food -- 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] FYI: Load times for a largish DB in 8.2 vs. 8.3 vs. 8.4
Vick Khera wrote: On Wed, Jun 17, 2009 at 10:50 AM, Todd A. Cooktc...@blackducksoftware.com wrote: The loads were all done on the same machine, with the DB going on a pair of SATA drives in a RAID-0 stripe. The machine has 2 non-HT Xeons and 8GB RAM. maintenance_work_mem was set to 512MB in all three cases. What if you double or triple the number of checkpoint segments? checkpoint_segments was set to 128 for all tests. -- todd -- 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] Question re 2 aggregates from 1 query
If I understand you correctly..? Yes, exactly! I think I was suffering from a flashback to a very old DBMS that didn't support that use of distinct... -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- 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] Could not reattach to shared memory
Tuan Hoang Anh wrote: I am running postgres 8.3.7 on windows server 2008 SP1. But this error alway occur : FATAL: could not reattach to shared memory (key=260, addr=0240): 487 .Here is my log 2009-06-17 20:45:18 ICT LOG: database system was shut down at 2009-06-17 17:42:45 ICT 2009-06-17 20:45:22 ICT LOG: database system is ready to accept connections 2009-06-17 21:36:45 ICT LOG: loaded library $libdir/plugins/plugin_debugger.dll FATAL: could not reattach to shared memory (key=260, addr=0240): 487 2009-06-17 21:36:48 ICT LOG: could not receive data from client: Unknown winsock error 10061 2009-06-17 21:36:48 ICT LOG: unexpected EOF on client connection When i run my application again and again, this error disappear. I have NOD32 and add Postgres\bin\*.* to exclusions of NOD32. My system : Postgres 8.3.7 Windows Server 2008 SP1. Postgres 8.4 beta 1 on other port. Postgres 8.1 on other port. NOD32 Yep, we have a TODO item for it, but no solution: Diagnose problem where shared memory can sometimes not be attached by postmaster children -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] running pg_dump from python
On Jun 15, 2009, at 5:17 AM, Jasen Betts wrote: On 2009-06-14, Garry Saddington ga...@schoolteachers.co.uk wrote: I ahve the following python file that I am running as an external method in Zope. def backup(): import os os.popen(c:/scholarpack/postgres/bin/pg_dump scholarpack c:/scholarpack/ancillary/scholarpack.sql) are you sure you're using os.popen correctly? you don't appear to be waiting for the pg_dump process to finish. Right, the popen stuff should be something like: p = os.popen(c:/scholarpack/postgres/bin/pg_dump scholarpack c:/ scholarpack/ancillary/scholarpack.sql 2 c:/scholarpack/ancillary/ dump.err) status = p.close() Then check status to see if the command was successful or not. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] FYI: Load times for a largish DB in 8.2 vs. 8.3 vs. 8.4
Todd A. Cook tc...@blackducksoftware.com writes: First, the numbers: PG VersionLoad time pg_database_size autovac -- 8.2.13179 min 92,807,992,820on 8.3.7 180 min 84,048,744,044on (defaults) 8.4b2 206 min 84,028,995,344on (defaults) 8.4b2 183 min 84,028,839,696off The bulk of the data is in 16 tables, each having about 55 million rows of the form (int, int, smallint, smallint, int, int, int). Each table has a single partial index on one of the integer columns. Given that it's multiple tables, it'd be possible for autovacuum to kick in and ANALYZE the data inserted into earlier tables while the later ones were still being loaded. If so, the discrepancy might be explained by 8.4's more-aggressive statistics target, which means that a background ANALYZE will take about 10x more work than before. If you have time to repeat the experiments, it would be interesting to see what happens with consistent default_statistics_target across 8.3 and 8.4. 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] postgresql-8.3.7 unexpected connection closures
I posted this a while back but got no useful responses. I have the following error message: Error connecting to the server: server closed the connection unexpectedly. This probably means that the server terminated abnormally before or while processing the request. It happens intermittently both when using pgAdminIII and when using QT so it is not a pgAdminIII issue. Often the connection seems to succeed but you get a series of error messages like the one above when attempting to open a database. It seems to be a timing issue. This is happening when using a local connection on vista, irrespective of whether the antivirus is on or off. I can't see anything to change in postgresql.conf that could alleviate this problem. This does not happen with postgresql-8.3.5 but does happen with postgresql-8.3.7. This is happening on two different machines (both Vista SP2) running different antivirus programs so I suspect there has been some change to postgres that has triggered this problem. Postgres has become unuseable on these machines. Thanks for may help Andrew -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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] postgresql-8.3.7 unexpected connection closures
On Fri, 2009-06-19 at 09:12 +1000, Andrew Maclean wrote: I posted this a while back but got no useful responses. I have the following error message: Error connecting to the server: server closed the connection unexpectedly. This probably means that the server terminated abnormally before or while processing the request. Look at the PostgreSQL server log to see what happened. On Windows, I think this is in your PostgreSQL data directory. (List members: This seems to confuse Windows admins a lot - they're not used to application log files, apparently. Is it worth having the win32 version of the server report sudden backend death via the Windows event logging system, with a reference to the Pg log file? I'm not sure, since it's not clear that most Windows admins look at Event Viewer either...) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql-8.3.7 unexpected connection closures
Thanks for the quick response, the log shows the following: 2009-06-19 07:51:47 EST LOG: database system was interrupted; last known up at 2009-06-18 19:14:37 EST 2009-06-19 07:51:47 EST LOG: database system was not properly shut down; automatic recovery in progress 2009-06-19 07:51:47 EST LOG: record with zero length at 0/6614E8 2009-06-19 07:51:47 EST LOG: redo is not required 2009-06-19 07:51:47 EST LOG: database system is ready to accept connections 2009-06-19 07:51:47 EST LOG: autovacuum launcher started 2009-06-19 07:54:51 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2009-06-19 07:54:51 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2009-06-19 07:54:51 EST LOG: could not receive data from client: Unknown winsock error 10061 2009-06-19 07:54:52 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 2009-06-19 10:47:35 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 FATAL: could not reattach to shared memory (key=252, addr=0208): 487 2009-06-19 10:49:04 EST WARNING: worker took too long to start; cancelled FATAL: could not reattach to shared memory (key=252, addr=0208): 487 On Fri, Jun 19, 2009 at 10:40 AM, Craig Ringercr...@postnewspapers.com.au wrote: On Fri, 2009-06-19 at 09:12 +1000, Andrew Maclean wrote: I posted this a while back but got no useful responses. I have the following error message: Error connecting to the server: server closed the connection unexpectedly. This probably means that the server terminated abnormally before or while processing the request. Look at the PostgreSQL server log to see what happened. On Windows, I think this is in your PostgreSQL data directory. (List members: This seems to confuse Windows admins a lot - they're not used to application log files, apparently. Is it worth having the win32 version of the server report sudden backend death via the Windows event logging system, with a reference to the Pg log file? I'm not sure, since it's not clear that most Windows admins look at Event Viewer either...) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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] postgresql-8.3.7 unexpected connection closures
On Fri, 2009-06-19 at 10:50 +1000, Andrew Maclean wrote: 2009-06-19 07:54:51 EST LOG: could not receive data from client: Unknown winsock error 10061 Winsock error 10061 is WSAECONNREFUSED (10061) Connection Refused. I presume that means the client has sent an RST packet, but ... wtf? Why? Is the client on the same machine as the server? If not, is the client running any sort of software firewall? You say the two machines have two different AV products, but you don't mention which ones. What products are they, and what versions of those products? I strongly suspect firewall / antivirus involvement. Can you _uninstall_ your AV/firewall on one of the Pg server machines, reboot it, and re-test? Do not just disable it, completely uninstall it, just for testing purposes. FATAL: could not reattach to shared memory (key=252, addr=0208): 487 I've seen discussion of this one before, but never any sort of resolution. http://www.google.com.au/search?hl=enq=%22could+not+reattach+to+shared +memory%22+%22[hackers]%22 Apparently it arises more often in the presence of antivirus software and the like, but isn't known to be directly caused by it. Maybe somone else can shed some more light on this one. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?
Hi, I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg with a prepare statement: EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__; EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = __LINE__; EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__; EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__; I get an SQL error: invalid statement name (null) on line ## (3rd line above) on the OPEN cursor statement. I have been digging a little in the ecpg output and noticed that the format of the ECPGprepare() function call has changed between 8.2.4 and 8.3++. Also the ecpg library has changed version libecpg.so.5 - libecpg.so.6. I am compiling my program on an (older) system with 8.2.4 installed and I need have my program running on a production system running 8.3.5 or higher. This seems to work fine, but for other reasons I wanted to upgrade my compile system with 8.3.5 and this started to give me the error mentioned above. An ldd on the program gives: libecpg.so.6 = /usr/local/Packages/pgsql-8.3.5/lib/libecpg.so.6 (0xb7ed8000) libpq.so.5 = /usr/local/Packages/pgsql-8.3.5/lib/libpq.so.5 (0xb7ebc000) which looks to be ok. (The 8.2.4 compiled program was using libecpg.so.5.) It seems to me that somehow the prepare statement (first line above) is compiled into a ECPGprepare() call with 5 parameters, but using a library function with only 3 parameters, even though it points to the libecpg.so.6, which includes code having the 5 parameter ECPGprepare() function. All PostgreSQL systems are natively installed from source on the various systems, i.e. compiled individually on each system. All my systems are running Linux, but with different versions of Linux. I have also tried the very newest version 8.4rc1, but with same result, the error above. Please help, Leif -- 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] postgresql-8.3.7 unexpected connection closures
One is running McAfee and the other is running Symantec Endpoint. It does not matter whether the firewalls are on or off. One thing I have noticed it that if I stop and restart the service everything seems to work Ok. Andrew On Fri, Jun 19, 2009 at 11:18 AM, Craig Ringercr...@postnewspapers.com.au wrote: On Fri, 2009-06-19 at 10:50 +1000, Andrew Maclean wrote: 2009-06-19 07:54:51 EST LOG: could not receive data from client: Unknown winsock error 10061 Winsock error 10061 is WSAECONNREFUSED (10061) Connection Refused. I presume that means the client has sent an RST packet, but ... wtf? Why? Is the client on the same machine as the server? If not, is the client running any sort of software firewall? You say the two machines have two different AV products, but you don't mention which ones. What products are they, and what versions of those products? I strongly suspect firewall / antivirus involvement. Can you _uninstall_ your AV/firewall on one of the Pg server machines, reboot it, and re-test? Do not just disable it, completely uninstall it, just for testing purposes. FATAL: could not reattach to shared memory (key=252, addr=0208): 487 I've seen discussion of this one before, but never any sort of resolution. http://www.google.com.au/search?hl=enq=%22could+not+reattach+to+shared +memory%22+%22[hackers]%22 Apparently it arises more often in the presence of antivirus software and the like, but isn't known to be directly caused by it. Maybe somone else can shed some more light on this one. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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] postgresql-8.3.7 unexpected connection closures
On Fri, 2009-06-19 at 11:30 +1000, Andrew Maclean wrote: One is running McAfee and the other is running Symantec Endpoint. It does not matter whether the firewalls are on or off. I increasingly wonder if Symantec or McAfee can be persuaded to offer a buildfarm machine with their software to PostgreSQL. Virus scanners on servers are an (IMO nearly insane) fact of life on Windows, apparently, and certainly on Windows desktops that may also run PostgreSQL, so it needs to get along with them. I'm far from certain that your issues are AV related, but if you want to track them down the first thing to do is UNINSTALL one of your AV products and see if the issues go away. Reinstall it once you know; the point is to test whether the AV is part of the problem or not. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?
can we see the original statement ? can you combine the 2 statements to produce the necessary cursor OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html takk Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Fri, 19 Jun 2009 03:08:35 +0200 From: l...@crysberg.dk To: pgsql-general@postgresql.org Subject: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ? Hi, I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg with a prepare statement: EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__; EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = __LINE__; EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__; EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__; I get an SQL error: invalid statement name (null) on line ## (3rd line above) on the OPEN cursor statement. I have been digging a little in the ecpg output and noticed that the format of the ECPGprepare() function call has changed between 8.2.4 and 8.3++. Also the ecpg library has changed version libecpg.so.5 - libecpg.so.6. I am compiling my program on an (older) system with 8.2.4 installed and I need have my program running on a production system running 8.3.5 or higher. This seems to work fine, but for other reasons I wanted to upgrade my compile system with 8.3.5 and this started to give me the error mentioned above. An ldd on the program gives: libecpg.so.6 = /usr/local/Packages/pgsql-8.3.5/lib/libecpg.so.6 (0xb7ed8000) libpq.so.5 = /usr/local/Packages/pgsql-8.3.5/lib/libpq.so.5 (0xb7ebc000) which looks to be ok. (The 8.2.4 compiled program was using libecpg.so.5.) It seems to me that somehow the prepare statement (first line above) is compiled into a ECPGprepare() call with 5 parameters, but using a library function with only 3 parameters, even though it points to the libecpg.so.6, which includes code having the 5 parameter ECPGprepare() function. All PostgreSQL systems are natively installed from source on the various systems, i.e. compiled individually on each system. All my systems are running Linux, but with different versions of Linux. I have also tried the very newest version 8.4rc1, but with same result, the error above. Please help, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Microsoft brings you a new way to search the web. Try Bing™ now http://www.bing.com?form=MFEHPGpubl=WLHMTAGcrea=TEXT_MFEHPG_Core_tagline_try bing_1x1
[GENERAL] Maintaining user roles and permissions in Postgres - general question
Does anyone have a recommendation for maintaining user permissions on a changing database? The lack of an option to grant specific rights to all objects of a given type within a Postgres db obviously places the burden on the administrator to keep roles updated as objects are added and dropped from a given database. Unfortunately for us, we don't have a dedicated db admin, so this task falls into the hands of developers who are probably less adapt at this kind of task ;) Is there a utility or set of scripts out there that helps a db owner with permissions admin? 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] Invalid statement name (null) in line ## - what am I doing wrong ?
Oops, forgot to include that, sorry. This is from a log output since it is dynamically generated: stmt= SELECT groupid, dg.ctrlid, userid, description, phoneno, ipaddr, online, active, dt.typename, null FROM devicegroup dg, device d, devtype dt WHERE userid = 23 AND typename = 'adm' AND dg.ctrlid = d.id AND dt.id = d.devtypeid ORDER BY d.id; Running this (copy/paste) in psql gives me the expected result as well as when using the 8.2.4 compiled version. I don't think I would be able to do what you suggests, since both fields, tables and where are dynamically generated based on many things. Leif - Martin Gainty mgai...@hotmail.com wrote: can we see the original statement ? can you combine the 2 statements to produce the necessary cursor OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1); http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html takk Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Fri, 19 Jun 2009 03:08:35 +0200 From: l...@crysberg.dk To: pgsql-general@postgresql.org Subject: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ? Hi, I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg with a prepare statement: EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__; EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = __LINE__; EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__; EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__; I get an SQL error: invalid statement name (null) on line ## (3rd line above) on the OPEN cursor statement. I have been digging a little in the ecpg output and noticed that the format of the ECPGprepare() function call has changed between 8.2.4 and 8.3++. Also the ecpg library has changed version libecpg.so.5 - libecpg.so.6. I am compiling my program on an (older) system with 8.2.4 installed and I need have my program running on a production system running 8.3.5 or higher. This seems to work fine, but for other reasons I wanted to upgrade my compile system with 8.3.5 and this started to give me the error mentioned above. An ldd on the program gives: libecpg.so.6 = /usr/local/Packages/pgsql-8.3.5/lib/libecpg.so.6 (0xb7ed8000) libpq.so.5 = /usr/local/Packages/pgsql-8.3.5/lib/libpq.so.5 (0xb7ebc000) which looks to be ok. (The 8.2.4 compiled program was using libecpg.so.5.) It seems to me that somehow the prepare statement (first line above) is compiled into a ECPGprepare() call with 5 parameters, but using a library function with only 3 parameters, even though it points to the libecpg.so.6, which includes code having the 5 parameter ECPGprepare() function. All PostgreSQL systems are natively installed from source on the various systems, i.e. compiled individually on each system. All my systems are running Linux, but with different versions of Linux. I have also tried the very newest version 8.4rc1, but with same result, the error above. Please help, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Microsoft brings you a new way to search the web. Try Bing™ now -- 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] Maintaining user roles and permissions in Postgres - general question
Postgres User wrote: Does anyone have a recommendation for maintaining user permissions on a changing database? The lack of an option to grant specific rights to all objects of a given type within a Postgres db obviously places the burden on the administrator to keep roles updated as objects are added and dropped from a given database. Unfortunately for us, we don't have a dedicated db admin, so this task falls into the hands of developers who are probably less adapt at this kind of task ;) Is there a utility or set of scripts out there that helps a db owner with permissions admin? your question is just too vague to even begin to know what it is you want ? there are scripts for GRANTALL like functionality. But, if you use nested roles for your permissions, these things just don't come up. Or if the database is owned by the account that creates the tables and accesses it, again, they don't come up at all. -- 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] Maintaining user roles and permissions in Postgres - general question
On Thu, Jun 18, 2009 at 8:03 PM, Postgres Userpostgres.develo...@gmail.com wrote: Does anyone have a recommendation for maintaining user permissions on a changing database? The lack of an option to grant specific rights to all objects of a given type within a Postgres db obviously places the burden on the administrator to keep roles updated as objects are added and dropped from a given database. Unfortunately for us, we don't have a dedicated db admin, so this task falls into the hands of developers who are probably less adapt at this kind of task ;) Is there a utility or set of scripts out there that helps a db owner with permissions admin? It's easy enough to write scripts to do this, HOWEVER, down that road may lie madness. Let's say you've got 100 different users who need access to various parts of your database. If you start assigning all kinds of permissions to each user, you're gonna go insane. What works better is to assign roles the proper permissions. So, hr_admin role can change records in hr tables, hr_user can read records in hr tables and only change one or two, and so on. Then when someone comes on as an HR user, you just grant them the role. They leave the HR group, you revoke the role. ding, job done. You only ever need to assign the rights once really, to the main role, and from then on it's just one assignment / revocation to a user or users. -- 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] postgresql-8.3.7 unexpected connection closures
Craig Ringer cr...@postnewspapers.com.au writes: I increasingly wonder if Symantec or McAfee can be persuaded to offer a buildfarm machine with their software to PostgreSQL. Virus scanners on servers are an (IMO nearly insane) fact of life on Windows, apparently, and certainly on Windows desktops that may also run PostgreSQL, so it needs to get along with them. There's always the friends don't let friends run servers on Windows approach. 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] postgresql-8.3.7 unexpected connection closures
On Fri, 2009-06-19 at 01:03 -0400, Tom Lane wrote: Craig Ringer cr...@postnewspapers.com.au writes: I increasingly wonder if Symantec or McAfee can be persuaded to offer a buildfarm machine with their software to PostgreSQL. Virus scanners on servers are an (IMO nearly insane) fact of life on Windows, apparently, and certainly on Windows desktops that may also run PostgreSQL, so it needs to get along with them. There's always the friends don't let friends run servers on Windows approach. ... a sentiment I can't really argue with most of the time. Alas, people are sometimes forced into it by braindead corporate policy, central IT management, and the like. It'd be nice if Pg could play well with at least the less horrifyingly broken virus scanners out there, at least when excluded from scanning. Mostly this seems to mean dealing with DLLs being injected into the process address space. Sometimes people also run Pg on home/work desktops, whether for development or just for small databases. It'd be nice if this use worked well in the face of a frankly rather hostile host (virus scanners, firewalls, etc). Of course, I've done enough development on Windows to know just how much I dislike it, so I'm hardly leaping to the task... I see lots of questions here that seem to be related to (a) virus scanner interference and (b) installation/reinstallation. Lots of the reinstall issues seem to be with people who don't really understand NT users, ACLs, etc and aren't really competent to admin a machine, but they do make me wonder if the Pg installer can do more to help them out, eg: The data directory you have specified (C:\PgData) already exists, but cannot be accessed by the user you want to run PostgreSQL as. Would you like to: [a] use a new data directory, [b] grant the postgresql user the rights to access the data directory you have specified, or [c] change the user you start PostgreSQL as to the user owning the data directory ? [clean install to new directory][grant access to old directory][change postgresql user][cancel installation] The data directory you have specified (C:\PgData) contains a database from an older version of PostgreSQL (8.2) that this version (8.3.6) cannot access. Would you like to use a new data directory C:\PgData-8.3, leaving the old one untouched? Note that PostgreSQL will not automatically convert your data. You REALLY should read the upgrading documentation before continuing. [clean install to new data directory][cancel installation] PostgreSQL has detected that another program, probably an older version of PostgreSQL, is listening on port 5432. If you want to use this version on the default port 5432, you will need to stop or uninstall the other program first. [Change PostgreSQL port][Cancel installation] Of course, if Windows development is un-fun, windows program installation and installer building is more so. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
