Message-ID: <55A029F0.70007@dalibo.com>
Date: Fri, 10 Jul 2015 22:24:16 +0200
From: Julien Rouhaud <julien.rouhaud@dalibo.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: "Campbell, Lance" <lance@illinois.edu>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: pg_dump a schema with data
References: 
 <B75CD08C73BD3543B97E4EF3964B7D701FF39921@CITESMBX1.ad.uillinois.edu>
In-Reply-To: 
 <B75CD08C73BD3543B97E4EF3964B7D701FF39921@CITESMBX1.ad.uillinois.edu>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On 10/07/2015 22:03, Campbell, Lance wrote:
> PostgreSQL 9.4
> 
> Assume you do the following command which dumps all of the data
> associated with a schema:
> 
>  
> 
> pg_dump –schema=schemaName  dbName > schemaName.out
> 
>  
> 
> Assume there are some 50 tables in the schema.  Some of these tables
> have foreign keys to each other.  During the dump of the schema, I would
> assume each table would be frozen/locked while it is being dumped.  But
> what about tables that have associates to a table that is being written
> out.  Will they be locked/frozen also.  Or does postgresql lock up all
> of the tables within the schema while it is being dumped.  Or are you
> just taking a risk that there might be miss associations between tables
> once the dump is complete.
> 
>  
> 
> My guess is that individual tables are locked/frozen while writing.  I
> would also assume there might be relationship issues between related
> tables if there was a lot of writing or deleting during the dump.
> 
>  

The tables aren't heavily locked or freezed during a pg_dump.  The only
locks on the tables are put to prevent them from being deleted or their
definition changed, but concurrent DML isn't a problem.

A pg_dump is consistent because it uses the "repeatable read"
transaction isolation.  You can look at the documentation for more
information:
http://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-REPEATABLE-READ

Regards.

> 
> Thanks,
> 
>  
> 
> Lance Campbell
> 
> University of Illinois
> 


-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin