Main

Course FAQ

This is the Course FAQ. You may add comments/tips here for the entire class.

Install PostgreSQL

First we need to install the database server.

Download the one click install from the EnterpriseDB site. They have the complete installers for Linux, Mac OS X and Windows.

In case you want to do a custom or source install, then do so from the PostgreSQL website.

Install GUI Frontend

The default PostgreSQL installation comes with the pgAdmin III tool that allows you to interact with the server and to issue queries against the database.

Here are the steps to follow to get to the SQL query tool in pgAdmin. First, launch pgAdmin, and click the "connect" to server button on top left corner. This will pop up the following window:

Next follow the steps shown in the fig below:

On Mac and Windows platforms, you can also download the Navicat GUI Lite version (free).

Documentation

The PostgreSQL documentation can be consulted for additional details.

The psql command shell documentation shows the list of commands and their descriptions. Note that the shell commands can be listed by typing \? at the command line, and the available SQL commands can be obtained by typing \h. More detail of a specific command like SELECT can be obtained by typing \h SELECT.

Finally pgAdmin III documentation shows the various tools and their usage.

Running PostgreSQL on Windows 7 Build 7000 (after Installing)

1. Make a directory C:\pgsql 2. Copy C:\Program Files\PostgreSQL\8.3\data to C:\pgsql\data

3. Right click on C:\pgsql\data\, go to Properties

   * General Tab
       - Uncheck Read-only
       - Click "Advanced"
            * Ensure Content indexing is disabled/unchecked
            * Click OK
        - Click Apply
    * Security Tab
        - Click Advanced
             * Owner Tab
                 - Edit
                 - Accept the UAC prompt that comes up
                       * Click 'Other users or groups' button
                           - enter 'postgres' in the text box (sans single quotes),
                           - click ok
                       * Check  "Replace owner on subcontainers and objects"
                       * Click OK (owner dialog)
                   - Click OK (advanced secuity dialog)
     * Click OK ('data properties dialog)

4. Make a new shortcut to start postgres:

           * "C:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe" start -D "/pgsql/data" -w

             * Note that the path to the data directories does -NOT- start with C:

5. Make a shortcut to stop it

           * "C:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe" stop -D "/pgsql/data" -w

6. Place shortcuts somewhere convenient

7. use start and stop shortcuts appropriately

You're going to have an annoying little taskbar window running while you use postgres, but at least you can use it for the moment.

If it still does't work, the only other related thing I changed was enabling the Secondary Logon Service (Start - Right click Computer -> Manage -> Accept UAC prompt -> Services and Application -> Services -> Secondary Logon -> Startup Type: Automatic -> Start -> OK. I doubt you need this, but I figured I'd mention it.

For good measure, it wouldn't hurt to put postgresql-8.3 as "Manual start", since we won't be needing a broken service running anyhow.