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 CADbMkNPT-Jz5PRSQ4RbUASYAjocV_KHUWapR+g8fNvhUAyRpxA@mail.gmail.com
Whole thread Raw
In response to Re: Problems with pg_upgrade after change of unix user running db.  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Problems with pg_upgrade after change of unix user running db.
List pgsql-general


On 3 October 2016 at 15:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> On 3 October 2016 at 14:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You're going to need to manually drop that operator from the source
>> database, as "=>" isn't a legal operator name anymore.  This appears
>> to be left over from a pre-9.0 version of hstore.

> Thanks for the quick reply.  How do I do that however?  Without dropping
> the extension itself that is:
>
> proddb_testing=# drop operator => (text, text);
> ERROR:  cannot drop operator =>(text,text) because extension hstore requires it
> HINT:  You can drop extension hstore instead.
>
> Is it possible that I need to do some form of hstore extension upgrade
> dance?

Ah, I'd been guessing that the operator was "loose", but if you still
have hstore 1.0 installed then yes that's the behavior I'd expect.
You need to do "alter extension hstore update".  In a 9.2 database
that should bring it to 1.1 which will get rid of the operator.

                        regards, tom lane

And it looks like now I'm back to the error that stopped me last time:

[root@igm-dbc-001 ~]# cd /usr/local/home/as-proddb/upgrade-logs/
[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8425; 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_20110101" already exists
    Command was: ALTER TABLE "js_activity_20110101"
    ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

I figured it might be because I'm on 9.2.17 so I upgraded to 9.2.18 and tried again but no luck:

[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8425; 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_20110101" already exists
    Command was: ALTER TABLE "js_activity_20110101"
    ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

What do you need from me to debug this?


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Problems with pg_upgrade after change of unix user running db.
Next
From: Tom Lane
Date:
Subject: Re: Problems with pg_upgrade after change of unix user running db.