alink="#0000ff">
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
- <P>Last updated: Thu May 6 10:22:34 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.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.2.</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 release notes mention whether <I>pg_upgrade</I> is available for the
release.</P>
+ <H4><A name="3.11">3.11</A>) What computer hardware should I use?</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 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>
<H2 align="center">Operational Questions</H2>
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>
of the table, with the original <SMALL>OID</SMALL>s, there is no
reason you can't do it:</P>
<PRE>
- CREATE TABLE new_table(old_oid oid, mycol int);
- SELECT old_oid, mycol INTO new FROM old;
- COPY new TO '/tmp/pgtable';
- DELETE FROM new;
- COPY new WITH OIDS FROM '/tmp/pgtable';
+ CREATE TABLE new_table(mycol int);
+ SELECT oid AS old_oid, mycol INTO tmp_table FROM old_table;
+ COPY tmp_table TO '/tmp/pgtable';
+ COPY new_table WITH OIDS FROM '/tmp/pgtable';
+ DROP TABLE tmp_table;
</PRE>
-<!--
- CREATE TABLE new_table (mycol int);
- INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM old_table;
--->
<P>O<SMALL>ID</SMALL>s are stored as 4-byte integers, and will
overflow at 4 billion. No one has reported this ever happening, and
we plan to have the limit removed before anyone does.</P>
<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