Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 11/27/2015 06:07 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx
>
> 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.klaver@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux