pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Checking a  size of a given database
6+ messages / 6 participants
[nested] [flat]

* Checking a  size of a given database
@ 2007-04-17 10:45 Chuck Amadi <chuck.amadi@rokcorp.com>
  2007-04-17 11:39 ` Re: Checking a  size of a given database Milen A. Radev <milen@radev.net>
  2007-04-18 04:18 ` Re: Checking a size of a given database adey <adey11@gmail.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Chuck Amadi @ 2007-04-17 10:45 UTC (permalink / raw)
  To: pgsql-admin

I have been given a task to try a proof of concept of migrating are active databases from PostgreSQL 8.0.1 to 8.2.3

I will install and configPostgreSQL on my local w/s and replicate the database users etc etc.

I have had a look inside /var/lib/pgsql/data/base and there are numerous  files (db's) which I believe is where I can check data base size.

1  108012  114465  122407  1261267  1261963  17229  17233  17234  17235  174066  19247  205009  21272  2793694  2794334  333245  333375

I am aware that Postgresql creates directories to keep each database in. 
These directories aren't names,they are kept as the *'OID's *of each 
database (oid's are*"object identifiers"*).

Previously I just run pg_dump and thus run ls -lh on the db dump. Anyway 
of checking a  size of a given database I have tried
the sql using select pg_database_size ('dbaname'); but no joy.

Cheers
Chuck

-- 
Chuck Amadi
ROK Corporation Limited
Ty ROK,
Dyffryn Business Park,
Llantwit Major Road,
Llandow,
Vale Of Glamorgan.
CF71 7PY
 
Tel: 01446 795 839
Fax: 01446 794 994
International Tel:   +44 1446 795 839

email: chuck.amadi@rokcorp.com

This email is confidential to the addressee only. If you do not believe
that you are the intended recipient, do not pass it on or copy it in any
way. Please delete it immediately.





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Checking a  size of a given database
  2007-04-17 10:45 Checking a  size of a given database Chuck Amadi <chuck.amadi@rokcorp.com>
@ 2007-04-17 11:39 ` Milen A. Radev <milen@radev.net>
  2007-04-18 02:01   ` Re: Checking a  size of a given database Robert Treat <robert@omniti.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Milen A. Radev @ 2007-04-17 11:39 UTC (permalink / raw)
  To: pgsql-admin

Chuck Amadi написа:
> I have been given a task to try a proof of concept of migrating are
> active databases from PostgreSQL 8.0.1 to 8.2.3
> 
> I will install and configPostgreSQL on my local w/s and replicate the
> database users etc etc.
> 
> I have had a look inside /var/lib/pgsql/data/base and there are
> numerous  files (db's) which I believe is where I can check data base size.
> 
> 1  108012  114465  122407  1261267  1261963  17229  17233  17234  17235 
> 174066  19247  205009  21272  2793694  2794334  333245  333375
> 
> I am aware that Postgresql creates directories to keep each database in.
> These directories aren't names,they are kept as the *'OID's *of each
> database (oid's are*"object identifiers"*).
> 
> Previously I just run pg_dump and thus run ls -lh on the db dump. Anyway
> of checking a  size of a given database I have tried
> the sql using select pg_database_size ('dbaname'); but no joy.


What does it mean "but no joy"? What was the error message?



-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Checking a  size of a given database
  2007-04-17 10:45 Checking a  size of a given database Chuck Amadi <chuck.amadi@rokcorp.com>
  2007-04-17 11:39 ` Re: Checking a  size of a given database Milen A. Radev <milen@radev.net>
@ 2007-04-18 02:01   ` Robert Treat <robert@omniti.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Robert Treat @ 2007-04-18 02:01 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Milen A. Radev <milen@radev.net>

On Tuesday 17 April 2007 07:39, Milen A. Radev wrote:
> Chuck Amadi написа:
> > I have been given a task to try a proof of concept of migrating are
> > active databases from PostgreSQL 8.0.1 to 8.2.3
> >
> > I will install and configPostgreSQL on my local w/s and replicate the
> > database users etc etc.
> >
> > I have had a look inside /var/lib/pgsql/data/base and there are
> > numerous  files (db's) which I believe is where I can check data base
> > size.
> >
> > 1  108012  114465  122407  1261267  1261963  17229  17233  17234  17235
> > 174066  19247  205009  21272  2793694  2794334  333245  333375
> >
> > I am aware that Postgresql creates directories to keep each database in.
> > These directories aren't names,they are kept as the *'OID's *of each
> > database (oid's are*"object identifiers"*).
> >
> > Previously I just run pg_dump and thus run ls -lh on the db dump. Anyway
> > of checking a  size of a given database I have tried
> > the sql using select pg_database_size ('dbaname'); but no joy.
>
> What does it mean "but no joy"? What was the error message?

Well, given that the function doesn't exist in 8.0.1, I'd guess it means that 
it didn't work for him.  In 8.0.x you need to use the dbsize contrib module 
to get database size, or match things up manually on disk with select 
oid,datname from pg_database. 

-- 
Robert Treat
Database Architect
http://www.omniti.com



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Checking a size of a given database
  2007-04-17 10:45 Checking a  size of a given database Chuck Amadi <chuck.amadi@rokcorp.com>
@ 2007-04-18 04:18 ` adey <adey11@gmail.com>
  2007-04-18 04:23   ` Re: Checking a size of a given database Joshua D. Drake <jd@commandprompt.com>
  1 sibling, 1 reply; 6+ messages in thread

From: adey @ 2007-04-18 04:18 UTC (permalink / raw)
  To: chuck.amadi@rokcorp.com; +Cc: pgsql-admin

I found the following script useful; for calculating database size:-

-- Query returns size of a database calculated from the no. 8k pages
allocated to tables
SELECT
 sum( relpages*8/1024 ) as MB
FROM
 pg_class
WHERE
-- relname != ''
-- relname = 'tablename'
 relnamespace = '2200'


On 4/17/07, Chuck Amadi <chuck.amadi@rokcorp.com> wrote:
>
> I have been given a task to try a proof of concept of migrating are active
> databases from PostgreSQL 8.0.1 to 8.2.3
>
> I will install and configPostgreSQL on my local w/s and replicate the
> database users etc etc.
>
> I have had a look inside /var/lib/pgsql/data/base and there are
> numerous  files (db's) which I believe is where I can check data base size.
>
>
> 1  108012  114465  122407  1261267  1261963  17229  17233  17234  17235  174066  19247  205009  21272  2793694  2794334  333245  333375
>
> I am aware that Postgresql creates directories to keep each database in.
> These directories aren't names,they are kept as the *'OID's *of each
> database (oid's are*"object identifiers"*).
>
> Previously I just run pg_dump and thus run ls -lh on the db dump. Anyway
> of checking a  size of a given database I have tried
> the sql using select pg_database_size ('dbaname'); but no joy.
>
> Cheers
> Chuck
>
> --
> Chuck Amadi
> ROK Corporation Limited
> Ty ROK,
> Dyffryn Business Park,
> Llantwit Major Road,
> Llandow,
> Vale Of Glamorgan.
> CF71 7PY
>
> Tel: 01446 795 839
> Fax: 01446 794 994
> International Tel:   +44 1446 795 839
>
> email: chuck.amadi@rokcorp.com
>
> This email is confidential to the addressee only. If you do not believe
> that you are the intended recipient, do not pass it on or copy it in any
> way. Please delete it immediately.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Checking a size of a given database
  2007-04-17 10:45 Checking a  size of a given database Chuck Amadi <chuck.amadi@rokcorp.com>
  2007-04-18 04:18 ` Re: Checking a size of a given database adey <adey11@gmail.com>
@ 2007-04-18 04:23   ` Joshua D. Drake <jd@commandprompt.com>
  2007-04-18 06:03     ` Re: Checking a size of a given database Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Joshua D. Drake @ 2007-04-18 04:23 UTC (permalink / raw)
  To: adey <adey11@gmail.com>; +Cc: pgsql-admin

adey wrote:
> I found the following script useful; for calculating database size:-

select pg_database_size?

http://www.postgresql.org/docs/8.2/static/functions-admin.html

>  
> -- Query returns size of a database calculated from the no. 8k pages 
> allocated to tables
> SELECT 
>  sum( relpages*8/1024 ) as MB
> FROM
>  pg_class
> WHERE
> -- relname != ''
> -- relname = 'tablename'
>  relnamespace = '2200'
> 
>  
> On 4/17/07, *Chuck Amadi* <chuck.amadi@rokcorp.com 
> <mailto:chuck.amadi@rokcorp.com>> wrote:
> 
>     I have been given a task to try a proof of concept of migrating are
>     active databases from PostgreSQL 8.0.1 to 8.2.3
> 
>     I will install and configPostgreSQL on my local w/s and replicate
>     the database users etc etc.
> 
>     I have had a look inside /var/lib/pgsql/data/base and there are
>     numerous  files (db's) which I believe is where I can check data
>     base size.
> 
>     1  108012  114465  122407  1261267  1261963  17229  17233  17234  17235  174066  19247  205009  21272  2793694  2794334  333245  333375
> 
>     I am aware that Postgresql creates directories to keep each database
>     in.
>     These directories aren't names,they are kept as the *'OID's *of each
>     database (oid's are*"object identifiers"*).
> 
>     Previously I just run pg_dump and thus run ls -lh on the db dump.
>     Anyway
>     of checking a  size of a given database I have tried
>     the sql using select pg_database_size ('dbaname'); but no joy.
> 
>     Cheers
>     Chuck
> 
>     --
>     Chuck Amadi
>     ROK Corporation Limited
>     Ty ROK,
>     Dyffryn Business Park,
>     Llantwit Major Road,
>     Llandow,
>     Vale Of Glamorgan.
>     CF71 7PY
> 
>     Tel: 01446 795 839
>     Fax: 01446 794 994
>     International Tel:   +44 1446 795 839
> 
>     email: chuck.amadi@rokcorp.com <mailto:chuck.amadi@rokcorp.com>
> 
>     This email is confidential to the addressee only. If you do not believe
>     that you are the intended recipient, do not pass it on or copy it in any
>     way. Please delete it immediately.
> 
> 
> 
>     ---------------------------(end of
>     broadcast)---------------------------
>     TIP 9: In versions below 8.0, the planner will ignore your desire to
>           choose an index scan if your joining column's datatypes do not
>           match
> 
> 


-- 

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Checking a size of a given database
  2007-04-17 10:45 Checking a  size of a given database Chuck Amadi <chuck.amadi@rokcorp.com>
  2007-04-18 04:18 ` Re: Checking a size of a given database adey <adey11@gmail.com>
  2007-04-18 04:23   ` Re: Checking a size of a given database Joshua D. Drake <jd@commandprompt.com>
@ 2007-04-18 06:03     ` Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jeff Frost @ 2007-04-18 06:03 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; +Cc: adey <adey11@gmail.com>; pgsql-admin

On Tue, 17 Apr 2007, Joshua D. Drake wrote:

> adey wrote:
>> I found the following script useful; for calculating database size:-
>
> select pg_database_size?
>
> http://www.postgresql.org/docs/8.2/static/functions-admin.html

That works great for 8.1+ but I believe the OP wants to do it with 8.0.1. 
If so, he'll have to install the dbsize contrib module to do it for the 8.0.1 
DB.

>>     I have been given a task to try a proof of concept of migrating are
>>     active databases from PostgreSQL 8.0.1 to 8.2.3
>>
>>     I will install and configPostgreSQL on my local w/s and replicate
>>     the database users etc etc.
>>
>>     I have had a look inside /var/lib/pgsql/data/base and there are
>>     numerous  files (db's) which I believe is where I can check data
>>     base size.
>>
>>     1  108012  114465  122407  1261267  1261963  17229  17233  17234  17235 
>> 174066  19247  205009  21272  2793694  2794334  333245  333375
>>
>>     I am aware that Postgresql creates directories to keep each database
>>     in.
>>     These directories aren't names,they are kept as the *'OID's *of each
>>     database (oid's are*"object identifiers"*).
>>
>>     Previously I just run pg_dump and thus run ls -lh on the db dump.
>>     Anyway
>>     of checking a  size of a given database I have tried
>>     the sql using select pg_database_size ('dbaname'); but no joy.
>>

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2007-04-18 06:03 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-17 10:45 Checking a  size of a given database Chuck Amadi <chuck.amadi@rokcorp.com>
2007-04-17 11:39 ` Milen A. Radev <milen@radev.net>
2007-04-18 02:01   ` Robert Treat <robert@omniti.com>
2007-04-18 04:18 ` Re: Checking a size of a given database adey <adey11@gmail.com>
2007-04-18 04:23   ` Re: Checking a size of a given database Joshua D. Drake <jd@commandprompt.com>
2007-04-18 06:03     ` Re: Checking a size of a given database Jeff Frost <jeff@frostconsultingllc.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