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
Date: Wed, 28 Feb 2024 11:38:00 -0500
Message-ID: <2317287.1709138280@sss.pgh.pa.us> (raw)
In-Reply-To: <PH0PR22MB335225F34C289A7A1D8E6B38E9582@PH0PR22MB3352.namprd22.prod.outlook.com>
References: <PH0PR22MB335225F34C289A7A1D8E6B38E9582@PH0PR22MB3352.namprd22.prod.outlook.com>
David Barron <david.barron@zencos.com> writes:
> I'm responsible for a couple of databases that have constraints and indexes on most, if not all, of the tables, which means that the tables have to be backed up and restored in the correct order to take the constraints into 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 this:
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
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: tgl@sss.pgh.pa.us, david.barron@zencos.com, pgsql-admin@lists.postgresql.org
Subject: Re: Backup strategy
In-Reply-To: <2317287.1709138280@sss.pgh.pa.us>
* 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