pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup strategy
8+ messages / 5 participants
[nested] [flat]
* Backup strategy
@ 2024-02-28 16:23 David Barron <david.barron@zencos.com>
0 siblings, 2 replies; 8+ messages in thread
From: David Barron @ 2024-02-28 16:23 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Good day all
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.
The solution I found was to use the -section option with pg_dump, like this:
pg_dump -h <server> -U <username> --if-exists -f /<backup path>/predata.sql -F p --section=pre-data -s -c <database>
pg_dump -h <server> -U <username> -f /<backup path>/data -F d -j 8 --section=data <database>
pg_dump -h <server> -U <username> -f /<backup path>/postdata.sql -F p --section=post-data -s <database>
This leaves me with two .sql files and a data directory containing compressed files of the data. The first sql file drops all the tables and schemas, then recreates them without most constraints or indexes. The second sql file creates all the constraints and indexes. The restore process that I use is like this:
psql -h <server> -U <username> -d <database> -f /<backup path>/predata.sql
pg_restore -h <server> -U <username> -d <database> -F d -j 8 --disable-triggers /<backup path>/data
psql -h <server> -U <username> -d <database> -f /<backup path>/postdata.sql
That works well in most cases, but not all. For some tables the predate.sql file still contains constraints inside the CREATE TABLE statements. So to get things to work I have to manually move the constraints out of the predate.sql and into the postdata.sql. Tedious.
First question, why are some constraints, but not all, still written to predate.sql? I assume it's because the table was created that way originally?
Second, is there a way to make certain constraints are not written to predate.sql? I don't see an option for pg_dump that will do that but it's worth the ask.
Third, is there a better technique for backing up and restoring databases with constraints?
Thanks
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup strategy
@ 2024-02-28 16:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Barron <david.barron@zencos.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2024-02-28 16:38 UTC (permalink / raw)
To: David Barron <david.barron@zencos.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Backup strategy
@ 2024-02-28 16:45 David Barron <david.barron@zencos.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 8+ messages in thread
From: David Barron @ 2024-02-28 16:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
If, for example, table a has a constraint that references rows in table b, table b has to be restored first, but pg_dump doesn't take that into account. So the restore tries to restore table a first, but can't because table b contains no data. That's what I ran into in general terms.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup strategy
@ 2024-02-28 16:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Barron <david.barron@zencos.com>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2024-02-28 16:48 UTC (permalink / raw)
To: David Barron <david.barron@zencos.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
David Barron <david.barron@zencos.com> writes:
> If, for example, table a has a constraint that references rows in table b, table b has to be restored first, but pg_dump doesn't take that into account.
Yes it does: it doesn't restore foreign key constraints until after
all the data is loaded. What I suspect is that by "constraint" you
mean some handmade hack like a trigger that looks at other tables.
But without details about what you're doing, it's hard to recommend
solutions.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup strategy
@ 2024-02-28 16:50 Peter Geoghegan <pg@bowt.ie>
parent: David Barron <david.barron@zencos.com>
2 siblings, 1 reply; 8+ messages in thread
From: Peter Geoghegan @ 2024-02-28 16:50 UTC (permalink / raw)
To: David Barron <david.barron@zencos.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Feb 28, 2024 at 11:45 AM David Barron <david.barron@zencos.com> wrote:
> 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.
Are you doing the backups on a physical replica?
I wonder if it could be related to an issue that this user also ran
into a little while ago:
https://postgr.es/m/17846-1a0e5ce976f4c01a@postgresql.org
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup strategy
@ 2024-02-28 16:52 David G. Johnston <david.g.johnston@gmail.com>
parent: David Barron <david.barron@zencos.com>
2 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2024-02-28 16:52 UTC (permalink / raw)
To: David Barron <david.barron@zencos.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Feb 28, 2024, 09:45 David Barron <david.barron@zencos.com> wrote:
>
> If, for example, table a has a constraint that references rows in table b,
> table b has to be restored first, but pg_dump doesn't take that into
> account. So the restore tries to restore table a first, but can't because
> table b contains no data. That's what I ran into in general terms.
>
It is illegal to write a check constraint that references another table.
It doesn't matter that hiding your illegal setup inside a back box function
prevents the system from stopping you. You need to fix your design so as
not to break the documented rules. Namely by using a trigger.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Backup strategy
@ 2024-02-28 16:53 David Barron <david.barron@zencos.com>
parent: Peter Geoghegan <pg@bowt.ie>
0 siblings, 0 replies; 8+ messages in thread
From: David Barron @ 2024-02-28 16:53 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Feb 28, 2024 at 11:45 AM David Barron <david.barron@zencos.com> wrote:
> 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.
Are you doing the backups on a physical replica?
I wonder if it could be related to an issue that this user also ran into a little while ago:
https://postgr.es/m/17846-1a0e5ce976f4c01a@postgresql.org
--
Peter Geoghegan
No, it is not a physical replica.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup strategy
@ 2024-02-28 18:22 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David Barron <david.barron@zencos.com>
1 sibling, 0 replies; 8+ messages in thread
From: Ron Johnson @ 2024-02-28 18:22 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Feb 28, 2024 at 11:23 AM David Barron <david.barron@zencos.com>
wrote:
> Good day all
>
>
>
> 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.
>
Clear, but, like Tom said, makes little sense.
Why? *Lots* of databases (including ours) have scads of foreign key
constraints and triggers. If pg_restore didn't "take that into account",
people would have been hollering about that 20 years ago.
pg_restore works perfectly *every time* I restore the whole database.
Thus... show a verbose whole database pg_dump and pg_restore, with error
messages.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-02-28 18:22 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-28 16:23 Backup strategy David Barron <david.barron@zencos.com>
2024-02-28 16:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 16:45 ` David Barron <david.barron@zencos.com>
2024-02-28 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 16:50 ` Peter Geoghegan <pg@bowt.ie>
2024-02-28 16:53 ` David Barron <david.barron@zencos.com>
2024-02-28 16:52 ` David G. Johnston <david.g.johnston@gmail.com>
2024-02-28 18:22 ` Ron Johnson <ronljohnsonjr@gmail.com>
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