Обсуждение: [Admin]To estimate the size of db
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
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
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 .
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
>
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
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
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