multiple_psql.md

Maintaining multiple PostrgeSQL versions in one environment

Use Docker

---

Install multiple PostgreSQL versions on your machine

Linux

Install the versioned PostgreSQL packages using your package manager. You should remove postgresql if you had it installed before.

Mac with Homebrew

If system is not new you can check what you already have installed.

    $ brew list                           -- check what is already installed
    $ brew search postgresql              -- search local taps (what is available for install) 

Install versions that you need:

    $ brew tap petere/postgresql
    $ brew install postgresql-9.6

Maintaince of multiply PG versions in one env need to be managed. One option is to use PostgreSQL formulae for the Homebrew package manager, created by Peter Eisentraut, PG contributor.

    $ brew install postgresql-x.y         -- any x.y version you use in other applications

If you have default PostreSQL installed remove it. You are safe to do it since each versioned package provide the same functionality.

    $ brew uninstall postrgresql
    $ rm -rf /usr/local/var/postgres

You will manage versions using 'postrgresql-common package', available to install from tap command you just called.

    $ brew install postgresql-common
    $ pg_createcluster 9.6 main            -- it will be created at default port 5432 or first available
    $ pg_createcluster x.y main            -- at port with incremented number for subsequent clusters

How to use it

Available commands:

    $ pg_createcluster [options] <version> <name>
    $ pg_lsclusters                            -- list clusters
    $ pg_dropcluster <version> <name>
    $ pg_ctlcluster <version> <name> <action>  -- start/stop/restart/reload

PostgreSQL interactive terminal:

    $ psql --cluster 9.6/main   <-d postgres> -- client command  (option to test using an existing <-d template1> if you do not have db yet)
    $ psql                                     -- will start last available version 
    $ psql -p 5432                             -- will start using a port number

Configuring Postgres

Postres works out of the box. It autmatically creates the default user postgres, with no password. Run postgres console to check env. Second command lists installed users.

$ psql -p port_number -U postgres

$postgres=# \du

To keep your database secure create a new user with chosen privileges either from psql using SQL or using postgres commands directly from teminal. In postgres terminal:

postgres=# CREATE ROLE username WITH LOGIN PASSWORD 'user_password' [OPTIONS];
postgres=# ALTER ROLE username CREATEDB;

Configure Rails with Postgres

In config/database.yml connect to PostreSQL. In Rails you need to explicitly connet through localhost (local socket, otherwise psql will connect via TCP/IP)

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  host: localhost
  username: <%= USER_NAME %>
  password: <%= USER_PASSWORD %>
  port: <%= ENV['POSTGRESQL_96_PORT'] %>

development, test:
  <<: *default
  database: <%= REPOSITORY_NAME %>_development
  
test:
  <<: *default
  database: <%= REPOSITORY_NAME %>_test 

You can to create & migrate database now. You need to make sure env variables are loaded or temporary directly included in database.yml file before your run rake commands. The reason is enviromental varialbles are loaded slightly differently in development than in production. ENV variables will return nil unless you are using gems like dotenv or figaro or directly typed then in database.yml file.

Run:

rake db:create
rake db:migrate

Rails, check connection to database

For Rails applications check in Rails console the currently running PG version from your Rails application

    ActiveRecord::Base.connection.select_value('SELECT version()')