Skip site navigation (1) Skip section navigation (2)

Re: pg_dump and truncate

From: "Feng Chen" <fchen(at)covergence(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: pg_dump and truncate
Date: 2007-02-22 16:41:59
Message-ID: 0D1719326D64BD4E9F92A0C12023767801B2D7E4@eserv.covergence.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello Tom,

Thanks a lot for your reply. It was an error I made - I was doing
"pg_dump dbname | gzip -9 > dbname-archive.gz >& /dev/null". As soon as
I removed ">& /dev/null" the file was not empty any more.

However, having the pg_dump and truncate inside a transaction, and
setting the isolation level to SERIALIZABLE seems not working as I
intended - I have data inserted to the database during the process and
they were removed by the truncate operation:

Data-0 -> inserted  |    
Data-1 -> inserted  |    Begin;
Data-2 -> inserted  |    SET transaction ISOLATION level SERIALIZABLE;
Data-3 -> inserted  |    pg_dump dbname | gzip -9 > dbname-archive.gz
Data-4 -> inserted  |    truncate table1;
Data-5 -> inserted  |    truncate table2;
Data-6 -> inserted  |    truncate table3;
Data-7 -> inserted  |    end; 

The resulted dbname-archive.gz contains data up to Data-0. However, the
truncated database does not have Data-1, Data-2, and Data-3 in it!

Why is that? I thought truncate supports transaction now - we use
PostgreSQL 8.1.2.

Thank you!

Feng

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: Thursday, February 22, 2007 2:28 AM
To: Feng Chen
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] pg_dump and truncate 

"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

In response to

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2007-02-22 16:48:58
Subject: BUG #3056: strange behave of CHECK constraint
Previous:From: TuxDate: 2007-02-22 13:36:00
Subject: BUG #3054: getopt_long () misbehaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group