Versions Compared

Old Version 13

changes.mady.by.user Ray Lee

Saved on

compared with

New Version Current

changes.mady.by.user Ray Lee

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Multiexcerpt macro
namePOSTGRESQL LINUX

Instructions based on the Postgresql wiki

bash


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:

bash

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:

bash

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

bash

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

bash

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

bash

then make the following changes to that file:

bash

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.

A max_prepared_transactions variable setting of 64 might be too memory consuming for your system. If this value is indeed too high for your system, you'll get an error when restarting the PostgreSQL server. Something like:

bash

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:

bash

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:

sql

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:

If you think you've encountered an error, send an email to the CollectionSpace "Talk" list at talk@collectionspace.org.  Include any error messages you've seen and give a detailed description of what steps you've already performed.

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

You're finished with the PostgreSQL setup. 

...

Multiexcerpt macro
namePOSTGRESQLTUNING


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


...