doc/src/sgml/start.sgml

   1 <!-- doc/src/sgml/start.sgml -->
   2
   3  <chapter id="tutorial-start">
   4   <title>Getting Started</title>
   5
   6   <sect1 id="tutorial-install">
   7    <title>Installation</title>
   8
   9    <para>
  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>.
  18    </para>
  19
  20    <para>
  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>)
  27     access is required.
  28    </para>
  29
  30    <para>
  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
  36     variables.
  37    </para>
  38
  39    <para>
  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.
  51    </para>
  52   </sect1>
  53
  54
  55   <sect1 id="tutorial-arch">
  56    <title>Architectural Fundamentals</title>
  57
  58    <para>
  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.
  64    </para>
  65
  66    <para>
  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
  70     (programs):
  71
  72     <itemizedlist>
  73      <listitem>
  74       <para>
  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>
  81       </para>
  82      </listitem>
  83
  84      <listitem>
  85       <para>
  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
  93        developed by users.
  94       </para>
  95      </listitem>
  96
  97     </itemizedlist>
  98    </para>
  99
 100    <para>
 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.
 107    </para>
 108
 109    <para>
 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     supervisor 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.)
 120    </para>
 121   </sect1>
 122
 123
 124   <sect1 id="tutorial-createdb">
 125    <title>Creating a Database</title>
 126
 127    <indexterm zone="tutorial-createdb">
 128     <primary>database</primary>
 129     <secondary>creating</secondary>
 130    </indexterm>
 131
 132    <indexterm zone="tutorial-createdb">
 133     <primary>createdb</primary>
 134    </indexterm>
 135
 136    <para>
 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.
 142    </para>
 143
 144    <para>
 145     Possibly, your site administrator has already created a database
 146     for your use.  In that case you can omit this step and skip ahead
 147     to the next section.
 148    </para>
 149
 150    <para>
 151     To create a new database from the command line, in this example named
 152     <literal>mydb</literal>, you use the following command:
 153 <screen>
 154 <prompt>$</prompt> <userinput>createdb mydb</userinput>
 155 </screen>
 156     If this produces no response then this step was successful and you can skip over the
 157     remainder of this section.
 158    </para>
 159
 160    <para>
 161     If you see a message similar to:
 162 <screen>
 163 createdb: command not found
 164 </screen>
 165     then <productname>PostgreSQL</productname> was not installed properly.  Either it was not
 166     installed at all or your shell's search path was not set to include it.
 167     Try calling the command with an absolute path instead:
 168 <screen>
 169 <prompt>$</prompt> <userinput>/usr/local/pgsql/bin/createdb mydb</userinput>
 170 </screen>
 171     The path at your site might be different.  Contact your site
 172     administrator or check the installation instructions to
 173     correct the situation.
 174    </para>
 175
 176    <para>
 177     Another response could be this:
 178 <screen>
 179 createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
 180         Is the server running locally and accepting connections on that socket?
 181 </screen>
 182     This means that the server was not started, or it is not listening
 183     where <command>createdb</command> expects to contact it.  Again, check the
 184     installation instructions or consult the administrator.
 185    </para>
 186
 187    <para>
 188     Another response could be this:
 189 <screen>
 190 createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "joe" does not exist
 191 </screen>
 192     where your own login name is mentioned.  This will happen if the
 193     administrator has not created a <productname>PostgreSQL</productname> user account
 194     for you.  (<productname>PostgreSQL</productname> user accounts are distinct from
 195     operating system user accounts.)  If you are the administrator, see
 196     <xref linkend="user-manag"/> for help creating accounts.  You will need to
 197     become the operating system user under which <productname>PostgreSQL</productname>
 198     was installed (usually <literal>postgres</literal>) to create the first user
 199     account.  It could also be that you were assigned a
 200     <productname>PostgreSQL</productname> user name that is different from your
 201     operating system user name; in that case you need to use the <option>-U</option>
 202     switch or set the <envar>PGUSER</envar> environment variable to specify your
 203     <productname>PostgreSQL</productname> user name.
 204    </para>
 205
 206    <para>
 207     If you have a user account but it does not have the privileges required to
 208     create a database, you will see the following:
 209 <screen>
 210 createdb: error: database creation failed: ERROR:  permission denied to create database
 211 </screen>
 212     Not every user has authorization to create new databases.  If
 213     <productname>PostgreSQL</productname> refuses to create databases
 214     for you then the site administrator needs to grant you permission
 215     to create databases.  Consult your site administrator if this
 216     occurs.  If you installed <productname>PostgreSQL</productname>
 217     yourself then you should log in for the purposes of this tutorial
 218     under the user account that you started the server as.
 219
 220     <footnote>
 221      <para>
 222       As an explanation for why this works:
 223       <productname>PostgreSQL</productname> user names are separate
 224       from operating system user accounts.  When you connect to a
 225       database, you can choose what
 226       <productname>PostgreSQL</productname> user name to connect as;
 227       if you don't, it will default to the same name as your current
 228       operating system account.  As it happens, there will always be a
 229       <productname>PostgreSQL</productname> user account that has the
 230       same name as the operating system user that started the server,
 231       and it also happens that that user always has permission to
 232       create databases.  Instead of logging in as that user you can
 233       also specify the <option>-U</option> option everywhere to select
 234       a <productname>PostgreSQL</productname> user name to connect as.
 235      </para>
 236     </footnote>
 237    </para>
 238
 239    <para>
 240     You can also create databases with other names.
 241     <productname>PostgreSQL</productname> allows you to create any
 242     number of databases at a given site.  Database names must have an
 243     alphabetic first character and are limited to 63 bytes in
 244     length.  A convenient choice is to create a database with the same
 245     name as your current user name.  Many tools assume that database
 246     name as the default, so it can save you some typing.  To create
 247     that database, simply type:
 248 <screen>
 249 <prompt>$</prompt> <userinput>createdb</userinput>
 250 </screen>
 251    </para>
 252
 253    <para>
 254     If you do not want to use your database anymore you can remove it.
 255     For example, if you are the owner (creator) of the database
 256     <literal>mydb</literal>, you can destroy it using the following
 257     command:
 258 <screen>
 259 <prompt>$</prompt> <userinput>dropdb mydb</userinput>
 260 </screen>
 261     (For this command, the database name does not default to the user
 262     account name.  You always need to specify it.)  This action
 263     physically removes all files associated with the database and
 264     cannot be undone, so this should only be done with a great deal of
 265     forethought.
 266    </para>
 267
 268    <para>
 269     More about <command>createdb</command> and <command>dropdb</command> can
 270     be found in <xref linkend="app-createdb"/> and <xref linkend="app-dropdb"/>
 271     respectively.
 272    </para>
 273   </sect1>
 274
 275
 276   <sect1 id="tutorial-accessdb">
 277    <title>Accessing a Database</title>
 278
 279    <indexterm zone="tutorial-accessdb">
 280     <primary>psql</primary>
 281    </indexterm>
 282
 283    <para>
 284     Once you have created a database, you can access it by:
 285
 286     <itemizedlist spacing="compact" mark="bullet">
 287      <listitem>
 288       <para>
 289        Running the <productname>PostgreSQL</productname> interactive
 290        terminal program, called <application><firstterm>psql</firstterm></application>, which allows you
 291        to interactively enter, edit, and execute
 292        <acronym>SQL</acronym> commands.
 293       </para>
 294      </listitem>
 295
 296      <listitem>
 297       <para>
 298        Using an existing graphical frontend tool like
 299        <application>pgAdmin</application> or an office suite with
 300        <acronym>ODBC</acronym> or <acronym>JDBC</acronym> support to create and manipulate a
 301        database.  These possibilities are not covered in this
 302        tutorial.
 303       </para>
 304      </listitem>
 305
 306      <listitem>
 307       <para>
 308        Writing a custom application, using one of the several
 309        available language bindings.  These possibilities are discussed
 310        further in <xref linkend="client-interfaces"/>.
 311       </para>
 312      </listitem>
 313     </itemizedlist>
 314
 315     You probably want to start up <command>psql</command> to try
 316     the examples in this tutorial.  It can be activated for the
 317     <literal>mydb</literal> database by typing the command:
 318 <screen>
 319 <prompt>$</prompt> <userinput>psql mydb</userinput>
 320 </screen>
 321     If you do not supply the database name then it will default to your
 322     user account name.  You already discovered this scheme in the
 323     previous section using <command>createdb</command>.
 324    </para>
 325
 326    <para>
 327     In <command>psql</command>, you will be greeted with the following
 328     message:
 329 <screen>
 330 psql (&version;)
 331 Type "help" for help.
 332
 333 mydb=&gt;
 334 </screen>
 335     <indexterm><primary>superuser</primary></indexterm>
 336     The last line could also be:
 337 <screen>
 338 mydb=#
 339 </screen>
 340     That would mean you are a database superuser, which is most likely
 341     the case if you installed the <productname>PostgreSQL</productname> instance
 342     yourself.  Being a superuser means that you are not subject to
 343     access controls.  For the purposes of this tutorial that is not
 344     important.
 345    </para>
 346
 347    <para>
 348     If you encounter problems starting <command>psql</command>
 349     then go back to the previous section.  The diagnostics of
 350     <command>createdb</command> and <command>psql</command> are
 351     similar, and if the former worked the latter should work as well.
 352    </para>
 353
 354    <para>
 355     The last line printed out by <command>psql</command> is the
 356     prompt, and it indicates that <command>psql</command> is listening
 357     to you and that you can type <acronym>SQL</acronym> queries into a
 358     work space maintained by <command>psql</command>.  Try out these
 359     commands:
 360     <indexterm><primary>version</primary></indexterm>
 361 <screen>
 362 <prompt>mydb=&gt;</prompt> <userinput>SELECT version();</userinput>
 363                                          version
 364 -------------------------------------------------------------------&zwsp;-----------------------
 365  PostgreSQL &version; on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
 366 (1 row)
 367
 368 <prompt>mydb=&gt;</prompt> <userinput>SELECT current_date;</userinput>
 369     date
 370 ------------
 371  2016-01-07
 372 (1 row)
 373
 374 <prompt>mydb=&gt;</prompt> <userinput>SELECT 2 + 2;</userinput>
 375  ?column?
 376 ----------
 377         4
 378 (1 row)
 379 </screen>
 380    </para>
 381
 382    <para>
 383     The <command>psql</command> program has a number of internal
 384     commands that are not SQL commands.  They begin with the backslash
 385     character, <quote><literal>\</literal></quote>.
 386     For example,
 387     you can get help on the syntax of various
 388     <productname>PostgreSQL</productname> <acronym>SQL</acronym>
 389     commands by typing:
 390 <screen>
 391 <prompt>mydb=&gt;</prompt> <userinput>\h</userinput>
 392 </screen>
 393    </para>
 394
 395    <para>
 396     To get out of <command>psql</command>, type:
 397 <screen>
 398 <prompt>mydb=&gt;</prompt> <userinput>\q</userinput>
 399 </screen>
 400     and <command>psql</command> will quit and return you to your
 401     command shell. (For more internal commands, type
 402     <literal>\?</literal> at the <command>psql</command> prompt.)  The
 403     full capabilities of <command>psql</command> are documented in
 404     <xref linkend="app-psql"/>.  In this tutorial we will not use these
 405     features explicitly, but you can use them yourself when it is helpful.
 406    </para>
 407
 408   </sect1>
 409  </chapter>