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

Re: pg_dump and truncate

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Feng Chen" <fchen(at)covergence(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_dump and truncate
Date: 2007-02-22 07:28:21
Message-ID: 16037.1172129301@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

Responses

pgsql-bugs by date

Next:From: JuanDate: 2007-02-22 08:18:47
Subject: BUG #3051: FATAL: too many trigger records found for relation "pg_authid"
Previous:From: Aaron ZedonisDate: 2007-02-21 20:57:14
Subject: BUG #3049: psql does not honor md5 in pg_hba.conf file

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