0

I'm running pg_dump -F custom for database backups, with --exclude-table-data for a very large audit table. I'm then exporting that table data in a separate dump file. It isn't referentially integral with the main dump.

As part of my restore strategy, I'd like to be able to restore the main dump, bring my app online and continue using the database immediately, then bring the audit data back in behind it. The trouble is, as soon as new audit data comes in at sequence 1, the import of the audit data fails as soon as it tries to insert over the top of the new data.

Is it possible to include the setting of the sequence in the main dump without including the table data?

I have considered removing the primary key, but there are other tables I'd also like to do this with, and they definitely do need the PK.

I'm using postgresql 13.

spume
  • 1,704
  • 1
  • 14
  • 19
  • 1
    But the sequence value is dumped even if you `--exclude-table-data` for a table. If that doesn't work for you, you must be doing something strange. Perhaps you could show how your objects are defined and how you dump the data. – Laurenz Albe Oct 06 '21 at 16:34
  • 1
    You're right - looks like I wasn't testing this very well! Thanks for shutting that down @LaurenzAlbe – spume Oct 06 '21 at 17:10
  • You could delete the question to avoid confusion. – Laurenz Albe Oct 06 '21 at 17:20
  • @LaurenzAlbe, are you sure? I just tested with `--exclude-table-data=*` and its equivalent `-s` and there is no `setval` to reset the sequence. – Adrian Klaver Oct 06 '21 at 17:20
  • @LaurenzAlbe. Alright, so if you specify a table then the sequence state is carried through. If you try a schema only on everything it is not. – Adrian Klaver Oct 06 '21 at 17:32
  • @AdrianKlaver If you exclude data from *all* tables, you are excluding the sequence data as well. Try excluding the data for only one table, and the sequence value will be dumped. – Laurenz Albe Oct 06 '21 at 17:33

1 Answers1

1

Instead of a sequence, which can build with a rownumber use uuids and a timestamp, so you have unique values and the order of insert doesn't matter. Uuids are a bit slower the ints.

Another possibility that you save th last audit Id in another table and set the sequence new like https://www.postgresql.org/docs/9.1/sql-altersequence.html

nbk
  • 45,398
  • 8
  • 30
  • 47