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 |