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.
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.
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:
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 |