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 client via your package manager.
sudo yum install pgadmin3
sudo apt-get install pgadmin3
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:
sudo su -
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
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.
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:
- Can create database objects
- Can create roles.
6. Click OK