Thread: Backup strategy
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
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
meansthat the tables have to be backed up and restored in the correct order to take the constraints into account. But
pg_dumpand pg_restore don't take that into account, so when doing restores I was running into errors. Hopefully that
isclear.
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
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
meansthat the tables have to be backed up and restored in the correct order to take the constraints into account. But
pg_dumpand pg_restore don't take that into account, so when doing restores I was running into errors. Hopefully that
isclear.
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
whatmasked 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_dumpdoesn't take that into account. So the restore tries to restore table a first, but can't because table b
containsno data. That's what I ran into in general terms.
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_dumpdoesn'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
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, whichmeans 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
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.
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, whichmeans 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.
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.