Re: A question about sequences and backup/restore cycles

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(dot)klaver(at)aklaver(dot)com