Friday 15 November 2013

Postgres 9.1 : Installing contrib modules on ec2

Having installed PostgreSQL 9.1 on my ec2 instance (Amazon Linux) following these instructions, I wanted to experiment with a few contrib modules. However, this process was not trivial, mainly because the installed postgres distribution did not have the contrib modules by default. Here's what I did :
  1. Checking if the modules exist :
    • cd /usr/pgsql-9.1/share/extension
    • Only plpgsql seems to be there.
  2. Extensions don't exist, preparing to install contrib. Check the specific postgres version first :
    • sudo su -
    • su - postgres
    • /usr/pgsql-9.1/bin/psql -p 5432
    • postgres=# select version();
                                                          version                                                    
      ---------------------------------------------------------------------------------------------------------------
       PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-54), 64-bit
      (1 row)
  3. Installing contrib :
    • sudo yum install postgresql91-contrib
      Error: Package: postgresql91-contrib-9.1.10-1PGDG.rhel5.x86_64 (pgdg91)
      Requires: libossp-uuid.so.15()(64bit)
  4. Whoops! Fixing errors and try installing again :
    • Downloaded the required rpm file from the following link.
    • wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/nick_at_seakr:/epel/RedHat_RHEL-5/x86_64/uuid-1.5.1-5.1.x86_64.rpm
    • rpm -Uvh libossp-uuid.so.15 (instructions found here)
  5. Success! Checking "/usr/pgsql-9.1/share/extension" indeed shows that the modules were installed. What extensions are installed on my current database?
    • sudo su -
    • su - postgres
    • /usr/pgsql-9.1/bin/psql -p 5432
    • postgres=# select * from pg_extension;
       extname | extowner | extnamespace | extrelocatable | extversion | extconfig | e
      xtcondition 
      ---------+----------+--------------+----------------+------------+-----------+--
      ------------
       plpgsql |       10 |           11 | f              | 1.0        |           | 
      (1 row)
    • You can use "postgres=# \dx" instead of the select statement if you want. Anyway, it looks like only plpgsql is installed.
  6. Let us install some other extensions. These are installed on the current database by default.
    • postgres=# create extension pg_trgm;
      CREATE EXTENSION
    • postgres=# create extension hstore;
      CREATE EXTENSION
    • postgres=# \dx
                                          List of installed extensions
        Name   | Version |   Schema   |                            Description                            
      ---------+---------+------------+-------------------------------------------------------------------
       hstore  | 1.0     | public     | data type for storing sets of (key, value) pairs
       pg_trgm | 1.0     | public     | text similarity measurement and index searching based on trigrams
       plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
      (3 rows)
Extra information :
  1. How to change the current database :
    • postgres=# \connect foobar
      psql (9.1.10, server 9.1.9)
      You are now connected to database "foobar" as user "postgres".
  2. How to check the current database :
    • postgres=# select current_database();
       current_database 
      ------------------
       postgres
      (1 row)
    • But I don't know why you'd need that in this case since you can check the database name in the prompt itself.
  3. Here's a list of modules to explore.
  4. What I ended up installing :
    • foobar=# \dx
                                               List of installed extensions
              Name        | Version |   Schema   |                            Description                            
      --------------------+---------+------------+-------------------------------------------------------------------
       hstore             | 1.0     | public     | data type for storing sets of (key, value) pairs
       pg_stat_statements | 1.0     | public     | track execution statistics of all SQL statements executed
       pg_trgm            | 1.0     | public     | text similarity measurement and index searching based on trigrams
       pgstattuple        | 1.0     | public     | show tuple-level statistics
       plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
       uuid-ossp          | 1.0     | public     | generate universally unique identifiers (UUIDs)
      (6 rows)

No comments:

Post a Comment