Hi everyone,
Some time ago I was selected to participate in Google season of docs
program to update the introductory tutorial. Please consider the attached
patch for the Getting Started part. Keeping the original information, i
tried to make it easy to follow, as well as added installation steps for
binary packages. I'll be glad to hear your feedback.
Many thanks to Alexander Lakhin for responding to my multiple questions and
verifying technical correctness.
--
Best regards,
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
diff --git a/doc/src/sgml/start.sgml b/doc/src/sgml/start.sgml
index 5b73557835..07313dac96 100644
--- a/doc/src/sgml/start.sgml
+++ b/doc/src/sgml/start.sgml
@@ -3,121 +3,682 @@
<chapter id="tutorial-start">
<title>Getting Started</title>
- <sect1 id="tutorial-install">
- <title>Installation</title>
+ <sect1 id="tutorial-system-setup">
+ <title>Setting up the System</title>
<para>
- Before you can use <productname>PostgreSQL</productname> you need
- to install it, of course. It is possible that
- <productname>PostgreSQL</productname> is already installed at your
- site, either because it was included in your operating system
- distribution or because the system administrator already installed
- it. If that is the case, you should obtain information from the
- operating system documentation or your system administrator about
- how to access <productname>PostgreSQL</productname>.
- </para>
+ To follow this tutorial, you need to have
+ <productname>PostgreSQL</productname> installed and configured.
+ You can install <productname>PostgreSQL</productname> using one of the
+ following ways:
- <para>
- If you are not sure whether <productname>PostgreSQL</productname>
- is already available or whether you can use it for your
- experimentation then you can install it yourself. Doing so is not
- hard and it can be a good exercise.
- <productname>PostgreSQL</productname> can be installed by any
- unprivileged user; no superuser (<systemitem>root</systemitem>)
- access is required.
- </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <link linkend="tutorial-install-bin">Install binary packages built by
+ PGDG</link>. PostgreSQL Global Development Group (PGDG) provides binary
+ packages for most popular operating systems of the Red Hat family,
+ SUSE, Debian, and Ubuntu. When you install these packages, some
+ of the initial server setup is completed automatically.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Use <productname>PostgreSQL</productname> binary packages provided in
+ the operating system distributions. If you choose such packages,
+ consult your operating system documentation on how to access them
+ and initialize the database cluster.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <link linkend="tutorial-install-src">Build PostgreSQL from source
+ code</link>. It requires more work, but enables you to install
+ <productname>PostgreSQL</productname> on supported platforms even if
+ binary packages that you need are unavailable, perform the installation
+ without superuser rights, and customize the installation.
+ </para>
+ </listitem>
+ </itemizedlist>
- <para>
- If you are installing <productname>PostgreSQL</productname>
- yourself, then refer to <xref linkend="installation"/>
- for instructions on installation, and return to
- this guide when the installation is complete. Be sure to follow
- closely the section about setting up the appropriate environment
- variables.
</para>
- <para>
- If your site administrator has not set things up in the default
- way, you might have some more work to do. For example, if the
- database server machine is a remote machine, you will need to set
- the <envar>PGHOST</envar> environment variable to the name of the
- database server machine. The environment variable
- <envar>PGPORT</envar> might 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 database, you should consult your
- site administrator or, if that is you, the documentation to make
- sure that your environment is properly set up. If you did not
- understand the preceding paragraph then read the next section.
- </para>
+ <important>
+ <para>
+ Avoid experimenting with <productname>PostgreSQL</productname>
+ installations on production systems. Depending on the system
+ configuration, re-installing <productname>PostgreSQL</productname>
+ can affect the current database cluster and result in data loss.
+ </para>
+ </important>
+
+ <sect2 id="tutorial-install-bin">
+ <title>Using PGDG Binary Packages for Linux</title>
+
+ <para>
+ To install and set up <productname>PostgreSQL</productname> from binary
+ packages built by PGDG, follow the instructions below for your
+ operating system:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <link linkend="pgdg-debian">Debian and Ubuntu systems</link>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <link linkend="pgdg-rhel">RHEL and SUSE systems</link>
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ </para>
+
+ <sect3 id="pgdg-debian">
+ <title>Debian and Ubuntu Systems</title>
+
+ <para>
+ To set up a <productname>PostgreSQL</productname> server on Debian or
+ Ubuntu, you only need to add the PGDG package repository and install the
+ packages; the rest of the setup is completed automatically.
+ Depending on the operating system, the commands to add the repository
+ and install the packages differ, so follow
+ the instructions provided for your system on the
+ <ulink url="https://www.postgresql.org/download/";>download page</ulink>.
+ </para>
+
+ <para>
+ For the purposes of this tutorial, it is enough to install
+ the <filename>postgresql-<replaceable>version</replaceable></filename>
+ server package, where <replaceable>version</replaceable> is the
+ <productname>PostgreSQL</productname> major version you would like to
+ use. All the required libraries and client utilities are automatically
+ installed by dependencies.
+ </para>
+
+ <para>
+ As <productname>PostgreSQL</productname> gets installed, a database
+ cluster is initialized on behalf of the <literal>postgres</literal>
+ system user, and the following <productname>PostgreSQL</productname>
+ directories are created:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Installation directory:
+ <filename>/usr/lib/postgresql/<replaceable>version</replaceable>/</filename>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Data directory:
+ <filename>/var/lib/postgresql/<replaceable>version</replaceable>/main/</filename>
+ </para>
+ <para>
+ Note that all configuration files are located under
+ <filename>/etc/postgresql/<replaceable>version</replaceable>/main/</filename>,
+ following the Debian conventions. Thus, you should specify this
+ directory as the database cluster configuration directory when using
+ server applications, such as <xref linkend="app-pg-ctl"/> or
+ <xref linkend="pgupgrade"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Log directory: <filename>/var/log/postgresql/</filename>
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ Once the cluster initialization completes, the server is started
+ as a service, and the server autostart is enabled. To manage the
+ <productname>PostgreSQL</productname> service, you can use your
+ operating system facilities or the <literal>pg_ctlcluster</literal>
+ utility provided in the server package. For example, to check the
+ server status, run:
+<programlisting>
+pg_ctlcluster <replaceable>version</replaceable> main status
+</programlisting>
+ </para>
+
+ <para>
+ For detailed information on the <literal>pg_ctlcluster</literal>
+ syntax, see the corresponding man page:
+<programlisting>
+man pg_ctlcluster
+</programlisting>
+ </para>
+
+ <para>
+ While <productname>PostgreSQL</productname> is started as a service,
+ avoid running <literal>pg_ctl</literal> to manage the server as it
+ can lead to unexpected results.
+ </para>
+ </sect3>
+
+ <sect3 id="pgdg-rhel">
+ <title>RHEL and SUSE Systems</title>
+ <procedure>
+ <step>
+ <title>Add the PGDG package repository and install the packages</title>
+ <para>
+ Depending on the operating system, the commands to add the repository
+ and install the packages will differ, so follow
+ the instructions provided for your system on the
+ <ulink url="https://www.postgresql.org/download/";>download page</ulink>.
+ </para>
+ <para>
+ For the purposes of this tutorial, it is enough to install the
+ <filename>postgresql<replaceable>version</replaceable>-server</filename>
+ package, where <replaceable>version</replaceable> is the
+ <productname>PostgreSQL</productname> major version. All the required
+ libraries and client utilities are automatically installed by
+ dependencies.
+ </para>
+ <para>
+ As <productname>PostgreSQL</productname> gets installed, the
+ <literal>postgres</literal> user and the
+ <literal>postgresql-<replaceable>version</replaceable></literal>
+ service are set up. The installation directory is
+ <filename>/usr/pgsql-<replaceable>version</replaceable>/</filename>.
+ </para>
+ </step>
+
+ <step>
+ <title>Initialize the database cluster</title>
+ <para>
+ On <literal>systemd</literal> systems,
+ run the <literal>postgresql-<replaceable>version</replaceable>-setup</literal>
+ script to initialize the database cluster:
+<programlisting>
+<replaceable>install-dir/bin/</replaceable>postgresql-<replaceable>version</replaceable>-setup initdb
+</programlisting>
+ </para>
+ <para>
+ On <literal>SysV</literal>-style systems, initialize the
+ database cluster using the system service facilities, as follows:
+<programlisting>
+service postgresql-<replaceable>version</replaceable> initdb
+</programlisting>
+ </para>
+ <para>
+ The database cluster is initialized on behalf of the
+ <literal>postgres</literal> user, in the
+ <filename>/var/lib/pgsql/<replaceable>version</replaceable>/data</filename>
+ data directory. Log files are located in
+ <filename>/var/lib/pgsql/<replaceable>version</replaceable>/data/log/</filename>
+ by default.
+ </para>
+ </step>
+
+ <step>
+ <title>Start the <productname>PostgreSQL</productname> server</title>
+ <para>
+ Use your operating system facilities to start
+ <productname>PostgreSQL</productname> as a service. To avoid starting
+ the server manually each time you reboot the system, you are also
+ recommended to enable server autostart.
+ </para>
+ <para>
+ On <literal>systemd</literal> systems:
+<programlisting>
+systemctl enable postgresql-<replaceable>version</replaceable>
+systemctl start postgresql-<replaceable>version</replaceable>
+</programlisting>
+ </para>
+ <para>
+ On <literal>SysV</literal> systems:
+<programlisting>
+chkconfig postgresql-<replaceable>version</replaceable> on
+service postgresql-<replaceable>version</replaceable> start
+</programlisting>
+ </para>
+ <para>
+ The server is started on behalf of the <literal>postgres</literal>
+ user.
+ </para>
+ </step>
+ </procedure>
+
+ <para>
+ Since you have started <productname>PostgreSQL</productname> as a
+ service, you should continue using the operating system facilities to
+ manage the server. Running <literal>pg_ctl</literal> for this purpose
+ can lead to unexpected results.
+ </para>
+
+ </sect3>
+ </sect2>
+
+ <sect2 id="tutorial-install-src">
+ <title>Building PostgreSQL from Source Code</title>
+
+ <para>
+ If a binary package is not available for your system, or you prefer
+ building <productname>PostgreSQL</productname> yourself in a
+ Unix-like environment, follow these steps:
+ </para>
+
+ <procedure>
+ <step>
+ <title>Download <productname>PostgreSQL</productname> source code</title>
+ <para>
+ Get <productname>PostgreSQL</productname> code from the
+ <productname>PostgreSQL</productname> download page or git repository,
+ as explained in <xref linkend="install-getsource"/>.
+ </para>
+ </step>
+
+ <step>
+ <title>Build and install <productname>PostgreSQL</productname></title>
+ <para>
+ Complete the installation from source code as explained in
+ <xref linkend="install-procedure"/>. If you do not have superuser
+ (<systemitem>root</systemitem>) access and would like to install
+ <productname>PostgreSQL</productname> as an unprivileged user, make
+ sure to set the <literal>--prefix</literal> option to point to a
+ location for which this user has write permissions. By default, the
+ installation path is <filename>/usr/local/pgsql</filename>,
+ which requires root access.
+ </para>
+ </step>
+
+ <step>
+ <title>Set up the appropriate environment variables</title>
+ <para>
+ In particular, make sure the <filename>bin</filename> subdirectory
+ of your installation directory is added to the <envar>PATH</envar>
+ environment variable for the user that performs the installation:
+<programlisting>
+PATH=<replaceable>install-dir</replaceable>/bin:$PATH
+export PATH
+</programlisting>
+ This setting allows to avoid specifying the full installation
+ path when working with the server later.
+ </para>
+ <para>
+ If you are installing <productname>PostgreSQL</productname> on a remote
+ system, you also need to set
+ the <envar>PGHOST</envar> environment variable to the name of the
+ database server system. If you have changed the default port
+ when configuring sources, the <envar>PGPORT</envar> environment variable
+ should also be changed accordingly.
+ See <xref linkend="configure-envvars"/> for details.
+ </para>
+ </step>
+
+ <step>
+ <title>Set up a separate OS user that will own the database cluster</title>
+ <para>
+ This step requires root access. If you have installed
+ <productname>PostgreSQL</productname> as an unprivileged user, you can
+ skip this step and complete the system setup on behalf of the same user,
+ but it is not recommended on production systems for security reasons.
+ </para>
+ <para>
+ Most binary packages automatically create a separate
+ <literal>postgres</literal> OS user that owns the server process
+ and all the cluster data, so let's create such a user, set up
+ a directory for our new database cluster that this user can access,
+ and then switch to this user:
+<programlisting>
+adduser postgres
+mkdir <replaceable>datadir</replaceable>
+chown postgres <replaceable>datadir</replaceable>
+su - postgres
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <title>Initialize the database cluster</title>
+ <para>
+ On behalf of the <literal>postgres</literal> user, run
+ <xref linkend="app-pg-ctl"/> with the <literal>initdb</literal> command,
+ specifying the data directory in the <literal>-D</literal> option:
+<programlisting>
+<replaceable>install-dir</replaceable>/bin/pg_ctl -D <replaceable>datadir</replaceable> initdb
+</programlisting>
+ This command creates a database cluster owned by the
+ <literal>postgres</literal> OS user.
+ </para>
+ </step>
+
+ <step>
+ <title>Start the <productname>PostgreSQL</productname> server</title>
+ <para>
+ The server must be started by the cluster owner, so run the following
+ command on behalf of the <literal>postgres</literal> user:
+<programlisting>
+<replaceable>install-dir</replaceable>/bin/pg_ctl -D <replaceable>datadir</replaceable> -l logfile start
+</programlisting>
+ To avoid starting the server manually each time you reboot
+ the system, you can enable server autostart as explained in
+ <xref linkend="server-start"/>.
+ </para>
+ </step>
+ </procedure>
+
+ <para>
+ To check that the server is running, you can run the following command:
+<programlisting>
+<replaceable>install-dir</replaceable>/bin/pg_ctl -D <replaceable>datadir</replaceable> status
+</programlisting>
+ </para>
+
+ </sect2>
</sect1>
+ <sect1 id="tutorial-access-server">
+ <title>Accessing the Server</title>
- <sect1 id="tutorial-arch">
- <title>Architectural Fundamentals</title>
+ <indexterm zone="tutorial-access-server">
+ <primary>accessing a <productname>PostgreSQL</productname> server</primary>
+ </indexterm>
<para>
- Before we proceed, you should understand the basic
- <productname>PostgreSQL</productname> system architecture.
- Understanding how the parts of
- <productname>PostgreSQL</productname> interact will make this
- chapter somewhat clearer.
+ <productname>PostgreSQL</productname> uses a client/server model.
+ To connect to a server, a client application is required.
+ <productname>PostgreSQL</productname> provides several
+ <link linkend="reference-client">client utilities</link> that you
+ can run from the command line to perform some common operations.
+ On the server side, you must have a database to connect to,
+ and a database user that has the right to work with this database.
</para>
<para>
- In database jargon, <productname>PostgreSQL</productname> uses a
- client/server model. A <productname>PostgreSQL</productname>
- session consists of the following cooperating processes
- (programs):
+ Database users are separate from operating system user accounts. They
+ exist within the server instance, and can be used to perform database
+ operations only. When the database cluster is initialized, the initial
+ user and the default database are created automatically. Their names
+ are the same as the name of the current OS user account. Thus, if the
+ cluster is initialized on behalf of the <literal>postgres</literal> OS
+ user, the <literal>postgres</literal> database user is set up, and the
+ <structname>postgres</structname> database is created.
+ </para>
- <itemizedlist>
+ <sect2>
+ <title>How It Works</title>
+ <para>
+ When a client connects to the server, a
+ <firstterm><productname>PostgreSQL</productname>
+ session</firstterm> is established, which comprises the following
+ cooperating processes (programs):
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ A server process, which manages database files, accepts connections
+ to the database from client applications, and performs database
+ actions on behalf of the clients. The database server program is
+ called <filename>postgres</filename>.
+ <indexterm><primary>postgres</primary></indexterm>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A client application to perform database operations. It can
+ be a terminal program, such as <xref linkend="app-psql"/>, a
+ graphical frontend tool like <application>pgAdmin</application>
+ or an office suite with <acronym>ODBC</acronym> or
+ <acronym>JDBC</acronym> support to create and manipulate a database,
+ or a custom application that uses one of the several
+ available language bindings discussed
+ in <xref linkend="client-interfaces"/>.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ </para>
+
+ <para>
+ A <productname>PostgreSQL</productname> server can handle
+ multiple concurrent connections from clients. When a client gets
+ connected to the server, <productname>PostgreSQL</productname>
+ starts (<quote>forks</quote>) a new process for this client.
+ From that point on, the client and the new server process
+ communicate without intervention by the original
+ <filename>postgres</filename> process. Thus, the
+ master server process is always running, waiting for
+ client connections, whereas client and associated server processes
+ come and go.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Establishing the First Connection</title>
+
+ <para>
+ If you have installed
+ <productname>PostgreSQL</productname> from PGDG binary packages on Linux,
+ the initial user is <literal>postgres</literal>. This is the only
+ only database user to connect as once you have initialized the cluster.
+ </para>
+
+ <para>
+ By default, client utilities use Unix domain socket for local
+ connections to the server, and the server uses the
+ <link linkend="auth-peer">peer authentication</link> method for such
+ connections. To authenticate a user, the server checks which OS user
+ has initiated the process to connect to the server and only allows
+ connections on behalf of the database user with the same name as
+ the current OS user. Thus, to establish the first connection to the
+ server, you have to log in as the <literal>postgres</literal> OS user.
+ Even though you do not have an explicit password for this user, you
+ can do it via the superuser account:
+<screen>
+<prompt>$</prompt> <userinput>sudo su postgres</userinput>
+</screen>
+ Now you can run a client application from the command line without
+ specifying any connection parameters, and it will connect to the
+ <literal>postgres</literal> database on behalf of the
+ <literal>postgres</literal> database user automatically. For example,
+ you can start <xref linkend="app-psql"/> or run the
+ <xref linkend="app-createuser"/> client utility to create a new
+ database user.
+ </para>
+
+ <para>
+ Suppose you would like to access the server from your
+ regular OS account <literal>joe</literal> using peer authentication.
+ To create a database user with the same name, run the following command:
+<screen>
+<prompt>$</prompt> <userinput>createuser joe --createdb</userinput>
+</screen>
+ This command connects to the <literal>postgres</literal> database as the
+ <literal>postgres</literal> user and creates a new database user
+ <literal>joe</literal>. The <option>--createdb</option> grants this user
+ the right to create databases in the future. If you do not see any errors
+ when running this command, the operation has been successful. Otherwise,
+ checkout <xref linkend="tutorial-troubleshooting"/> to figure out what
+ could have gone wrong.
+ </para>
+
+ </sect2>
+
+ <sect2>
+ <title>Using Other Connection Types</title>
+
+ <para>
+ Apart from peer authentication for local connections,
+ <productname>PostgreSQL</productname> also provides other
+ authentication methods that enable you to explicitly specify the database
+ user to connect as, without switching to the corresponding
+ OS user. Authentication settings for each connection type are defined in
+ the <link linkend="auth-pg-hba-conf">pg_hba.conf</link> file located
+ in your cluster configuration directory. Each line in this file provides
+ connection settings for a particular connection type, for example:
+<screen>
+# TYPE DATABASE USER ADDRESS METHOD
+# "local" is for Unix domain socket connections only
+local all all peer
+# IPv4 local connections:
+host all all 127.0.0.1/32 md5
+</screen>
+ </para>
+
+ <para>
+ When a client tries to connect to the server,
+ <filename>pg_hba.conf</filename> is parsed from top to bottom,
+ and the first authentication method that satisfies the provided
+ connection parameters is used.
+ </para>
+
+ <para>
+ Suppose you would like to establish a connection via TCP/IP. In this case,
+ you need to provide <literal>-U</literal> and <literal>-h</literal>
+ options to explicitly specify the database user and host for connection.
+ You also have to ensure that authentication parameters configured for the
+ selected database user correspond to the authentication method for this
+ connection type. For example, for password-based authentication methods,
+ such as <literal>md5</literal>, the database user must have a password
+ set. Thus, to use this authentication method for the
+ <literal>postgres</literal> user, you may first need to connect to the
+ server using peer authentication and change the password with the
+ <xref linkend="sql-alterrole"/> SQL command.
+ </para>
+
+ <para>
+ For remote connections, you also have to define the
+ <xref linkend="guc-listen-addresses"/> parameter in the
+ <filename>postgresql.conf</filename> configuration file to allow incoming
+ connections for a particular IP address of the server.
+ <productname>PostgreSQL</productname> server configuration
+ is out of scope of this tutorial, but you can learn more in
+ <xref linkend="config-setting"/>.
+ </para>
+
+ <para>
+ For details about the available connection types and authentication
+ methods, see <xref linkend="client-authentication"/>.
+ </para>
+ </sect2>
+
+ <sect2 id="tutorial-troubleshooting">
+ <title>Troubleshooting</title>
+
+ <para>
+ If the system has not been set up properly, you may encounter
+ an error when trying to access the server. This section lists the
+ most common error messages you can see when starting a client application.
+ Each error message starts with the application name that produced it, such
+ as <application>createdb</application> or <application>psql</application>.
+ For errors that are similar for all clients, the application name is
+ replaced with the <replaceable>client-app</replaceable> placeholder.
+ </para>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable>client-app</replaceable>: command not found</literal>
+ </term>
+ <listitem>
+ <para>
+ <productname>PostgreSQL</productname> is not installed on your system,
+ or the installation directory is not included into <envar>PATH</envar>.
+ If the server is running, try calling the command with an absolute path,
+ for example:
+<screen>
+<prompt>$</prompt> <userinput><replaceable>install_dir</replaceable>/createuser joe</userinput>
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>
+ <replaceable>client-app</replaceable>: could not connect to database postgres:
+ could not connect to server: No such file or directory
+ Is the server running locally and accepting
+ connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
+ </literal>
+ </term>
+ <listitem>
+ <para>
+ The client application cannot establish a Unix-domain socket
+ connection to a local server. Check that the server is started.
+ If you have built <productname>PostgreSQL</productname> from source code
+ with a non-default port, specify this port in the <literal>-p</literal>
+ option.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>
+ <replaceable>client-app</replaceable>: FATAL: Peer authentication failed for user "<replaceable>joe</replaceable>"
+ </literal>
+ </term>
<listitem>
<para>
- A server process, which manages the database files, accepts
- connections to the database from client applications, and
- performs database actions on behalf of the clients. The
- database server program is called
- <filename>postgres</filename>.
- <indexterm><primary>postgres</primary></indexterm>
+ The client tries to connect to the server as database user
+ <literal>joe</literal> using peer authentication, but the current OS
+ user is different. Log in as the corresponding OS user and try again, or
+ specify other connection options for a different authentication method.
</para>
</listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>
+ <replaceable>client-app</replaceable>: could not connect to database postgres:
+ FATAL: role "<replaceable>joe</replaceable>" does not exist
+ </literal>
+ </term>
+ <listitem>
+ <para>
+ <productname>PostgreSQL</productname> cannot find the
+ <replaceable>joe</replaceable> database user for one of the
+ following reasons:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Peer authentication is attempted, but the database user name is
+ different from your operating system user name. Log in as the
+ <replaceable>joe</replaceable> OS user and try again, or specify
+ other connection options to use a different authentication method.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Database user <replaceable>joe</replaceable> has not been created.
+ You need to log in as the OS user under which
+ <productname>PostgreSQL</productname> was installed (usually
+ <literal>postgres</literal>) to create the first database user.
+ See <xref linkend="user-manag"/> for help on creating accounts.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>
+ <literal>psql: FATAL: database "<replaceable>joe</replaceable>" does not exist</literal>
+ </term>
<listitem>
<para>
- The user's client (frontend) application that wants to perform
- database operations. Client applications can be very diverse
- in nature: a client could be a text-oriented tool, a graphical
- application, a web server that accesses the database to
- display web pages, or a specialized database maintenance tool.
- Some client applications are supplied with the
- <productname>PostgreSQL</productname> distribution; most are
- developed by users.
+ Peer authentication is attempted on behalf of the
+ <replaceable>joe</replaceable> user, but there is no database with the
+ same name. Run <xref linkend="app-createdb"/> to create this database,
+ or use the <option>-d</option> option to specify a different database
+ to connect to.
</para>
</listitem>
+ </varlistentry>
- </itemizedlist>
- </para>
+ </variablelist>
- <para>
- As is typical of client/server applications, the client and the
- server can be on different hosts. In that case they communicate
- over a TCP/IP network connection. You should keep this in mind,
- because the files that can be accessed on a client machine might
- not be accessible (or might only be accessible using a different
- file name) on the database server machine.
- </para>
+ </sect2>
- <para>
- The <productname>PostgreSQL</productname> server can handle
- multiple concurrent connections from clients. To achieve this it
- starts (<quote>forks</quote>) a new process for each connection.
- From that point on, the client and the new server process
- communicate without intervention by the original
- <filename>postgres</filename> process. Thus, the
- master server process is always running, waiting for
- client connections, whereas client and associated server processes
- come and go. (All of this is of course invisible to the user. We
- only mention it here for completeness.)
- </para>
</sect1>
@@ -134,230 +695,149 @@
</indexterm>
<para>
- The first test to see whether you can access the database server
- is to try to create a database. A running
- <productname>PostgreSQL</productname> server can manage many
- databases. Typically, a separate database is used for each
- project or for each user.
+ A running <productname>PostgreSQL</productname> server can manage
+ multiple databases. Typically, a separate database is used for each
+ project or for each user. The first database is created at
+ cluster initialization, and its name always coincides with the
+ name of the initial user.
</para>
<para>
- Possibly, your site administrator has already created a database
- for your use. In that case you can omit this step and skip ahead
- to the next section.
- </para>
-
- <para>
- To create a new database, in this example named
- <literal>mydb</literal>, you use the following command:
+ For the purposes of this tutorial, let's create a new database called
+ <structname>mydb</structname> on behalf of the <literal>postgres</literal>
+ user:
<screen>
+<prompt>$</prompt> <userinput>sudo su postgres</userinput>
<prompt>$</prompt> <userinput>createdb mydb</userinput>
</screen>
- If this produces no response then this step was successful and you can skip over the
- remainder of this section.
+ The database user that runs this command becomes the owner of the
+ newly created database and can later perform any operations with it.
</para>
<para>
- If you see a message similar to:
-<screen>
-createdb: command not found
-</screen>
- then <productname>PostgreSQL</productname> was not installed properly. Either it was not
- installed at all or your shell's search path was not set to include it.
- Try calling the command with an absolute path instead:
-<screen>
-<prompt>$</prompt> <userinput>/usr/local/pgsql/bin/createdb mydb</userinput>
-</screen>
- The path at your site might be different. Contact your site
- administrator or check the installation instructions to
- correct the situation.
- </para>
-
- <para>
- Another response could be this:
-<screen>
-createdb: could not connect to database postgres: could not connect to server: No such file or directory
- Is the server running locally and accepting
- connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
-</screen>
- This means that the server was not started, or it was not started
- where <command>createdb</command> expected it. Again, check the
- installation instructions or consult the administrator.
+ <productname>PostgreSQL</productname> allows you to create any
+ number of databases for the server instance. Database names must have an
+ alphabetic first character and are limited to 63 bytes in
+ length. It may be convenient to have a database with the same
+ name as your current user name. Many tools assume that database
+ name as the default, so you can omit the database name when
+ connecting to the server or creating a database.
</para>
<para>
- Another response could be this:
+ Let's see another example. If you have created a database user
+ <literal>joe</literal> as explained in the previous section, you can
+ log in as the <literal>joe</literal> OS user and create the
+ corresponding database, as follows:
<screen>
-createdb: could not connect to database postgres: FATAL: role "joe" does not exist
+<prompt>$</prompt> <userinput>createdb</userinput>
</screen>
- where your own login name is mentioned. This will happen if the
- administrator has not created a <productname>PostgreSQL</productname> user account
- for you. (<productname>PostgreSQL</productname> user accounts are distinct from
- operating system user accounts.) If you are the administrator, see
- <xref linkend="user-manag"/> for help creating accounts. You will need to
- become the operating system user under which <productname>PostgreSQL</productname>
- was installed (usually <literal>postgres</literal>) to create the first user
- account. It could also be that you were assigned a
- <productname>PostgreSQL</productname> user name that is different from your
- operating system user name; in that case you need to use the <option>-U</option>
- switch or set the <envar>PGUSER</envar> environment variable to specify your
- <productname>PostgreSQL</productname> user name.
+ The <application>createdb</application> utility uses peer
+ authentication to connect to the server as the <literal>joe</literal> user
+ and creates the <literal>joe</literal> database owned by the
+ <literal>joe</literal> database user, although you have not specified the
+ database name explicitly.
</para>
<para>
- If you have a user account but it does not have the privileges required to
- create a database, you will see the following:
+ If the database user does not have the privileges required to
+ create a database, you can see the following error message:
<screen>
createdb: database creation failed: ERROR: permission denied to create database
</screen>
- Not every user has authorization to create new databases. If
- <productname>PostgreSQL</productname> 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. If you installed <productname>PostgreSQL</productname>
- yourself then you should log in for the purposes of this tutorial
- under the user account that you started the server as.
-
- <footnote>
- <para>
- As an explanation for why this works:
- <productname>PostgreSQL</productname> user names are separate
- from operating system user accounts. When you connect to a
- database, you can choose what
- <productname>PostgreSQL</productname> user name to connect as;
- if you don't, it will default to the same name as your current
- operating system account. As it happens, there will always be a
- <productname>PostgreSQL</productname> user account that has the
- same name as the operating system user that started the server,
- and it also happens that that user always has permission to
- create databases. Instead of logging in as that user you can
- also specify the <option>-U</option> option everywhere to select
- a <productname>PostgreSQL</productname> user name to connect as.
- </para>
- </footnote>
+ It may happen if you have run <xref linkend="app-createuser"/> without the
+ <literal>--createdb</literal> option when creating a new database user.
+ You can later grant the database user the <literal>CREATEDB</literal>
+ privilege using the <xref linkend="sql-alterrole"/> SQL command.
+ Alternatively, you can continue using the initial user, which is a
+ superuser and hence always has the rights to create databases.
</para>
<para>
- You can also create databases with other names.
- <productname>PostgreSQL</productname> allows you to create any
- number of databases at a given site. Database names must have an
- alphabetic first character and are limited to 63 bytes in
- length. A convenient choice is to create a database with the same
- name as your current user name. Many tools assume that database
- name as the default, so it can save you some typing. To create
- that database, simply type:
-<screen>
-<prompt>$</prompt> <userinput>createdb</userinput>
-</screen>
- </para>
-
- <para>
- If you do not want to use your database anymore you can remove it.
- For example, if you are the owner (creator) of the database
- <literal>mydb</literal>, you can destroy it using the following
- command:
+ If you do not want to use your database anymore, you can remove it.
+ For example, if you are the owner of the database
+ <literal>joe</literal>, you can destroy it using the
+ <application>dropdb</application> utility, as follows:
<screen>
-<prompt>$</prompt> <userinput>dropdb mydb</userinput>
+<prompt>$</prompt> <userinput>dropdb joe</userinput>
</screen>
- (For this command, the database name does not default to the user
- account name. You always need to specify it.) This action
+ For this command, the database name does not default to the user
+ account name; you always need to specify it. This action
physically removes all files associated with the database and
- cannot be undone, so this should only be done with a great deal of
- forethought.
+ cannot be undone, so be careful when using this command in the future.
</para>
<para>
- More about <command>createdb</command> and <command>dropdb</command> can
- be found in <xref linkend="app-createdb"/> and <xref linkend="app-dropdb"/>
- respectively.
+ To learn more about <command>createdb</command> and
+ <command>dropdb</command> commands, see <xref linkend="app-createdb"/>
+ and <xref linkend="app-dropdb"/>, respectively.
</para>
</sect1>
- <sect1 id="tutorial-accessdb">
- <title>Accessing a Database</title>
+ <sect1 id="tutorial-psql">
+ <title>Using psql</title>
- <indexterm zone="tutorial-accessdb">
+ <indexterm zone="tutorial-psql">
<primary>psql</primary>
</indexterm>
<para>
- Once you have created a database, you can access it by:
-
- <itemizedlist spacing="compact" mark="bullet">
- <listitem>
- <para>
- Running the <productname>PostgreSQL</productname> interactive
- terminal program, called <application><firstterm>psql</firstterm></application>, which allows you
- to interactively enter, edit, and execute
- <acronym>SQL</acronym> commands.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Using an existing graphical frontend tool like
- <application>pgAdmin</application> or an office suite with
- <acronym>ODBC</acronym> or <acronym>JDBC</acronym> support to create and manipulate a
- database. These possibilities are not covered in this
- tutorial.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Writing a custom application, using one of the several
- available language bindings. These possibilities are discussed
- further in <xref linkend="client-interfaces"/>.
- </para>
- </listitem>
- </itemizedlist>
+ As mentioned before, you need to run a client application to access the
+ server. In the previous steps, we have already run
+ <xref linkend="app-createuser"/> and <xref linkend="app-createdb"/>
+ utilities to create a new user and database from the command line.
+ To perform a wider range of tasks, we are going to use
+ <xref linkend="app-psql"/>, a terminal program that enables you to
+ interactively enter, edit, and execute <acronym>SQL</acronym> commands.
+ </para>
- You probably want to start up <command>psql</command> to try
- the examples in this tutorial. It can be activated for the
- <literal>mydb</literal> database by typing the command:
+ <para>
+ Let's start <application>psql</application> and connect to the
+ <literal>mydb</literal> database on behalf of the <literal>postgres</literal> user:
<screen>
+<prompt>$</prompt> <userinput>sudo su postgres</userinput>
<prompt>$</prompt> <userinput>psql mydb</userinput>
</screen>
- If you do not supply the database name then it will default to your
- user account name. You already discovered this scheme in the
- previous section using <command>createdb</command>.
</para>
<para>
- In <command>psql</command>, you will be greeted with the following
- message:
+ The command prompt changes as follows:
<screen>
psql (&version;)
Type "help" for help.
-mydb=>
+mydb=#
</screen>
- <indexterm><primary>superuser</primary></indexterm>
- The last line could also be:
+ </para>
+
+ <para>
+ This prompt indicates that you have entered an interactive
+ <application>psql</application> shell where you can run SQL queries
+ from the command line. The hash sign at the end of the prompt
+ shows that <application>psql</application> client is connected
+ to the server as a database superuser that is not subject to
+ access controls. Otherwise, the last line would be as follows:
<screen>
-mydb=#
+mydb=>
</screen>
- That would mean you are a database superuser, which is most likely
- the case if you installed the <productname>PostgreSQL</productname> instance
- yourself. Being a superuser means that you are not subject to
- access controls. For the purposes of this tutorial that is not
- important.
</para>
<para>
- If you encounter problems starting <command>psql</command>
- then go back to the previous section. The diagnostics of
- <command>createdb</command> and <command>psql</command> are
- similar, and if the former worked the latter should work as well.
+ When in the <application>psql</application> shell, you cannot use
+ regular Unix commands or call other applications. For example,
+ if you would like to create a new database, you have to
+ run the <command>CREATE DATABASE</command> SQL command directly
+ instead of using <xref linkend="app-createdb"/>:
+<screen>
+<prompt>mydb=#</prompt> <userinput>CREATE DATABASE newdb;</userinput>
+CREATE DATABASE
+mydb=#
+</screen>
</para>
<para>
- The last line printed out by <command>psql</command> is the
- prompt, and it indicates that <command>psql</command> is listening
- to you and that you can type <acronym>SQL</acronym> queries into a
- work space maintained by <command>psql</command>. Try out these
- commands:
+ Let's try out some other commands:
<indexterm><primary>version</primary></indexterm>
<screen>
<prompt>mydb=></prompt> <userinput>SELECT version();</userinput>
@@ -381,30 +861,55 @@ mydb=#
</para>
<para>
- The <command>psql</command> program has a number of internal
- commands that are not SQL commands. They begin with the backslash
+ <application>psql</application> also provides its own set of meta-commands
+ to manipulate the server. They begin with the backslash
character, <quote><literal>\</literal></quote>.
- For example,
- you can get help on the syntax of various
- <productname>PostgreSQL</productname> <acronym>SQL</acronym>
- commands by typing:
+ For example, you can check the parameters of your current
+ client/server connection using the <literal>\conninfo</literal> command:
<screen>
-<prompt>mydb=></prompt> <userinput>\h</userinput>
+<prompt>mydb=></prompt> <userinput>\conninfo</userinput>
+You are connected to database "mydb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
</screen>
</para>
<para>
- To get out of <command>psql</command>, type:
+ Another useful command is <command>\password</command>, which enables you
+ to reset the password for your current database user without calling
+ <xref linkend="sql-alterrole"/>. It comes in handy if you would like
+ to use password-based authentication for the <literal>postgres</literal>
+ user in the future:
<screen>
-<prompt>mydb=></prompt> <userinput>\q</userinput>
+<prompt>mydb=></prompt> <userinput>\password</userinput>
+</screen>
+ </para>
+
+ <para>
+ The <literal>\h</literal> command displays help on the syntax of various
+ <acronym>SQL</acronym> commands supported by
+ <application>PostgreSQL</application>:
+<screen>
+<prompt>mydb=></prompt> <userinput>\h</userinput>
</screen>
- and <command>psql</command> will quit and return you to your
- command shell. (For more internal commands, type
- <literal>\?</literal> at the <command>psql</command> prompt.) The
+ </para>
+
+ <para>
+ For more internal commands, type
+ <literal>\?</literal> at the <command>psql</command> prompt. The
full capabilities of <command>psql</command> are documented in
<xref linkend="app-psql"/>. In this tutorial we will not use these
features explicitly, but you can use them yourself when it is helpful.
</para>
+ <para>
+ To get out of <command>psql</command>, use the <literal>\q</literal>
+ meta-command:
+<screen>
+<prompt>mydb=></prompt> <userinput>\q</userinput>
+</screen>
+ <command>psql</command> will quit and return you to your
+ command shell.
+ </para>
+
</sect1>
+
</chapter>
