alink="#0000ff">
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
- <P>Last updated: Sun Jul 11 23:58:24 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>
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