Re: [ADMIN] ]To estimate the size of db
Hi Duan, perhaps you can go the way via some system functions like: ## list tables and their size SELECTrelname AS table_name, pg_size_pretty(pg_relation_size(oid)) AS table_sz, pg_size_pretty(pg_total_relation_size(oid)) AS total_sz FROM pg_class WHERE relkind = 'r' ORDER BY pg_relation_size(oid) DESC; ## list db's and size SELECT datname AS db_name, pg_size_pretty(pg_database_size(oid)) AS db_size FROM pg_database ORDER BY pg_database_size(oid) DESC; hope that helps..::GERD::.. Am 22.05.2008 um 03:08 schrieb Duan Ligong: Hi, Scott Thanks for your reply. There's also the old fashioned way: I mean I want to estimate the size of the space which the db will occupy. Now there is no db. sudo su - postgres cd $PGDATA # or wherever you point with -D on startup du -sh . Regards Duan -- Duan Ligong TEL : 0561-75-1925-6201 800-81-569-6201 E-Mail : [EMAIL PROTECTED] - Original Message - From: "Scott Marlowe" <[EMAIL PROTECTED]> To: "Duan Ligong" <[EMAIL PROTECTED]> Cc: Sent: Thursday, May 22, 2008 4:08 AM Subject: Re: [ADMIN] [Admin]To estimate the size of db On Wed, May 14, 2008 at 7:33 PM, Duan Ligong [EMAIL PROTECTED]> wrote: Hi, all Is there a way to estimate the size of the space which a postgresql database occupies? or the max size of db? I know there are a lot of factors, such as the frequency of updating, data size, which affects the size of db and it seems difficult to estimate it precisely. There's also the old fashioned way: sudo su - postgres cd $PGDATA # or wherever you point with -D on startup du -sh . -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Error while executing pg_dump "invalid memory alloc request size 4294967293"
Hello All, We are using postgresql version 8.1 and our database size is 7gb. Ram Size is 2 GB. while trying to take backup through pg_dump i am getting following error. oka97: pg_dump amtdb > amtdb.out pg_dump: ERROR: invalid memory alloc request size 4294967293 pg_dump: SQL command to dump the contents of table "atbs2_logs" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967293 pg_dump: The command was: COPY public.atbs2_logs (sno, request_time, msisdn, phone_make, error_code, error_desc, reply_message, request_query, responce_time, request_type) TO stdout; When I tried to take a single table backup i.e is mentioned in the error named* "public.atbs2_logs" then again getting following error. * pg_dump: SQL command failed pg_dump: Error message from server: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor So we are unable to take backup , we have tried the vacuum and reindex but not of any use. Kindly help us Any help would be highly appreciate. Thanx in Advance. Amit Jain +91-9818450022
[ADMIN] Index problem...
Greetings... I'm getting an error. The error says... places=# REINDEX INDEX maps_map_id_seq; ERROR: "maps_map_id_seq" is not an index places=# SELECT * FROM maps; ERROR: missing chunk number 0 for toast value 131343627 I have tried reindexing the index, the table, and the database. I continue to get this error. What can I do to recover from this. Carol Walter PS Let me say that I know this is my fault. This index is very bloated. Yesterday, I tried to vacuum. I got errors because max_fsm_pages what set at a number that was too small. -- 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] Index problem...
You may want to try to reindex the associated toast table and see if that clears it up. To get the name of the associated toast table, you may run this query: select relname from pg_class where oid = (select reltoastrelid from pg_class where relname = 'maps'); Then, reindex it. reindex table "toast table name" .. --- Regards, Husam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Carol Walter Sent: Thursday, May 22, 2008 9:42 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Index problem... Greetings... I'm getting an error. The error says... places=# REINDEX INDEX maps_map_id_seq; ERROR: "maps_map_id_seq" is not an index places=# SELECT * FROM maps; ERROR: missing chunk number 0 for toast value 131343627 I have tried reindexing the index, the table, and the database. I continue to get this error. What can I do to recover from this. Carol Walter PS Let me say that I know this is my fault. This index is very bloated. Yesterday, I tried to vacuum. I got errors because max_fsm_pages what set at a number that was too small. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag ** -- 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] Index problem...
When I ran the query you specified I got relname of "pg_toast_16429. When I ran the REINDEX TABLE pg_toast_16429 the system responds that relation "pg_toast_16429" does not exist. Carol On May 22, 2008, at 1:02 PM, Tomeh, Husam wrote: You may want to try to reindex the associated toast table and see if that clears it up. To get the name of the associated toast table, you may run this query: select relname from pg_class where oid = (select reltoastrelid from pg_class where relname = 'maps'); Then, reindex it. reindex table "toast table name" .. --- Regards, Husam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Carol Walter Sent: Thursday, May 22, 2008 9:42 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Index problem... Greetings... I'm getting an error. The error says... places=# REINDEX INDEX maps_map_id_seq; ERROR: "maps_map_id_seq" is not an index places=# SELECT * FROM maps; ERROR: missing chunk number 0 for toast value 131343627 I have tried reindexing the index, the table, and the database. I continue to get this error. What can I do to recover from this. Carol Walter PS Let me say that I know this is my fault. This index is very bloated. Yesterday, I tried to vacuum. I got errors because max_fsm_pages what set at a number that was too small. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag ** -- 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] Index problem...
On Thu, May 22, 2008 at 01:37:56PM -0400, Carol Walter wrote: > When I ran the query you specified I got relname of "pg_toast_16429. > When I ran the REINDEX TABLE pg_toast_16429 the system responds that > relation "pg_toast_16429" does not exist. I think it should be pg_toast.pg_toast_16429 so: REINDEX TABLE pg_toast.pg_toast_16429; check relnamespace in pg_class for this table, and check it against pg_namespace to be sure. depesz -- 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] Index problem...
This gives the same error. I can select columns from the table although select * gets the error. I tried making a copy of the "maps" table using CREATE TABLE new table name (all columns) AS SELECT all columns FROM maps; This also gave me the same error. Carol On May 22, 2008, at 1:57 PM, hubert depesz lubaczewski wrote: On Thu, May 22, 2008 at 01:37:56PM -0400, Carol Walter wrote: When I ran the query you specified I got relname of "pg_toast_16429. When I ran the REINDEX TABLE pg_toast_16429 the system responds that relation "pg_toast_16429" does not exist. I think it should be pg_toast.pg_toast_16429 so: REINDEX TABLE pg_toast.pg_toast_16429; check relnamespace in pg_class for this table, and check it against pg_namespace to be sure. depesz -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] Index problem...
On Thu, May 22, 2008 at 03:41:38PM -0400, Carol Walter wrote: > This gives the same error. I can select columns from the table > although select * gets the error. I tried making a copy of the > "maps" table using CREATE TABLE new table name (all columns) AS > SELECT all columns FROM maps; This also gave me the same error. well. i think that it's time to move the PGDATA someplace else, initdb, and load backup. depesz -- 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] ]To estimate the size of db
Hi, Gerd perhaps you can go the way via some system functions like: Thank you very much and it's very helpful. Regards Duan ## list tables and their size SELECTrelname AS table_name, pg_size_pretty(pg_relation_size(oid)) AS table_sz, pg_size_pretty(pg_total_relation_size(oid)) AS total_sz FROM pg_class WHERE relkind = 'r' ORDER BY pg_relation_size(oid) DESC; ## list db's and size SELECT datname AS db_name, pg_size_pretty(pg_database_size(oid)) AS db_size FROM pg_database ORDER BY pg_database_size(oid) DESC; hope that helps..::GERD::.. Am 22.05.2008 um 03:08 schrieb Duan Ligong: Hi, Scott Thanks for your reply. There's also the old fashioned way: I mean I want to estimate the size of the space which the db will occupy. Now there is no db. sudo su - postgres cd $PGDATA # or wherever you point with -D on startup du -sh . Regards Duan -- Duan Ligong TEL : 0561-75-1925-6201 800-81-569-6201 E-Mail : [EMAIL PROTECTED] - Original Message - From: "Scott Marlowe" <[EMAIL PROTECTED]> To: "Duan Ligong" <[EMAIL PROTECTED]> Cc: Sent: Thursday, May 22, 2008 4:08 AM Subject: Re: [ADMIN] [Admin]To estimate the size of db On Wed, May 14, 2008 at 7:33 PM, Duan Ligong [EMAIL PROTECTED]> wrote: Hi, all Is there a way to estimate the size of the space which a postgresql database occupies? or the max size of db? I know there are a lot of factors, such as the frequency of updating, data size, which affects the size of db and it seems difficult to estimate it precisely. There's also the old fashioned way: sudo su - postgres cd $PGDATA # or wherever you point with -D on startup du -sh . -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] Error while executing pg_dump "invalid memory alloc request size 4294967293"
Hi , Make sure no one is connected to the database while doing the back up. Usually memory alloc error comes if its a bad RAM . check the pg_dump version and your database version match (this could be a guess) try file level backup and restore into new database. On 22/05/2008, Amit jain <[EMAIL PROTECTED]> wrote: > > Hello All, > > We are using postgresql version 8.1 and our database size is 7gb. Ram Size > is 2 GB. > > while trying to take backup through pg_dump i am getting following error. > > oka97: pg_dump amtdb > amtdb.out > pg_dump: ERROR: invalid memory alloc request size 4294967293 > pg_dump: SQL command to dump the contents of table "atbs2_logs" failed: > PQendcopy() failed. > pg_dump: Error message from server: ERROR: invalid memory alloc request > size 4294967293 > pg_dump: The command was: COPY public.atbs2_logs (sno, request_time, > msisdn, phone_make, error_code, error_desc, reply_message, request_query, > responce_time, request_type) TO stdout; > > When I tried to take a single table backup i.e is mentioned in the error > named* "public.atbs2_logs" then again getting following error. > * > > pg_dump: SQL command failed > pg_dump: Error message from server: server closed the connection > unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor > > > So we are unable to take backup , we have tried the vacuum and reindex but > not of any use. > > Kindly help us Any help would be highly appreciate. Thanx in Advance. > > > Amit Jain > > +91-9818450022 > > > > > > > -- Regards, Vishal Kashyap. Need help visit http://help.vishal.net.in
Re: [ADMIN] Error while executing pg_dump "invalid memory alloc request size 4294967293"
Hi, I have tried to taken backup while no one is connected. Should i change my RAM and then check it. Version are same of pg_dump and database version 8.1. Should I copy the data folder ? and then restore it with new installation or what do you mean to say kindly brief it please. I will be thankful to any help. thanks a lot. On Fri, May 23, 2008 at 8:53 AM, Vishal Mailinglist <[EMAIL PROTECTED]> wrote: > Hi , > > Make sure no one is connected to the database while doing the back up. > Usually memory alloc error comes if its a bad RAM . > check the pg_dump version and your database version match (this could be a > guess) > try file level backup and restore into new database. > > > On 22/05/2008, Amit jain <[EMAIL PROTECTED]> wrote: >> >> Hello All, >> >> We are using postgresql version 8.1 and our database size is 7gb. Ram Size >> is 2 GB. >> >> while trying to take backup through pg_dump i am getting following error. >> >> oka97: pg_dump amtdb > amtdb.out >> pg_dump: ERROR: invalid memory alloc request size 4294967293 >> pg_dump: SQL command to dump the contents of table "atbs2_logs" failed: >> PQendcopy() failed. >> pg_dump: Error message from server: ERROR: invalid memory alloc request >> size 4294967293 >> pg_dump: The command was: COPY public.atbs2_logs (sno, request_time, >> msisdn, phone_make, error_code, error_desc, reply_message, request_query, >> responce_time, request_type) TO stdout; >> >> When I tried to take a single table backup i.e is mentioned in the error >> named* "public.atbs2_logs" then again getting following error. >> * >> >> pg_dump: SQL command failed >> pg_dump: Error message from server: server closed the connection >> unexpectedly >> This probably means the server terminated abnormally >> before or while processing the request. >> pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor >> >> >> So we are unable to take backup , we have tried the vacuum and reindex but >> not of any use. >> >> Kindly help us Any help would be highly appreciate. Thanx in Advance. >> >> >> Amit Jain >> >> +91-9818450022 >> >> >> >> >> >> >> > > > -- > Regards, > Vishal Kashyap. > Need help visit > http://help.vishal.net.in
