Re: Query About vacuum and pg_dump

On Thu, 8 May 2003, PRAGATI SAVAIKAR wrote:

> We have got  "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
> 2.96 " running on Linux 7.3.
> There are 2 databases , each  of size 16 GB.
>
> I have 2 queries regarding vacuum and pg_dump.
>
> VACUUM :
>
> We run "vacuum " for each of the table in the database on daily basis.
> But it doesn't seem to reduce the disk space.
> We also tried using "vacuum analyze". But no change was observed.

Vacuum (without full) will generally only mark the free space as usable by
later updates or inserts.  Vacuum full will attempt to move rows around in
order to shrink the actual table, however it takes an exclusive lock while
doing so.

> pg_dump:
> we use pg_dump command to take the backup of the databases .
> I would like to know, how will it affect if I run the pg_dump while the
> tables are being used or when the transactions are being processed.

IIRC, pg_dump runs all its queries in a serializable transaction, so
inserts, updates and deletes to a table shouldn't affect the dumped data
for that table.