pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pg_dump a schema with data 2+ messages / 2 participants [nested] [flat]
* pg_dump a schema with data @ 2015-07-10 20:03 Campbell, Lance <lance@illinois.edu> 2015-07-10 20:24 ` Re: pg_dump a schema with data Julien Rouhaud <julien.rouhaud@dalibo.com> 0 siblings, 1 reply; 2+ messages in thread From: Campbell, Lance @ 2015-07-10 20:03 UTC (permalink / raw) To: pgsql-admin 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. Thanks, Lance Campbell University of Illinois ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_dump a schema with data 2015-07-10 20:03 pg_dump a schema with data Campbell, Lance <lance@illinois.edu> @ 2015-07-10 20:24 ` Julien Rouhaud <julien.rouhaud@dalibo.com> 0 siblings, 0 replies; 2+ messages in thread From: Julien Rouhaud @ 2015-07-10 20:24 UTC (permalink / raw) To: Campbell, Lance <lance@illinois.edu>; pgsql-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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-07-10 20:24 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2015-07-10 20:03 pg_dump a schema with data Campbell, Lance <lance@illinois.edu> 2015-07-10 20:24 ` Julien Rouhaud <julien.rouhaud@dalibo.com>
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