pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed[Admin]To estimate the size of db
7+ messages / 5 participants
[nested] [flat]
* [Admin]To estimate the size of db
@ 2008-05-15 01:33 Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-15 04:27 ` Re: [Admin]To estimate the size of db Jaime Casanova <systemguards@gmail.com>
2008-05-21 19:08 ` Re: [Admin]To estimate the size of db Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Duan Ligong @ 2008-05-15 01:33 UTC (permalink / raw)
To: pgsql-admin
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.
Regards
Duan
--
TEL : 0561-75-1925-6201
800-81-569-6201
E-Mail : l-duan@zd.cnes.jp.nec.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Admin]To estimate the size of db
2008-05-15 01:33 [Admin]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
@ 2008-05-15 04:27 ` Jaime Casanova <systemguards@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jaime Casanova @ 2008-05-15 04:27 UTC (permalink / raw)
To: Duan Ligong <l-duan@zd.cnes.jp.nec.com>; +Cc: pgsql-admin
On Wed, May 14, 2008 at 8:33 PM, Duan Ligong <l-duan@zd.cnes.jp.nec.com> 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?
>
if pg >= 8.1 then
http://www.postgresql.org/docs/8.1/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
else
there is a contrib/dbsize
end if
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Admin]To estimate the size of db
2008-05-15 01:33 [Admin]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
@ 2008-05-21 19:08 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-05-22 01:08 ` Re: ]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
1 sibling, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2008-05-21 19:08 UTC (permalink / raw)
To: Duan Ligong <l-duan@zd.cnes.jp.nec.com>; +Cc: pgsql-admin
On Wed, May 14, 2008 at 7:33 PM, Duan Ligong <l-duan@zd.cnes.jp.nec.com> 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 .
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ]To estimate the size of db
2008-05-15 01:33 [Admin]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-21 19:08 ` Re: [Admin]To estimate the size of db Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-05-22 01:08 ` Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-22 06:34 ` Re: ]To estimate the size of db Shane Ambler <pgsql@Sheeky.Biz>
2008-05-22 08:34 ` Re: ]To estimate the size of db Gerd Koenig <koenig@transporeon.com>
0 siblings, 2 replies; 7+ messages in thread
From: Duan Ligong @ 2008-05-22 01:08 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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 : l-duan@zd.cnes.jp.nec.com
----- Original Message -----
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Duan Ligong" <l-duan@zd.cnes.jp.nec.com>
Cc: <pgsql-admin@postgresql.org>
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 <l-duan@zd.cnes.jp.nec.com>
> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ]To estimate the size of db
2008-05-15 01:33 [Admin]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-21 19:08 ` Re: [Admin]To estimate the size of db Scott Marlowe <scott.marlowe@gmail.com>
2008-05-22 01:08 ` Re: ]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
@ 2008-05-22 06:34 ` Shane Ambler <pgsql@Sheeky.Biz>
1 sibling, 0 replies; 7+ messages in thread
From: Shane Ambler @ 2008-05-22 06:34 UTC (permalink / raw)
To: Duan Ligong <l-duan@zd.cnes.jp.nec.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
Duan Ligong wrote:
> I mean I want to estimate the size of the space which the db will occupy.
> Now there is no db.
With a bit of math and the info here -
http://www.postgresql.org/docs/8.3/interactive/storage-page-layout.html
you can start to get an idea.
Simple calculation would be 23 bytes overhead per row + length of data
in columns with one or two extra overhead on some types (like length of
string or array) also don't forget that text may be more than one byte
per char.
A similar estimate for indexes can come into play as well.
The final size will likely be a bit larger as you also get issues like
data alignment and unused space on a page that doesn't fit a row...
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ]To estimate the size of db
2008-05-15 01:33 [Admin]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-21 19:08 ` Re: [Admin]To estimate the size of db Scott Marlowe <scott.marlowe@gmail.com>
2008-05-22 01:08 ` Re: ]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
@ 2008-05-22 08:34 ` Gerd Koenig <koenig@transporeon.com>
2008-05-23 00:47 ` Re: ]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
1 sibling, 1 reply; 7+ messages in thread
From: Gerd Koenig @ 2008-05-22 08:34 UTC (permalink / raw)
To: Duan Ligong <l-duan@zd.cnes.jp.nec.com>; +Cc: pgsql-admin
Hi Duan,
perhaps you can go the way via some system functions like:
## list tables and their size
SELECT relname 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 : l-duan@zd.cnes.jp.nec.com
> ----- Original Message ----- From: "Scott Marlowe"
> <scott.marlowe@gmail.com>
> To: "Duan Ligong" <l-duan@zd.cnes.jp.nec.com>
> Cc: <pgsql-admin@postgresql.org>
> 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 <l-
>> duan@zd.cnes.jp.nec.com> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ]To estimate the size of db
2008-05-15 01:33 [Admin]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-21 19:08 ` Re: [Admin]To estimate the size of db Scott Marlowe <scott.marlowe@gmail.com>
2008-05-22 01:08 ` Re: ]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-22 08:34 ` Re: ]To estimate the size of db Gerd Koenig <koenig@transporeon.com>
@ 2008-05-23 00:47 ` Duan Ligong <l-duan@zd.cnes.jp.nec.com>
0 siblings, 0 replies; 7+ messages in thread
From: Duan Ligong @ 2008-05-23 00:47 UTC (permalink / raw)
To: pgsql-admin; +Cc: Gerd Koenig <koenig@transporeon.com>
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
> SELECT relname 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 : l-duan@zd.cnes.jp.nec.com
>> ----- Original Message ----- From: "Scott Marlowe"
>> <scott.marlowe@gmail.com>
>> To: "Duan Ligong" <l-duan@zd.cnes.jp.nec.com>
>> Cc: <pgsql-admin@postgresql.org>
>> 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 <l-
>>> duan@zd.cnes.jp.nec.com> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-05-23 00:47 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-15 01:33 [Admin]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-15 04:27 ` Jaime Casanova <systemguards@gmail.com>
2008-05-21 19:08 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-05-22 01:08 ` Re: ]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
2008-05-22 06:34 ` Re: ]To estimate the size of db Shane Ambler <pgsql@Sheeky.Biz>
2008-05-22 08:34 ` Re: ]To estimate the size of db Gerd Koenig <koenig@transporeon.com>
2008-05-23 00:47 ` Re: ]To estimate the size of db Duan Ligong <l-duan@zd.cnes.jp.nec.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox