Обсуждение: pg_dump a schema with data
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
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