PostgreSQL - Red Hat Edition: Graphical Tools Guide
PrevChapter 2. AdministratorNext

2.4. Tutorial

An excellent way to understand the functionality of a tool is through a tutorial. The following section is a tutorial on how to use Administrator.

It is assumed that during the installation of PostgreSQL - Red Hat Edition, the post-installer was run, which created the postgres userid and created a database cluster in /var/lib/pgsql/data. It is also assumed that Administrator was started by the superuser postgres.

Administrator uses JDBC to communicate with the PostgreSQL server and requires the use of TCP/IP. The following tutorial assumes that TCP/IP has been enabled and that the trust host-based authentication will allow connections from all locations. If you are unsure as to how to modify your PostgreSQL installation, refer to Section 6.4.3 Modifying Database Cluster Configuration Files for details on how to modify these values.

Topics to be covered in this tutorial are:

The tutorial uses the following design, which is a database named web_store that contains one user-defined schema named web_store_schema and four tables:

Figure 2-7. The web_store Design for the Tutorial

2.4.1. Adding a Cluster

After you start Administrator (as described in Section 2.2 Starting Administrator), the next task is to add a cluster, which is a collection of databases, users, and groups. When PostgreSQL - Red Hat Edition was installed, a cluster was created in /var/lib/pgsql/data, so that is used as the sample cluster.

Refer to the PostgreSQL 7.3 Administrator's Guide for details on creating a database cluster.

To add the cluster to the tool, from the Menu Bar choose File => Manage Clusters.

Figure 2-8. Accessing the Manage Clusters Dialog

The Manage Clusters dialog appears. In the Manage Clusters dialog, select New, which brings up the New Cluster dialog. The Nickname entered in this dialog will be displayed in the Tree View. The example uses a nickname of tutorial and connects to port 5432 on the local host with the postgres user name.

Figure 2-9. The New Cluster Dialog

Select OK to add the cluster. The cluster tutorial is now added to the list of clusters in the Manage Clusters dialog. Click [OK] to save the changes. The cluster tutorial is now visible in the Tree View.

Figure 2-10. The tutorial Cluster Detail View

Information about the cluster is detailed in the View Pane. Select the tutorial cluster and notice that the Status Bar has been updated to show the connection and current navigation context. Also note that the postmaster must be started on localhost for the connection to succeed.

To view the contents of the tutorial cluster, expand the tutorial node in the Tree View by clicking the [O-] beside the name.

Figure 2-11. The tutorial Cluster Tree View and Detail View

2.4.2. Creating a Database

In this section of the tutorial, you will create and comment on a database. The database will be named web_store and it will use the default owner, location, template, and encoding.

Select Databases in the Tree View to display the Databases Summary. The Summary View for Databases displays the name, owner, location and comment for each database in the cluster. There are two databases in the Database Summary, regression and template1 . The regression database was created by the PostgreSQL - Red Hat Edition post-installer (if the rh-postgresql-test package was installed and the "run regression test" option was selected at install time). The template1 database was created by initdb in the initial creation of the cluster. When the Databases Summary View is displayed, Administrator connects to each database listed in the view. The two connections listed on the Status Bar are connections to regression and template1.

Figure 2-12. The Databases Summary View

To create the database web_store, right-click on Databases in the Tree View and select CREATE DATABASE.

Figure 2-13. Accessing the CREATE DATABASE Dialog

The CREATE DATABASE dialog is displayed. Type the name for the database to be created in the Name For The New Database field: web_store

As mentioned, the defaults will be used for the optional parameters listed in the dialog.

Figure 2-14. The CREATE DATABASE Dialog

Click OK to create the new database.

Upon successful creation of the database, the Databases Summary View is updated to display the new web_store database. Notice that the Status Bar now has connections to all three databases listed in the Databases Summary View.

Figure 2-15. The Updated Databases Summary View

It is a good practice to add comments to objects. Add a comment to the database web_store by right-clicking web_store in the Tree View and selecting COMMENT.

Figure 2-16. Accessing the COMMENT Dialog

A COMMENT ON DATABASE dialog appears. Type in the Comment field: Tutorial sample database

Figure 2-17. The COMMENT Dialog

Click OK.

The Databases Summary View updates to include the new comment for database web_store.

To display details about the web_store database, select web_store in the Tree View. The database's object identifier (OID), owner, encoding, location, privileges, and comment are listed in the Detail View.

Figure 2-18. The web_store Database Detail View

Expand the web_store database Tree View to display the database object classes. Objects listed under Databases are Languages, Casts, and Schemas.

Figure 2-19. The web_store Database Tree View and Detail View

For more information on creating databases, refer to the CREATE DATABASE section of the PostgreSQL 7.3 Reference Manual.

2.4.3. Creating a Schema

In this section of the tutorial you will create a schema named web_store_schema. Defaults are used for the optional parameters.

Select Schemas in the Tree View to display the Schemas Summary. The Summary View for Schemas displays the name, owner, and comment for each schema in the database. When a database is created, two default schema are created with it: pg_catalog and public.

Figure 2-20. The Schema Summary View

To create a schema, right-click on Schemas listed under web_store and select CREATE SCHEMA.

Figure 2-21. Accessing the CREATE SCHEMA Dialog

The CREATE SCHEMA dialog is displayed.

Type the name for the schema to be created in the Name For The New Schema entry field: web_store_schema

Figure 2-22. The CREATE SCHEMA Dialog

Click OK to create the new schema.

Upon successful creation of the schema, the Schema Summary View is updated to display the new schema, web_store_schema.

Figure 2-23. The Updated Schema Summary View

To display details about web_store_schema, select web_store_schema in the Tree View. Objects listed under Schemas are Tables, Views, Sequences, Aggregates, Functions, Operator Classes, Operators, Data Types, Domains, and Conversions.

Figure 2-24. The web_store_schema Tree View and Detail View

For more information on creating schemas, refer to the CREATE SCHEMA section of the PostgreSQL 7.3 Reference Manual.

2.4.4. Creating a Table

In this section of the tutorial you will create a table.

To create a table, right-click on Tables listed under web_store_schema. The Table context menu appears. Select CREATE TABLE.

Figure 2-25. Accessing the CREATE TABLE Dialog

The CREATE TABLE dialog appears. Input for the CREATE TABLE command can be a very complex undertaking, so we will focus on just naming the table and creating columns.

For more information on creating tables, refer to the CREATE TABLE section of the PostgreSQL 7.3 Reference Manual.

You will be defining the table customers described at the beginning of the tutorial section, so type the name customers in the Name For The New Table entry box. Add the first column, customer_id with a data type of serial. Type customer_id in the Column Name field and type the data type serial in the Data Type entry field (or select the data type by using the combo-box arrow) using the Default Search Path for the schema identifier.

Figure 2-26. The CREATE TABLE Dialog

Click Add Column when you are finished. This adds the column to the table definition (but does not create the table). Do the same for each column of the table customers as shown in the following figure:

Figure 2-27. The Updated CREATE TABLE customers Dialog

When the last column (phone) has been added to the table definition, click OK to create the table. The Tables Summary View is updated to display the newly created table customers.

Figure 2-28. The Updated Tables Summary View

Select the table customers to display the Table Detail View.

Figure 2-29. The customers Table Detail View

Expanding the customers table node displays the classes of objects associated with the table. Columns, Constraints, Indexes, Rules, and Triggers are all defined under a Table.

Figure 2-30. The customers Table Tree View and Detail View

Expand the Columns node of the customers table and select the customer_id column. The Column Detail View displays information about the definition of that particular column.

Figure 2-31. The customer_id Column Detail View

2.4.5. Viewing Table Data

Starting with the release of Administrator 2.0, you are able to view table data. As there is no data in the customers table you just created, in this section you will view data from the pg_aggregate system table.

To view table data, we must navigate down to the pg_aggregate table resident in web_store's pg_catalog schema. Once there, right-click on pg_aggregate and select VIEW DATA.

Figure 2-32. Accessing the VIEW DATA Display

A screen appears detailing the rows and columns of data in the pg_aggregate table. By default, all data is displayed.

Figure 2-33. The pg_aggregate Table Data View

2.4.6. Creating Users and Groups

Creating a database installation is not just about data, it is also about who has access to what data. This section focuses on the creation of users and groups. The concepts behind users and groups are explained in the PostgreSQL 7.3 Administrator's Guide.

Refer to the CREATE USER section of the PostgreSQL 7.3 Reference Manual for more information on creating users.

When PostgreSQL - Red Hat Edition was installed, the user postgres was created. Select Users in the Tree View to display a summary of all the users in the tutorial cluster (in this case, the user postgres).

Figure 2-34. The tutorial Users Summary View

Click the [O-] beside Users in the Tree View to expand the Users node.

To create a user, right-click on Users and select CREATE USER.

Figure 2-35. Accessing the CREATE USER Dialog

The CREATE USER dialog is displayed. Insert information for the user new_user. Give this user "Create Database" and "Create User" privileges.

Figure 2-36. The CREATE USER Dialog

Click OK to create the user.

The user new_user has been created and the Tree View and Summary View have been updated accordingly.

Figure 2-37. The Updated tutorial Users Tree View and Summary View

To display information about the user, click the new_user node on the Tree View or on the new_user link in the Summary View. Information about this user appears in the View Pane.

Figure 2-38. The new_user User Detail View

2.4.7. Creating Groups

It is common practice to grant privileges to groups of users instead of to each user individually. When PostgreSQL - Red Hat Edition was installed, no groups were defined, so your next step will be to create a group and add users to it.

Refer to the CREATE GROUP command in the PostgreSQL 7.3 Reference Manual for more information on creating groups.

Figure 2-39. The tutorial Groups Summary View

To create a group with users postgres and new_user, right-click on the Groups node and select CREATE GROUP.

Figure 2-40. Accessing the CREATE GROUP Dialog

The CREATE GROUP dialog is displayed. Insert information for the group new_group. Notice that under Optional Parameters, a list of users defined for the cluster is displayed. Highlight the users you wish to include in the group. In this case, highlight new_user and postgres. (To do this, either press the [Ctrl] and select the users or select the first user, press [Shift] and select the last user to highlight all users.)

Figure 2-41. The CREATE GROUP Dialog

Next, click Add to move the highlighted users to the Users In Group list. The highlighted users are moved.

Figure 2-42. The CREATE GROUP new_group Dialog

Click OK to create the group.

The group new_group has been created and the Tree View and Summary View have been updated accordingly.

Figure 2-43. The Updated tutorial Groups Tree View and Summary View

2.4.8. Dropping Database Objects

Up to this point, the tutorial has been about creating database objects, users, and groups. Part of administering a database cluster is the deletion (or dropping) of objects. Dropping of objects from within Administrator is straightforward and consistent. In this section you will drop the database web_store as an example.

In the Tree View, right-click on the web_store database and select DROP DATABASE.

Figure 2-44. Accessing the DROP DATABASE Command

CautionCaution
 

If Preferences => Ask For Confirmations is enabled, a confirmation message is displayed; if you want to drop the object, select Yes; otherwise, select No. If Preferences => Ask For Confirmations is disabled, the database is dropped immediately.

Figure 2-45. The DROP DATABASE Confirmation

After the object is successfully dropped, the Tree View and Summary View are updated.

Figure 2-46. The Updated tutorial Databases Tree View and Summary View

For more information about dropping database objects, refer to the DROP commands listed in the PostgreSQL 7.3 Reference Manual.

PrevHomeNext
Using AdministratorUpTroubleshooting Administrator