Getting Started
How to begin work with Postgres for a new user.
Some of the steps required to use Postgres
can be performed by any Postgres user, and some must be done by
the site database administrator. This site administrator
is the person who installed the software, created
the database directories and started the
postmaster
process. This person does not have to be the Unix
superuser ("root")
or the computer system administrator; a person can install and use
Postgres without any special accounts or
privileges.
If you are installing Postgres yourself, then
refer to the Administrator's Guide for instructions on
installation, and return
to this guide when the installation is complete.
Throughout this manual, any examples that begin with
the character "%" are commands that should be typed
at the Unix shell prompt. Examples that begin with the
character "*" are commands in the Postgres query
language, Postgres SQL.
Setting Up Your Environment
This section discusses how to set up
your own environment so that you can use frontend
applications. We assume Postgres has
already been
successfully installed and started; refer to the Administrator's Guide
and the installation notes
for how to install Postgres.
Postgres is a client/server
application. As a user,
you only need access to the client portions of the installation
(an example
of a client application is the interactive monitor
psql).
For simplicity,
we will assume that Postgres has been
installed in the
directory /usr/local/pgsql. Therefore, wherever
you see the directory /usr/local/pgsql you should
substitute the name of the directory where
Postgres is
actually installed.
All Postgres commands are installed in
the directory
/usr/local/pgsql/bin. Therefore, you should add
this directory to your shell command path. If you use
a variant of the Berkeley C shell, such as csh or tcsh,
you would add
% set path = ( /usr/local/pgsql/bin path )
in the .login file in your home directory.
If you use
a variant of the Bourne shell, such as sh, ksh, or
bash, then you would add
% PATH=/usr/local/pgsql/bin:$PATH
% export PATH
to the .profile file in your home directory.
From now on, we will assume that you have added the
Postgres bin directory to your path.
In addition, we
will make frequent reference to setting a shell
variable
or setting an environment
variable
throughout
this document. If you did not fully understand the
last paragraph on modifying your search path, you
should consult the Unix manual pages that describe your
shell before going any further.
If your site administrator has not set things up in the
default way, you may have some more work to do. For example, if
the database
server machine is a remote machine, you
will need to set the PGHOST environment
variable to the name
of the database server machine. The environment variable
PGPORT may also have to be set. The bottom
line is this: if
you try to start an application program and it complains
that it cannot connect to the postmaster,
you should immediately consult your site administrator to make
sure that your
environment is properly set up.
Starting the Interactive Monitor (psql)
Assuming that your site administrator has properly
started the postmaster process and
authorized you to
use the database, you (as a user) may begin to start up
applications. As previously mentioned, you should add
/usr/local/pgsql/bin to your shell search path.
In most cases, this is all you should have to do in
terms of preparation.
Two different styles of connections
are supported. The site administrator will have chosen to allow
TCP/IP network connections
or will have restricted database access to local (same-machine)
socket connections only.
These choices become significant if you encounter problems in
connecting to a database, since you will want to confirm that you
are choosing an allowed connection option.
If you get the following error message from a
Postgres
command (such as psql or
createdb):
% psql template1
Connection to database 'postgres' failed.
connectDB() failed: Is the postmaster running and accepting connections
at 'UNIX Socket' on port '5432'?
or
% psql -h localhost template1
Connection to database 'postgres' failed.
connectDB() failed: Is the postmaster running and accepting TCP/IP
(with -i) connections at 'localhost' on port '5432'?
it is usually because
the postmaster is not running,
or
you are attempting to connect to the wrong server host.
If you get the following error message:
FATAL 1:Feb 17 23:19:55:process userid (2360) != database owner (268)
it means that the site administrator started the
postmaster
as the wrong user. Tell him to restart it as
the Postgres superuser.
Managing a Database
Now that Postgres is up and running we
can create some
databases to experiment with. Here, we describe the
basic commands for managing a database.
Most Postgres
applications assume that the database name, if not specified, is
the same as the name on your computer
account.
If your database administrator has set up your account without
database creation privileges,
then she should have told you what the name of your database is. If
this is the case, then you
can skip the sections on creating and destroying databases.
Creating a Database
Let's say you want to create a database named
mydb.
You can do this with the following command:
% createdb mydb
If you do not have the privileges required to create a database,
you will see
the following:
% createdb mydb
WARN:user "your username" is not allowed to create/destroy databases
createdb: database creation failed on mydb.
Postgres allows you to create any
number of databases
at a given site and you automatically become the
database administrator of the database you just created.
Database names must have an alphabetic first
character and are limited to 32 characters in length.
Not every user has authorization to become a database
administrator. If Postgres refuses to
create databases
for you, then the site administrator needs to grant you
permission to create databases. Consult your site
administrator if this occurs.
Accessing a Database
Once you have constructed a database, you can access it
by:
Running the Postgres terminal
monitor programs
(e.g. psql) which allows you to
interactively
enter, edit, and execute SQL commands.
Using an existing native frontend tool like
pgaccess or
ApplixWare (via
ODBC) to create and manipulate a
database.
Using a language like perl or tcl which has a supported
interface for Postgres. Some of
these languages also have convenient and powerful GUI toolkits
which can help you construct custom
applications. pgaccess, mentioned
above, is one such application written in tk/tcl and can be
used as an example.
Writing a C program using
the LIBPQ subroutine
library. This allows you to submit
SQL commands
from C and get answers and
status messages back to
your program. This interface is discussed further
in The PostgreSQL Programmer's Guide.
You might want to start up psql,
to try out the examples in this manual.
It can be activated for the mydb
database by typing the command:
% psql mydb
You will be greeted with the following message:
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: template1
mydb=>
This prompt indicates that the terminal monitor is listening
to you and that you can type SQL queries into a
workspace maintained by the terminal monitor.
The psql program responds to escape
codes that begin
with the backslash character, "\" For example, you
can get help on the syntax of various
Postgres SQL
commands by typing:
mydb=> \h
Once you have finished entering your queries into the
workspace, you can pass the contents of the workspace
to the Postgres server by typing:
mydb=> \g
This tells the server to process the query. If you
terminate your query with a semicolon, the "\g" is not
necessary.
psql will automatically process
semicolon terminated queries.
To read queries from a file, say myFile, instead of
entering them interactively, type:
mydb=> \i fileName
To get out of psql and return to Unix, type
mydb=> \q
and psql will quit and return
you to your command
shell. (For more escape codes, type \h at the
monitor prompt.)
White space (i.e., spaces, tabs and newlines) may be
used freely in SQL queries. Single-line
comments are denoted by
"--". Everything after the dashes up to the end of the
line is ignored. Multiple-line comments, and comments within a line,
are denoted by "/* ... */".
Destroying a Database
If you are the database administrator for the database
mydb, you can destroy it using the
following Unix command:
% dropdb mydb
This action physically removes all of the Unix files
associated with the database and cannot be undone, so
this should only be done with a great deal of forethought.