PostgreSQL database

This is the third in a row of databases with built-in support in Ruby on Rails. Similarly to MySQL we will skip installation and will use server from .zip archive. Go to PostgreSQL Windows download section and download zip archive. Unpack archive to the root of your C: drive (or any other one if you want). New directory pgsql will be created and all files will be extracted there. The very first thing we have to do is to initialize server instance. This will create all necessary files needed for PostgreSQL server. Go to the bin sub-directory within pgsql directory and execute initdb command and pass it directory where data files will be stored. Output should look similar to the one given below:

C:\pgsql\bin>initdb -D ..\data
The files belonging to this database system will be owned by user "bosko".
This user must also own the server process.

The database cluster will be initialized with locale "Serbian (Latin)_Serbia.1250".
The default database encoding has accordingly been set to "WIN1250".
initdb: could not find suitable text search configuration for locale "Serbian (Latin)_Serbia.1250"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory ../data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... windows
creating configuration files ... ok
creating template1 database in ../data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... not supported on this platform
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    "postgres" -D "..\data"
or
    "pg_ctl" -D "..\data" -l logfile start

As output of the initdb command shows us command for starting database server:

C:\pgsql\bin>postgres -D ..\data

Let's check whether our server is working. Don't forget to change 'bosko' with the user name under which you are working in Windows in the command below (this name is displayed in the first line of the log message of initdb command):

C:\pgsql\bin>psql -d template1 -U bosko
psql (9.4.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

template1=# \l
                                              List of databases
   Name    | Owner | Encoding |           Collate           |            Ctype            | Access privileges
-----------+-------+----------+-----------------------------+-----------------------------+-------------------
 postgres  | bosko | WIN1250  | Serbian (Latin)_Serbia.1250 | Serbian (Latin)_Serbia.1250 |
 template0 | bosko | WIN1250  | Serbian (Latin)_Serbia.1250 | Serbian (Latin)_Serbia.1250 | =c/bosko         +
           |       |          |                             |                             | bosko=CTc/bosko
 template1 | bosko | WIN1250  | Serbian (Latin)_Serbia.1250 | Serbian (Latin)_Serbia.1250 | =c/bosko         +
           |       |          |                             |                             | bosko=CTc/bosko
(3 rows)


template1=#

If you see above lines your PostgreSQL server is running and ready. It is now time to create database for our rwin_book application. For this you can use GUI application pgAdmin3.exe or already started psql console client. I will stick with the console application. In the psql prompt execute following command:

postgres=# create database rwin_book_devel;

In two chapters dedicated to native gems installation we installed sqlite3 and mysql2 gems so we were ready for these databases. For the PostgreSQL server we are still missing gem extension needed to connect to it. Before we configure our Rails application to use new database we have to install it. Add new line to Gemfile file.

gem 'pg'

and execute bundle install. At the time of writing this book, installed version is 0.18.1 which already has Windows binaries. Finally we have to configure Rails application to use new database. For that use postgresql for adapter name in database.yml and you should also change username to the name of Windows account under which PostgreSQL was initialized. As we did with previous databases we can now start database migration:

This ends our journey through built-in database engines support. All of them are functional and it is up to you which one you will use in Rails applications.