Re: pg_dump and truncate

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Feng Chen" <fchen(at)covergence(dot)com> writes:
> I was trying to do a live db archive using the following steps:

> 1.	Open a connection to the database;
> 2.	With the connection, do the following:
> BEGIN;
> SET transaction ISOLATION level SERIALIZABLE;

> 3.	Execute the following from the Linux shell:
> pg_dump dbname | gzip -9 > dbname-archive.gz

> 4.	With the same connection, do the following:
> truncate table1, table2,...;
> end;

> Well, the tables are truncated all right, but the db dump file is
> empty!!!

You weren't very clear about the timing here, but I suppose you did the
TRUNCATE before pg_dump managed to acquire AccessShareLock on the tables
(because you certainly couldn't have done it after).  There is some
startup delay for it to acquire lock on every table...

			regards, tom lane