Get Gentoo!
gentoo.org sites
gentoo.org Wiki Bugs Forums Packages
Planet Archives Sources
Infra Status

Gentoo Archives: gentoo-doc-cvs

From: "Sven Vermeulen (swift)" <swift@g.o>
To: gentoo-doc-cvs@l.g.o
Subject: [gentoo-doc-cvs] gentoo commit in xml/htdocs/doc/en: postgres-howto.xml
Date: Sat, 13 Aug 2011 11:57:56
Message-Id: 20110813115738.2E6E82004C@flycatcher.gentoo.org
1 swift 11/08/13 11:57:38
2
3 Modified: postgres-howto.xml
4 Log:
5 #330927 - Revamped documentation for PostgreSQL (almost a full rewrite), thanks to Aaron W. Swenson and Mikkel A. Clausen
6
7 Revision Changes Path
8 1.6 xml/htdocs/doc/en/postgres-howto.xml
9
10 file : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.6&view=markup
11 plain: http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?rev=1.6&content-type=text/plain
12 diff : http://sources.gentoo.org/viewvc.cgi/gentoo/xml/htdocs/doc/en/postgres-howto.xml?r1=1.5&r2=1.6
13
14 Index: postgres-howto.xml
15 ===================================================================
16 RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v
17 retrieving revision 1.5
18 retrieving revision 1.6
19 diff -u -r1.5 -r1.6
20 --- postgres-howto.xml 19 May 2008 21:09:45 -0000 1.5
21 +++ postgres-howto.xml 13 Aug 2011 11:57:38 -0000 1.6
22 @@ -1,715 +1,838 @@
23 <?xml version="1.0" encoding="UTF-8"?>
24 <!DOCTYPE guide SYSTEM "/dtd/guide.dtd">
25 -<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.5 2008/05/19 21:09:45 swift Exp $ -->
26 +<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.6 2011/08/13 11:57:38 swift Exp $ -->
27
28 -<guide link="/doc/en/postgres-howto.xml" lang="en">
29 -<title>PostgreSQL Guide</title>
30 +<guide link="/doc/en/postgresql-howto.xml" lang="en">
31 +<title>PostgreSQL Quick Start Guide</title>
32
33 <author title="Author">
34 - <mail link="chriswhite@g.o">Chris White</mail>
35 + <mail link="titanofold@g.o">Aaron W. Swenson</mail>
36 </author>
37 <author title="Editor">
38 - <mail link="neysx@g.o">Xavier Neys</mail>
39 + <mail link="pgsql-bugs@g.o">Mikkel A. Clausen</mail>
40 </author>
41
42 +
43 <abstract>
44 -This guide is meant to show the basic setup of PostgreSQL. The setup described
45 -here should be sufficient enough to use for basic web appplications, and any
46 -other program that provides PostgreSQL support.
47 +This is a quick start guide to PostgreSQL. It covers emerging PostgreSQL and
48 +configuring it. This is complementary to the official documentation, but does
49 +not supplant it.
50 </abstract>
51
52 <!-- The content of this document is licensed under the CC-BY-SA license -->
53 <!-- See http://creativecommons.org/licenses/by-sa/2.5 -->
54 <license/>
55
56 -<version>1.2</version>
57 -<date>2007-04-25</date>
58 +<version>8</version>
59 +<date>2011-08-08</date>
60
61 <chapter>
62 <title>Introduction</title>
63 <section>
64 -<title>PostgreSQL introduction</title>
65 +<title>A Little Bit About PostgreSQL</title>
66 <body>
67
68 <p>
69 -When talking to most developers about the different database solutions to use,
70 -two major databases will usually form the answer. One would be <c>MySQL</c>,
71 -and the other is what this document will refer to, <c>PostgreSQL</c>. The
72 -advantages of one over the other is a somewhat long winded debate, however it
73 -is just to say that PostgreSQL has had a more firm grasp on true relational
74 -database structure than MySQL. Most of the standard features such as
75 -<b>FOREIGN KEY</b> was only just added in MySQL 5. However, whatever the case
76 -may be, this document assumes that you have selected PostgreSQL as the
77 -database to use. The first place to start is the <c>emerge</c> process. In the
78 -next section, the installation process through emerge will be described, as
79 -well as the basic configuration.
80 +<uri link="http://www.postgresql.org">PostgreSQL</uri> is a free and open source
81 +relational database management system (RDBMS). It supports such things as
82 +transactions, schemata and foreign keys, and is often touted to more strictly
83 +adhere to the SQL standards and to be more secure, by default, than any other
84 +database, commercial or otherwise.
85 +</p>
86 +
87 +<p>
88 +Visit the <uri link="http://www.postgresql.org/about/">About</uri> page on
89 +postgresql.org for more information.
90 </p>
91
92 </body>
93 </section>
94 <section>
95 -<title>PostgreSQL installation</title>
96 +<title>What This Article Will Cover</title>
97 <body>
98
99 <p>
100 -To begin, we must first <c>emerge</c> the PostgreSQL package. To do so, run the
101 -following code to first ensure that the options for it are properly set:
102 +This article will guide you through the Gentoo specific steps to install the
103 +PostgreSQL RDBMS.
104 </p>
105
106 -<pre caption="Checking the PostgreSQL build options">
107 -# <i>emerge -pv postgresql</i>
108 +<p>
109 +The Ebuilds covered by this article are <uri
110 +link="http://packages.gentoo.org/package/dev-db/postgresql-docs">dev-db/postgresql-docs</uri>,
111 +<uri
112 +link="http://packages.gentoo.org/package/dev-db/postgresql-base">dev-db/postgresql-base</uri>
113 +and <uri
114 +link="http://packages.gentoo.org/package/dev-db/postgresql-server">dev-db/postgresql-server</uri>.
115 +</p>
116
117 -These are the packages that I would merge, in order:
118 +<p>
119 +This article assumes that you will be installing the latest, stable version of
120 +PostgreSQL; at the time of this writing, the version was 9.0.3. Adjust the
121 +commands in this article as necessary for your specific version.
122 +</p>
123
124 -Calculating dependencies ...done!
125 -[ebuild N ] dev-db/postgresql-8.0.4 -doc -kerberos +nls +pam +perl -pg-intdatetime +python +readline (-selinux) +ssl -tcl +xml +zlib 0 kB
126 -</pre>
127 +<impo>
128 +The 8.2 branch will have its upstream support dropped in December of 2011. Start
129 +planning your migration now.
130 +</impo>
131 +
132 +</body>
133 +</section>
134 +<section>
135 +<title>About the Ebuilds</title>
136 +<body>
137
138 <p>
139 -Here's a list of what the different build options indicate:
140 +The PostgreSQL ebuilds in Portage feature slotting based on the major version.
141 +This allows you to have two major versions of PostgreSQL operating
142 +simultaneously; 8.4 and 9.0 libraries and servers can be installed and serve at
143 +the same time. This is useful in such circumstances where you need to move data
144 +from an older database to a new database, or need to have a production and a
145 +testing database on the same machine. Also, this prevents a database,
146 +corresponding libraries or executables from being overwritten by an incompatible
147 +update. That would require migration which is described in this guide.
148 </p>
149
150 -<table>
151 -<tr>
152 - <th>USE Flag</th>
153 - <th>Meaning</th>
154 -</tr>
155 -<tr>
156 - <ti>doc</ti>
157 - <ti>
158 - This USE flag enables or disables the installation of documentation
159 - outside of the standard man pages. The one good time to disable this
160 - option is if you are low on space, or you have alternate methods of
161 - getting a hold of the documentation (online, etc.)
162 - </ti>
163 -</tr>
164 -<tr>
165 - <ti>kerberos</ti>
166 - <ti>
167 - When connecting to the database, with this option enabled, the admin
168 - has the option of using <c>kerberos</c> to authenticate their
169 - users/services to the database.
170 - </ti>
171 -</tr>
172 -<tr>
173 - <ti>nls</ti>
174 - <ti>
175 - If this option is enabled, PostgreSQL can utilize translated strings for
176 - non-English speaking users.
177 - </ti>
178 -</tr>
179 -<tr>
180 - <ti>pam</ti>
181 - <ti>
182 - If this option is enabled, and the admin configures the PostgreSQL
183 - configuration file properly, users/services will be able to login to a
184 - PostgreSQL database using <c>PAM</c> (Pluggable Authentication Module).
185 - </ti>
186 -</tr>
187 -<tr>
188 - <ti>perl</ti>
189 - <ti>
190 - If this option is enabled, <c>perl</c> bindings for PostgreSQL will be
191 - built.
192 - </ti>
193 -</tr>
194 -<tr>
195 - <ti>pg-intdatetime</ti>
196 - <ti>
197 - If this option is enabled, PostgreSQL will support 64 bit integer date
198 - types.
199 - </ti>
200 -</tr>
201 -<tr>
202 - <ti>python</ti>
203 - <ti>
204 - If this option is enabled, PostgreSQL will be built with
205 - <c>python</c> bindings.
206 - </ti>
207 -</tr>
208 -<tr>
209 - <ti>readline</ti>
210 - <ti>
211 - If this option is enabled, PostgreSQL will support <c>readline</c> style
212 - command line editing. This includes command history and isearch.
213 - </ti>
214 -</tr>
215 -<tr>
216 - <ti>selinux</ti>
217 - <ti>
218 - If this option is enabled, an <c>selinux</c> policy for PostgreSQL will be
219 - installed.
220 - </ti>
221 -</tr>
222 -<tr>
223 - <ti>ssl</ti>
224 - <ti>
225 - If this option is enabled, PostgreSQL will utilize the <c>OpenSSL</c>
226 - library to encrypt traffic between PostgreSQL clients and servers.
227 - </ti>
228 -</tr>
229 -<tr>
230 - <ti>tcl</ti>
231 - <ti>
232 - If this option is enabled, PostgreSQL will build <c>tcl</c> bindings.
233 - </ti>
234 -</tr>
235 -<tr>
236 - <ti>xml</ti>
237 - <ti>
238 - If this option is enabled, <c>XPATH</c> style xml support will be built.
239 - More information on using xml support with PostgreSQL can be found on:
240 - <uri link="http://www.throwingbeans.org/postgresql_and_xml.html">
241 - PostgreSQL and XML</uri>.
242 - </ti>
243 -</tr>
244 -<tr>
245 - <ti>zlib</ti>
246 - <ti>
247 - This isn't really used by PostgreSQL itself, but by <c>pg_dump</c> to
248 - compress the dumps it produces.
249 - </ti>
250 -</tr>
251 -</table>
252 +<p>
253 +Additionally, bug and security fixes, which are delivered via minor version
254 +updates, can be applied without fear of corrupting the database or the
255 +PostgreSQL installation itself; 9.0.2 can be updated to 9.0.3 as they are
256 +guaranteed to be compatible and require no more interaction from you than to
257 +emerge it and restart the server process &mdash; neither migration,
258 +reconfiguration nor initialization are necessary.
259 +</p>
260
261 <p>
262 -Once you've customized PostgreSQL to meet your specific needs, go ahead and
263 -start the <c>emerge</c>:
264 +Read the <uri link="http://www.postgresql.org/support/versioning">PostgreSQL
265 +Versioning Policy</uri> for more information.
266 </p>
267
268 -<pre caption="Emerge-ing PostgreSQL">
269 -# <i>emerge postgresql</i>
270 -<comment>(Output shortened)</comment>
271 ->>> /usr/lib/libecpg.so.5 -> libecpg.so.5.0
272 ->>> /usr/bin/postmaster -> postgres
273 - * Make sure the postgres user in /etc/passwd has an account setup with /bin/bash as the shell
274 - *
275 - * Execute the following command
276 - * emerge --config =postgresql-8.0.4
277 - * to setup the initial database environment.
278 - *
279 ->>> Regenerating /etc/ld.so.cache...
280 ->>> dev-db/postgresql-8.0.4 merged.
281 -</pre>
282 +</body>
283 +</section>
284 +<section>
285 +<title>What this Article Will Not Cover</title>
286 +<body>
287
288 <p>
289 -As shown by the einfo output, there is some post setup that must be done. The
290 -next chapter will look at the actual configuration of PostgreSQL.
291 +There is quite a bit that will not be covered. The <uri
292 +link="http://www.postgresql.org/docs/">official documentation</uri> is somewhere
293 +in the neighborhood of 2,000 pages. So, a lot of details will be left out in
294 +this quick start guide. Only Gentoo specific issues will be covered and some
295 +basic configuration guidelines.
296 </p>
297
298 </body>
299 </section>
300 </chapter>
301 -<chapter>
302 -<title>PostgreSQL configuration</title>
303 +
304 +<chapter id="installation">
305 +<title>Installation</title>
306 <section>
307 -<title>Setting up the initial database environment</title>
308 +<title>The Obsolete Ebuilds</title>
309 <body>
310
311 <p>
312 -As noted in the earlier <c>emerge</c> output, the initial database environment
313 -must be setup. However, before this is done, one thing needs to be considered.
314 -Unlike, say MySQL, PostgreSQL's "root" password is the password of the actual
315 -user. However, only the user is created by the ebuild <e>not</e> the password.
316 -So before we can begin, the password must be set for the postgres user:
317 +If you have any of the following ebuilds installed, then you have an older,
318 +obsolete Gentoo installation of PostgreSQL and should migrate now:
319 +dev-db/postgresql-libs, dev-db/postgresql-client, dev-db/libpq and/or
320 +dev-db/postgresql.
321 </p>
322
323 -<pre caption="Setting the password">
324 -# <i>passwd postgres</i>
325 -New UNIX password:
326 -Retype new UNIX password:
327 -passwd: password updated successfully
328 -</pre>
329 -
330 <p>
331 -Now that this is set up, the creation of the initial database environment can
332 -occur:
333 +This article does cover <uri link="#oldmigration">migrating</uri> from the old
334 +ebuilds to the new ones.
335 </p>
336
337 -<pre caption="Configuring the database environment with emerge --config">
338 -# <i>emerge --config =postgresql-8.0.4</i>
339 -
340 -
341 -Configuring pkg...
342 -
343 - * Creating the data directory ...
344 - * Initializing the database ...
345 -The files belonging to this database system will be owned by user "postgres".
346 -This user must also own the server process.
347 -
348 -The database cluster will be initialized with locale C.
349 +</body>
350 +</section>
351 +<section>
352 +<title>USE Flags</title>
353 +<body>
354
355 -fixing permissions on existing directory /var/lib/postgresql/data ... ok
356 -creating directory /var/lib/postgresql/data/global ... ok
357 -creating directory /var/lib/postgresql/data/pg_xlog ... ok
358 -creating directory /var/lib/postgresql/data/pg_xlog/archive_status ... ok
359 -creating directory /var/lib/postgresql/data/pg_clog ... ok
360 -creating directory /var/lib/postgresql/data/pg_subtrans ... ok
361 -creating directory /var/lib/postgresql/data/base ... ok
362 -creating directory /var/lib/postgresql/data/base/1 ... ok
363 -creating directory /var/lib/postgresql/data/pg_tblspc ... ok
364 -selecting default max_connections ... 100
365 -selecting default shared_buffers ... 1000
366 -creating configuration files ... ok
367 -creating template1 database in /var/lib/postgresql/data/base/1 ... ok
368 -initializing pg_shadow ... ok
369 -enabling unlimited row size for system tables ... ok
370 -initializing pg_depend ... ok
371 -creating system views ... ok
372 -loading pg_description ... ok
373 -creating conversions ... ok
374 -setting privileges on built-in objects ... ok
375 -creating information schema ... ok
376 -vacuuming database template1 ... ok
377 -copying template1 to template0 ... ok
378 +<table>
379 + <tr>
380 + <th>USE Flag</th>
381 + <th>Meaning</th>
382 + </tr>
383 + <tr>
384 + <ti>doc</ti>
385 + <ti>
386 + Include the <uri link="http://www.postgresql.org/docs/">online
387 + documentation</uri> to be stored on your system
388 + </ti>
389 + </tr>
390 + <tr>
391 + <ti>kerberos</ti>
392 + <ti>Support for utilizing Kerberos for authentication.</ti>
393 + </tr>
394 + <tr>
395 + <ti>ldap</ti>
396 + <ti>
397 + Support for utilizing LDAP authentication and connection parameter lookup.
398 + </ti>
399 + </tr>
400 + <tr>
401 + <ti>nls</ti>
402 + <ti>
403 + Enable the ability to display messages in a language other than
404 + English. Used in conjunction with the Portage variable LINGUAS.
405 + </ti>
406 + </tr>
407 + <tr>
408 + <ti>pam</ti>
409 + <ti>
410 + Support for utilizing Pluggable Authentication Modules for authentication.
411 + </ti>
412 + </tr>
413 + <tr>
414 + <ti>perl</ti>
415 + <ti>
416 + Enable support for using Perl to write functions and trigger procedures.
417 + </ti>
418 + </tr>
419 + <tr>
420 + <ti>pg-intdatetime (Deprecated)</ti>
421 + <ti>
422 + Use the newer, high resolution, 64-bit integer method for formatting
423 + timestamps instead of the older, floating point method. Unless you had a
424 + previous installation that utilized the deprecated method, leave this
425 + enabled. (See note.)
426 + </ti>
427 + </tr>
428 + <tr>
429 + <ti>pg_legacytimestamp</ti>
430 + <ti>
431 + Use the older, floating-point method for formatting timestamps instead of
432 + the higher resolution 64-bit integer method. Unless you had a previous
433 + installation that utilized this deprecated method, leave this USE flag
434 + disabled. (See note.)
435 + </ti>
436 + </tr>
437 + <tr>
438 + <ti>python</ti>
439 + <ti>
440 + Enable support for using Python to write functions and trigger procedures.
441 + </ti>
442 + </tr>
443 + <tr>
444 + <ti>readline</ti>
445 + <ti>
446 + You really want this enabled. Disabling removes command line editing and
447 + history in psql.
448 + </ti>
449 + </tr>
450 + <tr>
451 + <ti>selinux</ti>
452 + <ti>
453 + Install respective SELinux policy. This can only be enabled by using the
454 + SELinux profile.
455 + </ti>
456 + </tr>
457 + <tr>
458 + <ti>ssl</ti>
459 + <ti>Enable support for SSL connections.</ti>
460 + </tr>
461 + <tr>
462 + <ti>tcl</ti>
463 + <ti>
464 + Enable support for using Tcl to write functions and trigger procedures.
465 + </ti>
466 + </tr>
467 + <tr>
468 + <ti>threads</ti>
469 + <ti>
470 + Make the client libraries thread-safe. The rest of your system must be
471 + thread-safe as well.
472 + </ti>
473 + </tr>
474 + <tr>
475 + <ti>uuid</ti>
476 + <ti>
477 + Include support to generate a 128 bit random unique identifier. This is
478 + useful for merging databases together so the chances of collisions become
479 + extremely low.
480 + </ti>
481 + </tr>
482 + <tr>
483 + <ti>xml</ti>
484 + <ti>Enable SQL/XML support.</ti>
485 + </tr>
486 + <tr>
487 + <ti>zlib</ti>
488 + <ti>Support for compressed archives in pg_dump and pg_restore.</ti>
489 + </tr>
490 +</table>
491
492 -WARNING: enabling "trust" authentication for local connections
493 -You can change this by editing pg_hba.conf or using the -A option the
494 -next time you run initdb.
495 +<note>
496 +Flipping the 'pg-intdatetime' or the 'pg_legacytimestamp' will require you to do
497 +a dump and restore if any of your databases utilize timestamps. The two methods
498 +are incompatible with each other.
499 +</note>
500
501 -Success. You can now start the database server using:
502 +</body>
503 +</section>
504 +<section>
505 +<title>Start Emerging</title>
506 +<body>
507
508 - /usr/bin/postmaster -D /var/lib/postgresql/data
509 -or
510 - /usr/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start
511 +<pre caption="Emerging PostgreSQL server">
512 +# <i>emerge -av dev-db/postgresql-server</i>
513
514 - *
515 - * You can use /etc/init.d/postgresql script to run PostgreSQL instead of pg_ctl.
516 - *
517 +[ebuild N ] dev-db/postgresql-docs-9.0.3 0 kB
518 +[ebuild N ]dev-db/postgresql-base-9.0.3 USE="doc nls pam readline ssl zlib
519 + -kerberos -ldap -pg_legacytimestamp -threads" LINGUAS="-af -cs -de -es -fa -fr
520 + -hr -hu -it -ko -nb -pl -pt_BR -ro -ru -sk -sl -sv -tr -zh_CN -zh_TW" 0 kB
521 +[ebuild N ] dev-db/postgresql-server-9.0.3 USE="doc nls perl python
522 + -pg_legacytimestamp (-selinux) -tcl -uuid -xml" LINGUAS="-af -cs -de -es -fa
523 + -fr -hr -hu -it -ko -nb -pl -pt_BR -ro -ru -sk -sl -sv -tr -zh_CN -zh_TW" 0 kB
524 </pre>
525
526 <p>
527 -Now the initial database environment is setup. The next section will look at
528 -verifying the install and setting up users to access the database.
529 +You may receive a notice regarding that any of the above packages are blocked by
530 +any or all of the following packages: dev-db/postgresql-libs,
531 +dev-db/postgresql-client, dev-db/libpq or dev-db/postgresql. These packages are
532 +<b>not maintained</b> and obsoleted. Refer to the section on <uri
533 +link="#oldmigration">migration</uri> for how to handle this situation.
534 </p>
535
536 </body>
537 </section>
538 <section>
539 -<title>PostgreSQL database setup</title>
540 +<title>Preparing to Initialize the Database Cluster</title>
541 <body>
542
543 <p>
544 -Now that PostgreSQL is setup, it's a good idea at this point to verify the
545 -installation. First, make sure the service starts up ok:
546 +Once the packages have finished emerging, you may want to edit
547 +<path>/etc/conf.d/postgresql-9.0</path>. There are three lines that effect the
548 +defaults of the server and <b>cannot</b> be changed later without deleting the
549 +directory that contains the database cluster and reinitializing.
550 </p>
551
552 -<pre caption="Starting up the PostgreSQL service">
553 -# <i>/etc/init.d/postgresql start</i>
554 -* Starting PostgreSQL ... [ ok ]
555 -</pre>
556 +<p>
557 +<e>PGDATA</e> defines where to place the configuration files. <e>DATA_DIR</e>
558 +defines where to create the database cluster and related
559 +files. <e>PG_INITDB_OPTS</e> may contain any <uri
560 +link="http://www.postgresql.org/docs/current/static/app-initdb.html">extra
561 +options</uri> you would care to set. The extra options are <b>not</b> required
562 +as the reasonable defaults are, ahem, reasonable.
563 +</p>
564
565 <p>
566 -Once this is verified working, it's also a good idea to add it to the default
567 -runlevel so it starts at boot:
568 +In the following example, <e>PGDATA</e> states that the configuration files are
569 +to be located in <path>/etc/postgresql-9.0/</path>. <e>DATA_DIR</e> states that
570 +the database cluster should be installed to
571 +<path>/var/lib/postgresql/9.0/data/</path>, which is the default. If you decide
572 +to stray from the default, bear in mind that it is a <b>very good idea</b> to
573 +keep the major version in the path. <e>PG_INITDB_OPTS</e> states that the
574 +default locale should be <e>en_US.UTF-8</e>. That is, U.S. English ordering and
575 +formatting, and UTF-8 character encoding.
576 </p>
577
578 -<pre caption="Adding to the default runlevel">
579 -# <i>rc-update add postgresql default</i>
580 -* postgresql added to runlevel default
581 +<pre caption="Example contents of /etc/conf.d/postgresql-8.4">
582 +<comment># Location of configuration files</comment>
583 +PGDATA="/etc/postgresql-9.0/"
584 +
585 +<comment># Where the data directory is located/to be created</comment>
586 +DATA_DIR="/var/lib/postgresql/9.0/data"
587 +
588 +<comment># Additional options to pass to initdb.
589 +# See 'man initdb' for available options.</comment>
590 +PG_INITDB_OPTS="--locale=en_US.UTF-8"
591 </pre>
592
593 +<note>
594 +This only determines the default locale and character encoding. You can specify
595 +different locales and/or character encodings at database creation time
596 +(<c>CREATE DATABASE</c>) in the same database cluster.
597 +</note>
598 +
599 <p>
600 -Now that the service has started, it's time to try setting up a test database.
601 -To start out, let's create a test database by using the <c>createdb</c>
602 -command. We'll also pass along the <c>-U</c> option to set the user (it
603 -defaults to the current user name if you don't), and the <c>-W</c> option to
604 -request the password we created earlier. Finally we give it the name of the
605 -database we want to create:
606 +There are six locale options that can be set to override <e>--locale=</e>. The
607 +following table lists the six options that, if used, are to be formatted as:
608 +<c>--option=lo_LO.ENCODING</c>.
609 </p>
610
611 -<pre caption="Creating a database with createdb">
612 -$ <i>createdb -U postgres -W test</i>
613 -Password:
614 -CREATE DATABASE
615 +<table>
616 + <tr>
617 + <th>Option</th>
618 + <th>Effects</th>
619 + </tr>
620 + <tr>
621 + <ti>lc-collate</ti>
622 + <ti>String sort order</ti>
623 + </tr>
624 + <tr>
625 + <ti>lc-ctype</ti>
626 + <ti>
627 + Character classification (What is a letter? Its upper-case equivalent?)
628 + </ti>
629 + </tr>
630 + <tr>
631 + <ti>lc-messages</ti>
632 + <ti>Language of messages</ti>
633 + </tr>
634 + <tr>
635 + <ti>lc-monetary</ti>
636 + <ti>Formatting of currency amounts</ti>
637 + </tr>
638 + <tr>
639 + <ti>lc-numeric</ti>
640 + <ti>Formatting of numbers</ti>
641 + </tr>
642 + <tr>
643 + <ti>lc-time</ti>
644 + <ti>Formatting of dates and times</ti>
645 + </tr>
646 +</table>
647 +
648 +<p>
649 +So, if you would like the default to be English, but you want messages in, say,
650 +Swedish, then your <e>PG_INITDB_OPTS</e> would look like so:
651 +</p>
652 +
653 +<pre caption="Example">
654 +PG_INITDB_OPTS="--locale=en_US.UTF-8 --lc-messages=sv_SE.UTF-8"
655 </pre>
656
657 <p>
658 -The database was successfully created, and we can confirm that the database can
659 -run basic tasks. We'll go ahead and drop this database (remove it) with the
660 -<c>dropdb</c> command:
661 +A complete list of language and character encodings supported by the server can
662 +be found in the documentation, but your system must also support the respective
663 +languages and character encodings. Compare the output of <c>locale -a</c> to the
664 +<uri
665 +link="http://www.postgresql.org/docs/current/static/multibyte.html">encodings</uri>
666 +in the documentation.
667 </p>
668
669 -<pre caption="Dropping a database with dropdb">
670 -$ <i>dropdb -U postgres -W test</i>
671 -Password:
672 -DROP DATABASE
673 +<p>
674 +You can change your locale and encoding selections at database <uri
675 + link="http://www.postgresql.org/docs/current/static/sql-createdatabase.html">creation
676 + time.</uri> In order to change the locale for a database after you have
677 +created it, you must drop the database and start over again.
678 +</p>
679 +
680 +<pre caption="Finalize the installation">
681 +# <i>emerge --config dev-db/postgresql-server:9.0</i>
682 </pre>
683
684 <p>
685 -Right now, only the postgres user can run commands. Obviously this is not the
686 -sort of setup one would like in a multi-user environment. The next section will
687 -look at working with user accounts.
688 +This will create the database cluster and store all the related server files
689 +into <e>PGDATA</e> and <e>DATA_DIR</e>.
690 </p>
691
692 </body>
693 </section>
694 +</chapter>
695 +
696 +<chapter>
697 +<title>Configuration</title>
698 <section>
699 -<title>Setting up database user accounts</title>
700 +<title>Where the Configuration Files are Located</title>
701 <body>
702
703 <p>
704 -As mentioned earlier, having to login as the postgres user is somewhat
705 -undesirable in a mult-user environment. In most cases there will be various
706 -users and services accessing the server, and each have different permission
707 -requirements. So, to handle this, the <c>createuser</c> command can be used.
708 -This command is an alternative to running a few SQL queries, and is a lot more
709 -flexible from an admin standpoint. We'll go ahead and create two users, a
710 -'superuser' that can add other users and administer the db, and a standard user:
711 +This time the focus is upon the files in the <e>PGDATA</e> directory,
712 +<path>/etc/postgresql-9.0</path>, instead with primary focus on the
713 +<path>postgresql.conf</path> and <path>pg_hba.conf</path> files.
714 </p>
715
716 -<pre caption="Setting up the superuser">
717 -<comment>(replace chris with the username you'd like to use)</comment>
718 -$ <i>createuser -a -d -P -E -U postgres -W chris</i>
719 -Enter password for new user:
720 -Enter it again:
721 -Password:
722 -CREATE USER
723 -</pre>
724 +</body>
725 +</section>
726 +<section>
727 +<title>postgresql.conf</title>
728 +<body>
729
730 <p>
731 -There, we've created the superuser. The command line option <c>-a</c> specifies
732 -that this user can add other users. <c>-d</c> means that this user can create
733 -databases. <c>-P</c> let's you enter a password for the user and <c>-E</c> will
734 -encrypt it for security purposes. Now then, we'll test this new user's
735 -permissions out by setting up our standard user:
736 +This is the main configuration file. The line that you may find of immediate
737 +interest is <e>listen_addresses</e>. This variable defines to which addresses
738 +PostgreSQL will bind. By default, only localhost and the Unix socket are
739 +bound. Changing <e>listen_addresses</e> is not enough to enable remote
740 +connections. That will be covered in the next section. The <uri
741 +link="http://www.postgresql.org/docs/current/static/runtime-config.html">official
742 +documentation</uri> is fairly easy to understand and is exhaustive on all the
743 +settings available. It would behoove you to read that in addition to what is
744 +covered here as some things may change.
745 </p>
746
747 -<pre caption="Setting up the standard user">
748 -<comment>(replace chris with the username you've just created)</comment>
749 -$ <i>createuser -A -D -P -E -U chris -W testuser</i>
750 -Enter password for new user:
751 -Enter it again:
752 -Password:
753 -CREATE USER
754 -</pre>
755 +<p>
756 +Of secondary interest is the logging destination. By default, everything is
757 +logged to <path>postmaster.log</path> in the <e>DATA_DIR</e> directory. There is
758 +an entire subsection of <path>postgresql.conf</path> that covers a slew of
759 +options for how, what and where to log. The subsection is marked: ERROR
760 +REPORTING AND LOGGING.
761 +</p>
762
763 <p>
764 -Success! Our new user was created using the previously created superuser. The
765 -<c>-A</c> and <c>-D</c> options do the opposite of <c>-a</c> and <c>-d</c>, and
766 -instead deny the user the ability to create other users and databases. Now that
767 -there are users to work with, the next chapter will look at using the new
768 -database.
769 +Other than <e>listen_addresses</e> and the logging options, the rest of the
770 +defaults in <path>postgresql.conf</path> are reasonable enough to get you going.
771 </p>
772
773 </body>
774 </section>
775 -</chapter>
776 -<chapter>
777 -<title>Using PostgreSQL</title>
778 <section>
779 -<title>Setting up permissions</title>
780 +<title>pg_hba.conf</title>
781 <body>
782
783 <p>
784 -Now there is a user that can create databases and add other users, and the main
785 -postgres user that can do anything. The user created earlier can currently login
786 -to the server, and that's about it. In general, users need to be able to insert
787 -data and retrieve data, and sometimes any other number of tasks. So, for this
788 -new user to be able to do anything, they must be setup with the proper
789 -permissions. This can easily be done by passing the <c>-O</c> parameter to
790 -<c>createdb</c>. We'll start by making a new database, <b>MyDB</b> with our
791 -superuser that will be owned by the previous testuser:
792 +The <path>pg_hba.conf</path> file states who is allowed to connect to the
793 +database server and which authentication method must be used to establish the
794 +connection. Again, the documentation is quite exhaustive on the settings and
795 +what they all mean, but a few things are covered here for clarification.
796 </p>
797
798 -<pre caption="Creating the MyDB database">
799 -$ <i>createdb -O testuser -U chris -W MyDB</i>
800 -Password:
801 -CREATE DATABASE
802 +<pre caption="Default pg_hba.conf">
803 +<comment># TYPE DATABASE USER CIDR-ADDRESS METHOD
804 +
805 +# "local" is for Unix domain socket connections only</comment>
806 +local all all trust
807 +<comment># IPv4 local connections:</comment>
808 +host all all 127.0.0.1/32 trust
809 +<comment># IPv6 local connections:</comment>
810 +host all all ::1/128 trust
811 </pre>
812
813 <p>
814 -Alright, now we have a new MyDB database, and a testuser that can access it.
815 -To test this out, we'll login as the testuser to the new MyDB database. We'll
816 -do this with the <c>psql</c> program. This program is what's used to connect to
817 -the PostgreSQL database from command line. So connect to the new database like
818 -so:
819 +As has been mentioned before, by default the server is secure. Kind of. There is
820 +only one database role that is available for log in by default:
821 +<e>postgres</e>. And, the only way to initiate a connection to the database is
822 +through the <path>/var/run/postgresql/.s.PGSQL.5432</path> Unix socket, which is
823 +owned by the <e>postgres</e> system user and system group, or via localhost. Now
824 +for the "kind of" bit: Any user on the system can make a connection to the
825 +database through the localhost. Even as the <e>postgres</e> database superuser.
826 </p>
827
828 -<pre caption="Logging into the MyDB database as the testuser">
829 -$ <i>psql -U testuser -W MyDB</i>
830 -Password:
831 -Welcome to psql 8.0.4, the PostgreSQL interactive terminal.
832 +<p>
833 +To make a connection through the Unix socket, however, the users &mdash;
834 +including the users for other services such as <e>apache</e> &mdash; must be in
835 +the <e>postgres</e> system group. Use <c>gpasswd -a <e>user</e> postgres</c> to
836 +add <e>user</e> to the <e>postgres</e> group. Users not in the <e>postgres</e>
837 +group will be rejected with "Permission denied".
838 +</p>
839 +
840 +<warn>
841 +Never disable the Unix socket entirely. The initscripts require access to it in
842 +order to operate properly. The method can be changed freely.
843 +</warn>
844 +
845 +<p>
846 +The <e>trust</e> method is what allows any user to log on as any user without a
847 +password. It specifies just what it implies: Trust all connections for the given
848 +type to the given database from the given database user (but not the system
849 +user) from the given location without a password. This is what allows any user
850 +on the system to log on as any user through the localhost connection from the
851 +get go. This is not as dangerous as it seems, but does pose a serious security
852 +risk in most circumstances.
853 +</p>
854
855 -Type: \copyright for distribution terms
856 - \h for help with SQL commands
857 - \? for help with psql commands
858 - \g or terminate with semicolon to execute query
859 - \q to quit
860 +<p>
861 +The two methods you will most likely use are: <e>password</e> and
862 +<e>md5</e>. The password method only specifies that a password is required to
863 +start the connection and the password is sent "in-the-clear". This method is
864 +fine when such information will never leave the machine, such as connecting via
865 +the Unix socket or localhost. The md5 method is like password, but protects the
866 +password by using an md5 hash. This is what you want to use whenever the
867 +password is going to traverse a network.
868 +</p>
869
870 -MyDB=&gt;
871 +<p>
872 +At this point, this author would like to bring your attention to the last two
873 +lines, four lines including comments, of the <path>pg_hba.conf</path>
874 +file. PostgreSQL has native support for IPv6 regardless of your desires for such
875 +support. Additionally, IPv4 addresses are automatically mapped to IPv6
876 +addresses, <e>i.e.</e>, 127.0.0.1 will be mapped to ::FFFF:127.0.0.1 and as
877 +"pure" IPv6 ::FFFF:7F00:0001.
878 +</p>
879 +
880 +<p>
881 +There seems to be some misunderstanding, though, as to how host names are mapped
882 +to IP addresses. Let us take a look at the <path>/etc/hosts</path> file.
883 +</p>
884 +
885 +<pre caption="Example /etc/hosts">
886 +<comment># IPv4 and IPv6 localhost aliases</comment>
887 +127.0.0.1 localhost
888 +::1 localhost
889 </pre>
890
891 <p>
892 -So, the testuser is now logged into the database, and can begin to initiate
893 -some commands. To get a feel for using PostgreSQL, the next section will take a
894 -look at some of the basic commands in navigating the <c>psql</c> client.
895 +From the example above you can see that both an IPv4 and an IPv6 IP address are
896 +mapped to localhost. When <c>psql</c> refers to this file, it will grab the
897 +first match and use that as the address; in this case 127.0.0.1. When PostgreSQL
898 +parses this, it will match the IPv6 formatted address as well,
899 +e.g. ::ffff:127.0.0.1. If, however, the IPv6 address appears first, then
900 +<c>psql</c> will map to ::1 alone; ::1 is not the same as ::ffff:127.0.0.1. As
901 +such, if you do not have ::1 as a permitted means of access, <c>psql</c> will
902 +not be able to establish a connection. Furthermore, your kernel needs to support
903 +the IPv6 protocol.
904 +</p>
905 +
906 +<p>
907 +So, it is better to specify IP addresses alone to <c>psql</c> and in
908 +<path>pg_hba.conf</path> rather than to rely on <path>/etc/hosts</path> to be
909 +ordered properly, and it removes any doubt as to which IP addresses are allowed
910 +or to which server you will connect.
911 </p>
912
913 </body>
914 </section>
915 +</chapter>
916 +
917 +<chapter>
918 +<title>Starting the Server</title>
919 <section>
920 -<title>Basic PostgreSQL commands and creating a table</title>
921 +<title>Give It a Go!</title>
922 <body>
923
924 <p>
925 -For those who are used to MySQL, this is somewhat of a definite read. This is
926 -where PostgreSQL may get somewhat unique with regards to running commands. To
927 -start, here is a list of some commands that will be discussed:
928 -</p>
929 -
930 -<table>
931 -<tr>
932 - <th>Command</th>
933 - <th>Usage</th>
934 - <th>MySQL Equivalent</th>
935 -</tr>
936 -<tr>
937 - <ti>\c[onnect] [DBNAME|- [USER]]</ti>
938 - <ti>Connects to another database</ti>
939 - <ti>USE DATABASE</ti>
940 -</tr>
941 -<tr>
942 - <ti>\q</ti>
943 - <ti>Quit the <c>psql</c> client</ti>
944 - <ti>quit</ti>
945 -</tr>
946 -<tr>
947 - <ti>\i FILE</ti>
948 - <ti>Run commands from <c>FILE</c></ti>
949 - <ti>source FILE</ti>
950 -</tr>
951 -<tr>
952 - <ti>\o [FILE]</ti>
953 - <ti>Send query results to <c>FILE</c></ti>
954 - <ti>INTO OUTFILE, but outputs everything (not just SELECTS)</ti>
955 -</tr>
956 -<tr>
957 - <ti>\d [NAME]</ti>
958 - <ti>Describe a database or table (as well as other items)</ti>
959 - <ti>DESC(RIBE)</ti>
960 -</tr>
961 -<tr>
962 - <ti>\db [PATTERN]</ti>
963 - <ti>
964 - List available tables that match <c>PATTERN</c> (all if no pattern
965 - is given)
966 - </ti>
967 - <ti>SHOW TABLES</ti>
968 -</tr>
969 -</table>
970 +Now start PostgreSQL and set the password for the database superuser
971 +<e>postgres</e>. The commands are to be performed as 'root' in the following
972 +code listing:
973 +</p>
974 +
975 +<pre caption="Starting the Server">
976 +<comment>(Change 'trust' to 'password' for the localhost connections.)</comment>
977 +# <i>nano -w /etc/postgresql-9.0/pg_hba.conf</i>
978 +# <i>/etc/init.d/postgresql-9.0 start</i>
979 +postgresql-9.0 | * Starting PostgreSQL ... [ ok ]
980 +
981 +<comment>(Open a connection to the server and set the password.)</comment>
982 +# <i>psql -U postgres</i>
983 +psql (9.0.3)
984 +Type "help" for help.
985
986 -<p>
987 -With the exception of <c>\c[onnect]</c>, all the commands shown will be used
988 -later on in the section. So right now the database is empty. That said, we need
989 -to insert some data. The first step to inserting data, however, is to put it in
990 -a table. Right now there are no tables in the database, so we need to create
991 -one. This is done with the <c>CREATE TABLE</c> command. We'll make a table of
992 -items. They will contain a Product ID, Description, and price:
993 -</p>
994 +postgres=# <i>\password</i>
995 +Enter new password:
996 +Enter it again:
997 +postgres=# <i>\q</i>
998
999 -<pre caption="Creating the products table">
1000 -MyDB=> CREATE TABLE products (
1001 -MyDB(&gt; product_id SERIAL,
1002 -MyDB(&gt; description TEXT,
1003 -MyDB(&gt; price DECIMAL
1004 -MyDB(&gt; );
1005 -NOTICE: CREATE TABLE will create implicit sequence "products_product_id_seq"
1006 -for serial column "products.product_id"
1007 -CREATE TABLE
1008 +<comment>(Change 'trust' to 'password' for the local connection.)</comment>
1009 +# <i>nano -w /etc/postgresql-9.0/pg_hba.conf</i>
1010 +# <i>/etc/init.d/postgresql-9.0 reload</i>
1011 +postgresql-9.0 | * Reloading PostgreSQL configuration ... [ ok ]
1012 +# <i>rc-update add postgresql-9.0 default</i>
1013 + * service postgresql-9.0 added to runlevel default
1014 </pre>
1015
1016 <p>
1017 -You can ignore the NOTICE, it's perfectly harmless. Looking at the last line of
1018 -the function, <c>CREATE TABLE</c> seems to indicate that the command has
1019 -succeeded. However, let's go ahead and verify that the table was indeed
1020 -successfully created with the <c>\d</c> command:
1021 +At this point you are ready to continue on with the official <uri
1022 +link="http://www.postgresql.org/docs/current/static/tutorial.html">PostgreSQL
1023 +Tutorial</uri>. The tutorial will guide you through creating roles, databases,
1024 +schemata and all that fun and useful stuff.
1025 </p>
1026
1027 -<pre caption="Looking at the newly created table">
1028 -MyDB=&gt; <i>\d products</i>
1029 - Table "public.products"
1030 - Column | Type | Modifiers
1031 --------------+---------+------------------------------------------------------------------
1032 - product_id | integer | not null default nextval('public.products_product_id_seq'::text)
1033 - description | text |
1034 - price | numeric |
1035 -</pre>
1036 +</body>
1037 +</section>
1038 +</chapter>
1039 +
1040 +<chapter id="migrating">
1041 +<title>Migrating PostgreSQL</title>
1042 +<section>
1043 +<title>When You Need to Migrate</title>
1044 +<body>
1045
1046 <p>
1047 -Indeed the table was successfully created. Now that the table is created, it
1048 -needs to be populated with data. The next section will look at populating the
1049 -database with data.
1050 +There are only two reasons you would need to perform a migration: When moving
1051 +from one major version to another, <e>e.g.</e>, from PostgreSQL 8.4.7 to 9.0.3,
1052 +but not from 9.0.2 to 9.0.3; or when switching from the deprecated
1053 +floating-point timestamp format to the new 64-bit integer timestamp format.
1054 </p>
1055
1056 +<note>
1057 +You will need to migrate your database when you move from the obsolete ebuilds
1058 +&mdash; dev-db/libpq, dev-db/postgresql, dev-db/postgresql-libs, and
1059 +dev-db/postgresql-client &mdash; to the new ebuilds &mdash;
1060 +dev-db/postgresql-docs, dev-db/postgresql-base and dev-db/postgresql-server.
1061 +</note>
1062 +
1063 </body>
1064 </section>
1065 -<section>
1066 -<title>Inserting data into the database</title>
1067 +<section id="post90">
1068 +<title>Post-9.0 Migration</title>
1069 <body>
1070
1071 <p>
1072 -This section will look at the two ways of populating the newly created table
1073 -with data. First let's look at the most basic command, <c>INSERT</c>:
1074 +<e>pg_upgrade</e>, a new utility that comes along with 9.0 and later, simplifies
1075 +the migration process rather drastically.
1076 </p>
1077
1078 -<pre caption="INSERT syntax">
1079 -INSERT INTO [tablename] (column1,column2,column3) VALUES(value1,value2,value3)
1080 +<p>
1081 +However, there are two caveats with using pg_upgrade. Firstly, it does not
1082 +support configuration files being in a different directory than where the data
1083 +is stored. This can be resolved by using symbolic links. Lastly, you can only
1084 +use it to migrate from a database from 8.3 or newer. If you have an older
1085 +database you will need to follow the <uri link="#pre90">Pre-9.0 Migration</uri>
1086 +instructions.
1087 +</p>
1088 +
1089 +<pre caption="Migrating with pg_upgrade">
1090 +<comment>(Stop the servers you're going to migrate from and to.)</comment>
1091 +# <i>/etc/init.d/postgresql-8.4 stop</i>
1092 +# <i>/etc/init.d/postgresql-9.0 stop</i>
1093 +# <i>ln -s /etc/postgresql-8.4/*.conf /var/lib/postgresql/8.4/data/</i>
1094 +# <i>ln -s /etc/postgresql-9.0/*.conf /var/lib/postgresql/9.0/data/</i>
1095 +
1096 +<comment>(Change the method of database user 'postgres' to trust on local
1097 +connections on all databases.)</comment>
1098 +# <i>nano -w /etc/postgresql-8.4/pg_hba.conf</i>
1099 +# <i>nano -w /etc/postgresql-9.0/pg_hba.conf</i>
1100 +
1101 +<comment>You may need to change the permissions of '/var/lib/postgresql/' before
1102 +you perform the next step.</comment>
1103 +# <i>su - postgres</i>
1104 +$ <i>pg_upgrade -u postgres \
1105 + -d /var/lib/postgresql/8.4/data -D /var/lib/postgresql/9.0/data \
1106 + -b /usr/lib/postgresql-8.4/bin -B /usr/lib/postgresql-9.0/bin</i>
1107 +<comment>(Perform the tasks pg_upgrade tells you to do , if any.)</comment>
1108 +$ <i>logout</i>
1109 +
1110 +<comment>(Remove the symbolic links we created earlier.)</comment>
1111 +# <i>rm /var/lib/postgresql/8.4/data/*.conf</i>
1112 +# <i>rm /var/lib/postgresql/9.0/data/*.conf</i>
1113 +# <i>/etc/init.d/postgresql-9.0 start</i>
1114 </pre>
1115
1116 +</body>
1117 +</section>
1118 +<section id="pre90">
1119 +<title>Pre-9.0 Migration: With the New Ebuilds</title>
1120 +<body>
1121 +
1122 <p>
1123 -<c>tablename</c> contains the name of the table to insert the data into.
1124 -(column1,column2,column3) lets you specify the specific columns to insert the
1125 -values into. VALUES(value1,value2,value3) is the listing of values. The values
1126 -are inserted into the same order as the columns (column1 gets value1, column2
1127 -gets value2, column3 gets value3). These counts <e>must</e> be the same. So
1128 -let's go ahead and insert an item into the table:
1129 +Because the new ebuilds feature a more advanced slotting method than the
1130 +previous ones, the downtime is quite minimal, most likely minutes rather than
1131 +hours.
1132 </p>
1133
1134 -<impo>
1135 -From working with databases for a long time, I personally recommend specifying
1136 -<c>INSERT</c> statements exactly as above. Developers often make the mistake of
1137 -using <c>INSERT INTO</c> without specifying columns. This is unproductive, as
1138 -if a new column gets added to the database, it will cause in error if the value
1139 -to column count is not the same. You should <e>always</e> specify the columns
1140 -unless you're 300% sure you'll never add a column.
1141 -</impo>
1142 -
1143 -<pre caption="Inserting data into the table">
1144 -MyDB=&gt; <i>INSERT INTO products (description,price) VALUES('A test product', 12.00);</i>
1145 -INSERT 17273 1
1146 -</pre>
1147 +<p>
1148 +In the following examples, it is assumed that you are using the default
1149 +locations and port settings, and that you are migrating from 8.3 to 8.4. Adjust
1150 +accordingly if you have deviated from the default.
1151 +</p>
1152
1153 <p>
1154 -The last line needs a bit of explaining. The return of an insert command is an
1155 -OID (Object Identifier) and the number of rows inserted. OID's are a bit beyond
1156 -the scope of this guide, and the <uri
1157 -link="http://www.postgresql.org/docs/8.1/static/datatype-oid.html">PostgreSQL
1158 -manual</uri> has some good information on it. Now, for a situation where you
1159 -have 20,000 products, these insert statements can be a little tedious. However,
1160 -not all is lost. The <c>COPY</c> command can be used to insert data into a
1161 -table from a file or stdin. In this example, let's assume that you have a csv
1162 -(comma separated values) file, which contains the product id, description, and
1163 -price. The file looks like this:
1164 +If you have not already done so, follow the <uri
1165 +link="#installation">installation instructions</uri> before starting the
1166 +migration. Such a compile may hamper performance on the database server but it
1167 +can keep going.
1168 </p>
1169
1170 -<pre caption="products.csv">
1171 -2,meat,6.79
1172 -3,soup,0.69
1173 -4,soda,1.79
1174 -</pre>
1175 +<p>
1176 +A couple of files need to be tweaked before beginning the migration. Edit
1177 +<e>PGPORT</e> in the <path>/etc/conf.d/postgresql-8.4</path> configuration file
1178 +to 6543. (Any port number other than what your old installation is bound to will
1179 +do.)
1180 +</p>
1181
1182 <p>
1183 -Now we'll use the <c>COPY</c> command to populate our data:
1184 +Next, edit <path>/etc/postgresql-8.3/pg_hba.conf</path> so that only the
1185 +database superuser <e>postgres</e> can access the database cluster via the Unix
1186 +socket.
1187 </p>
1188
1189 -<impo>
1190 -The <c>COPY FROM STDIN</c> command is used because only the postgres user can
1191 -insert data from a file (for obvious security reasons).
1192 -</impo>
1193 +<pre caption="Migrate with the New Ebuilds">
1194 +# <i>cp -p /etc/postgresql-8.3/pg_hba.conf /etc/postgresql-8.4/</i>
1195
1196 -<pre caption="Using COPY to populate the products table">
1197 -MyDB=&gt; <i>COPY products FROM STDIN WITH DELIMITER AS ',';</i>
1198 -Enter data to be copied followed by a newline.
1199 -End with a backslash and a period on a line by itself.
1200 ->> <i>2,meat,6.79</i>
1201 ->> <i>3,soup,0.69</i>
1202 ->> <i>4,soda,1.79</i>
1203 ->> <i>\.</i>
1204 +<comment>(The following should be safe. Read the documentation to be sure.)</comment>
1205 +# <i> cp -p /etc/postgresql-8.3/postgresql.conf /etc/postgresql-8.4/</i>
1206 +<comment>
1207 +(Don't forget to copy over any other configuration files that you may need.)
1208 +</comment>
1209 +# <i>/etc/init.d/postgresql-8.3 reload</i>
1210 +# <i>/etc/init.d/postgresql-8.4 start</i>
1211 +
1212 +<comment>(Begin piping the data from the old cluster to the new cluster.)</comment>
1213 +# <i>pg_dumpall -U postgres -p 5432 | psql -U postgres -d postgres -p 6543</i>
1214 +# <i>/etc/init.d/postgresql-8.3 stop</i>
1215 +# <i>/etc/init.d/postgresql-8.4 stop</i>
1216 +
1217 +<comment>(Edit PGPORT back to 5432.)</comment>
1218 +# <i>nano -w /etc/conf.d/postgresql-8.4</i>
1219 +
1220 +<comment>(Allow users access once more.)</comment>
1221 +# <i>nano -w /etc/postgresql-8.4/pg_hba.conf</i>
1222 +# <i>/etc/init.d/postgresql-8.4 start</i>
1223 +# <i>rc-update del postgresql-8.3 &amp;&amp; rc-update add postgresql-8.4 default</i>
1224 </pre>
1225
1226 <p>
1227 -Unfortunately, this line doesn't return the same status information as the
1228 -<c>INSERT INTO</c> statement. How do we know the data was inserted? The next
1229 -section will look at running queries to check our data.
1230 +Hopefully everything went according to plan and you have a successfully updated
1231 +server that contains precisely the same data, bit for bit, as the old server.
1232 </p>
1233
1234 </body>
1235 </section>
1236 -<section>
1237 -<title>Using PostgreSQL queries</title>
1238 +<section id="oldmigration">
1239 +<title>Pre-9.0 Migration: From the Obsolete Ebuilds</title>
1240 <body>
1241
1242 <p>
1243 -This section will look at using the <c>SELECT</c> statement to view data in our
1244 -tables. The basic <c>SELECT</c> format looks like this:
1245 +You will need to schedule some downtime for your server. The old ebuilds
1246 +<b>cannot</b> be installed at the same time as the new ebuilds. As such, assume
1247 +that the server will have to be down for a few hours. Maybe for the weekend,
1248 +even.
1249 </p>
1250
1251 -<pre caption="SELECT syntax">
1252 -SELECT (column1,column2|*) FROM (table) [WHERE (conditionals)]
1253 -</pre>
1254 -
1255 <p>
1256 -There are two ways to select columns. The first is using <c>*</c> to select all
1257 -columns, and the second is to specify a list of specific columns you wish to
1258 -see. The second is quite handy when you want to find a specific column in a
1259 -rather large list of them. Let's start out with using <c>SELECT</c> with
1260 -<c>*</c> to specify all columns:
1261 +Before starting, you will need to deny access to the server, so that no changes
1262 +are made. You may also want to backup your <path>postgresql.conf</path> and
1263 +<path>pg_hba.conf</path> and any other configuration file that you deem
1264 +important.
1265 </p>
1266
1267 -<pre caption="Viewing the products table">
1268 -MyDB=&gt; <i>SELECT * FROM products;</i>
1269 - product_id | description | price
1270 -------------+----------------+-------
1271 - 1 | A test product | 12.00
1272 - 2 | meat | 6.79
1273 - 3 | soup | 0.69
1274 - 4 | soda | 1.79
1275 -(4 rows)
1276 +<pre caption="Steps to Migrate from the Obsolete Ebuilds">
1277 +# <i>pg_dumpall -U postgres > backup_file</i>
1278 +# <i>/etc/init.d/postgresql stop</i>
1279 +# <i>emerge -C dev-db/postgresql dev-db/libpq dev-db/postgresql-client \
1280 + dev-db/postgresql-client</i>
1281 +<comment>
1282 +(Follow the steps detailed in this article for installing and configuring the
1283 +server.)
1284 +</comment>
1285 +# <i>/etc/init.d/postgresql-8.4 start</i>
1286 +# <i>psql -f backup_file postgres</i>
1287 </pre>
1288
1289 <p>
1290 -As shown here, all the data we inserted earlier is indeed in the table. Now
1291 -let's say we only want to see the description and the price, and don't care
1292 -about the product id. In this case we'll use the column specific SELECT form:
1293 +You may break some packages that were built against those packages, but once you
1294 +have installed dev-db/postgresql-base and/or dev-db/postgresql-server you can
1295 +run <c>revdep-rebuild</c> to reemerge any packages that may have been broken.
1296 </p>
1297
1298 -<pre caption="Viewing specific columns from the products table">
1299 -MyDB=&gt; <i>SELECT description,price FROM products;</i>
1300 - description | price
1301 -----------------+-------
1302 - A test product | 12.00
1303 - meat | 6.79
1304 - soup | 0.69
1305 - soda | 1.79
1306 -(4 rows)
1307 -</pre>
1308 -
1309 -<p>
1310 -Now only the product and price is shown, letting us focus on only the important
1311 -data. Now let's say that we want to see only the items that are greater than
1312 -$2.00. Here's where the <c>WHERE</c> clause comes in handy:
1313 -</p>
1314 +</body>
1315 +</section>
1316 +</chapter>
1317
1318 -<pre caption="Viewing specific rows from the products table">
1319 -MyDB=&gt; <i>SELECT description,price FROM products WHERE price > 2.00;</i>
1320 - description | price
1321 -----------------+-------
1322 - A test product | 12.00
1323 - meat | 6.79
1324 -(2 rows)
1325 -</pre>
1326 +<chapter>
1327 +<title>Utilities</title>
1328 +<section>
1329 +<title>pgAdmin III</title>
1330 +<body>
1331
1332 <p>
1333 -Now a listing of products over $2.00 is displayed, focusing the data even more.
1334 -These forms of querying for information are very powerful, and can help create
1335 -extremely useful reports.
1336 +<uri link="http://www.pgadmin.org/">pgAdmin III</uri> is a graphical utility
1337 +for managing PostgreSQL.
1338 </p>
1339
1340 </body>
1341 </section>
1342 +</chapter>
1343 +
1344 +<chapter>
1345 +<title>Troubleshooting</title>
1346 <section>
1347 -<title>Conclusion</title>
1348 +<title>Server Lacks Instrumentation Functions</title>
1349 <body>
1350
1351 <p>
1352 -This concludes the PostgreSQL Guide. A big thanks goes to Masatomo Nakano, the
1353 -previous Gentoo PostgreSQL maintainer for his help in answering my questions.
1354 -Any suggestions on this guide should be sent to
1355 -<mail>chriswhite@g.o</mail>. For more extensive documentation, see the
1356 -<uri link="http://www.postgresql.org">PostgreSQL website</uri>.
1357 +This problem is easy to solve. What is difficult about it is finding the
1358 +answer. What is required is an import from a file that already exists on the
1359 +storage drive: <path>adminpack.sql</path>. To resolve this issue, run this
1360 +command:
1361 </p>
1362
1363 +<pre caption="Command to Add Instrumentation Functions">
1364 +# <i>psql -U postgres --file /usr/share/postgresql-9.0/contrib/adminpack.sql</i>
1365 +</pre>
1366 +
1367 </body>
1368 </section>
1369 </chapter>
Report Message
Find on MARC Find on Google Groups