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

On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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.
>
> I forgot to mention this earlier. This cluster is running 9.2.6 and I'm
attempting to upgrade to the latest 9.4.5

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