Skip to content

SettingUpPostgres

Jump to bottom
Joe Hellerstein edited this page Dec 8, 2015 · 1 revision

Setting up PostgreSQL and Other Files/Scripts

Everything needed for the second part of the assignment is available in an svn repos at ~cs186/sp09/postgress-8.0.3 ~cs186/sp09/hw1 on x86 linux instructional machines.

Note on conventions: In the labs we will use gray boxes with the '$' prompt to signify text that you should type in verbatim to the shell on one of the instructional machines using your cs186 account. Below we will provide an explanation of the commands you are entering.

To setup the environment start by:

$ /share/b/bin/mkhometmpdir
$ svn co file:///home/ff/cs186/sp09/postgres-8.0.3 /home/tmp/$USER/postgres-8.0.3
  1. This is a script set up by the instructional computing support to create a special temporary directory. We will keep the source code and other large things here to prevent problems with disk quotas. You should clear this directory out (since it won't contain anything you can't recreate easily) after you are done with the project
  2. This checks out the slightly modified (for space reasons) version of the postgres source tree from Subversion.

You can build and install PostgrSQL by using these commands:

$ cd /home/tmp/$USER/postgres-8.0.3
$ ./configure --prefix=$HOME/pgsql --enable-debug --enable-cassert
$ make
$ make install
  1. Move to the checked out source tree.
  2. ./configure is a script found in many unix/linux programs that automatically customizes the build process for your specific computer. It is auto generated by autoconf (This might take a while).
  3. Build the source using make (This might take a while).
  4. Install postgres into the location you specified during configuration (--prefix=$HOME/pgsql).

Now that you have build and installed postgres, you can test your installation by running the following commands:

$ ~/pgsql/bin/initdb -D ~/pgdata
$ ~/pgsql/bin/pg_ctl -D ~/pgdata -l pre.log start
$ ~/pgsql/bin/psql template1
  1. Initialize a default empty database for postgres to use at ~/pgdata.
  2. Start the background server using the the blank environment you just initialized and the logfile ./pre.log (You can look in here for debugging information about the running server).
  3. psql is a shell that you can type SQL commands or special postgres commands into (starting with a ). Since we have not yet created a database yet, we will use the default one "template1".

'''Note: If you encounter an error like "FATAL: user "" does not exist", you may be trying to connect to another student's Postgres instance on the same port number. See the troubleshooting section below. '''

This will open a postgres prompt from which you can type SQL commands to the database. Below are some example commands to try out. Feel free to play around to get a good feel for SQL. Can you implement the examples we talked about in class? To quit simply press ctl-d or \q.

template1=# CREATE DATABASE test;
template1=# \connect test;
test=# CREATE TABLE test_table (field VARCHAR(25));
test=# INSERT INTO test_table (field) VALUES('Hello World');
test=# SELECT * FROM test_table;
test=# DROP TABLE test_table;
  1. This is DDL to create a new database
  2. \connect lets you switch switch the connection to a new database (in this case from template1 to test)
  3. Create a table 'test_table" with a single field named 'field'.
  4. Insert the string 'Hello World' into the database. Note the use of single quotes instead of double quotes.
  5. Retrieve all the data from test_table
  6. Delete our experimental table.

The psql client can execute a batch of SQL commands saved in a file. You'll find this a straightforward way to avoid retyping while experimenting with syntax. Create a file (statements.sql) with these contents:

create table course (
  cid int,
  name varchar(20),
  room int,
  constraint pk_course primary key (cid)
);

create table student (
  sid int,
  name varchar(20),
  year int,
  constraint pk_student primary key (sid)
);

create table enrollment (
  cid int,
  sid int,
  constraint fk_student foreign key (sid) references student,
  constraint fk_course foreign key (cid) references course
);

You can execute the three DDL statements in batch like this:

$ ~/pgsql/bin/psql template1 -f statements.sql

Create another file (instance.sql) with this DML:

insert into course values (1, 'databases',302);
insert into course values (2, 'OS',310);
insert into course values (3, 'algorithms',325);
insert into course values (2,'computer vision',607);

insert into student values (1, 'michael', 1);
insert into student values (2, 'david', 2);
insert into student values (3, 'jim', 3);
insert into student values (4, 'pat', 4);
insert into student values (2, 'hector', 4);
insert into student values (5, 'jeff', 4);

/* these guys are all taking databases */
insert into enrollment values (1,1);
insert into enrollment values (1,2);
insert into enrollment values (1,3);
insert into enrollment values (1,4);
insert into enrollment values (1,5);

/* jeff is taking theory too */

insert into enrollment values(3, 5);

/* sorry, jeff */
delete from course where name = 'algorithms';

Run it:

$ ~/pgsql/bin/psql template1 -f instance.sql

Shut down Postgres:

$ ~/pgsql/bin/pg_ctl -D ~/pgdata stop

For more information about PostgreSQL usage, you can consult the version 8.0 manual.

Troubleshooting

Port conflict

The following is not recommended for class. Each class account has a unique port assigned to it via the PGPORT environment variable. You should not need to change it. Odds are good that if you do change it, you will accidentally conflict with somebody else!

That said, here are a few tips/commands to help you troubleshoot if you do run into a port conflict:

(1) See what port you are assigned:

$ echo $PGPORT

(2) To see if a port is taken, first stop postgres with the pg_ctl stop command. If that does not work for some reason, try to directly kill postgres:

$ pkill postgres

Next, after you're sure you are not running postgres yourself, look for a free port:

$ netstat -an | grep '<newport>'

If there is no output, you're good to go... if there is output, pick another port

(3) Change your PGPORT per shell session (you'll have to redo this every time you log in), like this:

$ setenv PGPORT <newport>

(4) Permanently change the port of your Postgres instance. Do either (3) or this approach, but not both.

First shut down the instance. Then change your port number in the Postgres configuration file using your favorite text editor, e.g:

$ ~/pgsql/bin/pg_ctl -D ~/pgdata stop
$ emacs ~/pgdata/postgresql.conf

Edit the line with the port number, making sure to remove the comment (#) at the beginning of the line. Port numbers within 129 - 32768 should be usable (but make sure it's not already taken!). Start your instance as usual, and start the your database connection using your specified port number.

$ ~/pgsql/bin/pg_ctl -D ~/pgdata -l <log name>.log start 
$ ~/pgsql/bin/psql template1 -p <port>
Clone this wiki locally