Обсуждение: 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
Oh it is the one we are working on.
One of my team members brought up this issue from a job where we worked on a vendor designed one.
I am convince we do not have an issue now.
Thanks for your expertise.
On Tue, Oct 22, 2019 at 4:42 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.