Version: 4.0.0

Installing PostgreSQL

Warning We recommend you install PostgreSQL 9.2 and above. We currently run our continuous integration tests on PostgreSQL 9.2. The PostgreSQL downloads page has packages (for most systems) that set up everything for you.

Fedora/CentOS

https://computingforgeeks.com/how-to-install-postgresql-on-fedora/

Debian/Ubuntu

sudo apt-get install postgresql

Mac

There are several good options:

We have had good experiences with Postgres.app and Macports.

Some of the recommended settings below may not apply to older versions of PostgreSQL.

Configuration file#

Most of the configurations below are made by updating the file postgresql.conf, usually located in /etc/postgres/version-nr/main.

Required Configurations#

Allow remote connections#

listen_addresses‘*’
port5432

Recommended Configurations#

The system works reasonably well with the default configuration. For better performance, we recommend to make the changes below.

Character Set Encoding#

You should only use either SQL_ASCII or UTF-8. If performance is an issue, the use of SQL_ASCII is strongly recommended. [1]

Procedures to change character encoding to SQL_ASCII in PostgreSQL 9.x:

sudo -u postgres psql
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';
\q
exit

you can check the expected screenshot here [2] .

Database Server Configuration#

Kernel Memory setting#

Please check your server kernel setting

getconf PAGE_SIZE
getconf _PHYS_PAGES
sysctl -a | grep -E "shmall|shmmax"

(use sudo if necessary)

Set

shmall = phys_pages / 2
shmmax = shmall * pagesize

by editing the file

/etc/sysctl.d/30-postgresql-shm.conf

and sourcing it

sudo sysctl -p /etc/sysctl.d/30-postgresql-shm.conf

PostgreSQL parameters#

For better performance. Read Tuning your PostgreSQL Server for more information.

ParameterSuggested value (build)
shared_buffers10-25% of RAM
temp_buffersaround 80MB
work_memaround 500MB but < 10% of RAM
maintenance_work_mem5% of RAM but < 20% of RAM
default_statistics_targetaround 250
random_page_costaround 2.0-2.5
effective_cache_size50% of RAM
synchronous_commitoff
geqo_threshold14
from_collapse_limit14
join_collapse_limit14
max_locks_per_transaction640
max_pred_locks_per_transaction640
checkpoint_segments128
checkpoint_timeout10min
checkpoint_completion_target0.9

Note that most of the changes above require starting postgres.

Note Depending on your system configuration (production or development), the type of sources used in the build (files or databases) and the load on your web application, you may need to increase the max_connections parameter (for example to 250).

Client Authentication#

You should also add a line to the pg_hba.conf file to allow logging in via password:

host all all 0.0.0.0/0 password

[1]

The InterMine system stores all text in the database in UTF-8 format. If you set PostgreSQL to LATIN-9, then PostgreSQL will perform some incorrect conversions, and may even give an error. Setting the format to UTF-8 results in PostgreSQL treating the text completely correctly, which is quite a complicated and slow operation in UTF-8.

If you set PostgreSQL to SQL_ASCII, then that is a special character set in Postgres, which basically means “do no conversions”. This is sufficient for almost all operations. All comparisons and index lookups will be done on a byte-by-byte basis, which is much faster than having to deal with Unicode’s complications.

Please try to treat InterMine as a black box. The fact that it uses PostgreSQL to store its data should be a detail that should be hidden as much as possible. The InterMine system is written in Java, and therefore handles all text in Unicode.

The template1 database is the database used as a template when you run the createdb command. Update the encoding for template1 to be SQL_ASCII, then every database you create from now on will have the correct encoding.

[2]

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 also: HikariCP and InterMine settings