CSL PostgreSQL Database Service

PostgreSQL 8.3 is now supported. The port number is now 5432, please see below for more details. The PostgreSQL 8.2 server on postgres.cs.wisc.edu (running on port 49173) has been turned off as of May 27th, 2008.

What Is This?

The CSL PostgreSQL database service lets you use databases for personal, class or research projects without having to worry about administering them. The CSL keeps the server running, upgrades it and makes daily backups.

How Do I Request A Database?

To request a database, fill out the "Request a PostgreSQL database" form on http://www-auth.cs.wisc.edu.

How Do I Use The Database?

The server is running on postgres.cs.wisc.edu on port 5432. Here's how to connect with a bunch of different programs:

psql (Command Line Interface)

/s/postgresql/bin/psql -h postgres.cs.wisc.edu -p 5432 <database name>

Please see the man page for psql for more information.

Perl

#!/s/std/bin/perl

use DBI;
use CSL::Pg;
$dbh = DBI->connect("dbi:Pg:dbname=<database name>;host=postgres.cs.wisc.edu;port=5432");

Please see the man pages for DBI and DBD::Pg for more information.

PHP

<?php
$dbh = pg_connect("host=postgres.cs.wisc.edu dbname=<database> port=5432");
?>

Python

#!/s/python-2.5/bin/python
from pyPgSQL import PgSQL
db=PgSQL.Connection("host='postgres.cs.wisc.edu' port='5432' dbname='<database>'")

For More information

• PostgreSQL website: All sorts of PostgreSQL-related information.
• Comprehensive Perl Archive Network: Information on the DBI and DBD::Pg Perl modules.
• PHP PostgreSQL pages: Information on using PHP with your database.
• pyPgSQL project page: Using the pyPgSQL Python module

Why Can't I Use Java?

We use Kerberos authentication on our database and the existing JDBC drivers do not support it. At this time you can not use the CSL database server with Java. If you find information to the contrary, please let us know.

With the impending release of PostgreSQL 8.3, JDBC support may be included. Testing against beta versions has been promising but there are still bugs to work out. Stay tuned.

How Can I Share The Database With Others?

By default, your database will have public usage rights on the default schema public, but if a user is not granted the proper access to a particular table, he/she/it may not access it. Likewise, if you create a new schema (a way of organizing and keeping tables...a schema is like a directory and tables are like files), only you will initially have access rights. In order to share your tables, schemas, and other database objects, you will have to GRANT the appropriate access to others. Please research the GRANT and REVOKE commands on the PostgreSQL website. For instance, if you would like to give a web script (running as www-user) all access to read and write a specific table in your database, run the following after connecting to the database:

• GRANT ALL ON [table] TO "www-user"; (The quotes are important)

Please keep in mind that anyone running a script as www-user will be able to access your database. Keep these caveats in mind when managing your database. For these reasons, we highly recommend that you do not give anything more than SELECT (read) access to your tables and USAGE access to your databases and schemas to PUBLIC (which is a PostgreSQL alias for "any user who can connect to your database"). It is highly recommended that access is given only to those who need it.

The scope of the PostgreSQL authorization and authentication scheme is too grand for this document. Please, as always, consult the PostgreSQL website for the most accurate, up to date information.

Are There Quotas On The Database?

There are no official disk space or bandwidth quotas on postgres.cs.wisc.edu. However, please remember that many people and projects use this server. Therefore, please try to keep your database size and I/O bandwidth usage low (about 5 GB of raw data is a practical limit). If you have any specific questions or if you need to run intensive queries on a very large database, please contact the lab and we will do our best to resolve the issue. If you are creating problems for other users on postgres.cs.wisc.edu, the lab will contact you and work with you to resolve the issue.

What About Feature <X>?

PostgreSQL is a very feature-rich database system, even in its base set. Included in the distribution are a number of useful, though non-default and occasionally unverified modules and server-side languages which can be activated on a per-database basis. For security and space considerations, we only activate the built-in PostgreSQL server-side language plpgsql by default. All other languages and modules are not activated by default, and must often be activated by an administrator.

If you need or would like one of these modules or languages in your database, please mail lab@cs.wisc.edu specifying the database, the module, and your reason for needing or wanting said module. Requests will be handled on a case-by-case basis and we will do what we can to get it installed in your database.

PostgreSQL Servers

Users may not install PostgreSQL database servers on the CSL network. Research projects that have special needs for a private PostgreSQL database server should contact the CSL by sending email to lab@cs.wisc.edu. We will work with you to install and maintain a server on an appropriate computer.

Where can I learn more about PostgreSQL?

Unfortunately, the scope of PostgreSQL is far too great for this document. As always, if you have questions, if you want to learn something, or if you just want to see official documentation, please consult the PostgreSQL website.