Обсуждение: Backup strategy

Поиск
Список
Период
Сортировка

Backup strategy

От
David Barron
Дата:

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

 

Re: Backup strategy

От
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 what masked the issue is your
use of --disable-triggers.

            regards, tom lane



RE: Backup strategy

От
David Barron
Дата:
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. 



Re: Backup strategy

От
Tom Lane
Дата:
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



Re: Backup strategy

От
Peter Geoghegan
Дата:
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



Re: Backup strategy

От
"David G. Johnston"
Дата:


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.

RE: Backup strategy

От
David Barron
Дата:
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.

Re: Backup strategy

От
Ron Johnson
Дата:
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.