Download 1-click installer

There is a one-click installer available for Microsoft Windows, Mac OS X and Linux. The installer can be found here: http://www.enterprisedb.com/products-services-training/pgdownload.

Simply click the download icon for your desired version of PostgreSQL, corresponding to your OS. Note that x86-32 means 32bit and x86-64 means 64bit.

We highly recommend that you install the latest available, stable version of PostgreSQL. (See also the PostgreSQL-related recommendations from Nuxeo, the vendor of the content management system used by CollectionSpace.)

Run the installation

Once the installer has been downloaded, follow the instructions on this page: http://www.enterprisedb.com/resources-community/pginst-guide.

When you are asked to make certain specific choices, choose as follows:

• When asked to select locale , select UTF-8. (If no UTF-8 option is provided, simply choose the UTF-8 option for your preferred language from the locale list; for example, en_US.UTF-8 for US English.)
• If you are asked whether to install StackBuilder, note that typically you will not want to install it.
  (StackBuilder installs some optional Web, replication, and ODBC tools that are not required by CollectionSpace. Note that certain add-on PostgreSQL modules, like PostGIS, may require that it be installed.)

(Note that some of the options above may not be presented, depending on your version of PostgreSQL and your operating system.)

Configuring PostgreSQL

To make PostgreSQL work with CollectionSpace, you will need to set up some users, and change some settings.

• Locate the pg_hba.conf config file. You can find this file in the data directory, within the directory in which you installed PostgreSQL.
• Open this file in your favorite text editor and follow the instructions below.

• Make sure the "local section" has md5 rather than ident as the last word (this is good to do, but not required - if you know postgres well and prefer to keep the setting of ident, this is okay to do):

# "local" is for Unix domain socket connections only
local   all             all                                     md5

• Make the section: "IPv4 local connections" look like the following:

# IPv4 local connections:
host    all             csadmin         samehost                md5

This restricts the set of PostgreSQL users who are granted local access to the three databases used by CollectionSpace, jbossdb, nuxeo and cspace. For example, only a PostgreSQL user named cspace, or the PostgreSQL superuser postgres, can access the cspace database from the local machine.

# IPv4 local connections:
host    all             csadmin        127.0.0.1/32            md5

• Make sure entries under "IPv6 local connections" are disabled (have a hash mark (#) as the first character on their lines), unless IPv6 is active on your system:

# IPv6 local connections:
#host    all             all             ::1/128                 md5

# IPv6 local connections:
host    all             csadmin        ::1/128                 md5

• If you are going to allow remote report authoring - authoring reports while connecting to the database from remote hosts - then you need to add an entry much like the following, but with your own subnet address entered in place of the example address below. (The example below would allow remote report authoring from hosts on a specific subnet of the UC Berkeley network.

host    nuxeo           reader          169.229.0.0/16          md5

Note that for remote report authoring to work, you will also have to make another change within a second configuration file, to the listen_addresses value in the postgresql.conf file. (See below for details on finding this file.) If you can control access to PostgreSQL via a firewall, you can enter a listen_addresses value of "*" for simplicity. Alternately, for more security, you can instead enter a comma-separated list of the individual IP addresses from which report authors will connect. (See also How to add and run reports for more details.)

Next you need a minor change in the PostgreSQL configuration file:

• Locate postgresql.conf, also found in the data directory of where you choose to install PostgreSQL.
• Open this file in a text editor of your choice, and make the following change:

max_prepared_transactions = 64

Restart the PostgreSQL service

Linux:
Make sure you are root or sudo the following command:

service postgresql restart

Mac:
Make sure you are the postgres user:

sudo su - postgres
pg_ctl restart -D /Library/PostgreSQL/9.1/data

To add PGDATA as a global environmental variable to /etc/profile follow the following steps:

sudo vi /etc/profile

Next, add the following line:

export PGDATA="/Library/PostgreSQL/9.1/data"

Once set, the previous command to restart the PostgreSQL server can be run without the -D flag.

Windows:
You should have administrator privileges. Right-click on the My Computer icon, and select Manage. In the Mangement UI, select Services and Applications->Services. Find the PostgreSQL service (something like: "postgresql-9.0 - PostgreSQL Server 9.0"), select it, and choose "Restart the Service".

Set up datatype casts

These are needed for normal functioning of Nuxeo on top of PostgreSQL, and are run from the psql console tool.

Open a PostgreSQL console to the template1 database:

Linux:
Run the following two commands in the console:

su - postgres
psql -d template1   #open the database template1

Mac:
Run the following two commands as the postgres user:

sudo su - postgres
psql -d template1

dyld: Library not loaded: @loader_path/../lib/libssl.dylib
  Referenced from: /usr/lib/libpq.5.dylib
  Reason: Incompatible library version: libpq.5.dylib requires version 1.0.0 or later, but libssl.0.9.8.dylib provides version 0.9.8

sudo cp /Library/PostgreSQL/9.1/lib/libssl.1.0.0.dylib /usr/lib
sudo cp /Library/PostgreSQL/9.1/lib/libcrypto.1.0.0.dylib /usr/lib
sudo ln -fs /usr/lib/libssl.1.0.0.dylib /usr/lib/libssl.dylib
sudo ln -fs /usr/lib/libcrypto.1.0.0.dylib /usr/lib/libcrypto.dylib

Additionally, the Mac OS X one-click install of PostgreSQL creates a shortcut to a PostgreSQL shell under the Applications/PostgreSQL 9.0/ directory. Click SQL Shell (psql).app to open a new terminal window with psql running.
From this terminal window you can access the template1 database by typing:

psql \c template1

Windows:
You will run psql from a command (cmd) window. Ensure that the postgres bin folder is in your PATH, or just invoke it with the full path to the installation location of the psql executable.

psql -d template1 -U postgres

Execute console commands:

CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(integer) IS 'convert integer to text';

CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(bigint) IS 'convert bigint to text';

Create the 'csadmin' user

At psql's command prompt (ending in #), switch to the postgres database from the template1 database by entering the following (starting with a backslash character as shown):

\c postgres

In response, you should see a message similar to this:  You are now connected to database "postgres" as user "postgres".

 Enter the following command to create a csadmin user with appropriate privileges.  (Be sure to substitute a password of your choosing for replacemewithyourpassword below:

CREATE ROLE csadmin LOGIN PASSWORD 'replacemewithyourpassword' SUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

In response, you should see a message similar to this: CREATE ROLE

Quit the psql program by typing \q (or press ctrl+d).

Now, restart the PostgreSQL server again (as described above).

See Also

Running & Installing PostgreSQL On Native Windows

Tuning

Tuning takes place in postgresql.conf, which can be found in the data directory of your PostgreSQL installation. Open this file with your favorite text editor, and edit the following values.

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

Then restart the PostgreSQL server. 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 file in the data directory of your PostgreSQL installation 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 PostgreSQL for the changes to take effect.

You're now done tuning PostgreSQL. Note that the values in this tuning section are examples - there might be values more suitable values for your system. For more details on the tuning, we refer to the tuning section in: https://doc.nuxeo.com/display/NXDOC/Configuring+PostgreSQL