Re: A question about sequences and backup/restore cycles
От | Adrian Klaver |
---|---|
Тема | Re: A question about sequences and backup/restore cycles |
Дата | |
Msg-id | 07d2c13f-7cda-cf95-bc4f-86fc8e20c467@aklaver.com обсуждение исходный текст |
Ответ на | Re: A question about sequences and backup/restore cycles (stan <stanb@panix.com>) |
Ответы |
Re: A question about sequences and backup/restore cycles
(Stan Brown <stanbrow@gmail.com>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: