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

Ed Sabol · Accepted Answer · 2020-08-16 18:50:09Z

20

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

edited Aug 16, 2020 at 18:50

Ed Sabol

5046 silver badges17 bronze badges

answered Jan 18, 2018 at 19:19

David Vasandani

1,9509 gold badges31 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Upgrade Postgres Extension / Install Specific Version

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related