From: Stephen Frost <sfrost@snowman.net>
To: Campbell, Lance <lance@illinois.edu>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Partition tables in AWS RDS environment
Date: Mon, 10 Apr 2017 10:31:18 -0400
Message-ID: <20170410143118.GK9812@tamriel.snowman.net> (raw)
In-Reply-To: <B75CD08C73BD3543B97E4EF3964B7D7047B49034@CITESMBX1.ad.uillinois.edu>
References: <B75CD08C73BD3543B97E4EF3964B7D7047B47CF7@CITESMBX1.ad.uillinois.edu>
<20170410135722.GI9812@tamriel.snowman.net>
<B75CD08C73BD3543B97E4EF3964B7D7047B49034@CITESMBX1.ad.uillinois.edu>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Greetings Lance,
* Campbell, Lance (lance@illinois.edu) wrote:
> In 9.6 if you do a pg_dump on a schema and you have a few very large tables in the schema will this cause the database to lock the table for writing?
In PG, readers do not block writers.
> This is an issue I ran into with older version of PostgreSQL using pg_dump on entire schemas. To get around this issue I partitioned the really large tables so that the locking would be a much shorter time period while running pg_dump. But this may not be necessary with 9.6
I'm not sure what issue you ran into, but pg_dump would block someone
from DROP'ing or TRUNACTE'ing a table (or other operations requiring a
very strong lock), not from simply doing an INSERT, UPDATE, or DELETE
(which take a lower lock that doesn't conflict with the locks pg_dump
takes).
Information about the various locks in PG is available here:
https://www.postgresql.org/docs/current/static/explicit-locking.html
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
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: sfrost@snowman.net, lance@illinois.edu
Subject: Re: Partition tables in AWS RDS environment
In-Reply-To: <20170410143118.GK9812@tamriel.snowman.net>
* 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