Postgres Pro
Downloads
Home   >   mailing lists

Re: Problems with pg_upgrade after change of unix user running db. - Mailing list pgsql-general

From Benedikt Grundmann
Subject Re: Problems with pg_upgrade after change of unix user running db.
Date
Msg-id CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com
Whole thread Raw
In response to Re: Problems with pg_upgrade after change of unix user running db.  (Bruce Momjian <bruce@momjian.us>)
Responses Re: Problems with pg_upgrade after change of unix user running db.
List pgsql-general
Tree view


On Wed, Nov 25, 2015 at 2:43 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Nov 25, 2015 at 08:04:49AM +0000, Benedikt Grundmann wrote:
>     You can see the 9.5 requirements in the pg_upgrade function
>     check_is_install_user().  You might as well just honor what that
>     requires as you will eventually be moving to 9.5.
>
>
> Thanks I'll try this in one of the next days.  Sorry for the radio silence in
> the last 2 days.  We have been quite busy at work.  I don't think I understand

Sure, no problem.  I would have liked to reply to this sooner too, but
had to do some research.

That worked (I also swapped the password columns so that I don't have to change pgpass entries).  But I then ran into a different problem a little later on.  I thought I quickly mention it here in case somebody can point me into the right direction:

...
Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster

*failure*
Consult the last few lines of "pg_upgrade_dump_16416.log" for
the probable cause of the failure.
child worker exited abnormally: Invalid argument

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

[as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
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;


Now js_activity is the parent table and js_activity_* are all child tables (for partitioning):

postgres_prod@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@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;

Again thanks in advance,

Bene

pgsql-general by date:

Previous
From: NTPT
Date:
Subject: Re: Old source code needed
Next
From: Jan de Visser
Date:
Subject: Re: Old source code needed