Database notes

PostgreSQL
Alembic

PostgreSQL

Setup postgres 8.4 on CentOS

service postgresql initdb
chkconfig postgresql on
service postgresql start

Config files at:

/var/lib/pgsql/data/postgresql.conf
/var/lib/pgsql/data/pg_hba.conf

Setup postgres 9.4 on Windows

I've had luck with postgres 9.4 (64-bit) from EnterpriseDB, via this.

Config files at:

%programfiles%\PostgreSQL\9.4\data\postgresql.conf
%programfiles%\PostgreSQL\9.4\data\pg_hba.conf

Firewall rules like this might need to be set up, depeding on your use case:

netsh advfirewall firewall add rule name=postgres action=allow protocol=TCP localport=5432 dir=in program="C:\Program Files\PostgreSQL\9.4\bin\postgres.exe"

Database creation on CentOS

To set up the database (in psql after sudoing to postgres):

create role xyzuser with LOGIN;
alter  role xyzuser with encrypted password 'FIXME';
create database xyzdb  with encoding = 'UTF-8';

Postgres 8.4 prefers lowercase database names!

See CREATE ROLE, CREATE TABLE, some help on authentication methods, and some psql tips.

Database creation on Windows

createuser --host=localhost --username=postgres --login --pwprompt --no-superuser --password dbname
createdb   --host=localhost --username=postgres --encoding=UTF8 --owner=svcname dbname

Database backup/restore

Backup:

pg_dump --host=localhost --username=postgres --no-owner --no-acl --format=c dbname > dbname-date.backup

Wiping the target database (in psql):

drop database dbname;
create database dbname  with encoding = 'UTF-8';

Wiping the target database (on command line):

dropdb     --host=localhost --username=postgres dbname
createdb   --host=localhost --username=postgres --encoding=UTF8 --owner=svcname dbname

Restore:

pg_restore --host=localhost --username=postgres --no-owner --no-acl --single-transaction --dbname=dbname dbname-date.backup

See docs here.

PSQL regex search and replace

\dt
select * from table1 where field2 ~ 'banana[.]f';
update table1 set field2 = regexp_replace(field2, 'banana[.]f', 'banana_f') where field2 ~ 'banana[.]';

Remote connections

Edit pg_hba.conf to allow more logins:

local    all  postgres             ident
local    all  kevin                ident
hostssl  all  all       9.9.9.9/7  md5
hostssl  all  all       ::1/128    md5

In that, md5 will do password auth, and hostssl (instead of host) will require SSL connections.

Edit the postgresql.conf to include:

listen_addresses = '*'
...
ssl = on

Finally open this up to the network via, e.g., iptables:

# iptables -A INPUT -p tcp -m tcp --dport 5432 -j my-list-of-netmasks
# /etc/init.d/iptables save

Use openssl to generate a key and certificates owned by postgres (like so):

/var/lib/pgsql/data/server.key
/var/lib/pgsql/data/server.crt
/var/lib/pgsql/data/root.crt (if self-signing, maybe just a duplicate)

Then copy the cert to the client and in pgAdmin III use the SSL mode "verify-full". (Described some here.)

Debugging slow queries

You can set a statement_timeout like so:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

def get(url):
    engine = create_engine(url)
    db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
    db_session.execute('set statement_timeout="1min"')
    return db_session

And after bumping up the track_activity_query_size (in postgresql.conf) you can copy the queries out of pgAdmin III's Server Status widget. (Or just type them into the CTRL-E query tool.)

Adding explain and explain analyze commands you can see the query plan, where the real meat is. Once you have those results, this tool is a life saver (because multiplying the loop actual times is not so obvious): http://explain.depesz.com/

Other performance tips

There's a nice summary of vacuuming here. And another example here.

There are notes on undead queries here.

Here's a chapter on full text search and on indexing. In SQLAlchemy, indexes can be set up as this notes with index=True or Index('user_index', Users.email, Users.ip_addr).

Alembic

To set up an alembic system:

alembic init alembic

Then modify env.py to point to the right Base.metadata. Easier if our model is in an installed package (e.g., in a virtualenv)...

You can choose which config file to use with -c (default: ./alembic.ini), or choose which section of INI to use with -n (default: alembic).

Then, creation/migration:

alembic -c %programdata%\svc\alembic.ini revision --autogenerate -m "add data model"
git add alembic/versions/12345678901_add_data_model.py

alembic -c %programdata%\svc\alembic.ini upgrade head
alembic -c %programdata%\svc\alembic.ini downgrade -1
alembic -c %programdata%\svc\alembic.ini upgrade head

iemelyanov/postgres-and-alembic-old-notes.md