Creating_PostgreSQL_databases_-_command-line_setup

This procedure describes how to create PostgreSQL databases via the command-line, covering:

• creation of the three databases - Quarantine (QF), Preservation (PF), and Digital Repository (DR)
• creating the database tables by applying the supplied SQL statements.

Note: If you like doing things the hard way, read on; if not, please create your databases using the pgAdmin interface (see the [Creating_PostgreSQL_databases_-_pgAdmin_setup] procedure).

The following descriptions assume a default installation of PostgreSQL on the machine where the DPR software is being used.

When PostgreSQL is installed, a user called postgres is usually created with permission to create and destroy databases. The following instructions assume that such a postgres user exists.

Linux

From the command-line:

1. Change to the location of your sql database-creation files:

<location of dpr-source/dpr/dist directory>;

2. Create the QF database:

createdb qf_local -U postgres -W -h localhost

Result: This command will prompt for the postgres user's password then create a new database called qf_local on your local PostgreSQL server.

3. Set up the qf_local database tables, apply the create_qf.sql file to the newly created database:

psql -U postgres -d qf_local -f create_qf.sql -W -h localhost

Note: After prompting for the postgres user's password, this will apply each line of the create_qf.sql file to the qf_local database. Don't be concerned about errors scrolling past for the first hundred or so lines of SQL. These cause no problems but occur because the first parts of the file attempt to apply SQL statements to database tables that don't yet exist. If the same create_qf.sql file is used to later clean out the qf_local database, these errors won't occur.

Note: If you get an error message about authorisation, you will need to edit the pg_hba.conf file:

sudo su -


vim /var/lib/pgsql/data/pg_hba.conf

At the bottom of the file, you will see a series of entries with Method of ident. Change ident to md5, so you get something similar to this:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

4. Optional. To check that the database has been created:

psql -U postgres qf_local
SELECT * FROM data_object;

Result: The empty data_object table should be displayed.

5. Repeat steps 2 and 3 above for the pf_local and the dr_local databases:

• PF:

  createdb pf_local -U postgres -W -h localhost
  psql -U postgres -d pf_local -f create_pf.sql -W -h localhost

• DR:

  createdb dr_local -U postgres -W -h localhost
  psql -U postgres -d dr_local -f create_dr.sql -W -h localhost

6. The DPR needs an initial user to be created for the first login. Once logged in, this user may use DPR's user management system to create further users. Create a database user called dpr with the password dpr and permission to create database tables and other users:

createuser -U postgres -a -d -P dpr -W -h localhost

7. Provide the new user's password twice when prompted, then provide the postgres user's password.

Result: The dpr user is created with the selected password.

Windows

From the command-line:

1. Change to the location of your sql database-creation files:

<location of dpr-source/dpr/dist directory>;

2. Create the QF database:

createdb -U postgres -W -h localhost qf_local

Result: This command will prompt for the postgres user's password then create a new database called qf_local on your local PostgreSQL server.

3. Set up the qf_local database tables, apply the create_qf.sql file to the newly created database:

psql -U postgres -d qf_local -f create_qf.sql -W -h localhost

Note: After prompting for the postgres user's password, this will apply each line of the create_qf.sql file to the qf_local database. Don't be concerned about errors scrolling past for the first hundred or so lines of SQL. These cause no problems but occur because the first parts of the file attempt to apply SQL statements to database tables that don't yet exist. If the same create_qf.sql file is used to later clean out the qf_local database, these errors won't occur.

Note: If you get an error message about authorisation, you will need to edit the pg_hba.conf file:

Go to: C:\Program Files\PostgreSQL\8.x\data\pg_hba.conf

Change all occurrences of ident to md5.

9. Restart postgresql service:

sudo service postgresql restart

4. Optional. To check that the database has been created:

psql -U postgres qf_local
SELECT * FROM data_object;

Result: The empty data_object table should be displayed.

5. Repeat steps 2 and 3 above for the pf_local and the dr_local databases:

• PF:

  createdb -U postgres -W -h localhost pf_local
  psql -U postgres -d pf_local -f create_pf.sql -W -h localhost

• DR:

  createdb -U postgres -W -h localhost dr_local
  psql -U postgres -d dr_local -f create_dr.sql -W -h localhost

6. The DPR needs an initial user to be created for the first login. Once logged in, this user may use DPR's user management system to create further users. Create a database user called dpr with the password dpr and permission to create database tables and other users:

createuser -U postgres -a -d -P dpr -W -h localhost

7. Provide the new user's password twice when prompted, then provide the postgres user's password.

Result: The dpr user is created with the selected password.