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:
Adding a cluster
Creating a database
Creating a schema
Creating a table
Viewing table data
Creating a user
Creating a group
Dropping a database.
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:
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.
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.
Select OK to add the cluster.
The cluster tutorial is now added to the list of clusters in the
Manage Clusters dialog.
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.
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.
To create the database web_store, right-click on Databases in the Tree View and select CREATE DATABASE.
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.
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.
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.
A COMMENT ON DATABASE dialog appears. Type in the Comment field: Tutorial sample database
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.
Expand the web_store database Tree View to display the database object classes. Objects listed under Databases are Languages, Casts, and Schemas.
For more information on creating databases, refer to the CREATE DATABASE section of the PostgreSQL 7.3 Reference Manual.
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.
To create a schema, right-click on Schemas listed under web_store and select CREATE SCHEMA.
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
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.
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.
For more information on creating schemas, refer to the CREATE SCHEMA section of the PostgreSQL 7.3 Reference Manual.
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.
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.
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:
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.
Select the table customers to display the 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.
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.
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.
A screen appears detailing the rows and columns of data in the pg_aggregate table. By default, all data is displayed.
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).
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.
The CREATE USER dialog is displayed. Insert information for the user new_user. Give this user "Create Database" and "Create User" privileges.
Click OK to create the user.
The user new_user has been created and the Tree View and Summary View have been updated accordingly.
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.
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.
To create a group with users postgres and new_user, right-click on the Groups node and select CREATE GROUP.
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
Next, click Add to move the highlighted users to the Users In Group list. The highlighted users are moved.
Click OK to create the group.
The group new_group has been created and the Tree View and Summary View have been updated accordingly.
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.
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.
After the object is successfully dropped, the Tree View and Summary View are updated.
For more information about dropping database objects, refer to the DROP commands listed in the PostgreSQL 7.3 Reference Manual.