Version: Next

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:

Postgres.app - Very easy for a development machine, requires zero configuration.
MacPorts
Homebrew
Manually
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	‘*’
port	5432

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.

Parameter	Suggested value (build)
shared_buffers	10-25% of RAM
temp_buffers	around 80MB
work_mem	around 500MB but < 10% of RAM
maintenance_work_mem	5% of RAM but < 20% of RAM
default_statistics_target	around 250
random_page_cost	around 2.0-2.5
effective_cache_size	50% of RAM
synchronous_commit	off
geqo_threshold	14
from_collapse_limit	14
join_collapse_limit	14
max_locks_per_transaction	640
max_pred_locks_per_transaction	640
checkpoint_segments	128
checkpoint_timeout	10min
checkpoint_completion_target	0.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