A question about sequnces and pg_restore

On Mon, Oct 21, 2019 at 05:24:37PM -0700, Adrian Klaver wrote:
> On 10/21/19 5:13 PM, stan wrote:
> >
> > I typically design a system with primary keys defined, like this:
> >
> >
> > CREATE TABLE employee (
> > employee_key integer DEFAULT nextval('employee_key_serial')
> > PRIMARY KEY ,
> >
> > I use scripts to build the database structures and load the data. I am
> > careful to get the dependencies in the correct order, so that the keys later
> > structures depend on already exist.
> >
> > Today I was going over the design for the current project with a friend,
> > whose expertise i respect. he said that he had issues on a system designed
> > by an OEM that had dependencies on keys developed from sequences after a
> > backup/restore cycle,
> >
> > Will I potentially have these issues? If so, what can I do different to
> > avoid this being an issue?
>
> It is not clear to me what you are doing:
>
> 1) Are you using pg_dump/pg_restore to populate a database?
> If so it will take care of the dependencies.
>
> 2) Are you using a home built method to populate the database?
> In that case you take responsibility for dependencies.
>

OK, let me elaborate.

During the development/test cycle. I routinely drop all objects in the
database for the project we are in the process of working on. This is my
standard way of doing this, and, I understand the dependencies, and keep
them working correctly with the scripts that recreate the database. This
work fine, as long as all that is in the database is test data.

At a point in time, we start putting real data in the db for continuing
testing, and later it becomes a production database with ONLY real data in
it.

At the 1st of those 2 points, we stop dropping/recreating the database. We
also start making backups using pg_dump at the 1st of these 2 points in
time.

My co-worker says that he had issues restoring a database designed by an
OEM because the design of that database depended on matching keys generated
by sequences on primary table with the keys stored in dependent tables. I
think I can see how this could happen if the keys assigned by the sequences
in the primary changes were recreated during the restore. This would
result in the keys restored in the dependent tables pointing to older, now
incorrect keys.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin