A question about sequences and backup/restore cycles

On 10/21/19 5:13 PM, stan wrote:

I typically design a system with primary keys defined, like this:

CREATE TABLE employee (
employee_key integer DEFAULT nextval('employee_key_serial')
PRIMARY KEY ,

I use scripts to build the database structures and load the data. I am
careful to get the dependencies in the correct order, so that the keys later
structures depend on already exist.

Today I was going over the design for the current project with a friend,
whose expertise i respect. he said that he had issues on a system designed
by an OEM that had dependencies on keys developed from sequences after a
backup/restore cycle,

Will I potentially have these issues? If so, what can I do different to
avoid this being an issue?

It is not clear to me what you are doing:

1) Are you using pg_dump/pg_restore to populate a database?
If so it will take care of the dependencies.

2) Are you using a home built method to populate the database?
In that case you take responsibility for dependencies.

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

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

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.klaver@aklaver.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.klaver@aklaver.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&gt;