Database: Postgres

Introduction

We use PostgresSQL, "the world's most advanced open source database". It's a transactional, ACID-compliant, object-relational database that implements SQL. Pretty baller. Currently we are on version 9.3.9.

Usage

1. Accessing Postgres

Local

In vagrant, the database is owned by the postgres user. To interact with the database you'll need to switch users: sudo su postgres

(venv)vagrant@vagrant-ubuntu-trusty-64:/opt/secondfunnel/app$ sudo su postgres
postgres@vagrant-ubuntu-trusty-64:/opt/secondfunnel/app$

Remember to exit when you are done to return to default user (vagrant in your virtual env)!

postgres@vagrant-ubuntu-trusty-64:/opt/secondfunnel/app$ exit
exit
(venv)vagrant@vagrant-ubuntu-trusty-64:/opt/secondfunnel/app$

Remote

On stage and production, the database is owned the willet user. When you ssh in, you should be connecting as that user.

2. Using Postgres

For the most part, the database is managed automatically by Django. Modifications to our data-model are handled by database migrations created for us by South. Sometimes you need to get your hands dirty. There are two main ways to work with Postgres: a) client applications and utilities and b) the psql interactive terminal. Both can be scripted.

Client applications and utilities

We frequently use pg_dump and pg_restore for getting database snapshots and backups. You can see some uses in /fabfile/database.py

• pg_dump documentation
• pg_restore documentation
• Complete list and documentation of Postgres client applications and utilities

The psql interactive terminal:

In the interactive terminal you can inspect and modify the database. In the vagrant environment, the database name is sfdb. See the vagrant/stage/production environment .yml files in /ansible/group_vars/ for more database settings. To open the interactive terminal:

postgres@vagrant-ubuntu-trusty-64:/opt/secondfunnel/app$ psql sfdb
postgres=#

Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that is processed by psql itself. These commands make psql more useful for administration or scripting. Meta-commands are often called slash or backslash commands. You quit the interactive terminal with the meta-command \q. Other useful meta-commands include \c sfdb to connect to the sfdb database:

postgres=# \c sfdb
You are now connected to database "sfdb" as user "postgres".
sfdb=#

\dt to get a list of all tables and \dt assets_category to inspect a specific table:

sfdb=# \dt assets_category
            List of relations
 Schema |      Name       | Type  | Owner 
--------+-----------------+-------+-------
 public | assets_category | table | sf
(1 row)

SQL statements can be entered directly into the psql terminal. For example, to give our vagrant user sf database creation priviledges:

postgres=# ALTER USER sf CREATEDB;
ALTER ROLE
postgres=# \q

• Complete list of Meta-commands
• Complete list of available SQL commands
• More info on PSQL

3. Common tasks

Creating a database dump

Use the command pg_dump sfdb > db.dump. The db.dump will be created in your app folder. Exit the postgres user to see it.

• If you get a Permission denied error, its likely because the db.dump file already exists in your app folder.
• For a remote machine, you will need to use the database password in /SecondFunnel/ansible/group_vars/[production|stage].yml
• To download the db dump to local, run this from your vagrant env: scp willet@__server_ip_address__:/opt/secondfunnel/app/db.dump db.dump

NOTE: If you are copying the database from production/stage to vagrant, you need to make sure you do not copy owners & privileges: pg_dump --no-owner --no-privileges sfdb > db.dump. When you pg_restore from the db.dump, you will need to make sure the db is assigned an owner using: createdb -O __owner_name__ sfdb. See /SecondFunnel/ansible/group_vars/vagrant.yml for db owner name.

Setting a postgres user password

There is no default password for the postgres user on most linux distributions. Some postgre commands require it though. To set it, in psql:

postgres=# ALTER USER postgres PASSWORD '_password_';
ALTER ROLE
postgres=# \q

Drop database

Use dropdb command:

postgres@vagrant-ubuntu-trusty-64:/opt/secondfunnel/app$ dropdb sfdb

If you get error dropdb: database removal failed: ERROR: database "sfdb" is being accessed by other users. Do the following to remove IDLE connections:

postgres=# SELECT pg_backend_pid();
pg_backend_pid 
----------------
           2974
(1 row)
postgres=# SELECT pg_cancel_backend(2974) FROM pg_stat_activity WHERE datname='sfdb';