man when you want to learn details
about a specific client/server/SQL command. For beginners, we recommend
reading the tutorials coming with PostgreSQL, online version is at
http://www.postgresql.org/idocs/index.php?tutorial.html.
You can also find the tutorials with v7.3 at
/usr/local/pgsql7_3/doc/html/tutorial.html.
For project0, you will use a shared installation of PostgreSQL (7.3) binaries. The installation is compiled for Itanium architectures and located at /usr/local/pgsql7_3. Related paths have been added to your PATH environment variable.
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, it is $HOME/pgdata (type echo $PGDATA to check it). Alternatively, you can specify the directory of the cluster at the command using the -D path option.
Type the command: initdb
A script will run that creates the directories, installs system tables and then exits. It should take a few seconds to a couple of minutes depending on system load. You should run this command once.
-p port_num 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. The port number can't be changed after server starts. We recommend
using
pg_ctl start -s -l $PGDATA/serverlog -o '-i'
to start the server
Type the command: createdb test
dropdb
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 hyphen followed by a pound sign. The hyphen tells you that a partial SQL
statement has been entered. For example: (the following is just to show what
psql looks like when you type SQL command, there is no table named student unless
you create it)
User=# SELECT firstname, lastname
User-# FROM students;
Notice that after the first line, the prompt changed. This is particularly helpful when you enter a multi-line 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:
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).User=# \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 white space as you want. Once the file is ready, from within psql you can execute the file: for example:
User=# \i schema.sql
When you are finished and want to exit psql:
User=# \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. 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:
ALTER USER userid WITH PASSWORD 'password';
pg_ctl stop
export DISPLAY=machine.andrew.cmu.edu:0.0
xhost + "Itanium_machine_name"
Above are basic steps you usually need to set up your database server.
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