SUNScholar/Prepare Ubuntu/S06/Ubuntu-12.04

BACK TO STEP 6

Step 6.1: Install PostgreSQL server software
sudo apt-get install postgresql-9.1 postgresql-contrib-9.1 libpg-java

Step 6.2: Setup the PostgreSQL server host based access permissions
See official documentation links below for detailed information about the "pg_hba.conf" file. ''' Check these host based permissions carefully. Remember security on your server is your responsibility! ''' Change database user permissions to "trust" only. sudo sed -i 's/ident/trust/' /etc/postgresql/9.1/main/pg_hba.conf
 * http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
 * https://wiki.duraspace.org/display/DSDOC4x/Installing+DSpace#InstallingDSpace-RelationalDatabase:%28PostgreSQLorOracle%29

sudo sed -i 's/md5/trust/' /etc/postgresql/9.1/main/pg_hba.conf

sudo sed -i 's/peer/trust/' /etc/postgresql/9.1/main/pg_hba.conf

See example below. local  all             postgres                                trust
 * 1) DO NOT DISABLE!
 * 2) If you change this first entry you will need to make sure that the
 * 3) database superuser can access the database using some other method.
 * 4) Noninteractive access to all databases is required during automatic
 * 5) maintenance (custom daily cronjobs, replication, and similar tasks).
 * 6) Database administrative login by Unix domain socket
 * 1) Database administrative login by Unix domain socket


 * 1) TYPE  DATABASE        USER            ADDRESS                 METHOD

local  all             all                                     trust host   all             all             127.0.0.1/32            trust host   all             all             ::1/128                 trust
 * 1) "local" is for Unix domain socket connections only
 * 1) IPv4 local connections:
 * 1) IPv6 local connections:
 * 1) Allow replication connections from localhost, by a user with the
 * 2) replication privilege.
 * 3) local  replication     postgres                                trust
 * 4) host   replication     postgres        127.0.0.1/32            trust
 * 5) host   replication     postgres        ::1/128                 trust

Restart database server.

sudo /etc/init.d/postgresql restart

Step 6.3: Create the PostgreSQL "dspace" DB user
Create the "dspace" DB user with full privileges. sudo createuser -U postgres -d -A -P dspace

Answer "y" for yes, for any of the user creation questions.

Step 6.4: Create the PostgreSQL "dspace" database
Enter the Ubuntu server postgres user shell. sudo su - postgres

Create the "dspace" database. createdb -E UNICODE dspace

Step 6.5: Setup PostgreSQL dspace DB user password, ownership and privileges
Connect to the PostgreSQL database server and enter a PostgreSQL database server shell. psql -U postgres -d dspace


 * Set the dspace DB user password:  SECURITY WARNING: Use your unique dspace database password for this on a production system ! 

ALTER ROLE dspace WITH PASSWORD 'XXXXXX';


 * Let the dspace DB user own the dspace database

ALTER DATABASE dspace OWNER TO dspace;


 * Grant all privileges for the dspace database to the dspace DB user

GRANT ALL PRIVILEGES ON DATABASE dspace TO dspace;


 * Add the "crypto" extension to the "dspace" database.

CREATE EXTENSION pgcrypto;


 * Quit the database shell.

\q

We exit from PostgreSQL database server postgres user shell and return to the Ubuntu server dspace user shell. exit

Step 6.6: Setup the PostgreSQL server host based access permissions to the dspace database
Type the following:

sudo -i

sudo echo "## DSpace DB user access">> /etc/postgresql/9.1/main/pg_hba.conf

sudo echo "host   dspace       dspace      127.0.0.1/32         md5" >> /etc/postgresql/9.1/main/pg_hba.conf

exit

Step 6.7: Setup maximum number of PostgreSQL server client connections
PLEASE NOTE: https://jira.duraspace.org/browse/DS-2563

Edit the postgresql config file:

sudo nano /etc/postgresql/9.1/main/postgresql.conf

Change the number of "max_connections" to 300. Please note: If you get connection errors, then adjust this value!

Save and exit the file.

Step 6.8: Increase the kernel shared memory for PostgreSQL server client connections
Edit the "/etc/sysctl.conf" file:

sudo nano /etc/sysctl.conf

Copy and paste the following to the end of the file: kernel.shmmax = 500000000 kernel.shmall = 500000000
 * 1) For PostgreSQL server client connections

Also see: http://www.postgresql.org/docs/9.1/static/kernel-resources.html

Save and exit the file.

Type the following in a terminal:

sudo sysctl -p

Step 6.9: Restart the PostgreSQL server
Type the following:

sudo /etc/init.d/postgresql restart