swift 11/08/13 11:57:38 Modified: postgres-howto.xml Log: #330927 - Revamped documentation for PostgreSQL (almost a full rewrite), thanks to Aaron W. Swenson and Mikkel A. Clausen
Revision Changes Path 1.6 xml/htdocs/doc/en/postgres-howto.xml file : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.6&view=markup plain: http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.6&content-type=text/plain diff : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?r1=1.5&r2=1.6 Index: postgres-howto.xml =================================================================== RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v retrieving revision 1.5 retrieving revision 1.6 diff -u -r1.5 -r1.6 --- postgres-howto.xml 19 May 2008 21:09:45 -0000 1.5 +++ postgres-howto.xml 13 Aug 2011 11:57:38 -0000 1.6 @@ -1,715 +1,838 @@ <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE guide SYSTEM "/dtd/guide.dtd"> -<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.5 2008/05/19 21:09:45 swift Exp $ --> +<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.6 2011/08/13 11:57:38 swift Exp $ --> -<guide link="/doc/en/postgres-howto.xml" lang="en"> -<title>PostgreSQL Guide</title> +<guide link="/doc/en/postgresql-howto.xml" lang="en"> +<title>PostgreSQL Quick Start Guide</title> <author title="Author"> - <mail link="chriswh...@gentoo.org">Chris White</mail> + <mail link="titanof...@gentoo.org">Aaron W. Swenson</mail> </author> <author title="Editor"> - <mail link="ne...@gentoo.org">Xavier Neys</mail> + <mail link="pgsql-b...@gentoo.org">Mikkel A. Clausen</mail> </author> + <abstract> -This guide is meant to show the basic setup of PostgreSQL. The setup described -here should be sufficient enough to use for basic web appplications, and any -other program that provides PostgreSQL support. +This is a quick start guide to PostgreSQL. It covers emerging PostgreSQL and +configuring it. This is complementary to the official documentation, but does +not supplant it. </abstract> <!-- The content of this document is licensed under the CC-BY-SA license --> <!-- See http://creativecommons.org/licenses/by-sa/2.5 --> <license/> -<version>1.2</version> -<date>2007-04-25</date> +<version>8</version> +<date>2011-08-08</date> <chapter> <title>Introduction</title> <section> -<title>PostgreSQL introduction</title> +<title>A Little Bit About PostgreSQL</title> <body> <p> -When talking to most developers about the different database solutions to use, -two major databases will usually form the answer. One would be <c>MySQL</c>, -and the other is what this document will refer to, <c>PostgreSQL</c>. The -advantages of one over the other is a somewhat long winded debate, however it -is just to say that PostgreSQL has had a more firm grasp on true relational -database structure than MySQL. Most of the standard features such as -<b>FOREIGN KEY</b> was only just added in MySQL 5. However, whatever the case -may be, this document assumes that you have selected PostgreSQL as the -database to use. The first place to start is the <c>emerge</c> process. In the -next section, the installation process through emerge will be described, as -well as the basic configuration. +<uri link="http://www.postgresql.org";>PostgreSQL</uri> is a free and open source +relational database management system (RDBMS). It supports such things as +transactions, schemata and foreign keys, and is often touted to more strictly +adhere to the SQL standards and to be more secure, by default, than any other +database, commercial or otherwise. +</p> + +<p> +Visit the <uri link="http://www.postgresql.org/about/";>About</uri> page on +postgresql.org for more information. </p> </body> </section> <section> -<title>PostgreSQL installation</title> +<title>What This Article Will Cover</title> <body> <p> -To begin, we must first <c>emerge</c> the PostgreSQL package. To do so, run the -following code to first ensure that the options for it are properly set: +This article will guide you through the Gentoo specific steps to install the +PostgreSQL RDBMS. </p> -<pre caption="Checking the PostgreSQL build options"> -# <i>emerge -pv postgresql</i> +<p> +The Ebuilds covered by this article are <uri +link="http://packages.gentoo.org/package/dev-db/postgresql-docs";>dev-db/postgresql-docs</uri>, +<uri +link="http://packages.gentoo.org/package/dev-db/postgresql-base";>dev-db/postgresql-base</uri> +and <uri +link="http://packages.gentoo.org/package/dev-db/postgresql-server";>dev-db/postgresql-server</uri>. +</p> -These are the packages that I would merge, in order: +<p> +This article assumes that you will be installing the latest, stable version of +PostgreSQL; at the time of this writing, the version was 9.0.3. Adjust the +commands in this article as necessary for your specific version. +</p> -Calculating dependencies ...done! -[ebuild N ] dev-db/postgresql-8.0.4 -doc -kerberos +nls +pam +perl -pg-intdatetime +python +readline (-selinux) +ssl -tcl +xml +zlib 0 kB -</pre> +<impo> +The 8.2 branch will have its upstream support dropped in December of 2011. Start +planning your migration now. +</impo> + +</body> +</section> +<section> +<title>About the Ebuilds</title> +<body> <p> -Here's a list of what the different build options indicate: +The PostgreSQL ebuilds in Portage feature slotting based on the major version. +This allows you to have two major versions of PostgreSQL operating +simultaneously; 8.4 and 9.0 libraries and servers can be installed and serve at +the same time. This is useful in such circumstances where you need to move data +from an older database to a new database, or need to have a production and a +testing database on the same machine. Also, this prevents a database, +corresponding libraries or executables from being overwritten by an incompatible +update. That would require migration which is described in this guide. </p> -<table> -<tr> - <th>USE Flag</th> - <th>Meaning</th> -</tr> -<tr> - <ti>doc</ti> - <ti> - This USE flag enables or disables the installation of documentation - outside of the standard man pages. The one good time to disable this - option is if you are low on space, or you have alternate methods of - getting a hold of the documentation (online, etc.) - </ti> -</tr> -<tr> - <ti>kerberos</ti> - <ti> - When connecting to the database, with this option enabled, the admin - has the option of using <c>kerberos</c> to authenticate their - users/services to the database. - </ti> -</tr> -<tr> - <ti>nls</ti> - <ti> - If this option is enabled, PostgreSQL can utilize translated strings for - non-English speaking users. - </ti> -</tr> -<tr> - <ti>pam</ti> - <ti> - If this option is enabled, and the admin configures the PostgreSQL - configuration file properly, users/services will be able to login to a - PostgreSQL database using <c>PAM</c> (Pluggable Authentication Module). - </ti> -</tr> -<tr> - <ti>perl</ti> - <ti> - If this option is enabled, <c>perl</c> bindings for PostgreSQL will be - built. - </ti> -</tr> -<tr> - <ti>pg-intdatetime</ti> - <ti> - If this option is enabled, PostgreSQL will support 64 bit integer date - types. - </ti> -</tr> -<tr> - <ti>python</ti> - <ti> - If this option is enabled, PostgreSQL will be built with - <c>python</c> bindings. - </ti> -</tr> -<tr> - <ti>readline</ti> - <ti> - If this option is enabled, PostgreSQL will support <c>readline</c> style - command line editing. This includes command history and isearch. - </ti> -</tr> -<tr> - <ti>selinux</ti> - <ti> - If this option is enabled, an <c>selinux</c> policy for PostgreSQL will be - installed. - </ti> -</tr> -<tr> - <ti>ssl</ti> - <ti> - If this option is enabled, PostgreSQL will utilize the <c>OpenSSL</c> - library to encrypt traffic between PostgreSQL clients and servers. - </ti> -</tr> -<tr> - <ti>tcl</ti> - <ti> - If this option is enabled, PostgreSQL will build <c>tcl</c> bindings. - </ti> -</tr> -<tr> - <ti>xml</ti> - <ti> - If this option is enabled, <c>XPATH</c> style xml support will be built. - More information on using xml support with PostgreSQL can be found on: - <uri link="http://www.throwingbeans.org/postgresql_and_xml.html";> - PostgreSQL and XML</uri>. - </ti> -</tr> -<tr> - <ti>zlib</ti> - <ti> - This isn't really used by PostgreSQL itself, but by <c>pg_dump</c> to - compress the dumps it produces. - </ti> -</tr> -</table> +<p> +Additionally, bug and security fixes, which are delivered via minor version +updates, can be applied without fear of corrupting the database or the +PostgreSQL installation itself; 9.0.2 can be updated to 9.0.3 as they are +guaranteed to be compatible and require no more interaction from you than to +emerge it and restart the server process — neither migration, +reconfiguration nor initialization are necessary. +</p> <p> -Once you've customized PostgreSQL to meet your specific needs, go ahead and -start the <c>emerge</c>: +Read the <uri link="http://www.postgresql.org/support/versioning";>PostgreSQL +Versioning Policy</uri> for more information. </p> -<pre caption="Emerge-ing PostgreSQL"> -# <i>emerge postgresql</i> -<comment>(Output shortened)</comment> ->>> /usr/lib/libecpg.so.5 -> libecpg.so.5.0 ->>> /usr/bin/postmaster -> postgres - * Make sure the postgres user in /etc/passwd has an account setup with /bin/bash as the shell - * - * Execute the following command - * emerge --config =postgresql-8.0.4 - * to setup the initial database environment. - * ->>> Regenerating /etc/ld.so.cache... ->>> dev-db/postgresql-8.0.4 merged. -</pre> +</body> +</section> +<section> +<title>What this Article Will Not Cover</title> +<body> <p> -As shown by the einfo output, there is some post setup that must be done. The -next chapter will look at the actual configuration of PostgreSQL. +There is quite a bit that will not be covered. The <uri +link="http://www.postgresql.org/docs/";>official documentation</uri> is somewhere +in the neighborhood of 2,000 pages. So, a lot of details will be left out in +this quick start guide. Only Gentoo specific issues will be covered and some +basic configuration guidelines. </p> </body> </section> </chapter> -<chapter> -<title>PostgreSQL configuration</title> + +<chapter id="installation"> +<title>Installation</title> <section> -<title>Setting up the initial database environment</title> +<title>The Obsolete Ebuilds</title> <body> <p> -As noted in the earlier <c>emerge</c> output, the initial database environment -must be setup. However, before this is done, one thing needs to be considered. -Unlike, say MySQL, PostgreSQL's "root" password is the password of the actual -user. However, only the user is created by the ebuild <e>not</e> the password. -So before we can begin, the password must be set for the postgres user: +If you have any of the following ebuilds installed, then you have an older, +obsolete Gentoo installation of PostgreSQL and should migrate now: +dev-db/postgresql-libs, dev-db/postgresql-client, dev-db/libpq and/or +dev-db/postgresql. </p> -<pre caption="Setting the password"> -# <i>passwd postgres</i> -New UNIX password: -Retype new UNIX password: -passwd: password updated successfully -</pre> - <p> -Now that this is set up, the creation of the initial database environment can -occur: +This article does cover <uri link="#oldmigration">migrating</uri> from the old +ebuilds to the new ones. </p> -<pre caption="Configuring the database environment with emerge --config"> -# <i>emerge --config =postgresql-8.0.4</i> - - -Configuring pkg... - - * Creating the data directory ... - * Initializing the database ... -The files belonging to this database system will be owned by user "postgres". -This user must also own the server process. - -The database cluster will be initialized with locale C. +</body> +</section> +<section> +<title>USE Flags</title> +<body> -fixing permissions on existing directory /var/lib/postgresql/data ... ok -creating directory /var/lib/postgresql/data/global ... ok -creating directory /var/lib/postgresql/data/pg_xlog ... ok -creating directory /var/lib/postgresql/data/pg_xlog/archive_status ... ok -creating directory /var/lib/postgresql/data/pg_clog ... ok -creating directory /var/lib/postgresql/data/pg_subtrans ... ok -creating directory /var/lib/postgresql/data/base ... ok -creating directory /var/lib/postgresql/data/base/1 ... ok -creating directory /var/lib/postgresql/data/pg_tblspc ... ok -selecting default max_connections ... 100 -selecting default shared_buffers ... 1000 -creating configuration files ... ok -creating template1 database in /var/lib/postgresql/data/base/1 ... ok -initializing pg_shadow ... ok -enabling unlimited row size for system tables ... ok -initializing pg_depend ... ok -creating system views ... ok -loading pg_description ... ok -creating conversions ... ok -setting privileges on built-in objects ... ok -creating information schema ... ok -vacuuming database template1 ... ok -copying template1 to template0 ... ok +<table> + <tr> + <th>USE Flag</th> + <th>Meaning</th> + </tr> + <tr> + <ti>doc</ti> + <ti> + Include the <uri link="http://www.postgresql.org/docs/";>online + documentation</uri> to be stored on your system + </ti> + </tr> + <tr> + <ti>kerberos</ti> + <ti>Support for utilizing Kerberos for authentication.</ti> + </tr> + <tr> + <ti>ldap</ti> + <ti> + Support for utilizing LDAP authentication and connection parameter lookup. + </ti> + </tr> + <tr> + <ti>nls</ti> + <ti> + Enable the ability to display messages in a language other than + English. Used in conjunction with the Portage variable LINGUAS. + </ti> + </tr> + <tr> + <ti>pam</ti> + <ti> + Support for utilizing Pluggable Authentication Modules for authentication. + </ti> + </tr> + <tr> + <ti>perl</ti> + <ti> + Enable support for using Perl to write functions and trigger procedures. + </ti> + </tr> + <tr> + <ti>pg-intdatetime (Deprecated)</ti> + <ti> + Use the newer, high resolution, 64-bit integer method for formatting + timestamps instead of the older, floating point method. Unless you had a + previous installation that utilized the deprecated method, leave this + enabled. (See note.) + </ti> + </tr> + <tr> + <ti>pg_legacytimestamp</ti> + <ti> + Use the older, floating-point method for formatting timestamps instead of + the higher resolution 64-bit integer method. Unless you had a previous + installation that utilized this deprecated method, leave this USE flag + disabled. (See note.) + </ti> + </tr> + <tr> + <ti>python</ti> + <ti> + Enable support for using Python to write functions and trigger procedures. + </ti> + </tr> + <tr> + <ti>readline</ti> + <ti> + You really want this enabled. Disabling removes command line editing and + history in psql. + </ti> + </tr> + <tr> + <ti>selinux</ti> + <ti> + Install respective SELinux policy. This can only be enabled by using the + SELinux profile. + </ti> + </tr> + <tr> + <ti>ssl</ti> + <ti>Enable support for SSL connections.</ti> + </tr> + <tr> + <ti>tcl</ti> + <ti> + Enable support for using Tcl to write functions and trigger procedures. + </ti> + </tr> + <tr> + <ti>threads</ti> + <ti> + Make the client libraries thread-safe. The rest of your system must be + thread-safe as well. + </ti> + </tr> + <tr> + <ti>uuid</ti> + <ti> + Include support to generate a 128 bit random unique identifier. This is + useful for merging databases together so the chances of collisions become + extremely low. + </ti> + </tr> + <tr> + <ti>xml</ti> + <ti>Enable SQL/XML support.</ti> + </tr> + <tr> + <ti>zlib</ti> + <ti>Support for compressed archives in pg_dump and pg_restore.</ti> + </tr> +</table> -WARNING: enabling "trust" authentication for local connections -You can change this by editing pg_hba.conf or using the -A option the -next time you run initdb. +<note> +Flipping the 'pg-intdatetime' or the 'pg_legacytimestamp' will require you to do +a dump and restore if any of your databases utilize timestamps. The two methods +are incompatible with each other. +</note> -Success. You can now start the database server using: +</body> +</section> +<section> +<title>Start Emerging</title> +<body> - /usr/bin/postmaster -D /var/lib/postgresql/data -or - /usr/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start +<pre caption="Emerging PostgreSQL server"> +# <i>emerge -av dev-db/postgresql-server</i> - * - * You can use /etc/init.d/postgresql script to run PostgreSQL instead of pg_ctl. - * +[ebuild N ] dev-db/postgresql-docs-9.0.3 0 kB +[ebuild N ]dev-db/postgresql-base-9.0.3 USE="doc nls pam readline ssl zlib + -kerberos -ldap -pg_legacytimestamp -threads" LINGUAS="-af -cs -de -es -fa -fr + -hr -hu -it -ko -nb -pl -pt_BR -ro -ru -sk -sl -sv -tr -zh_CN -zh_TW" 0 kB +[ebuild N ] dev-db/postgresql-server-9.0.3 USE="doc nls perl python + -pg_legacytimestamp (-selinux) -tcl -uuid -xml" LINGUAS="-af -cs -de -es -fa + -fr -hr -hu -it -ko -nb -pl -pt_BR -ro -ru -sk -sl -sv -tr -zh_CN -zh_TW" 0 kB </pre> <p> -Now the initial database environment is setup. The next section will look at -verifying the install and setting up users to access the database. +You may receive a notice regarding that any of the above packages are blocked by +any or all of the following packages: dev-db/postgresql-libs, +dev-db/postgresql-client, dev-db/libpq or dev-db/postgresql. These packages are +<b>not maintained</b> and obsoleted. Refer to the section on <uri +link="#oldmigration">migration</uri> for how to handle this situation. </p> </body> </section> <section> -<title>PostgreSQL database setup</title> +<title>Preparing to Initialize the Database Cluster</title> <body> <p> -Now that PostgreSQL is setup, it's a good idea at this point to verify the -installation. First, make sure the service starts up ok: +Once the packages have finished emerging, you may want to edit +<path>/etc/conf.d/postgresql-9.0</path>. There are three lines that effect the +defaults of the server and <b>cannot</b> be changed later without deleting the +directory that contains the database cluster and reinitializing. </p> -<pre caption="Starting up the PostgreSQL service"> -# <i>/etc/init.d/postgresql start</i> -* Starting PostgreSQL ... [ ok ] -</pre> +<p> +<e>PGDATA</e> defines where to place the configuration files. <e>DATA_DIR</e> +defines where to create the database cluster and related +files. <e>PG_INITDB_OPTS</e> may contain any <uri +link="http://www.postgresql.org/docs/current/static/app-initdb.html";>extra +options</uri> you would care to set. The extra options are <b>not</b> required +as the reasonable defaults are, ahem, reasonable. +</p> <p> -Once this is verified working, it's also a good idea to add it to the default -runlevel so it starts at boot: +In the following example, <e>PGDATA</e> states that the configuration files are +to be located in <path>/etc/postgresql-9.0/</path>. <e>DATA_DIR</e> states that +the database cluster should be installed to +<path>/var/lib/postgresql/9.0/data/</path>, which is the default. If you decide +to stray from the default, bear in mind that it is a <b>very good idea</b> to +keep the major version in the path. <e>PG_INITDB_OPTS</e> states that the +default locale should be <e>en_US.UTF-8</e>. That is, U.S. English ordering and +formatting, and UTF-8 character encoding. </p> -<pre caption="Adding to the default runlevel"> -# <i>rc-update add postgresql default</i> -* postgresql added to runlevel default +<pre caption="Example contents of /etc/conf.d/postgresql-8.4"> +<comment># Location of configuration files</comment> +PGDATA="/etc/postgresql-9.0/" + +<comment># Where the data directory is located/to be created</comment> +DATA_DIR="/var/lib/postgresql/9.0/data" + +<comment># Additional options to pass to initdb. +# See 'man initdb' for available options.</comment> +PG_INITDB_OPTS="--locale=en_US.UTF-8" </pre> +<note> +This only determines the default locale and character encoding. You can specify +different locales and/or character encodings at database creation time +(<c>CREATE DATABASE</c>) in the same database cluster. +</note> + <p> -Now that the service has started, it's time to try setting up a test database. -To start out, let's create a test database by using the <c>createdb</c> -command. We'll also pass along the <c>-U</c> option to set the user (it -defaults to the current user name if you don't), and the <c>-W</c> option to -request the password we created earlier. Finally we give it the name of the -database we want to create: +There are six locale options that can be set to override <e>--locale=</e>. The +following table lists the six options that, if used, are to be formatted as: +<c>--option=lo_LO.ENCODING</c>. </p> -<pre caption="Creating a database with createdb"> -$ <i>createdb -U postgres -W test</i> -Password: -CREATE DATABASE +<table> + <tr> + <th>Option</th> + <th>Effects</th> + </tr> + <tr> + <ti>lc-collate</ti> + <ti>String sort order</ti> + </tr> + <tr> + <ti>lc-ctype</ti> + <ti> + Character classification (What is a letter? Its upper-case equivalent?) + </ti> + </tr> + <tr> + <ti>lc-messages</ti> + <ti>Language of messages</ti> + </tr> + <tr> + <ti>lc-monetary</ti> + <ti>Formatting of currency amounts</ti> + </tr> + <tr> + <ti>lc-numeric</ti> + <ti>Formatting of numbers</ti> + </tr> + <tr> + <ti>lc-time</ti> + <ti>Formatting of dates and times</ti> + </tr> +</table> + +<p> +So, if you would like the default to be English, but you want messages in, say, +Swedish, then your <e>PG_INITDB_OPTS</e> would look like so: +</p> + +<pre caption="Example"> +PG_INITDB_OPTS="--locale=en_US.UTF-8 --lc-messages=sv_SE.UTF-8" </pre> <p> -The database was successfully created, and we can confirm that the database can -run basic tasks. We'll go ahead and drop this database (remove it) with the -<c>dropdb</c> command: +A complete list of language and character encodings supported by the server can +be found in the documentation, but your system must also support the respective +languages and character encodings. Compare the output of <c>locale -a</c> to the +<uri +link="http://www.postgresql.org/docs/current/static/multibyte.html";>encodings</uri> +in the documentation. </p> -<pre caption="Dropping a database with dropdb"> -$ <i>dropdb -U postgres -W test</i> -Password: -DROP DATABASE +<p> +You can change your locale and encoding selections at database <uri + link="http://www.postgresql.org/docs/current/static/sql-createdatabase.html";>creation + time.</uri> In order to change the locale for a database after you have +created it, you must drop the database and start over again. +</p> + +<pre caption="Finalize the installation"> +# <i>emerge --config dev-db/postgresql-server:9.0</i> </pre> <p> -Right now, only the postgres user can run commands. Obviously this is not the -sort of setup one would like in a multi-user environment. The next section will -look at working with user accounts. +This will create the database cluster and store all the related server files +into <e>PGDATA</e> and <e>DATA_DIR</e>. </p> </body> </section> +</chapter> + +<chapter> +<title>Configuration</title> <section> -<title>Setting up database user accounts</title> +<title>Where the Configuration Files are Located</title> <body> <p> -As mentioned earlier, having to login as the postgres user is somewhat -undesirable in a mult-user environment. In most cases there will be various -users and services accessing the server, and each have different permission -requirements. So, to handle this, the <c>createuser</c> command can be used. -This command is an alternative to running a few SQL queries, and is a lot more -flexible from an admin standpoint. We'll go ahead and create two users, a -'superuser' that can add other users and administer the db, and a standard user: +This time the focus is upon the files in the <e>PGDATA</e> directory, +<path>/etc/postgresql-9.0</path>, instead with primary focus on the +<path>postgresql.conf</path> and <path>pg_hba.conf</path> files. </p> -<pre caption="Setting up the superuser"> -<comment>(replace chris with the username you'd like to use)</comment> -$ <i>createuser -a -d -P -E -U postgres -W chris</i> -Enter password for new user: -Enter it again: -Password: -CREATE USER -</pre> +</body> +</section> +<section> +<title>postgresql.conf</title> +<body> <p> -There, we've created the superuser. The command line option <c>-a</c> specifies -that this user can add other users. <c>-d</c> means that this user can create -databases. <c>-P</c> let's you enter a password for the user and <c>-E</c> will -encrypt it for security purposes. Now then, we'll test this new user's -permissions out by setting up our standard user: +This is the main configuration file. The line that you may find of immediate +interest is <e>listen_addresses</e>. This variable defines to which addresses +PostgreSQL will bind. By default, only localhost and the Unix socket are +bound. Changing <e>listen_addresses</e> is not enough to enable remote +connections. That will be covered in the next section. The <uri +link="http://www.postgresql.org/docs/current/static/runtime-config.html";>official +documentation</uri> is fairly easy to understand and is exhaustive on all the +settings available. It would behoove you to read that in addition to what is +covered here as some things may change. </p> -<pre caption="Setting up the standard user"> -<comment>(replace chris with the username you've just created)</comment> -$ <i>createuser -A -D -P -E -U chris -W testuser</i> -Enter password for new user: -Enter it again: -Password: -CREATE USER -</pre> +<p> +Of secondary interest is the logging destination. By default, everything is +logged to <path>postmaster.log</path> in the <e>DATA_DIR</e> directory. There is +an entire subsection of <path>postgresql.conf</path> that covers a slew of +options for how, what and where to log. The subsection is marked: ERROR +REPORTING AND LOGGING. +</p> <p> -Success! Our new user was created using the previously created superuser. The -<c>-A</c> and <c>-D</c> options do the opposite of <c>-a</c> and <c>-d</c>, and -instead deny the user the ability to create other users and databases. Now that -there are users to work with, the next chapter will look at using the new -database. +Other than <e>listen_addresses</e> and the logging options, the rest of the +defaults in <path>postgresql.conf</path> are reasonable enough to get you going. </p> </body> </section> -</chapter> -<chapter> -<title>Using PostgreSQL</title> <section> -<title>Setting up permissions</title> +<title>pg_hba.conf</title> <body> <p> -Now there is a user that can create databases and add other users, and the main -postgres user that can do anything. The user created earlier can currently login -to the server, and that's about it. In general, users need to be able to insert -data and retrieve data, and sometimes any other number of tasks. So, for this -new user to be able to do anything, they must be setup with the proper -permissions. This can easily be done by passing the <c>-O</c> parameter to -<c>createdb</c>. We'll start by making a new database, <b>MyDB</b> with our -superuser that will be owned by the previous testuser: +The <path>pg_hba.conf</path> file states who is allowed to connect to the +database server and which authentication method must be used to establish the +connection. Again, the documentation is quite exhaustive on the settings and +what they all mean, but a few things are covered here for clarification. </p> -<pre caption="Creating the MyDB database"> -$ <i>createdb -O testuser -U chris -W MyDB</i> -Password: -CREATE DATABASE +<pre caption="Default pg_hba.conf"> +<comment># TYPE DATABASE USER CIDR-ADDRESS METHOD + +# "local" is for Unix domain socket connections only</comment> +local all all trust +<comment># IPv4 local connections:</comment> +host all all 127.0.0.1/32 trust +<comment># IPv6 local connections:</comment> +host all all ::1/128 trust </pre> <p> -Alright, now we have a new MyDB database, and a testuser that can access it. -To test this out, we'll login as the testuser to the new MyDB database. We'll -do this with the <c>psql</c> program. This program is what's used to connect to -the PostgreSQL database from command line. So connect to the new database like -so: +As has been mentioned before, by default the server is secure. Kind of. There is +only one database role that is available for log in by default: +<e>postgres</e>. And, the only way to initiate a connection to the database is +through the <path>/var/run/postgresql/.s.PGSQL.5432</path> Unix socket, which is +owned by the <e>postgres</e> system user and system group, or via localhost. Now +for the "kind of" bit: Any user on the system can make a connection to the +database through the localhost. Even as the <e>postgres</e> database superuser. </p> -<pre caption="Logging into the MyDB database as the testuser"> -$ <i>psql -U testuser -W MyDB</i> -Password: -Welcome to psql 8.0.4, the PostgreSQL interactive terminal. +<p> +To make a connection through the Unix socket, however, the users — +including the users for other services such as <e>apache</e> — must be in +the <e>postgres</e> system group. Use <c>gpasswd -a <e>user</e> postgres</c> to +add <e>user</e> to the <e>postgres</e> group. Users not in the <e>postgres</e> +group will be rejected with "Permission denied". +</p> + +<warn> +Never disable the Unix socket entirely. The initscripts require access to it in +order to operate properly. The method can be changed freely. +</warn> + +<p> +The <e>trust</e> method is what allows any user to log on as any user without a +password. It specifies just what it implies: Trust all connections for the given +type to the given database from the given database user (but not the system +user) from the given location without a password. This is what allows any user +on the system to log on as any user through the localhost connection from the +get go. This is not as dangerous as it seems, but does pose a serious security +risk in most circumstances. +</p> -Type: \copyright for distribution terms - \h for help with SQL commands - \? for help with psql commands - \g or terminate with semicolon to execute query - \q to quit +<p> +The two methods you will most likely use are: <e>password</e> and +<e>md5</e>. The password method only specifies that a password is required to +start the connection and the password is sent "in-the-clear". This method is +fine when such information will never leave the machine, such as connecting via +the Unix socket or localhost. The md5 method is like password, but protects the +password by using an md5 hash. This is what you want to use whenever the +password is going to traverse a network. +</p> -MyDB=> +<p> +At this point, this author would like to bring your attention to the last two +lines, four lines including comments, of the <path>pg_hba.conf</path> +file. PostgreSQL has native support for IPv6 regardless of your desires for such +support. Additionally, IPv4 addresses are automatically mapped to IPv6 +addresses, <e>i.e.</e>, 127.0.0.1 will be mapped to ::FFFF:127.0.0.1 and as +"pure" IPv6 ::FFFF:7F00:0001. +</p> + +<p> +There seems to be some misunderstanding, though, as to how host names are mapped +to IP addresses. Let us take a look at the <path>/etc/hosts</path> file. +</p> + +<pre caption="Example /etc/hosts"> +<comment># IPv4 and IPv6 localhost aliases</comment> +127.0.0.1 localhost +::1 localhost </pre> <p> -So, the testuser is now logged into the database, and can begin to initiate -some commands. To get a feel for using PostgreSQL, the next section will take a -look at some of the basic commands in navigating the <c>psql</c> client. +From the example above you can see that both an IPv4 and an IPv6 IP address are +mapped to localhost. When <c>psql</c> refers to this file, it will grab the +first match and use that as the address; in this case 127.0.0.1. When PostgreSQL +parses this, it will match the IPv6 formatted address as well, +e.g. ::ffff:127.0.0.1. If, however, the IPv6 address appears first, then +<c>psql</c> will map to ::1 alone; ::1 is not the same as ::ffff:127.0.0.1. As +such, if you do not have ::1 as a permitted means of access, <c>psql</c> will +not be able to establish a connection. Furthermore, your kernel needs to support +the IPv6 protocol. +</p> + +<p> +So, it is better to specify IP addresses alone to <c>psql</c> and in +<path>pg_hba.conf</path> rather than to rely on <path>/etc/hosts</path> to be +ordered properly, and it removes any doubt as to which IP addresses are allowed +or to which server you will connect. </p> </body> </section> +</chapter> + +<chapter> +<title>Starting the Server</title> <section> -<title>Basic PostgreSQL commands and creating a table</title> +<title>Give It a Go!</title> <body> <p> -For those who are used to MySQL, this is somewhat of a definite read. This is -where PostgreSQL may get somewhat unique with regards to running commands. To -start, here is a list of some commands that will be discussed: -</p> - -<table> -<tr> - <th>Command</th> - <th>Usage</th> - <th>MySQL Equivalent</th> -</tr> -<tr> - <ti>\c[onnect] [DBNAME|- [USER]]</ti> - <ti>Connects to another database</ti> - <ti>USE DATABASE</ti> -</tr> -<tr> - <ti>\q</ti> - <ti>Quit the <c>psql</c> client</ti> - <ti>quit</ti> -</tr> -<tr> - <ti>\i FILE</ti> - <ti>Run commands from <c>FILE</c></ti> - <ti>source FILE</ti> -</tr> -<tr> - <ti>\o [FILE]</ti> - <ti>Send query results to <c>FILE</c></ti> - <ti>INTO OUTFILE, but outputs everything (not just SELECTS)</ti> -</tr> -<tr> - <ti>\d [NAME]</ti> - <ti>Describe a database or table (as well as other items)</ti> - <ti>DESC(RIBE)</ti> -</tr> -<tr> - <ti>\db [PATTERN]</ti> - <ti> - List available tables that match <c>PATTERN</c> (all if no pattern - is given) - </ti> - <ti>SHOW TABLES</ti> -</tr> -</table> +Now start PostgreSQL and set the password for the database superuser +<e>postgres</e>. The commands are to be performed as 'root' in the following +code listing: +</p> + +<pre caption="Starting the Server"> +<comment>(Change 'trust' to 'password' for the localhost connections.)</comment> +# <i>nano -w /etc/postgresql-9.0/pg_hba.conf</i> +# <i>/etc/init.d/postgresql-9.0 start</i> +postgresql-9.0 | * Starting PostgreSQL ... [ ok ] + +<comment>(Open a connection to the server and set the password.)</comment> +# <i>psql -U postgres</i> +psql (9.0.3) +Type "help" for help. -<p> -With the exception of <c>\c[onnect]</c>, all the commands shown will be used -later on in the section. So right now the database is empty. That said, we need -to insert some data. The first step to inserting data, however, is to put it in -a table. Right now there are no tables in the database, so we need to create -one. This is done with the <c>CREATE TABLE</c> command. We'll make a table of -items. They will contain a Product ID, Description, and price: -</p> +postgres=# <i>\password</i> +Enter new password: +Enter it again: +postgres=# <i>\q</i> -<pre caption="Creating the products table"> -MyDB=> CREATE TABLE products ( -MyDB(> product_id SERIAL, -MyDB(> description TEXT, -MyDB(> price DECIMAL -MyDB(> ); -NOTICE: CREATE TABLE will create implicit sequence "products_product_id_seq" -for serial column "products.product_id" -CREATE TABLE +<comment>(Change 'trust' to 'password' for the local connection.)</comment> +# <i>nano -w /etc/postgresql-9.0/pg_hba.conf</i> +# <i>/etc/init.d/postgresql-9.0 reload</i> +postgresql-9.0 | * Reloading PostgreSQL configuration ... [ ok ] +# <i>rc-update add postgresql-9.0 default</i> + * service postgresql-9.0 added to runlevel default </pre> <p> -You can ignore the NOTICE, it's perfectly harmless. Looking at the last line of -the function, <c>CREATE TABLE</c> seems to indicate that the command has -succeeded. However, let's go ahead and verify that the table was indeed -successfully created with the <c>\d</c> command: +At this point you are ready to continue on with the official <uri +link="http://www.postgresql.org/docs/current/static/tutorial.html";>PostgreSQL +Tutorial</uri>. The tutorial will guide you through creating roles, databases, +schemata and all that fun and useful stuff. </p> -<pre caption="Looking at the newly created table"> -MyDB=> <i>\d products</i> - Table "public.products" - Column | Type | Modifiers --------------+---------+------------------------------------------------------------------ - product_id | integer | not null default nextval('public.products_product_id_seq'::text) - description | text | - price | numeric | -</pre> +</body> +</section> +</chapter> + +<chapter id="migrating"> +<title>Migrating PostgreSQL</title> +<section> +<title>When You Need to Migrate</title> +<body> <p> -Indeed the table was successfully created. Now that the table is created, it -needs to be populated with data. The next section will look at populating the -database with data. +There are only two reasons you would need to perform a migration: When moving +from one major version to another, <e>e.g.</e>, from PostgreSQL 8.4.7 to 9.0.3, +but not from 9.0.2 to 9.0.3; or when switching from the deprecated +floating-point timestamp format to the new 64-bit integer timestamp format. </p> +<note> +You will need to migrate your database when you move from the obsolete ebuilds +— dev-db/libpq, dev-db/postgresql, dev-db/postgresql-libs, and +dev-db/postgresql-client — to the new ebuilds — +dev-db/postgresql-docs, dev-db/postgresql-base and dev-db/postgresql-server. +</note> + </body> </section> -<section> -<title>Inserting data into the database</title> +<section id="post90"> +<title>Post-9.0 Migration</title> <body> <p> -This section will look at the two ways of populating the newly created table -with data. First let's look at the most basic command, <c>INSERT</c>: +<e>pg_upgrade</e>, a new utility that comes along with 9.0 and later, simplifies +the migration process rather drastically. </p> -<pre caption="INSERT syntax"> -INSERT INTO [tablename] (column1,column2,column3) VALUES(value1,value2,value3) +<p> +However, there are two caveats with using pg_upgrade. Firstly, it does not +support configuration files being in a different directory than where the data +is stored. This can be resolved by using symbolic links. Lastly, you can only +use it to migrate from a database from 8.3 or newer. If you have an older +database you will need to follow the <uri link="#pre90">Pre-9.0 Migration</uri> +instructions. +</p> + +<pre caption="Migrating with pg_upgrade"> +<comment>(Stop the servers you're going to migrate from and to.)</comment> +# <i>/etc/init.d/postgresql-8.4 stop</i> +# <i>/etc/init.d/postgresql-9.0 stop</i> +# <i>ln -s /etc/postgresql-8.4/*.conf /var/lib/postgresql/8.4/data/</i> +# <i>ln -s /etc/postgresql-9.0/*.conf /var/lib/postgresql/9.0/data/</i> + +<comment>(Change the method of database user 'postgres' to trust on local +connections on all databases.)</comment> +# <i>nano -w /etc/postgresql-8.4/pg_hba.conf</i> +# <i>nano -w /etc/postgresql-9.0/pg_hba.conf</i> + +<comment>You may need to change the permissions of '/var/lib/postgresql/' before +you perform the next step.</comment> +# <i>su - postgres</i> +$ <i>pg_upgrade -u postgres \ + -d /var/lib/postgresql/8.4/data -D /var/lib/postgresql/9.0/data \ + -b /usr/lib/postgresql-8.4/bin -B /usr/lib/postgresql-9.0/bin</i> +<comment>(Perform the tasks pg_upgrade tells you to do , if any.)</comment> +$ <i>logout</i> + +<comment>(Remove the symbolic links we created earlier.)</comment> +# <i>rm /var/lib/postgresql/8.4/data/*.conf</i> +# <i>rm /var/lib/postgresql/9.0/data/*.conf</i> +# <i>/etc/init.d/postgresql-9.0 start</i> </pre> +</body> +</section> +<section id="pre90"> +<title>Pre-9.0 Migration: With the New Ebuilds</title> +<body> + <p> -<c>tablename</c> contains the name of the table to insert the data into. -(column1,column2,column3) lets you specify the specific columns to insert the -values into. VALUES(value1,value2,value3) is the listing of values. The values -are inserted into the same order as the columns (column1 gets value1, column2 -gets value2, column3 gets value3). These counts <e>must</e> be the same. So -let's go ahead and insert an item into the table: +Because the new ebuilds feature a more advanced slotting method than the +previous ones, the downtime is quite minimal, most likely minutes rather than +hours. </p> -<impo> -From working with databases for a long time, I personally recommend specifying -<c>INSERT</c> statements exactly as above. Developers often make the mistake of -using <c>INSERT INTO</c> without specifying columns. This is unproductive, as -if a new column gets added to the database, it will cause in error if the value -to column count is not the same. You should <e>always</e> specify the columns -unless you're 300% sure you'll never add a column. -</impo> - -<pre caption="Inserting data into the table"> -MyDB=> <i>INSERT INTO products (description,price) VALUES('A test product', 12.00);</i> -INSERT 17273 1 -</pre> +<p> +In the following examples, it is assumed that you are using the default +locations and port settings, and that you are migrating from 8.3 to 8.4. Adjust +accordingly if you have deviated from the default. +</p> <p> -The last line needs a bit of explaining. The return of an insert command is an -OID (Object Identifier) and the number of rows inserted. OID's are a bit beyond -the scope of this guide, and the <uri -link="http://www.postgresql.org/docs/8.1/static/datatype-oid.html";>PostgreSQL -manual</uri> has some good information on it. Now, for a situation where you -have 20,000 products, these insert statements can be a little tedious. However, -not all is lost. The <c>COPY</c> command can be used to insert data into a -table from a file or stdin. In this example, let's assume that you have a csv -(comma separated values) file, which contains the product id, description, and -price. The file looks like this: +If you have not already done so, follow the <uri +link="#installation">installation instructions</uri> before starting the +migration. Such a compile may hamper performance on the database server but it +can keep going. </p> -<pre caption="products.csv"> -2,meat,6.79 -3,soup,0.69 -4,soda,1.79 -</pre> +<p> +A couple of files need to be tweaked before beginning the migration. Edit +<e>PGPORT</e> in the <path>/etc/conf.d/postgresql-8.4</path> configuration file +to 6543. (Any port number other than what your old installation is bound to will +do.) +</p> <p> -Now we'll use the <c>COPY</c> command to populate our data: +Next, edit <path>/etc/postgresql-8.3/pg_hba.conf</path> so that only the +database superuser <e>postgres</e> can access the database cluster via the Unix +socket. </p> -<impo> -The <c>COPY FROM STDIN</c> command is used because only the postgres user can -insert data from a file (for obvious security reasons). -</impo> +<pre caption="Migrate with the New Ebuilds"> +# <i>cp -p /etc/postgresql-8.3/pg_hba.conf /etc/postgresql-8.4/</i> -<pre caption="Using COPY to populate the products table"> -MyDB=> <i>COPY products FROM STDIN WITH DELIMITER AS ',';</i> -Enter data to be copied followed by a newline. -End with a backslash and a period on a line by itself. ->> <i>2,meat,6.79</i> ->> <i>3,soup,0.69</i> ->> <i>4,soda,1.79</i> ->> <i>\.</i> +<comment>(The following should be safe. Read the documentation to be sure.)</comment> +# <i> cp -p /etc/postgresql-8.3/postgresql.conf /etc/postgresql-8.4/</i> +<comment> +(Don't forget to copy over any other configuration files that you may need.) +</comment> +# <i>/etc/init.d/postgresql-8.3 reload</i> +# <i>/etc/init.d/postgresql-8.4 start</i> + +<comment>(Begin piping the data from the old cluster to the new cluster.)</comment> +# <i>pg_dumpall -U postgres -p 5432 | psql -U postgres -d postgres -p 6543</i> +# <i>/etc/init.d/postgresql-8.3 stop</i> +# <i>/etc/init.d/postgresql-8.4 stop</i> + +<comment>(Edit PGPORT back to 5432.)</comment> +# <i>nano -w /etc/conf.d/postgresql-8.4</i> + +<comment>(Allow users access once more.)</comment> +# <i>nano -w /etc/postgresql-8.4/pg_hba.conf</i> +# <i>/etc/init.d/postgresql-8.4 start</i> +# <i>rc-update del postgresql-8.3 && rc-update add postgresql-8.4 default</i> </pre> <p> -Unfortunately, this line doesn't return the same status information as the -<c>INSERT INTO</c> statement. How do we know the data was inserted? The next -section will look at running queries to check our data. +Hopefully everything went according to plan and you have a successfully updated +server that contains precisely the same data, bit for bit, as the old server. </p> </body> </section> -<section> -<title>Using PostgreSQL queries</title> +<section id="oldmigration"> +<title>Pre-9.0 Migration: From the Obsolete Ebuilds</title> <body> <p> -This section will look at using the <c>SELECT</c> statement to view data in our -tables. The basic <c>SELECT</c> format looks like this: +You will need to schedule some downtime for your server. The old ebuilds +<b>cannot</b> be installed at the same time as the new ebuilds. As such, assume +that the server will have to be down for a few hours. Maybe for the weekend, +even. </p> -<pre caption="SELECT syntax"> -SELECT (column1,column2|*) FROM (table) [WHERE (conditionals)] -</pre> - <p> -There are two ways to select columns. The first is using <c>*</c> to select all -columns, and the second is to specify a list of specific columns you wish to -see. The second is quite handy when you want to find a specific column in a -rather large list of them. Let's start out with using <c>SELECT</c> with -<c>*</c> to specify all columns: +Before starting, you will need to deny access to the server, so that no changes +are made. You may also want to backup your <path>postgresql.conf</path> and +<path>pg_hba.conf</path> and any other configuration file that you deem +important. </p> -<pre caption="Viewing the products table"> -MyDB=> <i>SELECT * FROM products;</i> - product_id | description | price -------------+----------------+------- - 1 | A test product | 12.00 - 2 | meat | 6.79 - 3 | soup | 0.69 - 4 | soda | 1.79 -(4 rows) +<pre caption="Steps to Migrate from the Obsolete Ebuilds"> +# <i>pg_dumpall -U postgres > backup_file</i> +# <i>/etc/init.d/postgresql stop</i> +# <i>emerge -C dev-db/postgresql dev-db/libpq dev-db/postgresql-client \ + dev-db/postgresql-client</i> +<comment> +(Follow the steps detailed in this article for installing and configuring the +server.) +</comment> +# <i>/etc/init.d/postgresql-8.4 start</i> +# <i>psql -f backup_file postgres</i> </pre> <p> -As shown here, all the data we inserted earlier is indeed in the table. Now -let's say we only want to see the description and the price, and don't care -about the product id. In this case we'll use the column specific SELECT form: +You may break some packages that were built against those packages, but once you +have installed dev-db/postgresql-base and/or dev-db/postgresql-server you can +run <c>revdep-rebuild</c> to reemerge any packages that may have been broken. </p> -<pre caption="Viewing specific columns from the products table"> -MyDB=> <i>SELECT description,price FROM products;</i> - description | price -----------------+------- - A test product | 12.00 - meat | 6.79 - soup | 0.69 - soda | 1.79 -(4 rows) -</pre> - -<p> -Now only the product and price is shown, letting us focus on only the important -data. Now let's say that we want to see only the items that are greater than -$2.00. Here's where the <c>WHERE</c> clause comes in handy: -</p> +</body> +</section> +</chapter> -<pre caption="Viewing specific rows from the products table"> -MyDB=> <i>SELECT description,price FROM products WHERE price > 2.00;</i> - description | price -----------------+------- - A test product | 12.00 - meat | 6.79 -(2 rows) -</pre> +<chapter> +<title>Utilities</title> +<section> +<title>pgAdmin III</title> +<body> <p> -Now a listing of products over $2.00 is displayed, focusing the data even more. -These forms of querying for information are very powerful, and can help create -extremely useful reports. +<uri link="http://www.pgadmin.org/";>pgAdmin III</uri> is a graphical utility +for managing PostgreSQL. </p> </body> </section> +</chapter> + +<chapter> +<title>Troubleshooting</title> <section> -<title>Conclusion</title> +<title>Server Lacks Instrumentation Functions</title> <body> <p> -This concludes the PostgreSQL Guide. A big thanks goes to Masatomo Nakano, the -previous Gentoo PostgreSQL maintainer for his help in answering my questions. -Any suggestions on this guide should be sent to -<mail>chriswh...@gentoo.org</mail>. For more extensive documentation, see the -<uri link="http://www.postgresql.org";>PostgreSQL website</uri>. +This problem is easy to solve. What is difficult about it is finding the +answer. What is required is an import from a file that already exists on the +storage drive: <path>adminpack.sql</path>. To resolve this issue, run this +command: </p> +<pre caption="Command to Add Instrumentation Functions"> +# <i>psql -U postgres --file /usr/share/postgresql-9.0/contrib/adminpack.sql</i> +</pre> + </body> </section> </chapter>
