pg_dump and truncate

["Feng Chen" <fchen(at)covergence(dot)com>]

Hello there,

 

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!!!

 

What went wrong with my logic? What should be the correct procedure?

Thank you!

Feng