CODEWITHSUNDEEP
postgresql
David Vasandani
David Vasandani

Reputation: 1930

Upgrade Postgres Extension / Install Specific Version

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

Upvotes: 11

Views: 15245

Answers (1)

David Vasandani
David Vasandani

Reputation: 1930

  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';

Upvotes: 19

Related Questions