Re: Problems with pg_upgrade after change of unix user running db.

On 11/27/2015 06:07 PM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
>>> My guess is you are sharing the constraint name "seqno_not_null" with
>>> multiple tables. I think you are going to have to dig into the system
>>> tables to see where that is referenced and fix it.
>
>> In the post below the OP shows the tables involved(they where inherited):
>> http://www.postgresql.org/message-id/CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com
>
> Inherited eh? Maybe related to 074c5cfbf.

From the OP's post:

The error:

pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR: constraint
"seqno_not_null" for relation "js_activity_2011" already exists
Command was: ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

The setup:

postgres_prod(at)proddb_testing=# select c.conname, c.conislocal,
c.coninhcount, c.convalidated as valid, (select relname from pg_class where
oid = c.conrelid) from pg_constraint c where conname = 'seqno_not_null'
order by relname;
conname │ conislocal │ coninhcount │ valid │ relname
────────────────┼────────────┼─────────────┼───────┼──────────────────
seqno_not_null │ t │ 0 │ f │ js_activity
seqno_not_null │ t │ 1 │ f │ js_activity_2009
seqno_not_null │ t │ 1 │ f │ js_activity_2010
seqno_not_null │ t │ 1 │ f │ js_activity_2011
seqno_not_null │ f │ 1 │ f │ js_activity_2012
seqno_not_null │ f │ 1 │ t │ js_activity_2013
seqno_not_null │ f │ 1 │ t │ js_activity_2014
seqno_not_null │ f │ 1 │ f │ js_activity_tip

[as-proddb(at)nyc-dbc-001 upgrade-logs]$ pg_restore
pg_upgrade_dump_16416.custom | fgrep seqno_not_null -B 1
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2013" ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2014" ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2010"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2009"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

>
> regards, tom lane
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com