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

On 3 October 2016 at 15:30, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Benedikt Grundmann <bgrundmann(at)janestreet(dot)com> writes:
> > On 3 October 2016 at 14:12, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)igm-dbc-001 ~]# cd /usr/local/home/as-proddb/upgrade-logs/
[root(at)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(at)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?