SUNScholar/Prepare Ubuntu/S06

 STEP 7

Step 6. Install PostgreSQL

DSpace can use the PostgreSQL or Oracle database server for the main database.

The following procedure installs the PostgreSQL database server and creates the "dspace" database and "dspace" database user with the very secret dspace database user password.

The PostgreSQL database server was chosen because it is open source software and this in keeping with the open access pathos.

If using the Ubuntu 14.04 server, then change all instances of 9.1 to 9.3 because Ubuntu 14.04 uses the 9.3 PostgreSQL database server.

Step 6.1: Install PostgreSQL server software

sudo apt-get install postgresql 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.

• http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
• http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html

Change database user permissions to "trust" only.

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

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.

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#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

Create the "dspace" database.

sudo createdb -E UNICODE dspace

Step 6.5: Setup PostgreSQL dspace DB user password, ownership and privileges

Enter the Ubuntu server postgres user shell.

sudo su - postgres

Connect to the PostgreSQL database server and enter a PostgreSQL database server shell.

psql -U postgres

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;

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

Edit the postgresql config file:

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

Change the number of "max_connections" to 300.

---

NANO Editor Help

CTL+O	= Save the file and then press Enter
CTL+X	= Exit "nano"
CTL+K	= Delete line
CTL+U	= Undelete line
CTL+W	= Search for %%string%%
CTL+\	= Search for %%string%% and replace with $$string$$
CTL+C	= Show line numbers

More info = http://en.wikipedia.org/wiki/Nano_(text_editor)

---

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:

# For PostgreSQL server client connections
kernel.shmmax = 500000000
kernel.shmall = 500000000

---

NANO Editor Help

CTL+O	= Save the file and then press Enter
CTL+X	= Exit "nano"
CTL+K	= Delete line
CTL+U	= Undelete line
CTL+W	= Search for %%string%%
CTL+\	= Search for %%string%% and replace with $$string$$
CTL+C	= Show line numbers

More info = http://en.wikipedia.org/wiki/Nano_(text_editor)

---

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

References

• http://www.postgresql.org/about/featurematrix
• http://manage.jujucharms.com/charms/trusty/postgresql
• http://manage.jujucharms.com/charms/precise/postgresql