Upgrade Postgres Extension / Install Specific Version

Question

1) How do you upgrade a postgres extension?
2) How do you install a specific extension version?

In production the version of hstore is out of date.

=> select * from pg_available_extensions where name ='hstore';
  name  | default_version | installed_version |                     comment
--------+-----------------+-------------------+--------------------------------------------------
 hstore | 1.3             | 1.1               | data type for storing sets of (key, value) pairs

All the other environments hstore is already at 1.3 so I don't have a way to test if create extension hstore; is all I need.

I'd like to test the upgrade first and ran Postgres 9.4.4 through Docker

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres:9.4.4

docker run -it --rm --link some-postgres:postgres postgres:9.4.4 psql -h postgres -U postgres

but hstore 1.3 was the default version

  name  | default_version | installed_version |                     comment
--------+-----------------+-------------------+--------------------------------------------------
 hstore | 1.3             | 1.3               | data type for storing sets of (key, value) pairs

Answer 1

1. To upgrade a postgres extension

   • install the latest version
     ALTER EXTENSION hstore UPDATE;
   • install a specific version
     • list available extensions
       SELECT * FROM pg_available_extension_versions WHERE name ='hstore';
     • install the specific version
       ALTER EXTENSION hstore UPDATE TO '1.3';

2. Install a specific version older than the default version

   • Download the version from the postgres repo to the pg_config extension directory.
     wget --directory-prefix /usr/share/postgresql/9.4/extension/ \ https://raw.githubusercontent.com/postgres/postgres/REL9_2_STABLE/contrib/hstore/hstore--1.1.sql
   • Check available versions
     SELECT * FROM pg_available_extension_versions WHERE name ='hstore';
   • Install specific version
     CREATE EXTENSION hstore WITH VERSION '1.1';