PostgreSQL Installation for Linux

Installing the PostgreSQL Server

Apt Package Manager (Debian and Ubuntu)

Ubuntu 22.04

Instructions based on the Postgresql wiki

Setting up the PostgreSQL Server

Changes to PostgreSQL Configuration Files

After installing the PostgreSQL server, a system user named postgres should have automatically been created.

In your Linux shell, change/switch to this user:

Changes to pg_hba.conf File

Next, edit the PostgreSQL pg_hba.conf configuration file using a tool like vi or vim. The location differs between Linux flavors, e.g.:

• Debian/Ubuntu: /etc/postgresql/12/main/pg_hba.conf

So, in Ubuntu 22.04, for example, open it up with your preferred text editor:

Towards the end of the file, add (or modify) the following lines of text to the section starting with something like "# IPv4 local connections:":

Next, make sure entries under "IPv6 local connections" are disabled by adding the '#' character to beginning of this line:

Save the changes and quit your editor.

Changes to postgresql.conf File

Finally, set the max_prepared_transactions property in the main PostgreSQL config file named postgresql.conf. The file's location differs between Linux flavors, e.g.:

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

So, in Ubuntu 22.04, for example, open it with your favorite text editor

then make the following changes to that file:

The max_prepared_transactions property might be commented out with the '#' character. If you see the '#' character at the beginning of this line, remove it.

Lowering this setting to 32 should also be fine, but you might need to adjust based on your system.

Save your changes and quit.

Log out of the 'postgres' account (use the shell 'exit' command to return to your normal linux account) and restart your PostgreSQL service:

Set up datatype 'casts'

Now we need to create some datatype casts that CollectionSpace uses. While logged in as the postgres user, enter the psql shell:

then enter the following commands into the psql shell:

Remain in the psql shell and continue to the next section.

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):

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/role with appropriate privileges.  (Be sure to substitute a password of your choosing for replacemewithyourpassword below:

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

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

Log out of the 'postgres' Linux account (the 'exit' command returns you to your normal linux account) and then restart the PostgreSQL server:

Verify that you are able to connect to the PostgreSQL server using the csadmin account username and password you just added:

If successful, you should be in the psql shell and see something like this:

Next, exit the psql shell by typing \q (or press ctrl+d).

You're finished with the PostgreSQL setup. 

Database Tuning

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

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

For example, for Debian/Ubuntu:

Edit the following value in that file:

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

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

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

finally set effective cache_size:

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

This will give you an output like so:

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

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

Also set the following values:

restart for changes to take effect

Debian/Ubuntu:

Report Authoring

If you want to run reports on you CollectionSpace instance, you will need to make changes to your "pg_hba.conf". See How to Add and Run Reports.

For example in Ubuntu you would open it in your favorite text editor:

If you are going to allow report authoring on the local machine, then you need to add this entry as well:

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. See also the documentation for pg_hba.conf for your PostgreSQL release for more details on how to specify a subnet address.) Don't use '169.229.0.0'! This is just an example IP address.

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. If you can control access to PostgreSQL via a firewall, you can use a value of "*" for simplicity,

or for more security, you can list the individual IP addresses from which report authors will connect.