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 @@ Getting Started - - Installation + + Setting up the System - Before you can use PostgreSQL you need - to install it, of course. It is possible that - PostgreSQL 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 PostgreSQL. - + To follow this tutorial, you need to have + PostgreSQL installed and configured. + You can install PostgreSQL using one of the + following ways: - - If you are not sure whether PostgreSQL - 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. - PostgreSQL can be installed by any - unprivileged user; no superuser (root) - access is required. - + + + + Install binary packages built by + PGDG. 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. + + + + + Use PostgreSQL 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. + + + + + Build PostgreSQL from source + code. It requires more work, but enables you to install + PostgreSQL on supported platforms even if + binary packages that you need are unavailable, perform the installation + without superuser rights, and customize the installation. + + + - - If you are installing PostgreSQL - yourself, then refer to - 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. - - 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 PGHOST environment variable to the name of the - database server machine. The environment variable - PGPORT 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. - + + + Avoid experimenting with PostgreSQL + installations on production systems. Depending on the system + configuration, re-installing PostgreSQL + can affect the current database cluster and result in data loss. + + + + + Using PGDG Binary Packages for Linux + + + To install and set up PostgreSQL from binary + packages built by PGDG, follow the instructions below for your + operating system: + + + + + Debian and Ubuntu systems + + + + + RHEL and SUSE systems + + + + + + + + Debian and Ubuntu Systems + + + To set up a PostgreSQL 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 + download page. + + + + For the purposes of this tutorial, it is enough to install + the postgresql-version + server package, where version is the + PostgreSQL major version you would like to + use. All the required libraries and client utilities are automatically + installed by dependencies. + + + + As PostgreSQL gets installed, a database + cluster is initialized on behalf of the postgres + system user, and the following PostgreSQL + directories are created: + + + + + + Installation directory: + /usr/lib/postgresql/version/ + + + + + Data directory: + /var/lib/postgresql/version/main/ + + + Note that all configuration files are located under + /etc/postgresql/version/main/, + following the Debian conventions. Thus, you should specify this + directory as the database cluster configuration directory when using + server applications, such as or + . + + + + + Log directory: /var/log/postgresql/ + + + + + + Once the cluster initialization completes, the server is started + as a service, and the server autostart is enabled. To manage the + PostgreSQL service, you can use your + operating system facilities or the pg_ctlcluster + utility provided in the server package. For example, to check the + server status, run: + +pg_ctlcluster version main status + + + + + For detailed information on the pg_ctlcluster + syntax, see the corresponding man page: + +man pg_ctlcluster + + + + + While PostgreSQL is started as a service, + avoid running pg_ctl to manage the server as it + can lead to unexpected results. + + + + + RHEL and SUSE Systems + + + Add the PGDG package repository and install the packages + + 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 + download page. + + + For the purposes of this tutorial, it is enough to install the + postgresqlversion-server + package, where version is the + PostgreSQL major version. All the required + libraries and client utilities are automatically installed by + dependencies. + + + As PostgreSQL gets installed, the + postgres user and the + postgresql-version + service are set up. The installation directory is + /usr/pgsql-version/. + + + + + Initialize the database cluster + + On systemd systems, + run the postgresql-version-setup + script to initialize the database cluster: + +install-dir/bin/postgresql-version-setup initdb + + + + On SysV-style systems, initialize the + database cluster using the system service facilities, as follows: + +service postgresql-version initdb + + + + The database cluster is initialized on behalf of the + postgres user, in the + /var/lib/pgsql/version/data + data directory. Log files are located in + /var/lib/pgsql/version/data/log/ + by default. + + + + + Start the <productname>PostgreSQL</productname> server + + Use your operating system facilities to start + PostgreSQL as a service. To avoid starting + the server manually each time you reboot the system, you are also + recommended to enable server autostart. + + + On systemd systems: + +systemctl enable postgresql-version +systemctl start postgresql-version + + + + On SysV systems: + +chkconfig postgresql-version on +service postgresql-version start + + + + The server is started on behalf of the postgres + user. + + + + + + Since you have started PostgreSQL as a + service, you should continue using the operating system facilities to + manage the server. Running pg_ctl for this purpose + can lead to unexpected results. + + + + + + + Building PostgreSQL from Source Code + + + If a binary package is not available for your system, or you prefer + building PostgreSQL yourself in a + Unix-like environment, follow these steps: + + + + + Download <productname>PostgreSQL</productname> source code + + Get PostgreSQL code from the + PostgreSQL download page or git repository, + as explained in . + + + + + Build and install <productname>PostgreSQL</productname> + + Complete the installation from source code as explained in + . If you do not have superuser + (root) access and would like to install + PostgreSQL as an unprivileged user, make + sure to set the --prefix option to point to a + location for which this user has write permissions. By default, the + installation path is /usr/local/pgsql, + which requires root access. + + + + + Set up the appropriate environment variables + + In particular, make sure the bin subdirectory + of your installation directory is added to the PATH + environment variable for the user that performs the installation: + +PATH=install-dir/bin:$PATH +export PATH + + This setting allows to avoid specifying the full installation + path when working with the server later. + + + If you are installing PostgreSQL on a remote + system, you also need to set + the PGHOST environment variable to the name of the + database server system. If you have changed the default port + when configuring sources, the PGPORT environment variable + should also be changed accordingly. + See for details. + + + + + Set up a separate OS user that will own the database cluster + + This step requires root access. If you have installed + PostgreSQL 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. + + + Most binary packages automatically create a separate + postgres 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: + +adduser postgres +mkdir datadir +chown postgres datadir +su - postgres + + + + + + Initialize the database cluster + + On behalf of the postgres user, run + with the initdb command, + specifying the data directory in the -D option: + +install-dir/bin/pg_ctl -D datadir initdb + + This command creates a database cluster owned by the + postgres OS user. + + + + + Start the <productname>PostgreSQL</productname> server + + The server must be started by the cluster owner, so run the following + command on behalf of the postgres user: + +install-dir/bin/pg_ctl -D datadir -l logfile start + + To avoid starting the server manually each time you reboot + the system, you can enable server autostart as explained in + . + + + + + + To check that the server is running, you can run the following command: + +install-dir/bin/pg_ctl -D datadir status + + + + + + Accessing the Server - - Architectural Fundamentals + + accessing a PostgreSQL server + - Before we proceed, you should understand the basic - PostgreSQL system architecture. - Understanding how the parts of - PostgreSQL interact will make this - chapter somewhat clearer. + PostgreSQL uses a client/server model. + To connect to a server, a client application is required. + PostgreSQL provides several + client utilities 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. - In database jargon, PostgreSQL uses a - client/server model. A PostgreSQL - 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 postgres OS + user, the postgres database user is set up, and the + postgres database is created. + - + + How It Works + + When a client connects to the server, a + PostgreSQL + session is established, which comprises the following + cooperating processes (programs): + + + + + 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 postgres. + postgres + + + + + + A client application to perform database operations. It can + be a terminal program, such as , a + graphical frontend tool like pgAdmin + or an office suite with ODBC or + JDBC support to create and manipulate a database, + or a custom application that uses one of the several + available language bindings discussed + in . + + + + + + + + A PostgreSQL server can handle + multiple concurrent connections from clients. When a client gets + connected to the server, PostgreSQL + starts (forks) a new process for this client. + From that point on, the client and the new server process + communicate without intervention by the original + postgres process. Thus, the + master server process is always running, waiting for + client connections, whereas client and associated server processes + come and go. + + + + + Establishing the First Connection + + + If you have installed + PostgreSQL from PGDG binary packages on Linux, + the initial user is postgres. This is the only + only database user to connect as once you have initialized the cluster. + + + + By default, client utilities use Unix domain socket for local + connections to the server, and the server uses the + peer authentication 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 postgres OS user. + Even though you do not have an explicit password for this user, you + can do it via the superuser account: + +$ sudo su postgres + + Now you can run a client application from the command line without + specifying any connection parameters, and it will connect to the + postgres database on behalf of the + postgres database user automatically. For example, + you can start or run the + client utility to create a new + database user. + + + + Suppose you would like to access the server from your + regular OS account joe using peer authentication. + To create a database user with the same name, run the following command: + +$ createuser joe --createdb + + This command connects to the postgres database as the + postgres user and creates a new database user + joe. The 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 to figure out what + could have gone wrong. + + + + + + Using Other Connection Types + + + Apart from peer authentication for local connections, + PostgreSQL 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 pg_hba.conf file located + in your cluster configuration directory. Each line in this file provides + connection settings for a particular connection type, for example: + +# 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 + + + + + When a client tries to connect to the server, + pg_hba.conf is parsed from top to bottom, + and the first authentication method that satisfies the provided + connection parameters is used. + + + + Suppose you would like to establish a connection via TCP/IP. In this case, + you need to provide -U and -h + 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 md5, the database user must have a password + set. Thus, to use this authentication method for the + postgres user, you may first need to connect to the + server using peer authentication and change the password with the + SQL command. + + + + For remote connections, you also have to define the + parameter in the + postgresql.conf configuration file to allow incoming + connections for a particular IP address of the server. + PostgreSQL server configuration + is out of scope of this tutorial, but you can learn more in + . + + + + For details about the available connection types and authentication + methods, see . + + + + + Troubleshooting + + + 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 createdb or psql. + For errors that are similar for all clients, the application name is + replaced with the client-app placeholder. + + + + + + client-app: command not found + + + + PostgreSQL is not installed on your system, + or the installation directory is not included into PATH. + If the server is running, try calling the command with an absolute path, + for example: + +$ install_dir/createuser joe + + + + + + + + + client-app: 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"? + + + + + The client application cannot establish a Unix-domain socket + connection to a local server. Check that the server is started. + If you have built PostgreSQL from source code + with a non-default port, specify this port in the -p + option. + + + + + + + + client-app: FATAL: Peer authentication failed for user "joe" + + - 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 - postgres. - postgres + The client tries to connect to the server as database user + joe 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. + + + + + + client-app: could not connect to database postgres: + FATAL: role "joe" does not exist + + + + + PostgreSQL cannot find the + joe database user for one of the + following reasons: + + + + + Peer authentication is attempted, but the database user name is + different from your operating system user name. Log in as the + joe OS user and try again, or specify + other connection options to use a different authentication method. + + + + + Database user joe has not been created. + You need to log in as the OS user under which + PostgreSQL was installed (usually + postgres) to create the first database user. + See for help on creating accounts. + + + + + + + + psql: FATAL: database "joe" does not exist + - 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 - PostgreSQL distribution; most are - developed by users. + Peer authentication is attempted on behalf of the + joe user, but there is no database with the + same name. Run to create this database, + or use the option to specify a different database + to connect to. + - - + - - 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. - + - - The PostgreSQL server can handle - multiple concurrent connections from clients. To achieve this it - starts (forks) a new process for each connection. - From that point on, the client and the new server process - communicate without intervention by the original - postgres 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.) - @@ -134,230 +695,149 @@ - The first test to see whether you can access the database server - is to try to create a database. A running - PostgreSQL server can manage many - databases. Typically, a separate database is used for each - project or for each user. + A running PostgreSQL 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. - 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. - - - - To create a new database, in this example named - mydb, you use the following command: + For the purposes of this tutorial, let's create a new database called + mydb on behalf of the postgres + user: +$ sudo su postgres $ createdb mydb - 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. - If you see a message similar to: - -createdb: command not found - - then PostgreSQL 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: - -$ /usr/local/pgsql/bin/createdb mydb - - The path at your site might be different. Contact your site - administrator or check the installation instructions to - correct the situation. - - - - Another response could be this: - -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"? - - This means that the server was not started, or it was not started - where createdb expected it. Again, check the - installation instructions or consult the administrator. + PostgreSQL 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. - Another response could be this: + Let's see another example. If you have created a database user + joe as explained in the previous section, you can + log in as the joe OS user and create the + corresponding database, as follows: -createdb: could not connect to database postgres: FATAL: role "joe" does not exist +$ createdb - where your own login name is mentioned. This will happen if the - administrator has not created a PostgreSQL user account - for you. (PostgreSQL user accounts are distinct from - operating system user accounts.) If you are the administrator, see - for help creating accounts. You will need to - become the operating system user under which PostgreSQL - was installed (usually postgres) to create the first user - account. It could also be that you were assigned a - PostgreSQL user name that is different from your - operating system user name; in that case you need to use the - switch or set the PGUSER environment variable to specify your - PostgreSQL user name. + The createdb utility uses peer + authentication to connect to the server as the joe user + and creates the joe database owned by the + joe database user, although you have not specified the + database name explicitly. - 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: createdb: database creation failed: ERROR: permission denied to create database - Not every user has authorization to create new databases. If - PostgreSQL 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 PostgreSQL - yourself then you should log in for the purposes of this tutorial - under the user account that you started the server as. - - - - As an explanation for why this works: - PostgreSQL user names are separate - from operating system user accounts. When you connect to a - database, you can choose what - PostgreSQL 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 - PostgreSQL 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 everywhere to select - a PostgreSQL user name to connect as. - - + It may happen if you have run without the + --createdb option when creating a new database user. + You can later grant the database user the CREATEDB + privilege using the SQL command. + Alternatively, you can continue using the initial user, which is a + superuser and hence always has the rights to create databases. - You can also create databases with other names. - PostgreSQL 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: - -$ createdb - - - - - 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 - mydb, 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 + joe, you can destroy it using the + dropdb utility, as follows: -$ dropdb mydb +$ dropdb joe - (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. - More about createdb and dropdb can - be found in and - respectively. + To learn more about createdb and + dropdb commands, see + and , respectively. - - Accessing a Database + + Using psql - + psql - Once you have created a database, you can access it by: - - - - - Running the PostgreSQL interactive - terminal program, called psql, which allows you - to interactively enter, edit, and execute - SQL commands. - - - - - - Using an existing graphical frontend tool like - pgAdmin or an office suite with - ODBC or JDBC support to create and manipulate a - database. These possibilities are not covered in this - tutorial. - - - - - - Writing a custom application, using one of the several - available language bindings. These possibilities are discussed - further in . - - - + As mentioned before, you need to run a client application to access the + server. In the previous steps, we have already run + and + utilities to create a new user and database from the command line. + To perform a wider range of tasks, we are going to use + , a terminal program that enables you to + interactively enter, edit, and execute SQL commands. + - You probably want to start up psql to try - the examples in this tutorial. It can be activated for the - mydb database by typing the command: + + Let's start psql and connect to the + mydb database on behalf of the postgres user: +$ sudo su postgres $ psql mydb - 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 createdb. - In psql, you will be greeted with the following - message: + The command prompt changes as follows: psql (&version;) Type "help" for help. -mydb=> +mydb=# - superuser - The last line could also be: + + + + This prompt indicates that you have entered an interactive + psql shell where you can run SQL queries + from the command line. The hash sign at the end of the prompt + shows that psql 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: -mydb=# +mydb=> - That would mean you are a database superuser, which is most likely - the case if you installed the PostgreSQL instance - yourself. Being a superuser means that you are not subject to - access controls. For the purposes of this tutorial that is not - important. - If you encounter problems starting psql - then go back to the previous section. The diagnostics of - createdb and psql are - similar, and if the former worked the latter should work as well. + When in the psql 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 CREATE DATABASE SQL command directly + instead of using : + +mydb=# CREATE DATABASE newdb; +CREATE DATABASE +mydb=# + - The last line printed out by psql is the - prompt, and it indicates that psql is listening - to you and that you can type SQL queries into a - work space maintained by psql. Try out these - commands: + Let's try out some other commands: version mydb=> SELECT version(); @@ -381,30 +861,55 @@ mydb=# - The psql program has a number of internal - commands that are not SQL commands. They begin with the backslash + psql also provides its own set of meta-commands + to manipulate the server. They begin with the backslash character, \. - For example, - you can get help on the syntax of various - PostgreSQL SQL - commands by typing: + For example, you can check the parameters of your current + client/server connection using the \conninfo command: -mydb=> \h +mydb=> \conninfo +You are connected to database "mydb" as user "postgres" via socket in "/var/run/postgresql" at port "5432". - To get out of psql, type: + Another useful command is \password, which enables you + to reset the password for your current database user without calling + . It comes in handy if you would like + to use password-based authentication for the postgres + user in the future: -mydb=> \q +mydb=> \password + + + + + The \h command displays help on the syntax of various + SQL commands supported by + PostgreSQL: + +mydb=> \h - and psql will quit and return you to your - command shell. (For more internal commands, type - \? at the psql prompt.) The + + + + For more internal commands, type + \? at the psql prompt. The full capabilities of psql are documented in . In this tutorial we will not use these features explicitly, but you can use them yourself when it is helpful. + + To get out of psql, use the \q + meta-command: + +mydb=> \q + + psql will quit and return you to your + command shell. + + +