2. PostgreSQL installation and InterMine basic settings - sergiocontrino/docs GitHub Wiki

##Install $ sudo apt-get install postgresql-9.3

##Set the database as SQL_ASCII Note: it was easier with 8.x

run psql as postgres

sudo -u postgres psql

and issue these commands:

update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with template = template0 encoding = 'SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C';
update pg_database set datistemplate = TRUE where datname = 'template1';
\c template1
update pg_database set datallowconn = FALSE where datname = 'template0';

screen shot:

postgres=# update pg_database set datallowconn = TRUE where datname = 'template0';
UPDATE 1
postgres=# \c template0
You are now connected to database "template0" as user "postgres".
template0=# update pg_database set datistemplate = FALSE where datname = 'template1';
UPDATE 1
template0=# drop database template1;
DROP DATABASE
template0=# create database template1 with template = template0 encoding = 'SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C';
CREATE DATABASE
template0=# update pg_database set datistemplate = TRUE where datname = 'template1';
UPDATE 1
template0=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# update pg_database set datallowconn = FALSE where datname = 'template0';
UPDATE 1

see http://intrac.flymine.org/wiki/BiosegInstallation
and http://intermine.readthedocs.org/en/latest/system-requirements/software/postgres/bioseg/

If not yet installed

sudo apt-get install postgresql-contrib-9.3
sudo apt-get install postgresql-server-dev-9.3

Get and install bioseg

wget http://www.bioinformatics.org/downloads/index.php/bioseg/bioseg-0.8.tar.gz
tar xzvf bioseg-0.8.tar.gz
cd release-0.8
make USE_PGXS=t clean
make USE_PGXS=t
sudo make USE_PGXS=t install

if necessary install make with sudo apt-get install make

as user postgres sudo su - postgres

run the commands

cd /usr/share/postgresql/9.3/contrib
psql -d template1 < bioseg.sql
psql -d template1
CREATE EXTENSION btree_gist;

see: http://intrac.flymine.org/wiki/PostgresSettings
and http://intermine.readthedocs.org/en/latest/system-requirements/software/postgres/postgres/

cd /etc/postgresql/9.3/main/

after your changes you will need to restart the databese

sudo su - postgres
/etc/init.d/postgresql restart

Note: some settings actually don't require restart of the database and can be done 'live'

edit the file postgresql.conf

you can also adjust and fine tune the access methods in the file pg_hba.conf

here some example settings:

local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host    all         all         172.25.137.0/23       md5

see http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html