Installing Postgresql extensions

[amee...@gmail.com]

I am in the process of building a rails app with geospatial capabilities. For this to work I need to install Postgis and enable its extension is the master and slave database. I have seen some solutions for doing such but would like some definitive direction. Please share your experiences for doing such.

Thanks   

[Kevin Menard]

Hi,

 

Your best best bet is to modify the generated config/rubber/deploy-postgresql.rb file.  In the bootstrap task, right after the "CREATE DATABASE" call, you'll want to add something like:

 

sudo -i -u postgres psql -c 'CREATE EXTENSION "postgis"' #{env.db_name}

 

Since PostGIS requires an extra package to be installed, you'll need to add that to your config/rubber/rubber-postgresql.yml, too.

 

In the "packages" section, you'll want to add something like:

 

packages: ["postgresql-#{postgresql_ver}", "postgresql-contrib-#{postgresql_ver}", "postgis"]

 

Please let me know if you have any other questions.

 

--

Kevin

 

 

On Sat, Jul 25, 2015, at 20:46, amee...@gmail.com wrote:

I am in the process of building a rails app with geospatial capabilities. For this to work I need to install Postgis and enable its extension is the master and slave database. I have seen some solutions for doing such but would like some definitive direction. Please share your experiences for doing such.

Thanks   

--

You received this message because you are subscribed to the Google Groups "rubber" group.

To unsubscribe from this group and stop receiving emails from it, send an email to rubber-ec2+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

 

[ameer singh]

Hi Kevin,

Thanks a lot. I will attempt it and let you know how it goes.

Ameer.

--
You received this message because you are subscribed to a topic in the Google Groups "rubber" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/rubber-ec2/msT5zvAqXh4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to rubber-ec2+...@googlegroups.com.

[Marchand]

A few add-ons for postgis:

On Sunday, July 26, 2015 at 7:53:40 PM UTC+2, Kevin Menard wrote:

Hi,

 

Your best best bet is to modify the generated config/rubber/deploy-postgresql.rb file.  In the bootstrap task, right after the "CREATE DATABASE" call, you'll want to add something like:

 

sudo -i -u postgres psql -c 'CREATE EXTENSION "postgis"' #{env.db_name}

              sudo -i -u postgres psql -c 'CREATE SCHEMA "postgis"' #{env.db_name}

              sudo -i -u postgres psql -c 'CREATE EXTENSION "PostGIS" WITH SCHEMA postgis' #{env.db_name}

              sudo -i -u postgres psql -c 'SET search_path "public, postgis"' #{env.db_name}

              sudo -i -u postgres psql -c 'GRANT ALL ON postgis.spatial_ref_sys TO PUBLIC' #{env.db_name}

              sudo -i -u postgres psql -c 'GRANT ALL ON postgis.geometry_columns TO PUBLIC' #{env.db_name}

 

Since PostGIS requires an extra package to be installed, you'll need to add that to your config/rubber/rubber-postgresql.yml, too.

 

In the "packages" section, you'll want to add something like:

 

packages: ["postgresql-#{postgresql_ver}", "postgresql-contrib-#{postgresql_ver}", "postgis"]

 

This turns out to be incomplete in my experience. 

A tid-bit: the process will load 9.4 versions when rubber-psotgresql.yml states

postgresql_ver: 9.3

postgis_ver: 2.1

#  postgresql_ver: 9.4

packages: ["postgresql-client-#{postgresql_ver}", libpq-dev, "postgresql-contrib-#{postgresql_ver}", "postgis", "postgresql-#{postgresql_ver}-postgis-#{postgis_ver}"]

** [out :: production.foo.com] Setting up postgresql-9.3-postgis-scripts (2.1.8+dfsg-5~97.git43a09cc.pgdg14.04+1) ...

 ** [out :: production.foo.com]

 ** [out :: production.foo.com] Setting up postgresql-client-9.4 (9.4.4-1.pgdg14.04+1) ...

Stating postgis alone is not sufficient.  Without digging into the code I assumed this is translated into an `sudo apt-get` call. that call needs to ends up specifying both postgis and the binding of a version to the postgresql version `install -y postgis postgresql-9.3-postgis-2.1`

[Kevin Menard]

On Mon, Sep 21, 2015, at 09:24, Marchand wrote:

A few add-ons for postgis:

 

On Sunday, July 26, 2015 at 7:53:40 PM UTC+2, Kevin Menard wrote:

Hi,

 

Your best best bet is to modify the generated config/rubber/deploy-postgresql.rb file.  In the bootstrap task, right after the "CREATE DATABASE" call, you'll want to add something like:

 

sudo -i -u postgres psql -c 'CREATE EXTENSION "postgis"' #{env.db_name}

 

 

              sudo -i -u postgres psql -c 'CREATE SCHEMA "postgis"' #{env.db_name}

              sudo -i -u postgres psql -c 'CREATE EXTENSION "PostGIS" WITH SCHEMA postgis' #{env.db_name}

              sudo -i -u postgres psql -c 'SET search_path "public, postgis"' #{env.db_name}

              sudo -i -u postgres psql -c 'GRANT ALL ON postgis.spatial_ref_sys TO PUBLIC' #{env.db_name}

              sudo -i -u postgres psql -c 'GRANT ALL ON postgis.geometry_columns TO PUBLIC' #{env.db_name}

 

 

Since PostGIS requires an extra package to be installed, you'll need to add that to your config/rubber/rubber-postgresql.yml, too.

 

In the "packages" section, you'll want to add something like:

 

packages: ["postgresql-#{postgresql_ver}", "postgresql-contrib-#{postgresql_ver}", "postgis"]

 

This turns out to be incomplete in my experience. 

 

A tid-bit: the process will load 9.4 versions when rubber-psotgresql.yml states

postgresql_ver: 9.3

postgis_ver: 2.1

#  postgresql_ver: 9.4

packages: ["postgresql-client-#{postgresql_ver}", libpq-dev, "postgresql-contrib-#{postgresql_ver}", "postgis", "postgresql-#{postgresql_ver}-postgis-#{postgis_ver}"]

 

Do you have more details of the wrong version being installed? I took the configuration from a production. As an FYI, if you need to lock postgis version down, you can do so by passing an array of the form: [packge_name, version].  See an example in the Passenger template of how to do it:

 

https://github.com/rubber/rubber/blob/3c9506a20cb690616e91d9df5baace3d14ac75d3/templates/passenger/config/rubber/rubber-passenger.yml#L12

 

PostgreSQL is an odd case because they embed the major version in the package name itself.

 

 

** [out :: production.foo.com] Setting up postgresql-9.3-postgis-scripts (2.1.8+dfsg-5~97.git43a09cc.pgdg14.04+1) ...

 ** [out :: production.foo.com]

 ** [out :: production.foo.com] Setting up postgresql-client-9.4 (9.4.4-1.pgdg14.04+1) ...

 

Stating postgis alone is not sufficient.  Without digging into the code I assumed this is translated into an `sudo apt-get` call. that call needs to ends up specifying both postgis and the binding of a version to the postgresql version `install -y postgis postgresql-9.3-postgis-2.1`

 

Can you elaborate on what you mean?  Everything from that packages list will be installed in a single 'apt-get install' command, much like you're showing here.  The versions are bound: "postgresql-#{postgresql_ver}-postgis-#{postgis_ver}" will do variable interpolation taking the values from the YAML configuration above. In this case, it'll resolve to "postgresql-9.3-postgis-2.1", which seems to align with what you think it should be.

 

--

Kevin

[Marchand]

Do you have more details of the wrong version being installed? 

Unfortunately not.  I've been iterating through this many times and cannot say for sure.

However AWS docs state that other extensions need to be installed.  Having done a manual process successfully, and after a bootstrap interrupt verified as postgres user

I always check that postgis is available:

      SELECT PostGIS_full_version();

and after a(n apparent) successful bootstrap

ERROR:  function postgis_full_version() does not exist

LINE 1: SELECT PostGIS_full_version();

      CREATE SCHEMA postgis;

      CREATE EXTENSION PostGIS WITH SCHEMA postgis;

      SET search_path = public, postgis;

      CREATE EXTENSION fuzzystrmatch WITH SCHEMA postgis;

      CREATE EXTENSION postgis_tiger_geocoder; # works RARELY, have written to aws about it

      CREATE EXTENSION postgis_topology;

I also 

      GRANT ALL ON postgis.spatial_ref_sys TO PUBLIC;

      GRANT ALL ON postgis.geometry_columns TO PUBLIC;

then SELECT PostGIS_full_version(); returns an acceptable string.

So that sequence works (even if it states that the objects already exist).

However, migrations fail  (undefined method `point' for #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter:)

and checking anew as postgres user, SELECT PostGIS_full_version(); returns the same error. Thus something is not 'sticking'...

Given my manual check and forcing of the values as postgres I am wondering whether it is a permissions issue and the config file being faulty...

Stating postgis alone is not sufficient.  Without digging into the code I assumed this is translated into an `sudo apt-get` call. that call needs to ends up specifying both postgis and the binding of a version to the postgresql version `install -y postgis postgresql-9.3-postgis-2.1`

 

Can you elaborate on what you mean?  Everything from that packages list will be installed in a single 'apt-get install' command, much like you're showing here.  The versions are bound: "postgresql-#{postgresql_ver}-postgis-#{postgis_ver}" will do variable interpolation taking the values from the YAML configuration above. In this case, it'll resolve to "postgresql-9.3-postgis-2.1", which seems to align with what you think it should be.

Yes, you interpreted my non-specificity correctly!  I configured it as such and have verified that the packages get installed. This is the present configuration:

deploy-postgresql.rb

            rubber.sudo_script "create_master_db", <<-ENDSCRIPT

              sudo -i -u postgres psql -c "#{create_user_cmd}"

              sudo -i -u postgres psql -c "#{create_replication_user_cmd}"

              sudo -i -u postgres psql -c "CREATE DATABASE \"#{env.db_name}\" WITH OWNER #{env.db_user}"

              sudo -i -u postgres psql -c 'CREATE SCHEMA "postgis"' #{env.db_name}

              sudo -i -u postgres psql -c 'CREATE EXTENSION PostGIS WITH SCHEMA postgis' #{env.db_name}

              sudo -i -u postgres psql -c 'SET search_path = public, postgis' #{env.db_name}

              sudo -i -u postgres psql -c 'CREATE EXTENSION fuzzystrmatch WITH SCHEMA postgis' #{env.db_name}

              sudo -i -u postgres psql -c 'CREATE EXTENSION postgis_tiger_geocoder' #{env.db_name}

              sudo -i -u postgres psql -c 'CREATE EXTENSION postgis_topology' #{env.db_name}

              sudo -i -u postgres psql -c 'GRANT ALL ON postgis.spatial_ref_sys TO PUBLIC' #{env.db_name}

              sudo -i -u postgres psql -c 'GRANT ALL ON postgis.geometry_columns TO PUBLIC' #{env.db_name}

            ENDSCRIPT

and rubber-postrgesql.yml

[...]

postgresql_ver: 9.4  # yep, it installs 9.4.4

postgis_ver: 2.1

postgresql_conf_dir: "/etc/postgresql/#{postgresql_ver}/main"

postgresql_data_dir: "/mnt/postgresql/#{postgresql_ver}/data"

postgresql_archive_dir: "/mnt/postgresql/#{postgresql_ver}/archive"

postgresql_pid_file: "/var/run/postgresql/#{postgresql_ver}-main.pid"

postgresql_ctl: "/usr/bin/env service postgresql"

# Capistrano needs db:primary role for migrate to work,

# so we might as well just make consistent across the board

role_dependencies:

  postgresql_master: [postgresql, "db:primary=true"]

  postgresql_slave: [postgresql, db]

  "db:primary=true": [postgresql, postgresql_master]

  db: [postgresql, postgresql_slave]

packages: ["postgresql-client-#{postgresql_ver}", libpq-dev, "postgresql-contrib-#{postgresql_ver}", "postgis", "postgresql-#{postgresql_ver}-postgis-#{postgis_ver}"]

gems: [pg]

roles:

  postgresql_master:

    db_server_id: 1

  postgresql_slave:

    db_server_id: 2

  db:

    packages: ["postgresql-client-#{postgresql_ver}", libpq-dev, "postgresql-contrib-#{postgresql_ver}", "postgis", "postgresql-#{postgresql_ver}-postgis-#{postgis_ver}"]

I have reached this stage upon `executing "sudo -p 'sudo password: '  bash -l /tmp/create_master_db"`

 ** [out :: production.foo.com] CREATE ROLE

 ** [out :: production.foo.com] CREATE ROLE

 ** [out :: production.foo.com] CREATE DATABASE

 ** [out :: production.foo.com] CREATE SCHEMA

 ** [out :: production.foo.com] CREATE EXTENSION

 ** [out :: production.foo.com] SET

 ** [out :: production.foo.com] CREATE EXTENSION

 ** [out :: production.foo.com] ERROR:  function soundex(character varying) does not exist

 ** [out :: production.foo.com] HINT:  No function matches the given name and argument types. You might nee

[Marchand]

I can confirm after multiple attempts what follows...

Both with `postgresql_ver: 9.3` and `postgresql_ver: 9.4` the stack bootstraps only when commenting out:

# sudo -i -u postgres psql -c 'CREATE EXTENSION postgis_tiger_geocoder' #{env.db_name}

When defining version in rubber-postgresq 9.3, psql declares both `psql (9.4.4, server 9.3.9)`

In both instances, when verifying as postgres user 

    SELECT PostGIS_full_version();

fails, notwithstanding when executing

    executing "sudo -p 'sudo password: '  bash -l /tmp/create_master_db"

the search path passed with the confirmation: 

    ** [out :: production.foo.com] SET

Setting as postgres user:

    SET search_path = public, postgis;

generates a PostGIS full_version, 

Observation.  When bootstrapping (I had missed this), the reconfigure postgresql script (line 117 of deploy-postgresql) hits a permission issue

  * executing "sudo -p 'sudo password: '  bash -l /tmp/reconfigure-postgresql"

    servers: ["production.foo.com"]

    [production.foo.com] executing command

    command finished in 792ms

  * executing "sudo -p 'sudo password: ' -u postgres /usr/lib/postgresql/9.4/bin/initdb -D /mnt/postgresql/9.4/data"

    servers: ["production.foo.com"]

    [production.foo.com] executing command

 ** [out :: production.foo.com] could not change directory to "/root": Permission denied

 ** [out :: production.foo.com]

 ** [out :: production.foo.com] The files belonging to this database system will be owned by user "postgres".

 ** [out :: production.foo.com]

 ** [out :: production.foo.com] This user must also own the server process.

relevant?

In both cases, deploy fails when hitting a t.point column definition, notwithstanding postgres setting `SET search_path = public, postgis;` and having a proper PostGIS_full_version

 ** [out :: production.foo.com] rake aborted!

 ** [out :: production.foo.com] StandardError: An error has occurred, this and all later migrations canceled:

 ** [out :: production.foo.com] undefined method `point' for #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::Tabl

Attempting manually on the server 

    cd /mnt/app-production/releases/20150922134123 && bundle exec rake RAILS_ENV=production  db:migrate

a couple of minor issues:

1) => Rails Error: Unable to access log file. Please ensure that /mnt/ride-production/releases/20150922134123/log/production.log exists and is chmod 0666. 

2) other log file from other gem stops migration cold:     Errno::EACCES: Permission denied - log/xxx.log

 which is a side question: how/where to declare these?

the migration halts at same point:

StandardError: An error has occurred, this and all later migrations canceled:

undefined method `point' for #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::TableDefinition:0x000000053b7978>/mnt/ride-production/shared/bundle/ruby/1.9.1/gems/rgeo-activerecord-0.5.0/lib/rgeo/active_record/common_adapter_elements.rb:107:in `method_missing'

and checking immediately after, postgres user has a PostGIS_full_version

Other observation: restarting the server, postgres user does lose the PostGIS_full_version and needs to set the search path anew.

[Sorry, i hesitated on whether to create a new thread, but in case someone stumbled upon this... may be better here]

[Marchand]

Update.

Turns out there is a rubber/common/database.yml  that overrides the traditional database.yml under the app's config directory (not too DRY-ish, eh?).

Anyhoo, the adapter there is naturally set to postgresql; changing it to postgis, alters behaviour in a more expected way.

However, migrations don't start:

`ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "geometry_columns" does not exist [...]: SELECT * FROM geometry_columns WHERE f_table_name='schema_migrations'`

that is usually a sign that the extension is not loaded.   Ahhhh but postigis is active `POSTGIS="2.1.8 r13780" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER` and `dire_production` is owned by `dire`.

Manually ran `GRANT ALL ON postgis.geometry_columns TO PUBLIC` and then the migration, but no behavioural modification.