alink="#0000ff">
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
- <P>Last updated: Sun Jul 11 21:10:08 EDT 2004</P>
+ <P>Last updated: Sun Nov 14 16:32:47 EST 2004</P>
<P>Current maintainer: Bruce Momjian (<A href=
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
directory?<BR>
<A href="#3.10">3.10</A>) Why do I need to do a dump and restore
to upgrade PostgreSQL releases?<BR>
- <A href="#3.11">3.10</A>) What computer hardware should I use?<BR>
+ <A href="#3.11">3.11</A>) What computer hardware should I use?<BR>
<H2 align="center">Operational Questions</H2>
from a function?<BR>
<A href="#4.26">4.26</A>) Why can't I reliably create/drop
temporary tables in PL/PgSQL functions?<BR>
- <A href="#4.27">4.27</A>) What replication options are available?<BR>
- <A href="#4.28">4.28</A>) What encryption options are available?<BR>
+ <A href="#4.28">4.27</A>) What encryption options are available?<BR>
<H2 align="center">Extending PostgreSQL</H2>
<H4><A name="1.4">1.4</A>) What non-Unix ports are available?</H4>
- <P><STRONG>Client</STRONG></P>
-
- <P>It is possible to compile the <I>libpq</I> C library, psql, and
- other interfaces and client applications to run on MS Windows platforms.
- In this case, the client is running on MS Windows, and communicates
- via TCP/IP to a server running on one of our supported Unix
- platforms. A file <I>win32.mak</I> is included in the distribution
- for making a Win32 <I>libpq</I> library and <I>psql</I>. PostgreSQL
- also communicates with <SMALL>ODBC</SMALL> clients.</P>
-
- <P><STRONG>Server</STRONG></P>
-
- <P>The database server can run on Windows NT and Win2k using
- Cygwin, the Cygnus Unix/NT porting library. See
- <I>pgsql/doc/FAQ_MSWIN</I> in the distribution or the MS Windows FAQ
- at <A href="http://www.PostgreSQL.org/docs/faqs/text/FAQ_MSWIN">
- http://www.PostgreSQL.org/docs/faqs/text/FAQ_MSWIN</A>.</P>
-
- <p>A native port to MS Win NT/2000/XP is currently being worked
- on. For more details on the current status of PostgreSQL on Windows see
- <a href="http://techdocs.postgresql.org/guides/Windows">
- http://techdocs.postgresql.org/guides/Windows</a> and
- <a href="http://momjian.postgresql.org/main/writings/pgsql/win32.html">
- http://momjian.postgresql.org/main/writings/pgsql/win32.html</a>.</p>
+ <P>Starting with version 8.0, PostgreSQL now runs natively on
+ Microsoft Windows NT-based operating systems like Win2000, WinXP,
+ and Win2003. A prepackaged installer is available at <a href=
+ "http://pgfoundry.org/projects/pginstaller">
+ http://pgfoundry.org/projects/pginstaller</a>.
<p>There is also a Novell Netware 6 port at
<a href="http://forge.novell.com">http://forge.novell.com</a>.</p>
<H4><A name="1.7">1.7</A>) What is the latest release?</H4>
- <P>The latest release of PostgreSQL is version 7.4.3.</P>
+ <P>The latest release of PostgreSQL is version 7.4.5.</P>
<P>We plan to have major releases every six to eight months.</P>
hosts?</H4>
<P>By default, PostgreSQL only allows connections from the local
- machine using Unix domain sockets. Other machines will not be able
- to connect unless you turn on tcpip_sockets in the postgresql.conf
- <B>and</B> enable host-based authentication by modifying the file
- <I>$PGDATA/pg_hba.conf</I> accordingly. This will allow TCP/IP
- connections.</P>
+ machine using Unix domain sockets or TCP/IP connections. Other
+ machines will not be able to connect unless you modify
+ listen_addresses in the postgresql.conf <B>and</B> enable
+ host-based authentication by modifying the file
+ <I>$PGDATA/pg_hba.conf</I> accordingly.</P>
<H4><A name="3.6">3.6</A>) How do I tune the database engine for
better performance?</H4>
<P>Because PC hardware is mostly compatible, people tend to believe that
all PC hardware is of equal quality. It is not. ECC RAM, SCSI, and
quality motherboards are more reliable and have better performance than
- less expensive hardware. PostgreSQL will run on almost any hardware
- but, if you are building a server where reliability and performance are
- concerns, it is wise to research your hardware options thoroughly. Our
- email lists can be used to discuss hardware options and tradeoffs.</P>
+ less expensive hardware. PostgreSQL will run on almost any hardware,
+ but if reliability and performance are important it is wise to
+ research your hardware options thoroughly. Our email lists can be used
+ to discuss hardware options and tradeoffs.</P>
<HR>
The size of the PostgreSQL database file containing this data can
be estimated as 6.4 MB:</P>
<PRE>
- 36 bytes: each row header (approximate)
+ 32 bytes: each row header (approximate)
24 bytes: one int field and one text field
+ 4 bytes: pointer on page to tuple
----------------------------------------
- 64 bytes per row
+ 60 bytes per row
The data page size in PostgreSQL is 8192 bytes (8 KB), so:
8192 bytes per page
- ------------------- = 128 rows per database page (rounded down)
- 64 bytes per row
+ ------------------- = 136 rows per database page (rounded down)
+ 60 bytes per row
100000 data rows
- -------------------- = 782 database pages (rounded up)
+ -------------------- = 735 database pages (rounded up)
128 rows per page
-782 database pages * 8192 bytes per page = 6,406,144 bytes (6.4 MB)
+735 database pages * 8192 bytes per page = 6,021,120 bytes (6 MB)
</PRE>
<P>Indexes do not require as much overhead, but do contain the data
</UL>
<P>
+ <P>In pre-8.0 releases, indexes often can not be used unless the data
+ types exactly match the index's column types. This is particularly
+ true of int2, int8, and numeric column indexes.</P>
+
<H4><A name="4.9">4.9</A>) How do I see how the query optimizer is
evaluating my query?</H4>
<SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL. This
will cause the query to be reparsed every time.</P>
- <H4><A name="4.27">4.27</A>) What replication options are available?
- </H4>
- <P>There are several master/slave replication options available.
- These allow only the master to make database changes and the slave
- can only do database reads. The bottom of <a
- href="http://gborg.PostgreSQL.org/genpage?replication_research">
- http://gborg.PostgreSQL.org/genpage?replication_research</a> lists
- them. A multi-master replication solution is being worked on at <a
- href="http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php">http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php</a>.</P>
-
- <H4><A name="4.28">4.28</A>) What encryption options are available?
+ <H4><A name="4.27">4.27</A>) What encryption options are available?
</H4>
<UL>
<LI><I>contrib/pgcrypto</I> contains many encryption functions for
projects / postgresql / blobdiff