Creating_PostgreSQL_databases_-_pgAdmin_setup

This procedure describes how to set up the QF, PF and DR databases via the pgAdmin III user interface.

Repeat the following procedures for each database:

• Create database
• Set up the tables for the new database.

Note: The Add the superuser procedure only needs to be done once.

• Install pgAdmin
  • Linux
  • Windows
• Install a new server
  • Linux
  • Windows
• Create database
• Set up the tables for the new database
• Add the superuser

Install pgAdmin

Linux

Install pgAdmin client via your package manager.

For Fedora:

sudo yum install pgadmin3

For Ubuntu:

sudo apt-get install pgadmin3

Windows

Download and install pgAdmin: http://www.pgadmin.org/download/windows.php

Install a new server

1. Start pgAdmin III

2. Select File - Add Server.

3. In the Name field, type server name.

4. In the Host name, type the IP address of the server.

5. The Port field should already be populated with 5432.

6. The Username and Maintenance DB fields should already be populated with 'postgres'. If not, see [Installing_PostgreSQL].

7. In the Password field, Enter the postgres password you created in [Installing_PostgreSQL].

8. Click OK.

Note: If you get an error message about authorisation, you will need to do the following.

9. Edit pg_hba.conf file:

Linux

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

Windows

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

10. Repeat steps 1 to 7.

Create database

1. Start pgAdmin III (in Windows, this can be done via Start / All Programs / PostgreSQL 8.x / pgAdmin III

2. Double click the localhost server in the left pane of the display and enter the password for the postgres user

3. Right click Databases and select New Database

4. Give the new database a name (e.g. qf_local)

5. Click OK

6. Repeat steps 3 to 4 for the pf and dr databases.

Set up the tables for the new database

1. Select the database you created above

2. Either select Tools / Query Tool or press the Execute arbitrary SQL query button

Result: This will open the SQL Query window

3. Select File / Open and navigate to the location of your dpr files (for example, dpr-source/dist/)

4. Select the create_qf.sql file and click Open

Result: The contents of the file will display in the query panel.

5. Manually delete all lines up to the first create table statement. These deleted statements are useful if the file is used to later clean out an existing database, but if applied to an empty database will generate errors that prevent pgAdmin III from continuing. This is in contrast to the command-line procedure, where the same errors do not halt the processing of further SQL statements.

6. To execute the query, select Query / Execute

Result: The query executes in the Output Pane

7. Exit the SQL Query window - do not save changes

8. Repeat steps 4 to 7 for the PF and DR databases, selecting create_pf.sql or create_dr.sql at step 4.

Add the superuser

To complete the database setup, you need to add a user called dpr with superuser permissions. This user can be used to connect to each of the databases created above.

1. Right click Login Roles and select New Login Role

Result: The New Login Role window, Properties tab is displayed.

2. In the Role Name field, type dpr

3. In the the Password field, type the desired password.

4. Select the Role Priviliges tab

5. Check the checkboxes for:

• Superuser
• Can create database objects
• Can create roles.

6. Click OK