* [gentoo-commits] gentoo commit in xml/htdocs/doc/en: postgres-howto.xml
@ 2008-05-19 21:09 Sven Vermeulen (swift)
0 siblings, 0 replies; 6+ messages in thread
From: Sven Vermeulen (swift) @ 2008-05-19 21:09 UTC (permalink / raw
To: gentoo-commits
swift 08/05/19 21:09:45
Log:
--
gentoo-commits@lists.gentoo.org mailing list
^ permalink raw reply [flat|nested] 6+ messages in thread
* [gentoo-commits] gentoo commit in xml/htdocs/doc/en: postgres-howto.xml
@ 2011-08-13 11:57 Sven Vermeulen (swift)
0 siblings, 0 replies; 6+ messages in thread
From: Sven Vermeulen (swift) @ 2011-08-13 11:57 UTC (permalink / raw
To: gentoo-commits
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="chriswhite@gentoo.org">Chris White</mail>
+ <mail link="titanofold@gentoo.org">Aaron W. Swenson</mail>
</author>
<author title="Editor">
- <mail link="neysx@gentoo.org">Xavier Neys</mail>
+ <mail link="pgsql-bugs@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>chriswhite@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>
^ permalink raw reply [flat|nested] 6+ messages in thread
* [gentoo-commits] gentoo commit in xml/htdocs/doc/en: postgres-howto.xml
@ 2011-08-13 11:58 Sven Vermeulen (swift)
0 siblings, 0 replies; 6+ messages in thread
From: Sven Vermeulen (swift) @ 2011-08-13 11:58 UTC (permalink / raw
To: gentoo-commits
swift 11/08/13 11:58:26
Modified: postgres-howto.xml
Log:
Correct link / name
Revision Changes Path
1.7 xml/htdocs/doc/en/postgres-howto.xml
file : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.7&view=markup
plain: http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.7&content-type=text/plain
diff : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?r1=1.6&r2=1.7
Index: postgres-howto.xml
===================================================================
RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- postgres-howto.xml 13 Aug 2011 11:57:38 -0000 1.6
+++ postgres-howto.xml 13 Aug 2011 11:58:26 -0000 1.7
@@ -1,8 +1,8 @@
<?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.6 2011/08/13 11:57:38 swift Exp $ -->
+<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.7 2011/08/13 11:58:26 swift Exp $ -->
-<guide link="/doc/en/postgresql-howto.xml" lang="en">
+<guide link="/doc/en/postgres-howto.xml" lang="en">
<title>PostgreSQL Quick Start Guide</title>
<author title="Author">
^ permalink raw reply [flat|nested] 6+ messages in thread
* [gentoo-commits] gentoo commit in xml/htdocs/doc/en: postgres-howto.xml
@ 2011-09-15 20:36 Sven Vermeulen (swift)
0 siblings, 0 replies; 6+ messages in thread
From: Sven Vermeulen (swift) @ 2011-09-15 20:36 UTC (permalink / raw
To: gentoo-commits
swift 11/09/15 20:36:20
Modified: postgres-howto.xml
Log:
Fix bug #383081 - Correct version for codelisting, thanks to Joost Roeleveld for reporting
Revision Changes Path
1.9 xml/htdocs/doc/en/postgres-howto.xml
file : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.9&view=markup
plain: http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.9&content-type=text/plain
diff : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?r1=1.8&r2=1.9
Index: postgres-howto.xml
===================================================================
RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v
retrieving revision 1.8
retrieving revision 1.9
diff -u -r1.8 -r1.9
--- postgres-howto.xml 4 Sep 2011 17:53:40 -0000 1.8
+++ postgres-howto.xml 15 Sep 2011 20:36:20 -0000 1.9
@@ -1,6 +1,6 @@
<?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.8 2011/09/04 17:53:40 swift Exp $ -->
+<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.9 2011/09/15 20:36:20 swift Exp $ -->
<guide lang="en">
<title>PostgreSQL Quick Start Guide</title>
@@ -23,8 +23,8 @@
<!-- See http://creativecommons.org/licenses/by-sa/2.5 -->
<license/>
-<version>8</version>
-<date>2011-08-08</date>
+<version>9</version>
+<date>2011-09-15</date>
<chapter>
<title>Introduction</title>
@@ -328,7 +328,7 @@
formatting, and UTF-8 character encoding.
</p>
-<pre caption="Example contents of /etc/conf.d/postgresql-8.4">
+<pre caption="Example contents of /etc/conf.d/postgresql-9.0">
<comment># Location of configuration files</comment>
PGDATA="/etc/postgresql-9.0/"
^ permalink raw reply [flat|nested] 6+ messages in thread
* [gentoo-commits] gentoo commit in xml/htdocs/doc/en: postgres-howto.xml
@ 2012-05-04 6:44 Joshua Saddler (nightmorph)
0 siblings, 0 replies; 6+ messages in thread
From: Joshua Saddler (nightmorph) @ 2012-05-04 6:44 UTC (permalink / raw
To: gentoo-commits
nightmorph 12/05/04 06:44:35
Modified: postgres-howto.xml
Log:
update postgres instrumentation functions for multiple versions, bug 414415
Revision Changes Path
1.10 xml/htdocs/doc/en/postgres-howto.xml
file : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.10&view=markup
plain: http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.10&content-type=text/plain
diff : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?r1=1.9&r2=1.10
Index: postgres-howto.xml
===================================================================
RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v
retrieving revision 1.9
retrieving revision 1.10
diff -u -r1.9 -r1.10
--- postgres-howto.xml 15 Sep 2011 20:36:20 -0000 1.9
+++ postgres-howto.xml 4 May 2012 06:44:35 -0000 1.10
@@ -1,8 +1,8 @@
<?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.9 2011/09/15 20:36:20 swift Exp $ -->
+<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.10 2012/05/04 06:44:35 nightmorph Exp $ -->
-<guide lang="en">
+<guide>
<title>PostgreSQL Quick Start Guide</title>
<author title="Author">
@@ -23,8 +23,8 @@
<!-- See http://creativecommons.org/licenses/by-sa/2.5 -->
<license/>
-<version>9</version>
-<date>2011-09-15</date>
+<version>10</version>
+<date>2012-05-04</date>
<chapter>
<title>Introduction</title>
@@ -823,16 +823,21 @@
<body>
<p>
-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:
+This problem is easy to solve, with the solution depending on the version you
+are using. 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 the command appropriate
+to the version you have:
</p>
-<pre caption="Command to Add Instrumentation Functions">
+<pre caption="Add instrumentation functions for versions less than 9.1">
# <i>psql -U postgres --file /usr/share/postgresql-9.0/contrib/adminpack.sql</i>
</pre>
+<pre caption="Add instrumentation functions for version 9.1 and up">
+# <i>psql -U postgres -c "CREATE EXTENSION adminpack;"</i>
+</pre>
+
</body>
</section>
</chapter>
^ permalink raw reply [flat|nested] 6+ messages in thread
* [gentoo-commits] gentoo commit in xml/htdocs/doc/en: postgres-howto.xml
@ 2012-06-13 9:10 Joshua Saddler (nightmorph)
0 siblings, 0 replies; 6+ messages in thread
From: Joshua Saddler (nightmorph) @ 2012-06-13 9:10 UTC (permalink / raw
To: gentoo-commits
nightmorph 12/06/13 09:10:59
Modified: postgres-howto.xml
Log:
add eselect step per comment #8 of bug #410717
Revision Changes Path
1.11 xml/htdocs/doc/en/postgres-howto.xml
file : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.11&view=markup
plain: http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.11&content-type=text/plain
diff : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?r1=1.10&r2=1.11
Index: postgres-howto.xml
===================================================================
RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v
retrieving revision 1.10
retrieving revision 1.11
diff -u -r1.10 -r1.11
--- postgres-howto.xml 4 May 2012 06:44:35 -0000 1.10
+++ postgres-howto.xml 13 Jun 2012 09:10:59 -0000 1.11
@@ -1,6 +1,6 @@
<?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.10 2012/05/04 06:44:35 nightmorph Exp $ -->
+<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.11 2012/06/13 09:10:59 nightmorph Exp $ -->
<guide>
<title>PostgreSQL Quick Start Guide</title>
@@ -23,8 +23,8 @@
<!-- See http://creativecommons.org/licenses/by-sa/2.5 -->
<license/>
-<version>10</version>
-<date>2012-05-04</date>
+<version>11</version>
+<date>2012-06-13</date>
<chapter>
<title>Introduction</title>
@@ -670,6 +670,10 @@
# <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>(Check available versions, then select yours)</comment>
+# <i>eselect postgresql list</i>
+# <i>eselect postgresql set 9.0</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>
^ permalink raw reply [flat|nested] 6+ messages in thread
end of thread, other threads:[~2012-06-13 9:11 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2011-08-13 11:57 [gentoo-commits] gentoo commit in xml/htdocs/doc/en: postgres-howto.xml Sven Vermeulen (swift)
-- strict thread matches above, loose matches on Subject: below --
2012-06-13 9:10 Joshua Saddler (nightmorph)
2012-05-04 6:44 Joshua Saddler (nightmorph)
2011-09-15 20:36 Sven Vermeulen (swift)
2011-08-13 11:58 Sven Vermeulen (swift)
2008-05-19 21:09 Sven Vermeulen (swift)
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox