MySQL is just about the most popular database in use. However, it's not the only database you should consider! While seemingly simple, there are many pitfalls one can stumble into.

For example, MySQL may not treat your data like your expect. Text length longer than a string limit will simply be cut, rather than raising an error. Additionally, type checking is less strict, possibly resulting in data inconsistencies when using it in your code.

This is not to to say MySQL is "bad". Instead, MySQL has a different point of view on how it should behave. Read more in the resources section at the bottom of this edition.

Many people prefer PostgreSQL for its data strictness. Others use it for its differentiating functionality. Some people (like me!) are just curious.

No matter which camp you sit in, let's dive in and see how to use PostgreSQL!

Install

First we need to install PostgreSQL. The latest release is 9.3.

If you're running Ubuntu 14.04, you don't need to add any repositories to get this version.

Older versions of Ubuntu may require the addition of the following repositories to get the latest version of PostgreSQL:

# Get 9.3 on ubuntu < 14.04
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
     sudo apt-key add -
sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" | \
     sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt-get update

Once the repositories are added (if you are adding them), we can install PostgreSQL with the following two packages:

# Install on any Ubuntu
sudo apt-get install -y postgresql postgresql-contrib

Start the service after it's installed if it's not already running:

sudo service postgresql start

Config

After installing PostgreSQL, it's almost ready to go. We can interact with it on the command line. First, however, let's see some basic configuration or use with a web application.

Configuration for Pgsql is located in a version-specific directory. On Ubuntu, these are found at /etc/postgresql/9.3/main. We'll edit various files in that directory.

Listen for Network Connections

By default, PostgreSQL listens on the local socket file /var/run/postgresql. We can, however, listen on a network to connect to it remotely:

sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" \
     /etc/postgresql/9.3/main/postgresql.conf

This edits the /etc/postgresql/9.3/main/postgresql.conf file and configures the listen_addresses directive to listen on all networks via the * setting.

You may not want Pgsql to listen on all networks. We can modify the listen_addresses directive some more to lock down access.

Here are some examples:

# Listen on all available ipv4 and ipv6 networks
listen_addresses = '0.0.0.0,::'

# ...or... Listen on a specific ipv4 network:
listen_addresses = '10.1.10.11'

# ...or... Listen on localhost and two specific ipv4 networks
listen_addresses = '127.0.0.1,10.1.10.11,192.168.33.10'

System Users

Unlike MySQL, Pgsql will create system users which can be used to run commands on the database. These users will be assigned a role within Pgsql, allowing the system user to be used as a Pgsq user.

After install Pgsql, you may notice the user postgres now exists. Log in as that user and try to enter the Pgsql CLI tool:

# Switch to user "postgres"
sudo su postgres

# Enter psql, the CLI tool for Postgres
psql

You'll then enter a psql shell. You can run many commands here. All commands, except for SQL queries, begin with a backslash character \.

# Listen all databases (and templates)
postgres=# \list

You likely have a blank database called "postgres" in addition to a few templates.

You can connect to that database using the \c command:

postgres=# \c postgres

Don't get confused between the user and database name. We're logged in as user "postgres" and happen to be looking at a database also named "postgres". Databases can be, but aren't required to be, created for each user.

This database doesn't have any tables. However, we can use \dt to list any "relations" (tables):

# List tables after connecting to database "postgres"
postgres=# \dt

There are no tables after a clean install, so let's just quit out of the psql tool for now. Use \q:

postgres=# \q

This is great when we're logged into a server. However, we need a way to login into the database from a web application!

We can do that by creating a role. This role will act as a user to connect with from code.

Roles & Users

We can control client authentication configuration in the /etc/postgresql/9.3/main/pg_hba.conf file. This sets how users can log into Pgsql.

To allow users access to a database, we can add a host entry to define authentication in the following format:

host database user address auth-method [auth-options]

The line will start with host. Then we can define a database allowed, the user allowed, and from what hosts the user can connect. Then we add the allowed authentication method/options.

Let's add a host entry for a future role we'll create. The host setting will allow access to all databases, from all users, from a specific range of network addresses. It expects passwords to be hashed using md5.

# Append the following to the pg_hba.conf file
echo "host    all         all         10.0.2.0/24           md5" | \
    tee -a /etc/postgresql/9.3/main/pg_hba.conf

That will allow connections using any user to any database as long as they connect from host 10.0.2.0/24, which is a range of IP addresses from 10.0.2.0 to 10.0.2.255.

By default, PostgreSQL will allow connections from "localhost" to any database from any user. Here we're allowing connections from the private-network 10.0.2.* as well.

For more information on the possibilities within this configuration file, see here: http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html.

After the preceeding configurations are made, you can start or restart PostgreSQL:

sudo service postgresql restart

Create New Role

System users can be users of Pgsql. However, there are also "roles". Roles are separate from system users. They can act as a user, or encapsulate a group of users. (You can assign a role to multiple users).

To connect to pgsql from your code base, we can create a role and treat it like a user.

sudo -u postgres psql -c "CREATE ROLE myloginuser LOGIN UNENCRYPTED PASSWORD 'somepassword' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;"

Here's what we did:

• sudo su -u postgres - Run the command as user "postgres"
• psql -c - Run the psql command. The -c flag lets us define a string with a command to use.
• The SQL used will then create a role
  • CREATE ROLE myloginuser - Creates a new role named "mloginuser".
  • LOGIN - Allow user to login
  • PASSWORD 'somepassword' - Define password as "somepassword".
  • NOSUPERUSER - The user is not a superuser
  • INHERIT - Role inherits privileges of other roles it is a member of
  • NOCREATEDB - Cannot create new databases
  • NOCREATEROLE - Cannot create other roles
  • NOREPLICATION - Cannot enable or be used for replication

There is more on roles and permissions here: http://www.postgresql.org/docs/8.1/static/user-manag.html

Also read more on creating roles here: http://www.postgresql.org/docs/9.3/static/sql-createrole.html (get information on above command there!)

There is also the createuser command. Try using createuser --interactive to create a new role!

Once a role/user is created, you can view roles from the psql tool:

# Output created roles
sudo -u postgres psql -c 'select * from pg_roles;'

Create DB

Finally we can create a database that our new user can access:

# Assuming you created role/user named "mylogiuser"
sudo -u postgres createdb --owner=myloginuser my_db_name

This creates a new database named "my_db_name", owned by our new role "myloginuser".

Explore the psql shell

Let's see what we've done by exploring the psql tool a bit more. Run the psql tool as user "postgres":

sudo -u postgres psql

Then we can use some familiar and new commands:

# List databases
postgres=# \list
List of databases
    Name    |    Owner    | Encoding  | Collate | Ctype |   Access privileges
------------+-------------+-----------+---------+-------+-----------------------
 my_db_name | myloginuser | SQL_ASCII | C       | C     |
 postgres   | postgres    | SQL_ASCII | C       | C     |
 template0  | postgres    | SQL_ASCII | C       | C     | =c/postgres          +
            |             |           |         |       | postgres=CTc/postgres
 template1  | postgres    | SQL_ASCII | C       | C     | =c/postgres          +
            |             |           |         |       | postgres=CTc/postgres
(4 rows)

We see our created database (owned by role "myloginyser"). We can also see the "out of the box" database postgres. Finally, there are two template databases.

Templates

Pgsql creates new databases by copying other ones. By default, it will copy template1, but you can also use template0, which comes with less assumptions (chiefly, no character encoding information).

Let's create our own template. This might be useful for creating UTF8-friendly databases.

First - create a new database based off of template0. We'll do this as a SQL command, as we're already logged into the psql tool:

postgres=# CREATE DATABASE template2
           TEMPLATE template0
           ENCODING 'UTF8'
           LC_COLLATE = 'en_US.UTF-8'
           LC_CTYPE = 'en_US.UTF-8';

Now we have a template named "template2" to create new UTF-8 friendly databases!

I'm testing this on Docker, which did not have UTF-8 locales installed by default. I had to run the command $ locale-gen en_us.UTF-8 to make it available. Then I was able to create a new template using UTF-8 character sets.

You may find that on normal systems, you already have a UTF-8 template available to you to use.

Exploring a Database

While still in the psql tool, let's drop the my_db_name database and re-create it using our new template:

# Drop data
postgres=# DROP DATABASE my_db_name;

# Re-create based off new template and owner
postgres=# CREATE DATABASE my_db_name
           TEMPLATE template2
           OWNER myloginuser;

Then we can connect to the new database and create a new table:

postgres=# \c my_db_name
my_db_name=# CREATE TABLE users (
    id serial PRIMARY KEY,
    email varchar(100) NOT NULL,
    password varchar(255) NOT NULL
);

Let's inspect that database:

# List tables
my_db_name=# \dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

# Explain the table "users"
my_db_name=# \d+ users
                                                      Table "public.users"
  Column  |          Type          |                     Modifiers                      | Storage  | Stats target | Description
----------+------------------------+----------------------------------------------------+----------+--------------+-------------
 id       | integer                | not null default nextval('users_id_seq'::regclass) | plain    |              |
 email    | character varying(100) | not null                                           | extended |              |
 password | character varying(255) | not null                                           | extended |              |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Has OIDs: no

Great, now we can insert some data:

# Insert data
my_db_name=# INSERT INTO users (email, password)
             VALUES ('me@example.com', 'plaintext!');

# Select that data
my_db_name=# SELECT * FROM users;
 id |     email      |  password
----+----------------+------------
  1 | me@example.com | plaintext!
(1 row)

From Code

That's all great, but let's make sure we can connect to this database from code!

I'll use some PHP. If you don't have PHP installed, we can quickly install the basic dependencies to use PosgreSQL:

# On Ubuntu:
sudo apt-get install -y php5 php5-cli php5-pgsql

Now let's create a quick script to connect to our database:

# File: pgsql.php
try
{
    $db = new PDO('pgsql:dbname=my_db_name;host=127.0.0.1',
        'myloginuser',
        'somepassword');
} catch(\Exception $e)
{
    echo $e->getMessage();
}

echo "Connected\n";

Note that I'm assuming the listen_addresses directive (configured in the beginning of this edition) is listening on localhost 127.0.0.1!

Now save the file and run this! If all is well, you should see that we're connected.

$ php pgsql.php
Connected

Let's expand on that just a little:

# File: pgsql.php
try
{
    $db = new PDO('pgsql:dbname=my_db_name;host=127.0.0.1',
        'myloginuser',
        'somepassword');

    # Send errors as exceptions
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    # Run a query
    $sql = "SELECT * FROM users;"
    $results = $db->query($sql);

    foreach($results as $row)
    {
        var_dump($row);
    }
} catch(\Exception $e)
{
    echo $e->getMessage();
}

This will give us an error!

$ php pgsql.php
SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for relation users

It turns out that tables will be owned by the user who created them. Above, we created the table using user "postgres". User "myloginuser" doesn't have permission to read from that table.

Let's drop this table and recreate it in code, so that user myloginuser owns the table.

sudo -u postgres psql -c "DROP TABLE my_db_name.users";

Then we can create it in code, insert some data, and select it:

# File: pgsql.php
try
{
    $db = new PDO('pgsql:dbname=my_db_name;host=127.0.0.1',
        'myloginuser',
        'somepassword');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $db->query(create_sql());
    $db->query(insert_sql());

    $selectSQL = "SELECT * FROM users";
    $results = $db->query($selectSQL);

    foreach($results as $row)
    {
        var_dump($row);
    }

} catch(\Exception $e)
{
    echo $e->getMessage()."\n";
}

function create_sql()
{
    return "CREATE TABLE users (
    id serial PRIMARY KEY,
    email varchar(100) NOT NULL,
    password varchar(255) NOT NULL
)";
}

function insert_sql()
{
    return "INSERT INTO users (email, password) VALUES ('me@example.com', 'plaintext!')";
}

Save that and run it (just once!):

$ php pgsql.php
array(6) {
  ["id"]=>
  int(1)
  [0]=>
  int(1)
  ["email"]=>
  string(14) "me@example.com"
  ["password"]=>
  string(10) "plaintext!"
}

This successfully creates the table, inserts data, and retrieves that data without error! Because the user "myloginuser" created the table, it also owns it (and had permission to read/write to it).

Resources

• Here's a video with some examples of what MySQL might do to your data.
• Here's a good write up on MySQL vs PosgreSQL