Re: A question about sequences and backup/restore cycles - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: A question about sequences and backup/restore cycles |
Date | |
Msg-id | a1048170-3b64-ddd1-9a00-0881a2cc0140@aklaver.com Whole thread Raw |
In response to | A question about sequences and backup/restore cycles (stan <stanb@panix.com>) |
Responses |
Re: A question about sequences and backup/restore cycles
|
List | pgsql-general |
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
pgsql-general by date: