15-415 Database Applications

Fall 09

Getting Started with PostgreSQL

Where to Get Help

This document will help you get started on the PostgreSQL, the DBMS that will be used for all the homeworks. For beginners, we recommend reading the tutorials coming with PostgreSQL. The online version is at http://www.postgresql.org/docs/8.3/static/index.html

When you first

Before Running PostgreSQL

Only do this step if you want to change your shell.
The default profile in your accounts contains the required environment variables.
It is recommended that you do not change the default values.

FYI: Only If you change your shell to something other than bourne-shell variants, you should set the following environment variables:

  1. Add /usr0/local/bin to PATH variable
  2. Add /usr0/local/lib to LD_LIBRARY_PATH variable
  3. Set PGPORT to a unique port number. $UID should work as a unique port number.
  4. Set PGDATA to an existing writable directory. By default it is set to ~/db.

Step 1: Initializing Database

    $ ~badityap415/setup_db.sh

This program will setup a clean instance of the database for you. It does the following
  1. Creates a new database directory at $PGDATA, i.e. ~/db by default
  2. Creates a new database named by your user id, i.e. badityap415 for me.
  3. Loads the assignment data using ~badityap415/loading.sql file.

Now you have everything you need to run a database server!

In case the database gets curropted for some reason and you want to start afresh, you can always run the script again.

Step2: Starting the Server

You need to start the server every time you log in to a machine and stop it before you log out.  PostgreSQL comes with a utility called "pg_ctl" which allows for safe starting and stopping of the server.  Run the following command to start the server.

      $ pg_ctl start -o -i

Trouble shooting:

If you got the following complains
LOG: could not bind IPv4 socket: Address already in use
HINT: Is another postmaster already ru
nning on port 8888?

1) you need to kill the zombie postermasters (find the process ids by "ps -U user-id", then "kill -9 processid")
2) and rm the lock from /tmp/ (e.g. .s.PGSQL.4151 and .s.PGSQL.4151.lock),
3) or check if someone else is using that port, just change the PGPORT to other number (i.e., ls -la /tmp/ see if there are other locks on the port you set).

The option -o passes the quoted text to the server, which in this case the -i option tells the server to accept connections via TCP/IP. We recommend using

      $ pg_ctl start -s -l $PGDATA/serverlog -o '-i'

to start the server.

Step 3: Running SQL Commands

Now you are ready to add/delete/query data on the database you have just created! PostgreSQL comes with a utility called psql which enables interactive processing of SQL commands.

        $ psql [database]

Notice that the parameter to "psql" is the name of database on the server. If you do not specify a database, "psql" will attempt to connect to the database with the same name as your login (created for you by setup_db.sh). There are two ways to input queries. You can directly input SQL commands in the command line or you can use files.

Step 3.1: Using psql command

Once "psql" is started, you can enter any SQL command. Commands can be multi-line and MUST have a semicolon at the end. "psql" has its own prompt, which includes the database name and either a equals sign or hyphen followed by a pound sign. The hyphen tells you that a partial SQL statement has been entered. For example,

        test=# CREATE TABLE students
        test-#  (id int, firstname char(15), lastname char(15), email char(30));

Notice that after the first line, the prompt changed. This is particularly helpful when you enter a multi-line statement.  The above SQL command will create a table named "student" with four fields.

Step 3.2: Using files

Typing SQL directly into "psql" can be error-prone and leaves no log of the commands you ran. You may want to put a number of SQL commands into a file and then have "psql" run each of those commands. The file can contain multi-lined statements along with as much white space as you want. Once the file is ready, from within psql you can execute the file. For example:

        test=# \i query.sql

The file "query.sql" may contain the following SQL commands:

        $ more query.sql
        INSERT INTO students VALUES
        (1950009, 'Joe', 'Smith', 'jsmith@andrew.cmu.edu');

        SELECT * FROM students;

The above file will insert a row to the "students" table and show the content of the table.

Step 3.3: Using non-SQL psql commands

Apart from SQL, psql recognizes few other useful commands. You already know about \i, the following list describes a few other commonly used commands.

Please read the documentation for "psql" at: http://www.postgresql.org/docs/8.2/static/app-psql.html. We highly recommend at least skimming the online documentation.

PostgreSQL will compute the entire answer BEFORE it shows any results. When running long queries, it may appear as though the server has frozen, but it may just be working. You can stop a long running query by pressing ^C.

NOTE: In your SQL queries, you need to put table names (or attribute names) in double-quotes if they include capital letters. Otherwise you'll get an error from PostgreSQL that it doesn't recognize the table (attribute).

Step 4: Stopping Server

Stopping the server also uses the "pg_ctl" utility. Type the command:

        $ pg_ctl stop

Please stop the server before you log out from the system completely. If you keep forgetting to shutdown, please copy ~badityap415/bash_logout to ~/.bash_logout. This will ensure automatic shutdown on logout.

Optional Steps

Step 5: Creating Database

You can have multiple databases on the same server. This can be useful if you want to have separate projects (or just separate scratch space) on the server. SQL commands can not interact with more than one database at a time. Once you create a database, it exists until you delete it. That is, stopping the database server won't delete the database.

The following command will create a database named "test".

        $ createdb test

You should see a line CREATE DATABASE indicating the request was successful.  The opposite request:

        $ dropdb test

will remove the "test" database.

Step 6: Change the DBA Password (Optional)

PostgreSQL maintains its own database of users. By default the only user created is the same as the UNIX login of the person who ran the initdb command (i.e. your account). This account has all permissions. The account is not created with a password, so you should assign one. At the "psql" prompt type: (Replace the userid and password with your own login id and desired password.)

        test=# ALTER USER userid WITH PASSWORD 'password';

If the command is successful, "psql" will display "ALTER USER" After you've changed the password, you can exit "psql". Depending on the server configuration, password will be stored in clear-text or encrypted text. In our experimental environment, it will be stored in encryped text.

Step 7: Enable Security (Optional)

Now that you have defined a password for your account, you can enable security. The file "$PGDATA/pg_hba.conf" contains the configuration options for security. The file contains lots of comments describing the various options for the interested reader. We'll suggest a moderate policy. The end of the file (lines 47-48) contains the actual configuration. Please change the method from "trust" to "md5" at line 47 and line 48. After the change, anyone attempting to connect to the database (either by local UNIX or TCP/IP socket) will be required to specify a username and password. By default, "psql" will use your UNIX login as the username and then prompt for the password.

If you have altered your password by issuing the "ALTER USER username WITH UNENCRYPTED PASSWORD 'password'"" command, the password will be stored in clear-text and you need to use "password" instead of "md5" in the METHOD field.

$LastChangedDate: 2009-09-13 10:04:07 -0400 (Sun, 13 Sep 2009) $, $Author: badityap $