Dealing with Postgres "/tmp/.s.PGSQL.5432" error

I have dealt with this error quite a few times, but so infrequently that I forget the solution. When upgrading or wiping and reinstalling PostgreSQL via Homebrew, sometimes a few things get dropped by the scripts for some reason.

Situation: Postgres is running, database has been created, I can log in via psql and interact with the database just fine. Even Django, running via the development runserver, can interact with the database fine. However, when you try to test the production server on Apache, you get the dreaded error:

could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

Here is the solution:
Quick check to see if /var/pgsql_socket exists:

ls /var/      # nope, nothing here

So then just make one, per the instructions found here

sudo mkdir /var/pgsql_socket/
ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/

Reload your apache page and voila, it works.

System config: OSX 10.12.5 (Sierra), httpd24, postgresql@9.5 (9.5.7), django 1.9.2

Creating a Mountain Lion development environment

So I had to set up a new dev environment at work on Mountain Lion. It uses the following components

• Hombrew
• Python 2.7.5
• Django 1.5.2
• Apache 2.2.25
• Postgres 9.2.4
• R 3.0.1
• Virtualenv

and a bunch of other python libraries and stuff. This system is set up for Bioinformatics development, so there are things that are specific to that. The build is documented on our internal wiki, but I copied the build instructions to the *new* code.ex(python) wiki in my personal github account. The direct link to the build instructions is here.

Finding values in one table that are not in another

This site has a very good explanation of NOT IN, LEFT JOIN,where NULL, and NOT EXISTS statements for Finding values in one table that are not in another

Matching limited elements using Postgres IN clause

So you have two tables, one a sequence table holds unique sequence ids.  The other table hold sequence mutations and has a 1 to many relationship with the sequence table.  So for a given sequence id in the mutation table, there are many records, one for each mutation.

Now you have two sets of mutations you are interested in, a primary set A and a secondary set B.  You want to know how many sequences contain 1 and only 1 mutation from set A and 1 and only 1 from set B.


select count(*) from (
SELECT distinct a.seq_id
FROM sequence a
JOIN seq_mutations b ON b.seq_id = a.seq_id
JOIN seq_mutations c ON c.seq_id = a.seq_id
WHERE b.mutation IN ('mutA','mutB','mutC')
AND c.mutation IN ('mut1', 'mut2', 'mut3', 'mut4', 'mut5')
GROUP BY a.seq_id
HAVING 
  count(distinct b.mutation) = 1 
  AND count(distinct c.mutation) = 1) a

This query gives you exact, fine control over the conditions you want to test for.  If you then want to test for 1 and only 1 mutation in set A and 2 and only 2 mutations in set B, just change

count(distinct c.mutation) = 2

in the HAVING clause.  This also lets you use > and <, so you can ask for 3 or more mutations by using

count(distinct c.mutation) >= 3

You can also vary the count for b.mutation to adjust the number of mutations allowed from set A, so any combination can now be extracted.

Thanks to Jake for the code help.

Convert Postgres column from text to numeric type

ALTER TABLE foo ALTER COLUMN col TYPE NUMERIC USING col::numeric

You can use this with data in the table.  All data must be convertable (no alpha chars). You can also specify numeric formatting here if you like.

How to get Django to see multiple PostgreSQL schemas

Took awhile to figure this out, so here goes.

First create a PostgreSQL user that will be used by Django to connect to the database.  This is the user that will be included in the settings.py file for the database connection section.

Log into PostgreSQL as admin/superuser and issue the following command:

GRANT USAGE SCHEMA foo TO django_user;


(Or GRANT USAGE to any role which has django_user as a (direct or indirect) member.)
(Or GRANT ALL ... if that is what you want.)

The next step is to change the default schema search path.  To make a permanent change, do the following:

ALTER ROLE django_user SET SEARCH_PATH to "$user",public,your_schema;

Log out and log back in for the change to take effect.  You can test the outcome by doing a \dt and you should see all table from all schemas that the role has been granted access to.

You can now run manage.py inspectdb and it will see all tables in all schemas.  Don't know yet how it will treat tables with the same name in different schemas, as it is no longer required to prefix the schema name in a query, although it can still be done.

Adding PHP module to default OS 10.6.1 PHP stack

The current system is Snow Leopard 10.6.1 and I want to add PostgreSQL support to the default PHP installation.  Snow Leopard comes with PHP 5.3.4 already installed in Apple's weird, distributed way.  However, the current distro for PHP is 5.3.6 at the time of this writing, so what to do?  I found the solution scattered across many different blogs, so I am synthesizing it here.  None of this was my own creation.

First, grab a copy of the source code that matches what is already installed.  Probably won't find it on PHP.net, so try this link: php-5.3.4

I created a /src directory to store source code in.  Copy the tar file into here or a similar directory and unpack it.

Change to that directory:

>cd /src/php-5.3.4

Set some environment variables before doing the configuration

>export MACOSX_DEPLOYMENT_TARGET=10.6.7

>export CFLAGS="-arch x86_64"

>export CXXFLAGS="-arch x86_64"

>export LDFLAGS="-arch x86_64"

Go to the pgsql source directory in php ext folder

>cd ext/pgsql

Compile the extension module

>phpize

>./configure

>make

The extension will be found here

>cd /src/php-5.3.4/ext/pgsql/.libs/

>ls

-rwxr-xr-x  1 Bali  admin   154K Mar 29 12:41 pgsql.so

Copy the extension to the extensions library and make sure it is executable

>sudo cp pgsql.so 

/usr/lib/php/extensions/no-debug-non-zts-20090626/

>cd  

/usr/lib/php/extensions/no-debug-non-zts-20090626/

>sudo chmod +x pgsql.so

Create a copy of the php.ini file if one does not already exist

>sudo cp /etc/php.ini.default /etc/php.ini

Edit the php.ini file and add the following two lines:

extension_dir="/usr/lib/php/extensions/no-debug-non-zts-20090626/"

extension=pgsql.so

Save and then test that the extension is loaded properly by running the following at the command line:

>php -m

You should see a list of installed modules, including pgsql.  Then go back and restart Apache

>/usr/sbin/apachectl graceful

Run phpinfo to verify the module has been loaded.  You may have to scroll down to see it.

That is it.

Connecting to PostgreSQL with Python and Psycopg2

Basic syntax for making a database connection, executing and retrieving data:

import psycopg2 as pg

# create database connection
try:
   conn = pg.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
except:
   print "Unable to connect to database"


# create database cursor
cur = conn.cursor()


# execute SQL and fetch results
cur.execute("""SELECT datname from pg_database""")
rows = cur.fetchall()


print "\nShow database results:\n"
for row in rows:
   print row[0]