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: 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$

2. Using Postgres

There are two main ways to work with Postgres: a) client applications and utilities and b) the psql interactive terminal. Both can be scripted.

a. 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

b. 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=#

You quit the interactive terminal with \q. More info on PSQL

3. 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 'postgres'; -- replace 'postgres' with whatever password you want except ''
ALTER ROLE
postgres=# \q