Install psycopg2 (PostgreSQL adapter for Python) on OSX

I was playing with Django with Postgres backend, and I had little difficulty installing “psycopg2” the Python DB adapter for Postgres on my Mac OSX.

I’ve installed Postgres using Postgres.app for OSX which is straight forward and standard Mac app.

But when I tried installing “psycopg2” using “pip” (the python package manager) I got an error:

$ pip install psycopg2
:
Error: pg_config executable not found.
:

I just searched for “pg_config” in my system:

$ find / -name pg_config  2>/dev/null
/Applications/Postgres.app/Contents/Versions/9.3/bin/pg_config

Then added to my PATH env and pip worked:

$ export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.3/bin/
$ pip install psycopg2

Accelerating Postgres connections with PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL, connection pooling makes Postgres connection much faster, which is important in Web applications.

Here I will explain the steps I used to configure it under Ubuntu 14.04 LTS.

Step 1: We would configure users allowed to connect to PgBouncer:

$ sudo vi /etc/pgbouncer/userlist.txt
"rayed"  "pgbouncer_password_for_rayed"

Step 2: We configure databases PgBouncer will pool for, and how PgBouncer will authenticate user:

$ sudo vi /etc/pgbouncer/pgbouncer.ini
:
[databases]
rayed = host=localhost user=rayed password=postgres_password_for_rayed
:
auth_type = md5
;auth_file = /8.0/main/global/pg_auth
auth_file = /etc/pgbouncer/userlist.txt


The default value for “auth_type” is “trust” which means a system user “rayed” will be allowed to connect to Postgres user “rayed”, I change to “md5” to force a password checking from the file “/etc/pgbouncer/userlist.txt”.

Step 3: We will allow PgBouncer to start:

$ sudo vi /etc/default/pgbouncer 
:
START=1
:


The default value is “0” which means don’t start PgBouncer ever, it is a way to make sure you know what you are doing 🙂

Step 4: Starting pgBouncer:

$ sudo service pgbouncer start

Step 5: Test the connection, by default “psql” connect using port “5432”, and pgBouncer use “6432”, so to test a pgBouncer connection we would use the following command:

$ psql -p 6432 


If you get “Auth failed” error make, make sure the password you entered is the one you typed in step 1, if the command doesn’t ask for password try it with “-W” option, e.g. “psql -p 6432 -W”.

PostgreSQL Replication

We will assume we have two servers:

  • Master IP 192.168.100.51
  • Slave IP 192.168.100.52

MASTER

Create replication user “repl”:

sudo -u postgres createuser -P repl
sudo -u postgres psql
> ALTER ROLE repl REPLICATION;

Allow “repl” user to connect from the slave:

$ vi /etc/postgresql/9.1/main/pg_hba.conf
:
host    replication     repl            192.168.100.52/32        md5

Setup replication parameters:

vi /etc/postgresql/9.1/main/postgresql.conf
:   
listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 5
:

Create a copy of the database and copy to the slave:

service postgresql stop
cd /var/lib/postgresql/9.1
tar -czf main.tar.gz main/
service postgresql start
scp main.tar.gz rayed@slave:

SALVE

Restore data from master:

service postgresql stop
cd /var/lib/postgresql/9.1
rm -rf main   # BE CAREFUL MAKE SURE YOU ARE IN SLAVE
tar -xzvf ~rayed/main.tar.gz
rm /var/lib/postgresql/9.1/main/postmaster.pid

Create recovery.conf file in data directory:

vi /var/lib/postgresql/9.1/main/recovery.conf
:   
standby_mode = 'on'
primary_conninfo = 'host=192.168.100.51 port=5432 user=repl password=repl_password
:   

Edit postgresql.conf:

vi /etc/postgresql/9.1/main/postgresql.conf
:
hot_standby = on
:

Start the slave server again, and check the log file

service postgresql start
tail -f /var/log/postgresql/postgresql-9.1-main.log 

For more info:

Login to PostgreSQL without typing a Password

If you to connect to PostgreSQL without typing a password, you can do it by having your password in the file “.pgpass”.

The file reside in your home directory and must be readable to you only:

touch ~/.pgpass
chmod 600 ~/.pgpass

The format is simple:

hostname:port:database:username:password 

For example:

*:*:my_database:rayed:my_hard_password

The “*” means any host using any port.

This of course will work for “psql” and other tools to like “pg_dump”.

PostgreSQL: New Project Setup

For each new project that need PostgreSQL you should create its own user and its own DB, the following are the steps need for that.

Installation

$ sudo aptitude install postgresql
$ sudo aptitude install python-psycopg2  # For Django access

Require a Password

$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf
:
#local   all             all                      peer
local    all             all                      md5
:
$ sudo service postgresql restart    

Create Postgres User

$ sudo -u postgres createuser -P my_user
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Create DB

$ sudo -u postgres createdb my_db -O my_user

Test It

$ psql -U my_user my_db   

After login you issue any SQL statement, you should also try the following commands:

  • \d list tables.
  • \l list databases.
  • \du list users.

PostgreSQL on Ubuntu from scratch

My notes on installing and configuring PostgreSQL on Ubuntu Linux.

PostgreSQL is used by many large web sites and services, including Skype, Reddit, Instagram, “.org” registerer, check longer list here Prominent Users

Best of all it isn’t owned by anyone, not Oracle nor anyone else!

Continue reading PostgreSQL on Ubuntu from scratch