Re: A question about sequences and backup/restore cycles

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

--

UNIX is basically a simple operating system, but you have to be a genius to
understand the simplicity.

Dennis Ritchie <https://www.brainyquote.com/authors/dennis-ritchie-quotes>