For the first project, you will use a shared installation of PostgreSQL (version 7.2.2) binaries. The installation is compiled for x86 architectures. You should be able to use rhombus.cs.berkeley.edu, pentagon.cs.berkeley.edu, and torus.cs.berkeley.edu. Although po.eecs.berkeley.edu will run the software, we have been advised by instruction support NOT to use that machine.
The first step is to initialize a 'database cluster' which is where PostgreSQL will store all your data files. The environmental variable PGDATA is the directory where the cluster will be initialized. By default, this $HOME/pgdata. Alternatively, you can specify the directory of the cluster at the command using the -D /thepath option.
Type the command: initdb
A script will run that creates the directories, starts the database server, installs system tables and then exits. It should take about 30 seconds to a couple of minutes depending on system load. You should run this command once.
Each PostgreSQL server must run a separate port. We have assigned each login account a port which is automatically assigned to the environmental variable PGPORT. You can check your assignment by typing echo $PGPORT. Alternatively, you could specify the port when starting the server using the -p portnum option.
You do not have to do anything for this step, it is automatically handled.
PostgreSQL comes with a utility called pg_ctl which allows for the safe starting and stopping of the server.
Type the command: pg_ctl start -o '-i'
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.
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.
Type the command: createdb test
You should see a line CREATE DATABASE indicating the request was successful. The one parameter test is the name of the database that should be created. You can issue additional createdb commands. The opposite request, dropdb, will remove a database.
PostgreSQL comes with a utility called psql which enables interactive processing of SQL commands.
Type the command: psql test
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.
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 hypen follwed by a pound sign. The equals or hypen tells you whether a partial SQL statement has been entered. For example:cs186-groups=# SELECT firstname, lastname
cs186-groups-# FROM students;
Notice that after the first line, the prompt changed. This is particularly helpful when you enter a multiline statement.
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.
psql stores that last query you ran in a buffer. You can access the buffer and modify the query. This is especially helpful if it is a long command and you made a mistake. By default EMACS is used as the editor, however you can change this by setting the EDITOR environmental variable. To access the buffer:cs186-groups=# \e
Once you finish editing the buffer, exit and SAVE. psql will automatically run the query.
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 whitespace as you want. Once the file is ready, from within psql you can execute the file:cs186-groups=# \i schema.sql
When you are finished and want to exit psql:cs186-groups=# \q
psql has a number of useful features, read the documentation for psql at: http://www.postgresql.org/idocs/index.php?app-psql.html. We highly recommend at least skimming the online documentation.
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. cs186-xx). This account has all permissions. The account is not created with a password, so you should assign one.
At the psql prompt type: ALTER USER "userid" WITH PASSWORD 'temp';
Be sure to replace userid with your login, and temp with the password you want. The password is stored in clear text, so do not use the same password you use elsewhere. If the command is successful, psql will display ALTER USER. After you've changed the password, exit psql.
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 180-181) contains the actual configuration.
Change line 180 to: local all password
Change line 181 to: host all 127.0.0.1 0.0.0.0 password
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.
Stopping the server also uses the pg_ctl utility
Type the command: pg_ctl stop
For your protection, whenever you log off the system will automatically run pg_ctl stop for you.
For SQL reference: http://www.postgresql.org/idocs/index.php?sql-commands.html
For client utilities: http://www.postgresql.org/idocs/index.php?reference-client.html
For server setup: http://www.postgresql.org/idocs/index.php?runtime.html