PostgreSQL Discussion - Reduce size of $PGDATA for "demo cdrom"?

Author

Reduce size of $PGDATA for "demo cdrom"?

Philip Hallstrom

2005-04-27, 11:23 am

Hi all -
We've got a product built on FreeBSD and PostgreSQL 7.4.2 that
I've had to fit onto an installable CDROM tradeshows and customer demos.
This is the only way I've found to ensure an easy to re-install option
for the non-technical folks at the tradeshows should they corrupt the box
itself. This part all works fine.

However, I've only got 700mb to work with and the bootable CDROM takes a
chunk of that as does the rest of our app.

Doing a pg_dumpall of the database results in a 369Kb file.

However, $PGDATA is around 60mb.

root@tradeshow:/local/pgsql/data% du -hcs base/* pg_xlog/*
4.4M base/1
4.4M base/17141
4.4M base/17144
4.4M base/17145
6.6M base/17146
5.4M base/17147
16M pg_xlog/0000000000000006
16M pg_xlog/0000000000000007
62M total

My question is what's the best way to trim that down? I realize I could
remove it completely and have my install script do an initdb, etc, but if
there's anyway to keep the installation intact to begin with I'd prefer
that.

Is there any other way to trim the size of those files? Maybe dump
everything out, "clean up", then restore it. Then immediately burn things
to CD? Or if there are postgresql.conf options I can set to not keep WAL
logs, etc around that would work too since this is just for tradeshows...

Thanks all!

-philip

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Scott Marlowe

2005-04-27, 11:23 am

On Wed, 2005-04-27 at 10:10, Philip Hallstrom wrote:
> Hi all -
> We've got a product built on FreeBSD and PostgreSQL 7.4.2 that
> I've had to fit onto an installable CDROM tradeshows and customer demos.
> This is the only way I've found to ensure an easy to re-install option
> for the non-technical folks at the tradeshows should they corrupt the box
> itself. This part all works fine.
>
> However, I've only got 700mb to work with and the bootable CDROM takes a
> chunk of that as does the rest of our app.
>
> Doing a pg_dumpall of the database results in a 369Kb file.
>
> However, $PGDATA is around 60mb.
>
> root@tradeshow:/local/pgsql/data% du -hcs base/* pg_xlog/*
> 4.4M base/1
> 4.4M base/17141
> 4.4M base/17144
> 4.4M base/17145
> 6.6M base/17146
> 5.4M base/17147
> 16M pg_xlog/0000000000000006
> 16M pg_xlog/0000000000000007
> 62M total
>
>
> My question is what's the best way to trim that down? I realize I could
> remove it completely and have my install script do an initdb, etc, but if
> there's anyway to keep the installation intact to begin with I'd prefer
> that.

My first recommendation would be to put everything into one database.
it looks like you've got 6 databases. If you've still got the template0
database, you can probably get rid of that one as well. If you're not
going to need to create any new databases you can maybe drop template1
too.

psql somedbotherthanthete
mplateones
update pg_database set datistemplate=false;

drop database template0;
drop database template1;

Set the checkpoint_segments = 1 in the postgresql.conf file. Not sure
how much that will help, but it should keep the checkpoint files at 16
meg.

Lastly, put those other four or databases into separate schemas in one
database.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Bruno Wolff III

2005-04-27, 8:24 pm

On Wed, Apr 27, 2005 at 10:23:19 -0500,
Scott Marlowe < smarlowe@g2switchwor
ks.com> wrote:
>
> My first recommendation would be to put everything into one database.
> it looks like you've got 6 databases. If you've still got the template0
> database, you can probably get rid of that one as well. If you're not
> going to need to create any new databases you can maybe drop template1
> too.

Isn't template0 needed for dumps, since they are relative to template0?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Scott Marlowe

2005-04-28, 9:23 am

On Wed, 2005-04-27 at 20:44, Bruno Wolff III wrote:
> On Wed, Apr 27, 2005 at 10:23:19 -0500,
> Scott Marlowe < smarlowe@g2switchwor
ks.com> wrote:
>
> Isn't template0 needed for dumps, since they are relative to template0?

No, it's template1, and you only need it for pg_dumpall. pg_dump should
still work.

Note that without template1, things like psql -l will fail. But you can
still connect to the one database you know the name of.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sponsored Links