Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cxaO0-000455-Uf for pgsql-admin@arkaria.postgresql.org; Mon, 10 Apr 2017 14:33:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cxaO0-0000O9-Hj for pgsql-admin@arkaria.postgresql.org; Mon, 10 Apr 2017 14:33:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cxaMB-0002qz-BW for pgsql-admin@postgresql.org; Mon, 10 Apr 2017 14:31:51 +0000 Received: from tamriel.snowman.net ([72.66.115.51]) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cxaM8-0001Og-Vo for pgsql-admin@postgresql.org; Mon, 10 Apr 2017 14:31:50 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 936075F794; Mon, 10 Apr 2017 10:31:18 -0400 (EDT) Date: Mon, 10 Apr 2017 10:31:18 -0400 From: Stephen Frost To: "Campbell, Lance" Cc: "pgsql-admin@postgresql.org" Subject: Re: Partition tables in AWS RDS environment Message-ID: <20170410143118.GK9812@tamriel.snowman.net> References: <20170410135722.GI9812@tamriel.snowman.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="4CPORQ9CUzJLGWrC" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/3.13.0-91-generic (x86_64) X-Uptime: 10:27:00 up 275 days, 15:51, 32 users, load average: 0.31, 0.15, 0.15 User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --4CPORQ9CUzJLGWrC Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable 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 tabl= es 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_dum= p on entire schemas. To get around this issue I partitioned the really lar= ge tables so that the locking would be a much shorter time period while run= ning 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 =66rom 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 --4CPORQ9CUzJLGWrC Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJY65c2AAoJEO1sijiDR2RVg4UQALPwV22U8c8Ef+vSEVuUCMtn LFALZVu7RPsKaC3kUNzCrAGd3lef14rB9bjKmxXdhnxez0PvMnIwMgS6FzzEkbH7 62XKFc04o5yghi3R+JO7WXvkmSsaniCk7Yhni28lXgsyWnx0epfARyjbeSBqmme7 uDdp0m4Xy9FsD9VSZJJgQ/iGQm46wxmtlUIt/EywSfcXSTyoIXjoCS5mMfS54sxw qZ/KRnaOLEAHw7oNhM8ok0SUbEnlnV6tVkrP1tD5i8yCvjN1P/AOR0fvB0B6AUgQ jCNO8VON7TdtpbD0H34eo6tweQerdvmGqqKzk5MBZ/JrkM/qgv8dwi5qZpAJuXCE A63abnbroh/9XuNUIqWcRTMZVnno4q5cXIJ7sdVOL01d4ojV1G01q9y3MkQ0UXZV gPaTEOoIPqC4o37jQDz7D/9z2hSTedXLTH2c1vun+3DnOgrZi6SU9i7vecswTcPz KhkbsnGy7P1ILpBxzfD0woL4zRWBDN0FGm7UUltOdA+NOue59GaCJ6HhYDk/0Gbn ckGO+rdNlwe1DvmQH9KngvcX/z+PPsQVSmDQ8RoVTdh6fCNoU89enZjFEWcELQME Z/GftFvAxMOyO9nTBx54+qhhYLIpQcOBgf+o+iUbbZRbUqju0qsPqQLKeQXucK4v ATjEVda/W5f+lWlSOiqS =k1xv -----END PGP SIGNATURE----- --4CPORQ9CUzJLGWrC--