From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Barron <david.barron@zencos.com>
cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Backup strategy
In-reply-to: 
 <PH0PR22MB335225F34C289A7A1D8E6B38E9582@PH0PR22MB3352.namprd22.prod.outlook.com>
References: 
 <PH0PR22MB335225F34C289A7A1D8E6B38E9582@PH0PR22MB3352.namprd22.prod.outlook.com>
Comments: In-reply-to David Barron <david.barron@zencos.com>
	message dated "Wed, 28 Feb 2024 16:23:23 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2317286.1709138280.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 28 Feb 2024 11:38:00 -0500
Message-ID: <2317287.1709138280@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2317287.1709138280%40sss.pgh.pa.us>
Precedence: bulk

David Barron <david.barron@zencos.com> writes:
> I'm responsible for a couple of databases that have constraints and inde=
xes on most, if not all, of the tables, which means that the tables have t=
o be backed up and restored in the correct order to take the constraints i=
nto account.  But pg_dump and pg_restore don't take that into account, so =
when doing restores I was running into errors.  Hopefully that is clear.

Your statement is clear, but you have not provided any explanation of
*why* pg_dump fails to cope with your database.  It generally does
manage to handle foreign key constraints without help.

> The solution I found was to use the -section option with pg_dump, like t=
his:

Splitting up the dump is frequently counterproductive, so I have a
feeling this isn't the best way to proceed.  In any case, --section
still emits all the same items in the same order, so it's not really
likely to fix problems.  Probably what masked the issue is your
use of --disable-triggers.

			regards, tom lane