postgres

In this article, we'll install PostgreSQL on Ubuntu 14.04, create separate users and databases, relax the postgres authentication to allow remote connections & connect to the server in a few different ways.

What I've found is that once you've managed to install & configure it correctly, PostgreSQL runs reliably in the background and hardly ever complains.

If you are new to it (coming from a MySQL background), you might get confused with how user accounts and authentication work. All you need to know right now is that PostgreSQL uses your operating system user accounts to control access to databases.

Actually, It's a lot more advanced than that, but that's enough to chew on for now.

Let's get things running:

Install & Configure your PostgreSQL Server

Log into Ubuntu & Update it

> ssh root@<your server>
> apt-get update
> apt-get upgrade

Install PostgreSQL 9.3

This step we usually do with the root account, so make sure you are in the correct user account.

> apt-get install postgresql postgresql-contrib

We don't usually install a web server on our database servers, but if that's something you are interested in, check out this article.

Once installed, PostgreSQL has a built in user account called postgres and it's your super user account for your database. Please note: you should not be connecting to your database from an external source using the super user account (bad things can & do happen).

Before moving on, let's check to see if the server is running:

> ps ax | grep postgresql

You should see something like this:

11207 ? S 0:18 /usr/lib/postgresql/9.3/bin/postgres...

Set up your new Linux User Accounts

The accounts you will set up here will be what you will be using to connect to your database. The connection strings you use in your applications will contain the usernames and passwords for these accounts.

> adduser <username>

I like to use the names of our products that will be connecting to the database as the OS usernames.

Please choose as a strong password for the new user accounts you choose here and be sure not to lose it.

You can confirm that the user has been created by typing (it should be at the end of the list):

> cat /etc/passwd

Update the postgres root User Password

First, we'll switch to our postgres super user account and change it's password:

> sudo su - postgres

You are now in the postgres user account session, next we'll log into PostgreSQL itself using the psql CLI and change it's password:

> psql
> \password

You'll be asked to provide a new password when the \password command runs.

Keep in mind that psql is an interactive terminal for working with PostgreSQL. It's commands are somewhat different to what you might be used to:

Here are a few examples:

\password       Change your password
\q              Exit psql
\l              List all databases accessible by the current account
\du             List roles
\c <db name>    Connect to a database
\dt             List all tables in a connected database
\d <table>      List columns in the selected table
\conninfo       Show information about your currect connection

Here's a useful cheatsheet with more commands.

Let's exit psql so we can move on:

> \q

Create new Roles & Database

First, we're going to create a new PostgreSQL role to allow us to connect connect our Linux account to our database:

> createuser --interactive

You'll be asked a few questions about this new role, including the name for it. Think of a role as a user account with some extra mapping ability.

Next, we'll create a database with the same name as our linux system user account that we created earlier:

> createdb <database name>

NB: If you previously created a user account called "my_application", the command would need to be createdb my_application.

Last thing here would be to change ownership of the newly created database to your new role:

> psql
> ALTER DATABASE <database name> OWNER TO <role name>;

The user you just created has no password so you can't connect remotely using it. We'll need to set a password for this account before proceeding:

> ALTER USER <username> WITH PASSWORD '<new password>';

Configure PostgreSQL to Allow Remote Connections

This part is quite simple, it requires you to edit two configuration files, but I won't be going into depth further than enabling remote connections and password protecting those. I've added a few reference articles at the end if you wish to continue reading about it.

First, you want to edit your /etc/postgresql/9.3/main/postgresql.conf file:

> nano /etc/postgresql/9.3/main/postgresql.conf

Look for this line:

#listen_addresses = 'localhost'

And change it to:

listen_addresses = '*'

We're telling PostgreSQL to list for connections from any source, not only locally.

Next, we'll need to specify rules in the postgres host based authentication file (pg_hba.conf):

> nano /etc/postgresql/9.3/main/pg_hba.conf

You will need to add the following two lines to the end of it:

host     all   all   0.0.0.0/0   md5
hostssl  all   all   0.0.0.0/0   md5

In short, this is telling postgres to allow connections from any IP/host to any database as long as the usernames & passwords match.

The md5 bit is one of a few postegresql authentication modes:

Lastly, you'll have to restart the postgres server after changing any of the configurations:

> service postgresql restart

If your server is in production and being used by thousands and you don't want to reboot it, you can simple run the following command as the postgres user:

> select pg_reload_conf();

Connect to your Database Remotely

There are various way to connect to your database server, here are the most common options:

1) Connecting to PostgreSQL using a connection string

postgresql://<username>:<password>@<server>:5432/<database>
2) Connecting to PostgreSQL via the Command Line
psql -h <server> -U <username> -d <database> -W

The -W tells psql that you will be entering a password.

3) Connecting to PostgreSQL from ActiveRecord
ActiveRecord::Base.establish_connection(
  adapter:   'postgresql',
  host:      '<server>',
  database:  '<database>',
  username:  '<username>',
  password:  '<password>',
  encoding:  'utf8',
  port:      5432
)

Another common thing to do is set your database connection as the DATABASE_URL globaly variable and access it in your code:

ActiveRecord::Base.establish_connection(ENV['DATABASE_URL'])
4) GUI Tools

There are a few nice GUI tools that make managing your PostgreSQL database pretty easy:

Backing up & Restoring your PostgreSQL Database

Backups are essential with any database server and it's quite simple to do with PostgreSQL.

> pg_dump -h <server> -p 5432 -U <username> <database> > <local file>

Restoring your database is also as simple with a single command:

> psql -h <server> -U <username> -d <database> < <local file>

Useful Links