15-415 Database Applications

Spring 12

Getting Started with PostgreSQL

Where to Get Help

When you first

Before Running PostgreSQL

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

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. binf415 for me.
3. Loads the assignment data using ~/../binf415/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 running 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

        $ 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.

• To describe tables in the database, use \d or \d table_name
• To exit from psql, use \q
• To edit your query in vi instead of psql command line, use \e

Please read the documentation for "psql" at: http://www.postgresql.org/docs/8.3/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 ~/../binf415/bash_logout to ~/.bash_logout. This will ensure automatic shutdown on logout.

Optional Steps

Step 5: Creating Database

        $ createdb test

        $ dropdb test

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';

Step 7: Enable Security (Optional)

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.