Installing PostgreSQL¶
Important
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 setting 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.
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
See also: HikariCP and InterMine settings