[ADMIN] Out of memory
On a computer with 2G Ram running Debian Squeeze and Postgresql 8.4.8-0squeeze2 I made a dump using 'pg_dump kb kb.sql'. I copied the file to another computer with 8G RAM running Debian wheezy/sid/Postgresql 8.4.8-2 and tried to load that data by running 'psql -f kb.sql' - a process which produced a lot of errors ending with psql:kb.sql:4189263: invalid command \N psql:kb.sql:4189277: invalid command \N psql:kb.sql:4189278: invalid command \N psql:kb.sql:4189279: invalid command \nMost psql:kb.sql:4189280: invalid command \N psql:kb.sql:4189281: invalid command \N psql:kb.sql:4189282: invalid command \N psql:kb.sql:4189283: invalid command \N psql:kb.sql:4189284: invalid command \N psql:kb.sql:7991569: ERROR: out of memory DETAIL: Cannot enlarge string buffer containing 0 bytes by 1311240426 more bytes. What is causing the 'invalid command' lines? And the memory problem? There are several foreign keys in the database. Regards Johann -- Johann SpiesTelefoon: 021-808 4699 Databestuurder / Data manager Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie Centre for Research on Evaluation, Science and Technology Universiteit Stellenbosch. Children, obey your parents in the Lord: for this is right. Ephesians 6:1 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] vacuumdb question/problem
Thanks to everyone for their response and help. I still have some more questions that hopefully someone can help me with as I have not yet been able to solve my vacuumdb problem. The posting of data to the table in question is extremely slow...yesterday I saw that it took over 6 min to post just 124 rows of data. That is just not acceptable. Additionally, we have about 9,000 to 11,000 products that come in daily (some contain one row of data...others may be several hundred lines) and are posted to the database. The system used to run with maybe 10-100 products in the queue (that is before I posted over 200 million rows). Yesterday, there were over 25,000 products in the queue waiting to be processed - which is bad for our operational use. I think the table got even more bloated when I tried to do the vacuum full last week and had to kill the process - it never completed. From the info I received from previous posts, I am going to change my game plan. If anyone has thoughts as to different process or can confirm that I am on the right track, I would appreciate your input. 1. I am going to run a CLUSTER on the table instead of a VACUUM FULL. But I have a few questions that are not clear from the info I have found on the internet regarding this process. The table name is 'pecrsep', and the database is hd_ob92rha. It has multiple columns in the table, but I am choosing the 'obsdate' as my index (I believe 'index' refers to the name of a column in the table?). Some of the columns are listed: lid | pe1 | pe2 | dur | idur | t | s | e | p | obstime | z | z0015 | z0030 | z0045 | z0100 | z0115 | z0130 ... etc Anyway, if I interpret the info correctly the first time a CLUSTER is run, I have to run something like: CLUSTER obsdate ON pecrsep (CLUSTER /indexname/ ON /tablename/) Is it true that the first time I run a CLUSTER, I must provide an 'indexname' or can I just run: CLUSTER pecrsep (CLUSTER tablename)? Or is it better to provide an indexname? 2. I know there are a lot of variables such as table size, etc. Are we looking at something that will take couple hours...or 24+ hours? Is there a way to monitor the CLUSTER process to ensure its working? Is there any way to get output to give an estimate as to how much of the CLUSTER has completed...and how much is left to run? 3. With the info from previous posts, I am certainly rethinking the use of VACUUM FULL. After I run the CLUSTER, should I REINDEX the table, or is that redundant to the CLUSTER? If I run the REINDEX, I would do it just for the single table: REINDEX TABLE pecrsep; Any idea as to how long this may take (i.e 1-2 hours or 24+ hours)? 4. If either/both the CLUSTER or REINDEX are successful, that would indicate that I don't need to run a VACUUM FULL...correct? 5. Then I would run a VACUUM ANALYZE as suggested. Does this sound like an appropriate plan? After I do all this on the single table, should I repeat something similar for the whole database? Or should I just attack the largest tables? One last question, can anyone recommend a good postgres book for me to purchase? I guess I should repeat that we are still running version 8.2.6 of postgres on Linux machines. Is there an appropriate book for this version available? Thanks again for your help...I hope I didn't ask too many questions, but the database is in poor shape and I need to get it working more efficiently quickly. Best Regards, Dave attachment: david_ondrejik.vcf -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] how to make PostgreSQL using all memory and chaching the DB completely there
Christoph Anton Mitterer christoph.anton.mitte...@physik.uni-muenchen.de wrote: Well I'm still very unsure on how to configure many values... We've increased e.g. shared_buffers, temp_buffers, work_mem, maintenance_work_mem, max_stack_depth... and this greatly improved performance. But I can hardly judge to which values I should increase all these (and a few more). Maybe the ellipses cover these, but wal_buffers and checkpoint_segments should generally be adjusted, too. I almost always need to tweak some of the costing factors, too; but appropriate settings there depend not only on your hardware, but also your schema, data, and application mix. The best source of information on this that I know is Greg Smith's PostgreSQL 9.0 High Performance book. (It also discusses older versions, so don't let the 9.0 in the title put you off.) Some of these settings are best tuned through an iterative process of monitoring while making small adjustments. http://www.postgresql.org/docs/books/ -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] vacuumdb question/problem
David Ondrejik david.ondre...@noaa.gov wrote: The posting of data to the table in question is extremely slow...yesterday I saw that it took over 6 min to post just 124 rows of data. That is just not acceptable. Additionally, we have about 9,000 to 11,000 products that come in daily (some contain one row of data...others may be several hundred lines) and are posted to the database. The system used to run with maybe 10-100 products in the queue (that is before I posted over 200 million rows). Yesterday, there were over 25,000 products in the queue waiting to be processed - which is bad for our operational use. Sounds like bloat. I think the table got even more bloated when I tried to do the vacuum full last week and had to kill the process - it never completed. VACUUM FULL can increase bloat if it doesn't complete. Even when it completes it tends to bloat indexes. From the info I received from previous posts, I am going to change my game plan. If anyone has thoughts as to different process or can confirm that I am on the right track, I would appreciate your input. 1. I am going to run a CLUSTER on the table instead of a VACUUM FULL. If you have room for a second copy of your data, that is almost always much faster, and less prone to problems. But I have a few questions that are not clear from the info I have found on the internet regarding this process. The table name is 'pecrsep', and the database is hd_ob92rha. It has multiple columns in the table, but I am choosing the 'obsdate' as my index (I believe 'index' refers to the name of a column in the table?). No, it refers to the name of an index. For example, you might have an index to support your primary key declaration named pecrsep_pkey. If you are using psql, type \d pecrsep When I do that for our Party table in our statewide copy of the data, I see these lines near the bottom: Indexes: Party_pkey PRIMARY KEY, btree (countyNo, caseNo, partyNo) CLUSTER Party_EAccountNo btree (countyNo, eAccountNo) Party_SearchName btree (searchName, countyNo) So I could choose to cluster this table using Party_pkey, Party_EAccountNo, or Party_SearchName. The primary key has been set as the default if I don't specify an index. Anyway, if I interpret the info correctly the first time a CLUSTER is run, I have to run something like: CLUSTER obsdate ON pecrsep (CLUSTER /indexname/ ON /tablename/) For 8.2 that is correct. (In more recent versions that syntax is deprecated in favor of a new wording which is more natural for most people.) Is it true that the first time I run a CLUSTER, I must provide an 'indexname' or can I just run: CLUSTER pecrsep (CLUSTER tablename)? Or is it better to provide an indexname? If an index name has been set for a default on that table, and that's the index you want to use, it really doesn't matter. 2. I know there are a lot of variables such as table size, etc. Are we looking at something that will take couple hours...or 24+ hours? Before I gave up on VACUUM FULL as an overall bad idea for us, I had left a VACUUM FULL on a large table (which had some bloat) running when I left work Friday, and found it still running Monday morning. I canceled the VACUUM FULL, noticed it was now *further* bloated, and used CLUSTER. In our case, with that table, it finished in a few hours. Is there a way to monitor the CLUSTER process to ensure its working? I guess you could look at the generated files, but I've never heard of it failing, short of running the data area out of space. Is there any way to get output to give an estimate as to how much of the CLUSTER has completed...and how much is left to run? If you calculated an estimate of the non-bloated heap space you could watch the files being created for the new copy as it runs. Remember that all the indexes also need to be built after the heap is populated. 3. With the info from previous posts, I am certainly rethinking the use of VACUUM FULL. Good. After I run the CLUSTER, should I REINDEX the table, or is that redundant to the CLUSTER? That would be redundant. At completion of the CLUSTER, the table has brand new, freshly built indexes. Any idea as to how long this may take (i.e 1-2 hours or 24+ hours)? If you know the speed of your drives, you can set a lower bound on it, I guess. *At least* as much time as it takes to read the entire table through the specified index, plus the time to sequentially write the un-bloated heap, plus the time to build all indexes on the table. Caching effects can make this tricky to estimate. 4. If either/both the CLUSTER or REINDEX are successful, that would indicate that I don't need to run a VACUUM FULL...correct? REINDEX would not eliminate heap bloat. 5. Then I would run a VACUUM ANALYZE as suggested. Be sure to run autovacuum. You probably want to run it with settings more aggressive than the 8.2 defaults. We generally supplement
[ADMIN] Followup on 'Standby promotion does not work'
Couple of months back, Josh Berkus posted an issue with promotion of the standby in Streaming replication. Subject 'Standby promotion does not work' in the pgsql-hackers forum. It seemed that during failover, it was not possible to repoint the rest of the slaves to the new master. Is this resolved ? Thanks.
[ADMIN] replication_timeout does not seem to be working
On 9.1, Beta3 I set the following on master replication_timeout = 10s # in milliseconds; 0 disables With no slaves running, I expect a failure in about 10s. But any Insert just hangs. Any idea ? Thanks.
[ADMIN] revoked permissions on table still allows users to see table's structure
Hi All I'm new to the list, but have a few years as postgres user. I want to share what I consider a rare behavior of postgresql regarding database object's premissions: I have noticed that there is no way (at least no one I know) to prevent a user from seeing the table's structures in a database. I created a new user (user1) and do the following: 'revoke all on all tables in schema public from public;' After that, user1 cant do select, inserts , etc from database's tables. But still is able to see my table's structure: voicemax=select * from tasks; ERROR: permission denied for relation tasks voicemax= delete from tasks where task_id=6; ERROR: permission denied for relation tasks voicemax= \d tasks Table public.tasks Column | Type | Modifiers -++- task_id | bigint | not null task_name | character varying(32) | not null description | character varying(128) | enabled | integer| default 0 Indexes: tasks_task_name_key UNIQUE, btree (task_name) The same behavior occurs when this user is logged from pgAdmin. User can see all table's structure, even if have no privileges on database tables. Is this a normal behavior of the product ? Is there a way to prevent a user from seeing my table's, procedure's and function's code ? Thanks in advance. -- Juan R. Cuervo Soto Quality Telecom Ltd www.quality-telecom.net PBX : (575) 3693300 CEL : (57) 301-4174865 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
On Thu, Jul 21, 2011 at 6:08 PM, Juan Cuervo (Quality Telecom) juanrcue...@quality-telecom.net wrote: Hi All I'm new to the list, but have a few years as postgres user. I want to share what I consider a rare behavior of postgresql regarding database object's premissions: I have noticed that there is no way (at least no one I know) to prevent a user from seeing the table's structures in a database. Is this a normal behavior of the product ? Yep. Completely normal. Is there a way to prevent a user from seeing my table's, procedure's and function's code ? Don't let them connect to the db? That's all I can think of. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
