PuppetDB 2.2 and pg_trgm extension

[Paul Seymour]

Hello,

Bumping my installation up to Postgres 9.3.5 and PuppetDB 2.2 I noticed in the release notes about the pg_trgm extension (hence the Postgres update)

However on installing them and setting up the extension (seemingly correctly):-

$ psql

psql (9.3.5)

Type "help" for help.

postgres=# \dx

                                    List of installed extensions

  Name   | Version |   Schema   |                            Description

---------+---------+------------+-------------------------------------------------------------------

 pg_trgm | 1.1     | public     | text similarity measurement and index searching based on trigrams

 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

(2 rows)

postgres-# \q

I still get this on startup of PuppetDB2.2:-

2014-08-30 08:05:56,029 WARN  [c.p.p.s.migrate] Missing PostgreSQL extension `pg_trgm`

We are unable to create the recommended pg_trgm indexes due to

the extension not being installed correctly. Run the command:

    CREATE EXTENSION pg_trgm;

as the database super user on the PuppetDB database to correct

this, then restart PuppetDB.

Which is odd. My Postgres install is non-standard as I need to move the paths around for my environment but that shouldn't matter.

Any ideas. These are on RHEL6.5.

Cheers

Paul 

[Paul Seymour]

Ignore me didn't add the extension to the PuppetDB database.

Added it to the puppetdb DB and hey presto:-

2014-08-30 08:40:27,935 INFO  [c.p.p.s.migrate] Creating additional index `fact_paths_path_trgm`

Too early in the morning for this sh..

Cheers

Paul

[Taylor Leese]

I tried the same thing and got the error below. Any ideas?

puppetdb=# create extension pg_trgm;

ERROR:  could not open extension control file "/usr/share/postgresql/9.3/extension/pg_trgm.control": No such file or directory

- Taylor

[Ken Barber]

> I tried the same thing and got the error below. Any ideas?
>
> puppetdb=# create extension pg_trgm;
>
> ERROR: could not open extension control file
> "/usr/share/postgresql/9.3/extension/pg_trgm.control": No such file or
> directory

Seems odd, pg_trgm should be shipped with PostgreSQL. Maybe its a bug
in that package or pg_trgm is shipped separately?

I can probably take a look here. What distro and distro release is
this exactly? Is this the PG shipped with the distro or is it from
PGDG upstream?

ken.

[Ellison Marks]

As a note, I installed from the postgres yum repositories, as the version in stock centos 6 was getting too old. I had to install the -contrib package to get that extension.

[Taylor Leese]

Ken - I'm using Ubuntu 14 and I installed PuppetDB via https://forge.puppetlabs.com/puppetlabs/puppetdb. I also should have noted that I got this error while upgrading from PuppetDB 2.1 to 2.2. Postgres is version 9.3.5 if I remember correctly. Admittedly, I'm not very familiar with Postgres.

Ellison - How would I go about installing this "contrib" package to see if that helps?

- Taylor

[Taylor Leese]

Ken - Also, to be even more specific I'm using the following version of the puppetdb module:

mod 'puppetlabs/puppetdb',   

  :git => 'g...@github.com:puppetlabs/puppetlabs-puppetdb.git',   

  :ref => 'ba3049796f89aec8fb2857cb04d7f6b4dd71c4dc'

[Ellison Marks]

If you're using ubuntu, It would probably just be

sudo apt-get install postgresql-contrib

You might need to restart postgres after that, not sure.

On Wednesday, October 1, 2014 2:10:40 PM UTC-7, Taylor Leese wrote:

[Taylor Leese]

That did the trick. Thanks.