2

I am unable to upgrade my postgresql server from 9.4 to 9.5 due to the following error message:

pg_restore: creating OPERATOR "public.->"
pg_restore: creating OPERATOR "public.<@"
pg_restore: creating OPERATOR "public.=>"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1617; 2617 17937 OPERATOR => william
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "=>"
LINE 1: CREATE OPERATOR => (
                        ^
    Command was: CREATE OPERATOR => (
    PROCEDURE = "tconvert",
    LEFTARG = "text",
    RIGHTARG = "text"
);

I have not been able to find anything relevant by googling. The most relevant thing I can find is someone who encountered this issue with a much older version of hstore, and the fix is not clear.

I am using homebrew on a Mac. I would work around the issue with pg_dumpall, but I can no longer run that because the 9.4 binaries are loading the libs for 9.5 following the upgrade. Is there a way around this?

wmakley
  • 1,233
  • 9
  • 18
  • It looks like `"public.=>"` should probably be `"public"."=>"` . (and mixing versions is generally not a good idea) – wildplasser Jul 31 '16 at 19:35
  • Yeah but I didn't create it, it is just part of the hstore extension I am using. I am not mixing versions, I am just trying to upgrade my old data from 9.4 to 9.5. The only reason I am trying to run 9.4 now that 9.5 is installed is to try other methods of migrating my data. I want to do this on the production server, so I need to find a viable method. – wmakley Aug 01 '16 at 04:24

3 Answers3

1

This is expected, see the release notes for PostgreSQL 9.5:

  • Allow => to specify named parameters in function calls (Pavel Stehule)

    Previously only := could be used. This requires removing the possibility for => to be a user-defined operator. Creation of user-defined => operators has been issuing warnings since PostgreSQL 9.0.

You'll have to use a different name for the operator. There is no workaround short of hacking up PostgreSQL.

It might be that your problem is an old installation of the hstore contrib module that was never migrated to an extension. In that case you can try to upgrade it in the old database before exporting like this:

CREATE EXTENSION hstore FROM unpackaged;

That would get rid of the => operator that has been deprecated for a long time. You'd have to change all application code that relies on it.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks, but I didn't create the operator, and I don't use it in any of my procedures. I don't understand where it came from or how to remove it, I just want to upgrade my database. Everything I have ever installed is totally "out of the box". :( – wmakley Aug 01 '16 at 13:29
  • No, there is no operator `public.=>` in core PostgreSQL. In case it is a leftover from an old `hstore` installation, I've amended my answer to address that case. – Laurenz Albe Aug 01 '16 at 13:55
  • Unfortunately there is a different error: "ERROR: type "hstore" does not exist" I got my upgrade to work by reverting to the old version and using pg_dumpall. – wmakley Aug 01 '16 at 14:46
  • Well, the old `public.=>` will also not be created with this method. – Laurenz Albe Aug 01 '16 at 14:59
  • Again, I was not using it. I just want my hstore in 9.5 to be completely vanilla and current. – wmakley Aug 02 '16 at 13:56
  • Then you'll have to manually remove the cruft that's there in the old installation and `CREATE EXTENSION hstore`. The extension can be upgraded without problems. – Laurenz Albe Aug 02 '16 at 14:00
1

The other solutions either were not options, or they didn't work.
The solution that worked for me was:

ALTER EXTENSION hstore UPDATE TO '1.1';

Source: https://www.postgresql.org/message-id/22170.1457479307%40sss.pgh.pa.us

phemmer
  • 6,882
  • 3
  • 33
  • 31
0

Not a complete solution, but in the end I reverted to the previous version and used pg_dumpall to backup my databases and reload them in the new version, completely forgetting about carrying over defunct operators. In homebrew this was as simple as changing a symlink:

cd /usr/local/lib
rm postgresql
ln -sv ../../Cellar/postgresql/9.4.5_2/lib/postgresql .
/usr/local/Cellar/postgresql/9.4.5_2/bin/pg_ctl -D /usr/local/var/postgres -l logfile start # start old cluster
/usr/local/Cellar/postgresql/9.4.5_2/bin/pg_dumpall > backup.sql
/usr/local/Cellar/postgresql/9.4.5_2/bin/pg_ctl -D /usr/local/var/postgres -l logfile stop # stop old cluster
rm postgresql
ln -sv ../../Cellar/postgresql/9.5.3/lib/postgresql .

The rest of the upgrade process I take from https://kkob.us/2016/01/09/homebrew-and-postgresql-9-5/

Loading the data is just psql < backup.sql No hstore errors this time!

wmakley
  • 1,233
  • 9
  • 18