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

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.

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

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

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

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