Re: pg_dump a schema with data

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