N.B. 9.1 needs patching Example with 9.3

ipcs -lm gives information about the server memory settings

e.g.

max number of segments = 4096                  //SHMMNI
max seg size (kbytes) = 32768                  //SHMMAX
max total shared memory (kbytes) = 8388608     //SHMALL
min seg size (bytes) = 1

also sudo sysctl -a | grep -E "shmall|shmmax" can be used

kernel.shmmax = 33554432
kernel.shmall = 2097152

The suggested setting (pp 101) depends on 2 system settings, PAGE_SIZE and _PHYS_PAGES which can be obtained with getconf (e.g. getconf PAGE_SIZE)

shmall = phys_pages / 2
shmmax = shmall * pagesize

The 2 values need to be added to /etc/sysctl.conf, and the change to be made effective issuing the command sudo sysctl -p

In our case

kernel.shmmax = 271051274240
kernel.shmall = 66174628

The new result of ipcs is

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 264698510
max total shared memory (kbytes) = 264698512
min seg size (bytes) = 1

Note: the unit for kernel.shmall is pages, not bytes.

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

wget http://www.bioinformatics.org/downloads/index.php/bioseg/bioseg-0.8.tar.gz

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

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

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

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

For optimum performance. Read http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server for more information.

$ sudo adduser sql Follow steps adding password sql $ sudo su $ su postgres $ createdb userprofile $ psql userprofile $ CREATE USER sql WITH PASSWORD 'sql'; $ GRANT ALL PRIVILEGES ON DATABASE userprofile to sql; $ \q $ exit