Обсуждение: A question about sequences and backup/restore cycles

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?


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

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.

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/22/19 10:48 AM, stan wrote:
Please reply to list also:
Ccing list.


> 
> Sorry if my description was not clear.
> 
> No, we  do not mix test, and production data. Let me try to clarify the
> question. Looking at a pg_dump, I see the following:
> 
> 
> CREATE SEQUENCE public.customer_key_serial
>      START WITH 1
>     INCREMENT BY 1
>     NO MINVALUE
>     NO MAXVALUE
>     CACHE 1;
> 
> 
> Yet, in the same pg_dump file I have:
> 
> 
> 
> 
> COPY public.customer (customer_key, cust_no, name, c_type, location,
> bill_address_1, bill_address_2, bill_city, bill_state, bill_zip,
> bill_country, bill_attention, bill_addressee, ship_address_1,
> ship_address_2, ship_addressee, ship_attention, ship_city, ship_state,
> ship_zip, office_phone_area_code, office_phone_exchange,
> office_phone_number, office_phone_extension, cell_phone_area_code,
> cell_phone_exchange, cell_phone_number, ship_phone_area_code,
> ship_phone_exchange, ship_phone_number, ship_phone_extension,
> fax_phone_area_code, fax_phone_exchange, fax_phone_number, status, modtime)
> FROM stdin;
> 1       12      Made Up Inc.    INDUSTRIAL_CONVEYOR     \N      101 Nowhere
> Ave.        \N      LaGrange        GA      00000   \N      \N      \N
> \N      \N      \N      \N      \N      \N      00000   \N      \N      \N
>     \N      \N      \N      \N      \N      \N      \N      \N      \N
>     \N      \N      ACTIVE  2019-09-30 23:55:04.594203+00
>     2       5       Jimmys Favorite Customer.       PLASTICS        \N
>     56 Somewhere St.        \N      No Such City    SC      00000   \N
>     \N      \N      \N      \N      \N      \N      \N      \N      00000
>     \N      \N      \N      \N      \N      \N      \N      \N      \N
>     \N      \N      \N      \N      \N      ACTIVE  2019-09-30
>     23:55:04.636827+00
> 
> So it appears to me the customer table is going to get (correctly) populated
> with the originally generated keys, yet the sequence will want to return a 1
> the next time it is called, when a new customer gets inserted.
> 
> Am I missing something here?
> 

Yes something like this, in dump file, for non-serial sequence:

CREATE SEQUENCE public.plant1_p_item_no_seq
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALUE
     CACHE 1;

SELECT pg_catalog.setval('public.plant1_p_item_no_seq', 5509, true);

or for serial sequence:

CREATE SEQUENCE public.avail_headers_line_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALUE
     CACHE 1;

ALTER TABLE ONLY public.avail_headers ALTER COLUMN line_id SET DEFAULT 
nextval('public.avail_headers_line_id_seq'::regclass);


SELECT pg_catalog.setval('public.avail_headers_line_id_seq', 53, true);

If you want to see how things are run on a higher level do something like:

pg_dump -Fc -d some_db -f db.out

pg_restore -l db_out > db_toc.txt

-l on pg_restore creates a TOC(table of contents) showing the ordering 
of the schema recreation.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Oct 22, 2019 at 12:48:41PM -0700, Adrian Klaver wrote:
> On 10/22/19 10:48 AM, stan wrote:
> Please reply to list also:
> Ccing list.
>
>
> >
> > Sorry if my description was not clear.
> >
> > No, we  do not mix test, and production data. Let me try to clarify the
> > question. Looking at a pg_dump, I see the following:
> >
> >
> > CREATE SEQUENCE public.customer_key_serial
> >      START WITH 1
> >     INCREMENT BY 1
> >     NO MINVALUE
> >     NO MAXVALUE
> >     CACHE 1;
> >
> >
> > Yet, in the same pg_dump file I have:
> >
> >
> >
> >
> > COPY public.customer (customer_key, cust_no, name, c_type, location,
> > bill_address_1, bill_address_2, bill_city, bill_state, bill_zip,
> > bill_country, bill_attention, bill_addressee, ship_address_1,
> > ship_address_2, ship_addressee, ship_attention, ship_city, ship_state,
> > ship_zip, office_phone_area_code, office_phone_exchange,
> > office_phone_number, office_phone_extension, cell_phone_area_code,
> > cell_phone_exchange, cell_phone_number, ship_phone_area_code,
> > ship_phone_exchange, ship_phone_number, ship_phone_extension,
> > fax_phone_area_code, fax_phone_exchange, fax_phone_number, status, modtime)
> > FROM stdin;
> > 1       12      Made Up Inc.    INDUSTRIAL_CONVEYOR     \N      101 Nowhere
> > Ave.        \N      LaGrange        GA      00000   \N      \N      \N
> > \N      \N      \N      \N      \N      \N      00000   \N      \N      \N
> >     \N      \N      \N      \N      \N      \N      \N      \N      \N
> >     \N      \N      ACTIVE  2019-09-30 23:55:04.594203+00
> >     2       5       Jimmys Favorite Customer.       PLASTICS        \N
> >     56 Somewhere St.        \N      No Such City    SC      00000   \N
> >     \N      \N      \N      \N      \N      \N      \N      \N      00000
> >     \N      \N      \N      \N      \N      \N      \N      \N      \N
> >     \N      \N      \N      \N      \N      ACTIVE  2019-09-30
> >     23:55:04.636827+00
> >
> > So it appears to me the customer table is going to get (correctly) populated
> > with the originally generated keys, yet the sequence will want to return a 1
> > the next time it is called, when a new customer gets inserted.
> >
> > Am I missing something here?
> >
>
> Yes something like this, in dump file, for non-serial sequence:
>
> CREATE SEQUENCE public.plant1_p_item_no_seq
>     START WITH 1
>     INCREMENT BY 1
>     NO MINVALUE
>     NO MAXVALUE
>     CACHE 1;
>
> SELECT pg_catalog.setval('public.plant1_p_item_no_seq', 5509, true);
>
> or for serial sequence:
>
> CREATE SEQUENCE public.avail_headers_line_id_seq
>     AS integer
>     START WITH 1
>     INCREMENT BY 1
>     NO MINVALUE
>     NO MAXVALUE
>     CACHE 1;
>
> ALTER TABLE ONLY public.avail_headers ALTER COLUMN line_id SET DEFAULT
> nextval('public.avail_headers_line_id_seq'::regclass);
>
>
> SELECT pg_catalog.setval('public.avail_headers_line_id_seq', 53, true);
>
> If you want to see how things are run on a higher level do something like:
>
> pg_dump -Fc -d some_db -f db.out
>
> pg_restore -l db_out > db_toc.txt
>
> -l on pg_restore creates a TOC(table of contents) showing the ordering of
> the schema recreation.
>

Oh, excellent.!!

Thanks for the patience to teach me about this.

Does make me wonder what the vendor did to create our issue on their
database.

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

On 10/22/19 1:35 PM, stan wrote:
> On Tue, Oct 22, 2019 at 12:48:41PM -0700, Adrian Klaver wrote:
>> On 10/22/19 10:48 AM, stan wrote:
>> Please reply to list also:
>> Ccing list.
>>
>>
>>>
>>> Sorry if my description was not clear.
>>>
>>> No, we  do not mix test, and production data. Let me try to clarify the
>>> question. Looking at a pg_dump, I see the following:
>>>
>>>
>>> CREATE SEQUENCE public.customer_key_serial
>>>       START WITH 1
>>>     INCREMENT BY 1
>>>     NO MINVALUE
>>>     NO MAXVALUE
>>>     CACHE 1;
>>>
>>>
>>> Yet, in the same pg_dump file I have:
>>>
>>>
>>>
>>>
>>> COPY public.customer (customer_key, cust_no, name, c_type, location,
>>> bill_address_1, bill_address_2, bill_city, bill_state, bill_zip,
>>> bill_country, bill_attention, bill_addressee, ship_address_1,
>>> ship_address_2, ship_addressee, ship_attention, ship_city, ship_state,
>>> ship_zip, office_phone_area_code, office_phone_exchange,
>>> office_phone_number, office_phone_extension, cell_phone_area_code,
>>> cell_phone_exchange, cell_phone_number, ship_phone_area_code,
>>> ship_phone_exchange, ship_phone_number, ship_phone_extension,
>>> fax_phone_area_code, fax_phone_exchange, fax_phone_number, status, modtime)
>>> FROM stdin;
>>> 1       12      Made Up Inc.    INDUSTRIAL_CONVEYOR     \N      101 Nowhere
>>> Ave.        \N      LaGrange        GA      00000   \N      \N      \N
>>> \N      \N      \N      \N      \N      \N      00000   \N      \N      \N
>>>      \N      \N      \N      \N      \N      \N      \N      \N      \N
>>>      \N      \N      ACTIVE  2019-09-30 23:55:04.594203+00
>>>      2       5       Jimmys Favorite Customer.       PLASTICS        \N
>>>      56 Somewhere St.        \N      No Such City    SC      00000   \N
>>>      \N      \N      \N      \N      \N      \N      \N      \N      00000
>>>      \N      \N      \N      \N      \N      \N      \N      \N      \N
>>>      \N      \N      \N      \N      \N      ACTIVE  2019-09-30
>>>      23:55:04.636827+00
>>>
>>> So it appears to me the customer table is going to get (correctly) populated
>>> with the originally generated keys, yet the sequence will want to return a 1
>>> the next time it is called, when a new customer gets inserted.
>>>
>>> Am I missing something here?
>>>
>>
>> Yes something like this, in dump file, for non-serial sequence:
>>
>> CREATE SEQUENCE public.plant1_p_item_no_seq
>>      START WITH 1
>>      INCREMENT BY 1
>>      NO MINVALUE
>>      NO MAXVALUE
>>      CACHE 1;
>>
>> SELECT pg_catalog.setval('public.plant1_p_item_no_seq', 5509, true);
>>
>> or for serial sequence:
>>
>> CREATE SEQUENCE public.avail_headers_line_id_seq
>>      AS integer
>>      START WITH 1
>>      INCREMENT BY 1
>>      NO MINVALUE
>>      NO MAXVALUE
>>      CACHE 1;
>>
>> ALTER TABLE ONLY public.avail_headers ALTER COLUMN line_id SET DEFAULT
>> nextval('public.avail_headers_line_id_seq'::regclass);
>>
>>
>> SELECT pg_catalog.setval('public.avail_headers_line_id_seq', 53, true);
>>
>> If you want to see how things are run on a higher level do something like:
>>
>> pg_dump -Fc -d some_db -f db.out
>>
>> pg_restore -l db_out > db_toc.txt
>>
>> -l on pg_restore creates a TOC(table of contents) showing the ordering of
>> the schema recreation.
>>
> 
> Oh, excellent.!!
> 
> Thanks for the patience to teach me about this.
> 
> Does make me wonder what the vendor did to create our issue on their
> database.
> 

I thought it was your database you where concerned about?

In any case tracking down the issue would require more information then 
has been provided. Like I said previously I would start with automated 
scripts that did not get the memo about the database changing under them.


-- 
Adrian Klaver
adrian.klaver@aklaver.com