Re: A question about sequences and backup/restore cycles

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Tue, 22 Oct 2019 13:42:10 -0700

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@xxxxxxxxxxx