Migration to PostgreSQL on Rails

In the field of relational databases, PostgreSQL is often the first choice. There are many reasons.
To name a few:

  • A JSON datatype (and JSON functions)
  • HSTORE datatype for storing non-relational data as key value pair (document-oriented database emulation possible)
  • Support for additional datatypes, such as Booleans and Ranges
  • Filter Based Indexes (index a particular column based on some filter)
  • LIMIT and OFFSET
  • Full Text Search Features
  • Geodata (by Postgis extension)

However, if the application is no longer a prototype, but should contain relevant and productive data, it makes sense to migrate to PostgreSQL.

Install PostgreSQL

First of all, the installation of PostgreSQL:

# Ubuntu or dIstribution of choice
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Oftentimes, the local user is set up with peer login. However, for all other users (for example the applications database account) should be md5 to demand password authentication:

# /etc/postgresql/9.5/main/pg_hba.conf
TYPE	DATABASE	USER		ADDRESS		METHOD
local	all		christian			peer            
local	all		all				md5

Please note, the configuration changes only take effect after restarting the Postgres server:

sudo service postgresql restart

In order to be able to deploy the database user for the application, the template database postgres has to be accessed by accounting with the system user postgres:

sudo -i -u postgres
psql postgres

The account can be created (but only with restricted rights):

create user alice with password 'AwEs0Me!' createdb;

PostgreSQL on Rails

The Ruby on Rails application requires the PostgreSQL adapter:

# Gemfile
gem 'pg'

The old database adapter (SQLite3, MySQL …) can be deleted.
After:

bundle install

The Postgres adapter can now be configured with the previously created database user in the database configuration file:

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  username: alice
  password: 'AwEs0Me!'

production:
  <<: *default
  database: awesome_app_production

The database is created:

rake db:create

Database migration

However, to transfer the data and structure from the old DBMS to Postgres, a few steps are necessary. For example, there has to be paid attention to database-specific data types.
Sure, a dump could be generated from the old database, the created SQL script can be modified manually and restored with pg_restore into the Postgres database. This approach is a bit cumbersome.
The migration with an ORM is a quicker approach. The ORMs job is to resolve the inconsistencies. A migration from SQLite3 to Postgres with Sequel:

gem install sequel
sequel -C sqlite://db/production.sqlite3 postgres://alice:AwEs0Me!@localhost/awesome_app_production

The database has been migrated and can be used afterwards.
In the Postgres console:

psql -U alice -d awesome_app_production

by listing available tables:

# awesome_app_production
\d+

 Schema	| Name			| Type		| Owner	| Size		| Description
--------+-----------------------+---------------+-------+---------------+-------------
 public | users			| table		| alice	| 8192 bytes	| 
 public	| schema_migrations	| table		| alice	| 16 kB		|