Обсуждение: [Admin]To estimate the size of db

Поиск
Список
Период
Сортировка

[Admin]To estimate the size of db

От
"Duan Ligong"
Дата:
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

Re: [Admin]To estimate the size of db

От
"Jaime Casanova"
Дата:
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

Re: [Admin]To estimate the size of db

От
"Scott Marlowe"
Дата:
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 .

Re: ]To estimate the size of db

От
"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
>


Re: ]To estimate the size of db

От
Shane Ambler
Дата:
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

Re: ]To estimate the size of db

От
Gerd Koenig
Дата:
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


Re: ]To estimate the size of db

От
"Duan Ligong"
Дата:
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