1
<!-- doc/src/sgml/start.sgml -->
3
<chapter id="tutorial-start">
4
<title>Getting Started</title>
6
<sect1 id="tutorial-install">
7
<title>Installation</title>
10
Before you can use <productname>PostgreSQL</productname> you need
11
to install it, of course. It is possible that
12
<productname>PostgreSQL</productname> is already installed at your
13
site, either because it was included in your operating system
14
distribution or because the system administrator already installed
15
it. If that is the case, you should obtain information from the
16
operating system documentation or your system administrator about
17
how to access <productname>PostgreSQL</productname>.
21
If you are not sure whether <productname>PostgreSQL</productname>
22
is already available or whether you can use it for your
23
experimentation then you can install it yourself. Doing so is not
24
hard and it can be a good exercise.
25
<productname>PostgreSQL</productname> can be installed by any
26
unprivileged user; no superuser (<systemitem>root</systemitem>)
31
If you are installing <productname>PostgreSQL</productname>
32
yourself, then refer to <xref linkend="installation">
33
for instructions on installation, and return to
34
this guide when the installation is complete. Be sure to follow
35
closely the section about setting up the appropriate environment
40
If your site administrator has not set things up in the default
41
way, you might have some more work to do. For example, if the
42
database server machine is a remote machine, you will need to set
43
the <envar>PGHOST</envar> environment variable to the name of the
44
database server machine. The environment variable
45
<envar>PGPORT</envar> might also have to be set. The bottom line is
46
this: if you try to start an application program and it complains
47
that it cannot connect to the database, you should consult your
48
site administrator or, if that is you, the documentation to make
49
sure that your environment is properly set up. If you did not
50
understand the preceding paragraph then read the next section.
55
<sect1 id="tutorial-arch">
56
<title>Architectural Fundamentals</title>
59
Before we proceed, you should understand the basic
60
<productname>PostgreSQL</productname> system architecture.
61
Understanding how the parts of
62
<productname>PostgreSQL</productname> interact will make this
63
chapter somewhat clearer.
67
In database jargon, <productname>PostgreSQL</productname> uses a
68
client/server model. A <productname>PostgreSQL</productname>
69
session consists of the following cooperating processes
75
A server process, which manages the database files, accepts
76
connections to the database from client applications, and
77
performs database actions on behalf of the clients. The
78
database server program is called
79
<filename>postgres</filename>.
80
<indexterm><primary>postgres</primary></indexterm>
86
The user's client (frontend) application that wants to perform
87
database operations. Client applications can be very diverse
88
in nature: a client could be a text-oriented tool, a graphical
89
application, a web server that accesses the database to
90
display web pages, or a specialized database maintenance tool.
91
Some client applications are supplied with the
92
<productname>PostgreSQL</productname> distribution; most are
101
As is typical of client/server applications, the client and the
102
server can be on different hosts. In that case they communicate
103
over a TCP/IP network connection. You should keep this in mind,
104
because the files that can be accessed on a client machine might
105
not be accessible (or might only be accessible using a different
106
file name) on the database server machine.
110
The <productname>PostgreSQL</productname> server can handle
111
multiple concurrent connections from clients. To achieve this it
112
starts (<quote>forks</quote>) a new process for each connection.
113
From that point on, the client and the new server process
114
communicate without intervention by the original
115
<filename>postgres</filename> process. Thus, the
116
master server process is always running, waiting for
117
client connections, whereas client and associated server processes
118
come and go. (All of this is of course invisible to the user. We
119
only mention it here for completeness.)
124
<sect1 id="tutorial-createdb">
125
<title>Creating a Database</title>
127
<indexterm zone="tutorial-createdb">
128
<primary>database</primary>
129
<secondary>creating</secondary>
132
<indexterm zone="tutorial-createdb">
133
<primary>createdb</primary>
137
The first test to see whether you can access the database server
138
is to try to create a database. A running
139
<productname>PostgreSQL</productname> server can manage many
140
databases. Typically, a separate database is used for each
141
project or for each user.
145
Possibly, your site administrator has already created a database
146
for your use. He should have told you what the name of your
147
database is. In that case you can omit this step and skip ahead
152
To create a new database, in this example named
153
<literal>mydb</literal>, you use the following command:
155
<prompt>$</prompt> <userinput>createdb mydb</userinput>
157
If this produces no response then this step was successful and you can skip over the
158
remainder of this section.
162
If you see a message similar to:
164
createdb: command not found
166
then <productname>PostgreSQL</> was not installed properly. Either it was not
167
installed at all or your shell's search path was not set to include it.
168
Try calling the command with an absolute path instead:
170
<prompt>$</prompt> <userinput>/usr/local/pgsql/bin/createdb mydb</userinput>
172
The path at your site might be different. Contact your site
173
administrator or check the installation instructions to
174
correct the situation.
178
Another response could be this:
180
createdb: could not connect to database postgres: could not connect to server: No such file or directory
181
Is the server running locally and accepting
182
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
184
This means that the server was not started, or it was not started
185
where <command>createdb</command> expected it. Again, check the
186
installation instructions or consult the administrator.
190
Another response could be this:
192
createdb: could not connect to database postgres: FATAL: role "joe" does not exist
194
where your own login name is mentioned. This will happen if the
195
administrator has not created a <productname>PostgreSQL</> user account
196
for you. (<productname>PostgreSQL</> user accounts are distinct from
197
operating system user accounts.) If you are the administrator, see
198
<xref linkend="user-manag"> for help creating accounts. You will need to
199
become the operating system user under which <productname>PostgreSQL</>
200
was installed (usually <literal>postgres</>) to create the first user
201
account. It could also be that you were assigned a
202
<productname>PostgreSQL</> user name that is different from your
203
operating system user name; in that case you need to use the <option>-U</>
204
switch or set the <envar>PGUSER</> environment variable to specify your
205
<productname>PostgreSQL</> user name.
209
If you have a user account but it does not have the privileges required to
210
create a database, you will see the following:
212
createdb: database creation failed: ERROR: permission denied to create database
214
Not every user has authorization to create new databases. If
215
<productname>PostgreSQL</productname> refuses to create databases
216
for you then the site administrator needs to grant you permission
217
to create databases. Consult your site administrator if this
218
occurs. If you installed <productname>PostgreSQL</productname>
219
yourself then you should log in for the purposes of this tutorial
220
under the user account that you started the server as.
224
As an explanation for why this works:
225
<productname>PostgreSQL</productname> user names are separate
226
from operating system user accounts. When you connect to a
227
database, you can choose what
228
<productname>PostgreSQL</productname> user name to connect as;
229
if you don't, it will default to the same name as your current
230
operating system account. As it happens, there will always be a
231
<productname>PostgreSQL</productname> user account that has the
232
same name as the operating system user that started the server,
233
and it also happens that that user always has permission to
234
create databases. Instead of logging in as that user you can
235
also specify the <option>-U</option> option everywhere to select
236
a <productname>PostgreSQL</productname> user name to connect as.
242
You can also create databases with other names.
243
<productname>PostgreSQL</productname> allows you to create any
244
number of databases at a given site. Database names must have an
245
alphabetic first character and are limited to 63 bytes in
246
length. A convenient choice is to create a database with the same
247
name as your current user name. Many tools assume that database
248
name as the default, so it can save you some typing. To create
249
that database, simply type:
251
<prompt>$</prompt> <userinput>createdb</userinput>
256
If you do not want to use your database anymore you can remove it.
257
For example, if you are the owner (creator) of the database
258
<literal>mydb</literal>, you can destroy it using the following
261
<prompt>$</prompt> <userinput>dropdb mydb</userinput>
263
(For this command, the database name does not default to the user
264
account name. You always need to specify it.) This action
265
physically removes all files associated with the database and
266
cannot be undone, so this should only be done with a great deal of
271
More about <command>createdb</command> and <command>dropdb</command> can
272
be found in <xref linkend="APP-CREATEDB"> and <xref linkend="APP-DROPDB">
278
<sect1 id="tutorial-accessdb">
279
<title>Accessing a Database</title>
281
<indexterm zone="tutorial-accessdb">
282
<primary>psql</primary>
286
Once you have created a database, you can access it by:
288
<itemizedlist spacing="compact" mark="bullet">
291
Running the <productname>PostgreSQL</productname> interactive
292
terminal program, called <application><firstterm>psql</></application>, which allows you
293
to interactively enter, edit, and execute
294
<acronym>SQL</acronym> commands.
300
Using an existing graphical frontend tool like
301
<application>pgAdmin</application> or an office suite with
302
<acronym>ODBC</> or <acronym>JDBC</> support to create and manipulate a
303
database. These possibilities are not covered in this
310
Writing a custom application, using one of the several
311
available language bindings. These possibilities are discussed
312
further in <xref linkend="client-interfaces">.
317
You probably want to start up <command>psql</command> to try
318
the examples in this tutorial. It can be activated for the
319
<literal>mydb</literal> database by typing the command:
321
<prompt>$</prompt> <userinput>psql mydb</userinput>
323
If you do not supply the database name then it will default to your
324
user account name. You already discovered this scheme in the
325
previous section using <command>createdb</command>.
329
In <command>psql</command>, you will be greeted with the following
333
Type "help" for help.
337
<indexterm><primary>superuser</primary></indexterm>
338
The last line could also be:
342
That would mean you are a database superuser, which is most likely
343
the case if you installed <productname>PostgreSQL</productname>
344
yourself. Being a superuser means that you are not subject to
345
access controls. For the purposes of this tutorial that is not
350
If you encounter problems starting <command>psql</command>
351
then go back to the previous section. The diagnostics of
352
<command>createdb</command> and <command>psql</command> are
353
similar, and if the former worked the latter should work as well.
357
The last line printed out by <command>psql</command> is the
358
prompt, and it indicates that <command>psql</command> is listening
359
to you and that you can type <acronym>SQL</acronym> queries into a
360
work space maintained by <command>psql</command>. Try out these
362
<indexterm><primary>version</primary></indexterm>
364
<prompt>mydb=></prompt> <userinput>SELECT version();</userinput>
366
-----------------------------------------------------------------------
367
PostgreSQL &version; on i586-pc-linux-gnu, compiled by GCC 2.96, 32-bit
370
<prompt>mydb=></prompt> <userinput>SELECT current_date;</userinput>
376
<prompt>mydb=></prompt> <userinput>SELECT 2 + 2;</userinput>
385
The <command>psql</command> program has a number of internal
386
commands that are not SQL commands. They begin with the backslash
387
character, <quote><literal>\</literal></quote>.
389
you can get help on the syntax of various
390
<productname>PostgreSQL</productname> <acronym>SQL</acronym>
393
<prompt>mydb=></prompt> <userinput>\h</userinput>
398
To get out of <command>psql</command>, type:
400
<prompt>mydb=></prompt> <userinput>\q</userinput>
402
and <command>psql</command> will quit and return you to your
403
command shell. (For more internal commands, type
404
<literal>\?</literal> at the <command>psql</command> prompt.) The
405
full capabilities of <command>psql</command> are documented in
406
<xref linkend="app-psql">. In this tutorial we will not use these
407
features explicitly, but you can use them yourself when it is helpful.