PostgreSQL Installation under Linux

Table of Content


Installing the PostgreSQL Server

We highly recommend that you install the latest available, stable version of PostgreSQL 9.x, if it is available to you via your Linux package manager. (There are sufficient advantages to running latest available versions that in many cases, it may be desirable to install PostgreSQL in some way other than via a package manager, if the latter restricts you to an older version. See PostgreSQL Installation - Windows, Mac OS X, Generic Linux for one such option.)

The current major version of PostgreSQL available through many package managers, as of this writing, is 9.6. (See also the PostgreSQL-related recommendations from Nuxeo, the vendor of the content management system used by CollectionSpace.)

Apt Package Manager (Debian and Ubuntu)

Ubuntu 16.04

No preview available. Please publish the page first.

If you're experiencing issues please see our Troubleshooting Guide.

 

Setting up the PostgreSQL Server

No preview available. Please publish the page first.

If you're experiencing issues please see our Troubleshooting Guide.

 

Database Tuning

 

Unknown macro: {multi-excerpt}

Tuning the database is OPTIONAL! CollectionSpace will run even if none of the steps below are carried out. They are merely steps to tweak the database and improve performance when running with CollectionSpace (Nuxeo)

The below is a condensed version of the instructions on Nuxeo's Configuring PostgreSQL page. If you want more details than are given below, please refer to that page.

More generically, the PostgreSQL wiki page on Tuning Your PostgreSQL Server may also be helpful.

Tuning takes place in postgresql.conf - the location differ among OS' flavours. Some examples are:

  • Debian/Ubuntu: /etc/postgresql/9.6/main/postgresql.conf

For example, for Debian/Ubuntu:

vim /etc/postgresql/9.6/main/postgresql.conf

Edit the following value in that file:

shared_buffers = 512MB

Now on the command line, set the kernel.shmmax value.. This value should at least be shared_buffer + 128MB

sysctl kernel.shmmax=650000000

If you get an error, you need to up the value of kernel.shmax

You need to register the final value of shmmax:

Open /etc/sysctl.conf

vim /etc/sysctl.conf

and add the following line - replacing the value with the value you set on for shmmax:

kernel.shmmax = 650000000

finally set effective cache_size:

Do this by running (and printing stats in megabytes):

free -m

This will give you an output like so:

free -m
             total       used       free     shared    buffers     cached
Mem:          4096       3302        793          0       253        1226
-/+ buffers/cache:       1822       2273
Swap:          511        216        295

find your effective cache_size by adding the number under buffers and cached. In the above example:
253+1226=1479

To this number you add the shared_buffers value .. so we get a total of:
1479+512=1991 ~ 2GB

again edit postgres conf

vim /etc/postgresql/9.6/main/postgresql.conf

and set effective cache size to the value you found in the previous steps.

effective_cache_size = 2GB

Also set the following values:

work_mem = 16MB                         # min 64kB
maintenance_work_mem = 32MB             # min 1MB
random_page_cost = 2.0

restart for changes to take effect

Debian/Ubuntu:

/etc/init.d/postgresql restart

Fedora/CentOS/RedHat:

service postgresql restart

Report Authoring

 

Macro name parameter (mn) is required.

If you're experiencing issues please see our Troubleshooting Guide.