2 <Title>Getting Started</Title>
6 How to begin work with <ProductName>Postgres</ProductName> for a new user.
11 Some of the steps required to use <ProductName>Postgres</ProductName>
12 can be performed by any Postgres user, and some must be done by
13 the site database administrator. This site administrator
14 is the person who installed the software, created
15 the database directories and started the <Application>postmaster</Application>
16 process. This person does not have to be the UNIX
17 superuser (<Quote>root</Quote>)
18 or the computer system administrator; a person can install and use
19 <ProductName>Postgres</ProductName> without any special accounts or privileges.
23 If you are installing <ProductName>Postgres</ProductName> yourself, then
24 refer to the Administrator's Guide for instructions on installation, and return
25 to this guide when the installation is complete.
29 Throughout this manual, any examples that begin with
30 the character <Quote>%</Quote> are commands that should be typed
31 at the UNIX shell prompt. Examples that begin with the
32 character <Quote>*</Quote> are commands in the Postgres query
33 language, Postgres <Acronym>SQL</Acronym>.
37 <Title>Setting Up Your Environment</Title>
40 This section discusses how to set up
41 your own environment so that you can use frontend
42 applications. We assume <ProductName>Postgres</ProductName> has already been
43 successfully installed and started; refer to the Administrator's Guide
44 and the installation notes
45 for how to install Postgres.
49 <ProductName>Postgres</ProductName> is a client/server application. As a user,
50 you only need access to the client portions of the installation (an example
51 of a client application is the interactive monitor <Application>psql</Application>).
53 we will assume that <ProductName>Postgres</ProductName> has been installed in the
54 directory <FileName>/usr/local/pgsql</FileName>. Therefore, wherever
55 you see the directory <FileName>/usr/local/pgsql</FileName> you should
56 substitute the name of the directory where <ProductName>Postgres</ProductName> is
58 All <ProductName>Postgres</ProductName> commands are installed in the directory
59 <FileName>/usr/local/pgsql/bin</FileName>. Therefore, you should add
60 this directory to your shell command path. If you use
61 a variant of the Berkeley C shell, such as csh or tcsh,
64 % set path = ( /usr/local/pgsql/bin path )
66 in the <FileName>.login</FileName> file in your home directory. If you use
67 a variant of the Bourne shell, such as sh, ksh, or
68 bash, then you would add
70 % PATH=/usr/local/pgsql/bin PATH
73 to the .profile file in your home directory.
74 From now on, we will assume that you have added the
75 <ProductName>Postgres</ProductName> bin directory to your path. In addition, we
76 will make frequent reference to <Quote>setting a shell
77 variable</Quote> or <Quote>setting an environment variable</Quote> throughout
78 this document. If you did not fully understand the
79 last paragraph on modifying your search path, you
80 should consult the UNIX manual pages that describe your
81 shell before going any further.
85 If your site administrator has not set things up in the
86 default way, you may have some more work to do. For example, if the database
87 server machine is a remote machine, you
88 will need to set the <Acronym>PGHOST</Acronym> environment variable to the name
89 of the database server machine. The environment variable
90 <Acronym>PGPORT</Acronym> may also have to be set. The bottom line is this: if
91 you try to start an application program and it complains
92 that it cannot connect to the <Application>postmaster</Application>,
93 you should immediately consult your site administrator to make sure that your
94 environment is properly set up.
100 <Title>Starting the Interactive Monitor (psql)</Title>
103 Assuming that your site administrator has properly
104 started the <Application>postmaster</Application> process and authorized you to
105 use the database, you (as a user) may begin to start up
106 applications. As previously mentioned, you should add
107 <FileName>/usr/local/pgsql/bin</FileName> to your shell search path.
108 In most cases, this is all you should have to do in
109 terms of preparation.
113 As of <ProductName>Postgres</ProductName> v6.3, two different styles of connections
114 are supported. The site administrator will have chosen to allow TCP/IP network connections
115 or will have restricted database access to local (same-machine) socket connections only.
116 These choices become significant if you encounter problems in connecting to a database.
120 If you get the following error message from a <ProductName>Postgres</ProductName>
121 command (such as <Application>psql</Application> or <Application>createdb</Application>):
125 Connection to database 'postgres' failed.
126 connectDB() failed: Is the postmaster running and accepting connections
127 at 'UNIX Socket' on port '5432'?
133 % psql -h localhost template1
134 Connection to database 'postgres' failed.
135 connectDB() failed: Is the postmaster running and accepting TCP/IP
136 (with -i) connections at 'localhost' on port '5432'?
139 it is usually because (1) the <Application>postmaster</Application> is not running,
140 or (2) you are attempting to connect to the wrong server host.
141 If you get the following error message:
144 FATAL 1:Feb 17 23:19:55:process userid (2360) != database owner (268)
147 it means that the site administrator started the <Application>postmaster</Application>
148 as the wrong user. Tell him to restart it as
149 the <ProductName>Postgres</ProductName> superuser.
154 <Title>Managing a Database</Title>
157 Now that <ProductName>Postgres</ProductName> is up and running we can create some
158 databases to experiment with. Here, we describe the
159 basic commands for managing a database.
163 Most <ProductName>Postgres</ProductName>
164 applications assume that the database name, if not specified, is the same as the name on your computer
169 If your database administrator has set up your account without database creation privileges,
170 then she should have told you what the name of your database is. If this is the case, then you
171 can skip the sections on creating and destroying databases.
175 <Title>Creating a Database</Title>
178 Let's say you want to create a database named <Database>mydb</Database>.
179 You can do this with the following command:
186 If you do not have the privileges required to create a database, you will see
190 WARN:user "your username" is not allowed to create/destroy databases
191 createdb: database creation failed on mydb.
196 <ProductName>Postgres</ProductName> allows you to create any number of databases
197 at a given site and you automatically become the
198 database administrator of the database you just created. Database names must have an alphabetic first
199 character and are limited to 32 characters in length.
200 Not every user has authorization to become a database
201 administrator. If <ProductName>Postgres</ProductName> refuses to create databases
202 for you, then the site administrator needs to grant you
203 permission to create databases. Consult your site
204 administrator if this occurs.
209 <Title>Accessing a Database</Title>
212 Once you have constructed a database, you can access it
215 <ItemizedList Mark="bullet" Spacing="compact">
218 running the <ProductName>Postgres</ProductName> terminal monitor programs
219 (e.g. <Application>psql</Application>) which allows you to interactively
220 enter, edit, and execute <Acronym>SQL</Acronym> commands.
225 writing a <Acronym>C</Acronym> program using the LIBPQ subroutine
226 library. This allows you to submit <Acronym>SQL</Acronym> commands
227 from <Acronym>C</Acronym> and get answers and status messages back to
228 your program. This interface is discussed further
229 in <xref linkend="libpq" endterm="libpq">.
234 You might want to start up <Application>psql</Application>,
235 to try out the examples in this manual.
236 It can be activated for the <Database>mydb</Database>
237 database by typing the command:
242 You will be greeted with the following message:
244 Welcome to the POSTGRESQL interactive sql monitor:
245 Please read the file COPYRIGHT for copyright terms of POSTGRESQL
247 type \? for help on slash commands
249 type \g or terminate with semicolon to execute query
250 You are currently connected to the database: template1
257 This prompt indicates that the terminal monitor is listening
258 to you and that you can type <Acronym>SQL</Acronym> queries into a
259 workspace maintained by the terminal monitor.
260 The <Application>psql</Application> program responds to escape codes that begin
261 with the backslash character, <Quote>\</Quote> For example, you
262 can get help on the syntax of various
263 <ProductName>Postgres</ProductName> <Acronym>SQL</Acronym> commands by typing:
268 Once you have finished entering your queries into the
269 workspace, you can pass the contents of the workspace
270 to the <ProductName>Postgres</ProductName> server by typing:
275 This tells the server to process the query. If you
276 terminate your query with a semicolon, the <Quote>\g</Quote> is not
277 necessary. <Application>psql</Application> will automatically process semicolon terminated queries.
278 To read queries from a file, say myFile, instead of
279 entering them interactively, type:
284 To get out of <Application>psql</Application> and return to UNIX, type
289 and <Application>psql</Application> will quit and return you to your command
290 shell. (For more escape codes, type <Command>\h</Command> at the monitor
292 White space (i.e., spaces, tabs and newlines) may be
293 used freely in <Acronym>SQL</Acronym> queries. Single-line comments are denoted by
294 <Quote>--</Quote>. Everything after the dashes up to the end of the
295 line is ignored. Multiple-line comments, and comments within a line,
296 are denoted by <Quote>/* ... */</Quote>
301 <Title>Destroying a Database</Title>
304 If you are the database administrator for the database
305 <Database>mydb</Database>, you can destroy it using the following UNIX command:
309 This action physically removes all of the UNIX files
310 associated with the database and cannot be undone, so
311 this should only be done with a great deal of forethought.