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

On Wed, Nov 25, 2015 at 2:43 PM, Bruce Momjian <bruce(at)momjian(dot)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(at)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(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;

Again thanks in advance,

Bene