Ruby on Rails c нуля!

A few months ago I throught about migrating from MySQL to PostgreSQL. PostgeSQL has more features and is fast, stable and secure relative DB. And it is also free, open-source and has many different extensions. The main drawback of PostgreSQL is that it is more complicated than MySQL, so you should spend more time to learn how to work with it or hire an admin.

In this series of articles I want to describe how to drop MySQL, start to use PostgreSQL and how to be happy with this powerfull solution without hiring a DBA (only if you doesnt work with big and high-loaded projects).

Let’s start by installing PostgreSQL on your development machine.

I work on Ubuntu Linux 12.10 so I will describe process of installing PostgreSQL on Ubuntu 12.10. To install PostgreSQL 9.2 on your Ubuntu you should use this command in your terminal:

$ sudo apt-get install postgresql-9.2

After installing PostgreSQL you can check PostgreSQL version on your machine:

$ psql —version
psql (PostgreSQL) 9.2.2

So now you have PostgreSQL installed on your Ubuntu 12.10 machine.

You can also install PostgreSQL not only from PPA repository, but from the source code. Here I will show you how to do this:

$ wget http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.gz
$ tar fvxz postgresql-9.2.2.tar.gz
$ cd postgresql-9.2.2
$ ./configure
$ make
$ sudo make install

Than check PostgreSQL version:

$ psql —version
psql (PostgreSQL) 9.2.2

After installing PostgreSQL 9.2 you should create the PostgreSQL user:

$ sudo useradd postgres
$ sudo passwd postgres

Now you should work with your PostgreSQL only from the postgres user because of security reasons.

After installing PostgreSQL and creating postgres user you should create the PostgreSQL data directory. The standart path is /usr/local/pgsql/data so you should do:

$ sudo mkdir /usr/local/pgsql/data

And add privileges to work with it for postgres user:

$ sudo chown postgres:postgres /usr/local/pgsql/data

Than you should initialize the data directory:

$ cd /usr/local/pgsql/bin/
$ su postgres
$ ./initdb -D /usr/local/pgsql/data

And after this you can start PostgreSQL server with:

$ ./postgres -D /usr/local/pgsql/data

or like this:

$ ./pg_ctl -D /usr/local/pgsql/data

Now you can check the PostgreSQL processes:

$ ps aux | grep postgres

postgres 11137  0.0  0.0  63976  1752 pts/3    S    10:23   0:00 su postgres
postgres 11145  0.0  0.0   4396   684 pts/3    S+   10:23   0:00 sh
postgres 11204  0.0  0.0  55944  6668 pts/3    S    10:28   0:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
postgres 11207  0.0  0.0  55944   908 ?        Ss   10:28   0:00 postgres: checkpointer process                        
postgres 11208  0.0  0.0  55944   916 ?        Ss   10:28   0:00 postgres: writer process                              
postgres 11209  0.0  0.0  55944   912 ?        Ss   10:28   0:00 postgres: wal writer process                          
postgres 11210  0.0  0.0  56640  1992 ?        Ss   10:28   0:00 postgres: autovacuum launcher process                 
postgres 11211  0.0  0.0  24320   812 ?        Ss   10:28   0:00 postgres: stats collector process 

Here you see that PostgreSQL is running under the postgres user.

To stop the PostgreSQL server run this commend your terminal:

$ ./pg_ctl stop -D /usr/local/pgsql/data

You can also restart PostgreSQl with this:

$ ./pg_ctl restart -D /usr/local/pgsql/data

Let’s create our first database

You can create a new empty database from the PostgreSQL console (psql) or with special utility. Here is how you can do this:

$ psql -h localhost
psql (9.2.2)
Type «help» for help.

postgres=# create database my_test_database;
CREATE DATABASE

To get the list of databases use ‘\l’ command:

postgres=# \l

                                     List of databases
       Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------------+----------+----------+-------------+-------------+-----------------------
 my_test_database | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                  |          |          |             |             | postgres=CTc/postgres
 template1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                  |          |          |             |             | postgres=CTc/postgres
(4 rows)

To drop a database use this command:

postgres=# drop database my_test_database;

The second way to create a database is using the createdb utility. Here I will show how to do this:

$ ./createdb my_test_database_2

For now you can install PostgreSQL 9.2 on Ubuntu and create empty databases with it. In the next chapter we will learn more about configuring PostgreSQL.