[GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Ross Boylan
I have a postgres server for which du reports 1188072 /var/lib/postgresql/8.2/main on Linux system. The server has only one real database, which is for bacula. When I dump the database, it's 73Mg. This is immediately after I did a full vacuum and restarted the server. Also, bacula=> SELECT reln

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Joris Dobbelsteen
Ross Boylan wrote: I have a postgres server for which du reports 1188072 /var/lib/postgresql/8.2/main on Linux system. The server has only one real database, which is for bacula. When I dump the database, it's 73Mg. This is immediately after I did a full vacuum and restarted the server. Also,

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Ross Boylan
On Sun, 2008-03-30 at 20:27 +0200, Joris Dobbelsteen wrote: > Ross Boylan wrote: > > I have a postgres server for which du reports > > 1188072 /var/lib/postgresql/8.2/main > > on Linux system. > > The server has only one real database, which is for bacula. When I dump > > the database, it's

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Joris Dobbelsteen
Ross Boylan wrote: On Sun, 2008-03-30 at 20:27 +0200, Joris Dobbelsteen wrote: Ross Boylan wrote: I have a postgres server for which du reports 1188072 /var/lib/postgresql/8.2/main on Linux system. The server has only one real database, which is for bacula. When I dump the database, i

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Tom Lane
Ross Boylan <[EMAIL PROTECTED]> writes: > reindexing had a huge effect. So the indexes were indeed bloated. There are some known usage patterns in which regular vacuum isn't very good at reclaiming space in b-tree indexes. For example if you make daily entries in an index by date and later remov

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Ross Boylan
On Sun, 2008-03-30 at 21:22 +0200, Joris Dobbelsteen wrote: > From the top contenders, about half are indexes, so you are stuck > with > ~200 MB of data in the tables. > Postgresql has some wasted space due to placement of the tuples in a > block and overhead for each block and row. I don't kno

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Joris Dobbelsteen
>-Original Message- >From: Ross Boylan [mailto:[EMAIL PROTECTED] >Sent: Sunday, 30 March 2008 23:43 >To: Joris Dobbelsteen >Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org >Subject: Re: [GENERAL] database 1.2G, pg_dump 73M?! > >On Sun, 2008-03-30 at 21:22

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Ross Boylan
On Sun, 2008-03-30 at 22:59 +0100, Joris Dobbelsteen wrote: > >-Original Message- > >From: Ross Boylan [mailto:[EMAIL PROTECTED] > >Sent: Sunday, 30 March 2008 23:43 > >To: Joris Dobbelsteen > >Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org > >

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Joris Dobbelsteen
>-Original Message- >From: Ross Boylan [mailto:[EMAIL PROTECTED] >Sent: Monday, 31 March 2008 0:23 >To: Joris Dobbelsteen >Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org >Subject: RE: [GENERAL] database 1.2G, pg_dump 73M?! > > >On Sun, 2008-03-30 at 22:

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-03-30 Thread Dragan Zubac
Ross Boylan wrote: I'm not a DB admin; I only play one on my computer. I clearly need to figure out how to get regular vacuum, analyze, and reindexing going (if they aren't going already). Thanks for all your help. Ross 1. optimize your sql queries and 'understand' index usage,don't inde

Re: [GENERAL] database 1.2G, pg_dump 73M?!

2008-04-05 Thread Anton Melser
> I am still trying to figure out if the database was getting any > automatic vacuuming at all. The Postgres documentation (the database is > 8.2, though I'm moving to 8.3 soon) sounds as if it's on automatically, > but the Debian-specific documentation suggests I may need to do some > additi