PostgreSQL Installation - Windows, Mac OS X, Generic Linux
If you are installing CollectionSpace in an apt-compatible (Debian/Ubuntu) or yum-compatible (Fedora, CentOS, Red Hat) Linux flavour, we encourage you to follow the instructions in PostgreSQL Installation under Linux instead. Those instructions use the native Linux package managers, and will help ensure your PostgreSQL install can be kept up to date over time.
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.)
Several of the steps in the instructions below assume that you are installing PostgreSQL 9.1. If you are installing a higher version, please replace 9.1
in those instructions with your installed PostgreSQL version number, as appropriate.
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 theUTF-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 thedata
directory, within the directory in which you installed PostgreSQL. - Open this file in your favorite text editor and follow the instructions below.
On Mac OS X this defaults to /Library/PostgreSQL/9.1
.
For Mac OS X you will need to be the super user to edit any of the following configuration files.
- 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.
PostgreSQL 9.x under Windows 7 may possibly not accept the term samehost
under the CIDR-ADDRESS column. If this is the case then use the address form 127.0.0.1/32
:
# 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
Recent systems, such as PCs running operating systems like Windows 7 and Windows Vista, may come with IPv6 enabled by default. If IPv6 is enabled on your system, you might experiment with the following configuration (untried by this writer), noting that ::1/128
signifies the loopback (aka "localhost") address:
# 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 thedata
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
A max_prepared_transactions variable setting of 64 might be too memory consuming for your system. If so, set it lower. If you prefer another value than 64, such as 32, this should be OK too.
If this value is indeed too high for your system, you'll get an error when restarting PostgreSQL. Something like:
Restarting PostgreSQL ... database server: main The PostgreSQL server failed to start. Please check the log output ... FATAL: could not create shared memory segment: ...
Restart the PostgreSQL service
If you encounter any error messages while starting the PostgreSQL server, please see the "Server Start-up Failures" section of the PostgreSQL Starting the Server documentation.
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
The -D flag indicates the location of the PostgreSQL data directory, which can also be set as the environmental variable PGDATA. By default it can be found in /Library/PostgreSQL/9.1/data
.
If you get a 'not found' error when attempting to run pg_ctl
, try specifying the full path to that file, in the bin
directory of where you choose to install PostgreSQL; e.g.: /Library/PostgreSQL/9.1/bin/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:
If you get a 'not found' error when attempting to run psql
, try specifying the full path to that file, in the bin
directory of where you choose to install PostgreSQL; e.g. under Mac OS X: /Library/PostgreSQL/9.1/bin/psql
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
You may get an error the first time running the psql
command using OS X 10.8.X, Mountain Lion. This error may look like the following:
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
This is thought to be related how Homebrew on Mac OS X and Mountain Lion conflict with certain system libraries. A tested solution to this problem is to copy over and link the library versions PostgreSQL is packaged with over to /usr/lib
where the libraries are stored on Mountain Lion.
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
This is just one solution to the issue, yet a better solution may be to revisit Homebrew and check on compatibility issues on Mountain Lion.
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
The tuning of the database is OPTIONAL! CollectionSpace will run even if none of the below steps are carried out. They are merely steps to tweak the database and improve the performance when running with CollectionSpace (Nuxeo).
The below is a boiled down version of the instructions on this page:Â https://doc.nuxeo.com/display/NXDOC/Configuring+PostgreSQL. If you want more details on what you are doing, than what is given below, please refer to the link.
There might be values more suitable for your system, than the values given below.
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