PostgreSQL Documentation

Connecting to the PostgreSQL data base manager (applies to v 9.1.3)

On the client side, there needs to be a postgresql driver file (xxxxx.jar) in the CLASSPATH. I am using postgresql-9.1-902.jdbc4.jar obtained from http://jdbc.postgresql.org/download.html. This particular one is needed for postgresql v 9.1.3 and sun java v 1.6 or java v 1.7. In what follows it is assumed postgresql v 9.1.3 is already installed. The example that follows is taken from from experiments on gauss.ececs.uc.edu as the server machine and my laptop as the client machine. Hence there are numerous references to franco. These should be changed accordingly for other users. A database server is started, a database created, and data items added using the steps below.

  1. A Database must be created. Use the following command on the machine that will be running the server as user postgres:

       pg_ctl -D /home/httpd/html/Courses/c694/data initdb
    

    where /home/httpd/html/Courses/c694/data is the directory used in this example and should be replaced by the desired data repository. This directory will be owned by postgres so postgres needs permission to create it and its contents. Some of the config files in this directory may need to be modified before starting the server. In /home/httpd/html/Courses/c694/data edit file pg_hba.conf (still on the server machine) and uncomment or add the lines

           local   all    all          ident
           host    all    127.0.0.1    ident
           host    all    0.0.0.0      ident
    
    the last line may not be needed.

  2. Start the dbms server. Use the following command as user postgres:

       pg_ctl -D /home/httpd/html/Courses/c694/data -l log start
    

    The server is now listening on port 5432.

  3. Create a role for a user. The postgres administrator must create a role for franco:
      sudo su
      su postgres
      postgres> psql
      postgres=# CREATE ROLE franco WITH LOGIN CREATEDB;
      CREATE ROLE
      postgres=# \du
                                   List of roles
       Role name |                   Attributes                   | Member of
      -----------+------------------------------------------------+-----------
       franco    | Create DB                                      | {}
       postgres  | Superuser, Create role, Create DB, Replication | {}
    

  4. Create a database to connect to. The data base can be created locally (client) or remotely (server machine). On the remote machine issue the following to create a database called franco_db as user franco:

           createdb franco_db
    

  5. The Driver classes must be visible to the client Java code. Suppose all the code is to be run from the current directory. Download the latest driver from http://jdbc.postgresql.org/download.html and put the file in the current directory. The file is postgesql-9.1-902.jdbc4.jar. Do this: jar xf postgesql-9.1-902.jdbc4.jar The result is a directory org with subdirectory postgresql. In that subdirectory find Driver.class. This is the java driver that enables connection to postgresql. It may be necessary to do something like this: export CLASSPATH=.:$CLASSPATH.

  6. Connect to franco_db. To connect to the franco_db database on host gauss.ececs.uc.edu use:

          String url = "jdbc:postgresql://gauss.ececs.uc.edu/franco_db";
          ...
          try {
             Class.forName("org.postgresql.Driver");
          }
          ...
          con = DriverManager.getConnection(url, "franco", "");
    

    See, for example, CreateCoffees.java. Running that class file adds COFFEES to the franco_db database which can easily be seen in the ../Data/base/franco_db directory on the server machine. The same holds for CreateSuppliers. You can run the code as follows:

         java -Djdbc.drivers=org.postgresql.Driver CreateCoffees gauss.ececs.uc.edu
    

    But in the given source code the Driver is specified as

         Class.forName("org.postgresql.Driver");
    

    so the -D option is not necessary. Use the following Java line to see verbose messages (on the console) while connecting:

         DriverManager.setLogWriter(new printWriter(System.out));
    

    Do the same for CreateSuppliers.java. After InsertCoffees and InsertSuppliers compile and run PrintColumns.java to see the new table. Display the results using OutputApplet.java.

  7. Miscellaneous. Procedure for tunneling to gauss. The following uses this Makefile.

    Using this Makefile to compile and run the code, do the following: