From: Julien Rouhaud <julien.rouhaud@dalibo.com>
To: Campbell, Lance <lance@illinois.edu>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: pg_dump a schema with data
Date: Fri, 10 Jul 2015 22:24:16 +0200
Message-ID: <55A029F0.70007@dalibo.com> (raw)
In-Reply-To: <B75CD08C73BD3543B97E4EF3964B7D701FF39921@CITESMBX1.ad.uillinois.edu>
References: <B75CD08C73BD3543B97E4EF3964B7D701FF39921@CITESMBX1.ad.uillinois.edu>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: julien.rouhaud@dalibo.com, lance@illinois.edu
Subject: Re: pg_dump a schema with data
In-Reply-To: <55A029F0.70007@dalibo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox