PostgreSQL resources

PostgreSQL on CDF for St. George Students

The environment

• There are two postgreSQL servers on CDF. You are assigned to one of these two servers based on your last name:
  • dbsrv1.cdf for students whose last name starts with A through L
  • dbsrv2.cdf for students whose last name starts with M through Z
  Please use only the server that is assigned to you. This will distribute the load more evenly and allow you to always know which server you have stored your work on.
• The CDF administrators have created a PostgreSQL user for each student in this course, on these servers.
  The user name is the same as the student' CDF login name.
  The server is accepting only local connections (i.e. no networked connections), and is trusting the Unix user authentication (i.e., the students do not need a password to connect).
• The CDF administrators have set up a separate database for each student in this course, called csc343h-<your username>.
  
  

Running PostgreSQL on CDF:

• To interface with the PostgreSQL server(s), use the psql command. For more information on this command, use
  • man psql, and/or
  • PostgreSQL documentation.
  It can be used in both interactive and batch mode.
• To connect to the database:
  1. ssh dbsrv[1, 2] (check The environment above to determine whether to use 1 or 2!)
     note: if you are accessing cdf remotely: ssh -x -l dbsrv[1, 2].cdf.toronto.edu — UTM students will need to do this)
  2. issue one the following commands:
     • Interactive mode: psql csc343h-username
       This will give the student an interactive shell to type in SQL queries.
     • Batch mode: psql <file>,
       where <file> is a file containing your SQL queries.
• Having connected to a database, you can issue PostgreSQL commands.

PostgreSQL in Bladen Computer Labs for UTSC Students

The environment

1. Login to the node that hosts PostgreSQL

2. Interface with the PostgreSQL

• Interactive mode: psql -U <database_username>
              This will give you an interactive shell to type in SQL queries.

• Batch mode: psql -U <database_username> < <file>
              where <file> is a file containing your SQL queries.
              An example is: psql -U huangtest < test1.sql

You can check out PostgreSQL documentation in the man page ( man psql ), and/or at http://www.postgresql.org/docs/8.2/interactive/index.html

Or better yet ... download it and run it on your home computer!

Downloads, documentation, discussions and much more can be found at the PostgreSQL home page.

For those of you who are having problems installing PostgreSQL on your home machines, I reccomend that you exchange ideas on the course newsgroup to help each other out and learn from each other's mistakes and make extensive use of Google and documentation available at PostgreSQL.

Download:

• FTP site
• Binary files
• Source

PostgreSQL Documentation:

Installation

• Windows: FAQ | Screenshots
  
• Mac: Apple Developer | Some Binaries | pgsqlformac
  
• Linux: FAQ | Install     Most linux distros come with postgresql preinstalled; Please check yours.

JDBC

• The JDBC driver is installed at the CDF as well.  
• The JAR file is in /local/packages/jdbc-postgresql.  
• There also are files HelloPostgresql.java and HelloPostgresql.txt in the same directory.
  These files gives an example of using the JDBC driver at the CDF.

• If you are required to use Java to interface with the PostgreSQL, you need to incorporate the JDBC driver already installed on node3201.
• The path for the driver is /usr/postgresql/postgresql/share/postgresql.jar.
• There are also example java files along with the readme which explain how to run it:
  • /usr/postgresql/postgresql/share/HelloPostgresql.java
  • /usr/postgresql/postgresql/share/HelloPostgresql.ReadMe

Resources at PostgresSQL.org:


PostgreSQL JDBC Driver | FAQ | Tutorial



EBooks

Practical PostgreSQL

For everything else, there is always our good friend, Google!

---