If you're like me, you put off switching from MySQL to Postgres for way too long because you couldn't figure out how to get Postgres working. So here I am documenting how to install the software, create a database, and connect to it a few different ways. It's not really that difficult, just different.

Note that if you're setting up a new project, PHPProjectInitializer will generate a lot of the boilerplate for you.

Install Postgres

sh$ sudo apt-get install postgresql-9.3 postgresql-client-common postgresql-client-9.3

Connect to postgres as the 'postgres' user and poke around

sh$ sudo su postgres -c psql

Maybe list the existing databases:

postgres=# \l

List client commands:

postgres=# \?

Create a new database and a user to manage it

postgres=# CREATE DATABASE "coolnewdatabase";
postgres=# CREATE USER "coolnewuser" WITH PASSWORD 'Mah pass werd';
postgres=# GRANT ALL PRIVILEGES ON DATABASE "coolnewdatabase" TO "coolnewuser";

Create a script to connect as the new user

This is pretty much necessary if you want to connect without having to type a password every time.

sh$ mkdir -p ~/bin && nano ~/bin/coolnewdatabase-psql && chmod +x ~/bin/coolnewdatabase-psql

Script contents:


export PGPASSWORD='Mah pass werd'
exec psql coolnewdatabase -U coolnewuser -h localhost -p 5432 "$@"

Install the Postgres extension for PHP

sh$ sudo apt-get install php5 php5-cli php5-pgsql

That will install the Postgres PDO extension.

Connect from PHP

I will leave the rest of the project set-up to you, but if you were to configure Doctrine to connect to your new database, the configuration options would look like this:

    "dbname": "coolnewdatabase",
    "host": "localhost",
    "user": "coolnewuser",
    "password": "Mah pass werd",
    "driver": "pdo_pgsql"

In PHPProjectInitializer-style projects, this would be in config/dbc.json.