Studio I.1: Installing and Using PostgreSQL

Author: David Levermore

1 Overview

This studio introduces PostgreSQL, an industrial grade open source Database Management System. It contains two parts: the first one shows a step by step procedure to install the PostgreSQL software, and the second the general context.

Part I

Installation

Step 1. Download the latest version of the PostgreSQL application from the PostgreSQL website, (http://www.postgresql.org/) and execute the file. Generally we will accept the defaults during the installation process, therefore, click next unless otherwise instructed. The first dialog specifies the language to be used during the installation, where English is the default. Choose an appropriate language and select Start from the buttons in the lower right hand corner. This tutorial uses the English language option.

Figure 1 - Installation Step 1

Step 2. Close existing applications that are running, with the exception of PostgreSQL and click the Next button.

Figure 2 - Installation Step 2

Step 3. Read the installation notes for the latest updates to PostgreSQL.

Figure 3 - Installation Step 3

Step 4. Deselect the option to install the JDBC Driver, OLEDB Provider, Npgsql Driver, and ensure that the ODBC driver is selected. Of course, if you are familiar with the purposes of the deselected drivers, you may install them. No support will be provided however. Leave the remaining options unchanged.

Figure 4 - Installation Step 4

Step 5. PostgreSQL will be installed as service account under Windows 2000 and Windows XP. Accept the default Service name, Account name, and Account Domain (this should be the name of your PC). Select a password for this service account name and click Next. Note that this account name simply allows the PostgreSQL application the ability to perform certain management function on your computer, such as Starting the Database when you power up your computer, and Stopping the database when you shut down your computer. You do not log in as this user.

Figure 5 - Installation Step 5

Step 6. The installation process alerts you that the account does not exist and offers to create it for you. Choose Yes.

Step 7. In Figure 6, modify the superuser credentials. You can change the superuser name, but it is recommended that "postgres" be maintained. Note that this postgres user has full rights to the PostgreSQL database cluster, which includes among other functionality: creating new databases, adding new users, and managing databases tables.

Figure 6 - Installation Step 6

Step 8. Accept the default procedural languages (PL). In this case, PL/pgsql is the only option available, whereas the others are made available following the installation of other available PostgreSQL modules. Click Next.

Figure 7 - Installation Step 7

Step 9. The PostgreSQL database is extensible, and a number of interesting modules have been made available from third-party developers. The TSearch2 module for example makes full-text search available in the PostgreSQL. For our purposes however, no modules need to be added, and so the defaults can be accepted. Click Next.

Figure 8 - Installation Step 8

Step 10. Click Next to begin the installation.

Figure 9 - Installation Step 9

Step 11. The installation is complete when indicated by the PostgreSQL installer.

Figure 10 - Installation Step 10

Step 12. The pgAdmin III application is used to manipulate the database server and to create database tables and other objects. Start PgAdmin III from the All Programs Menu (Windows XP): Start > All Programs > PostgreSQL 8.0 > pgAdmin III, and a window similar to Figure 11 will appear. Note that it contains a listing for a single server, the default PostgreSQL server you just installed. Aside: It is possible to connect to external databases with this tool, but that is an exercise left up to the reader. You must right click on the PostgreSQL Server to connect to it, and it will prompt you for the password as shown in Figure 12.

Figure 11 - The Default Database Server

Step 13. Enter the password for the postgres superuser which was defined in Step 6.

Figure 12 - Password Prompt to Connect to the Database Server

Step 14. If the password is correct, the window in Figure 13 will adjust to display the objects of the PostgreSQL Server. At this point no databases exist, and only a single user has access (postgres). It is good practice to create a user with lesser credentials than the postgres user. To do so, right-click on the Users sub-menu and select New User. The window in Figure 14 will appear.

Figure 13 - The Objects in the Default Database Server

Step 15. Enter a new Username, and provide a unique password. You can also assign superuser privileges for this user as indicated by the superuser, and create databases check boxes. For the purposes of this tutorial ensure that these two check boxes are selected.

Figure 14 - Adding a New User

Step 16. Following the addition of the new user in Step 14, create a new database, and ensure that the owner of this new database is the user specified in Step 15. To do so, right click on the Databases sub-menu and select New Database.

Figure 15 - Adding a New Database

Step 17. It is now possible to disconnect from this server, and log in with the credentials created in Steps 14 and 15. Right click on the running PostgreSQL server and select disconnect. The PostgreSQL server will disconnect and show a red X through its icon. Right click again on the Server icon and select New Server. Create the new server with the credentials created in Steps 14 and 15. That is, Address (localhost), Description (My New Database), InitialDB (testdatabase), and your selected Username and Password. Click OK and the window in Step 18 will appear.

Figure 16 - Adding a New Database Server

Step 18. Note that the new server appears and it contains a single database (testdatabase). Expand the Database Menu to reveal the objects contained within the database. Of interest here is the Tables object that is contained within the Schemas object. The PostgreSQL manual provides a definition of Schemas, which differs from the standard SQL definition. For the purposes of this tutorial It is safe to create tables in the public schema. Right click on the Tables object and select New Table. The window to appear allows the creation of database tables within the selected database as shown in Step 19.

Figure 17 - Objects in a Database

Step 19. Give this new table a unique name, and specify that the owner has the credentials indicated in Step 15. This will also be the default for tables created in the database.

Figure 18 - Creating a New Database Table

Step 20. Add columns to the database table, and specify the data type and other necessary attributes as shown in Figures 19a and 19b.

Figure 19a & 19b - Adding Columns (Fields) to a Database Table

Step 21. You can specify constraints on columns and tables by selecting the Constraints tab. Here the Primary Key, Foreign Key and Unique constraints among other are specified.

Figure 20 - Adding Constraints to the Database Table

Step 22. The SQL tab reveals the SQL syntax of this table.

Figure 21 - Viewing the SQL DDL Syntax of the Database Table

Step 23. To view the contents of the table, right click the Tables tab and select the table "new table" to view the data within the table as shown in Figure 22. Since no data has been added to the table, then we do not expect to see any data as demonstrated in Figure 23.

Figure 22 - View the Contents of the Database Table, I

Figure 23 - View the Contents of the Database Table, II

Step 26. We demonstrate how data can be added to newtable in Figure 24. Select the START button and then select RUN from the menu. Type cmd into the input box and select OK. A command box similar to Figure 24 will appear. Change to the PostgreSQL working directory, cd C:\Program Files\PostgreSQL\8.0\bin and execute the following command.

psql -d testdatabase -U username

This command puts the user "username" into the database named "testdatabase". To insert data into newtable which exists in testdatabase execute the following SQL query from the command line:

INSERT INTO newtable VALUES ('1', leverd);

Figure 24 also illustrates this example. Note what happens when we try to enter a new record with the same unique ID - the database alerts you to the duplicate key error.

Figure 24 - Adding Data to the Database Table

Step 27. To check if the data has been added to the table, right-click the Tables sub-menu and select View Data. As shown in Figure 25, the table contains the data that was added in Step 26.

Figure 25 - View the Contents of the Database Table, III

Part II

1. Please browse the PostgreSQL portal to identify additional database administration tools available to you. Briefly discuss, based on the information provided there, how you can use some of these tools to improve your database's usability, productivity and/or performance.
2. Find some references on the Web that compare Microsoft Access, MySQL, Oracle, and PostgreSQL. Summarize the basic points of these comparisons.
3. Many Web sites do NOT include a direct connection of the homepages to a database. Identify three sites that do. Explain, briefly, what evidence you have seen that support your assertion of the direct connection.
4. Beyond Web applications, please point out three examples (from any sources, such as RPI, your work, and open literature) of enterprise database system. Do you think that PostgreSQL could be used as the Database Management System to operate the databases in these examples - why or why not?