Add pg_upgrade to /contrib; will be in 9.0 beta2.

Add documentation.

Supports migration from PG 8.3 and 8.4.

3 files changed, 445 insertions, 2 deletions

diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index 3ed2023b1b5..645783a1a63 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/contrib.sgml,v 1.16 2010/01/28 23:59:52 adunstan Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/contrib.sgml,v 1.17 2010/05/12 02:19:11 momjian Exp $ -->
 
 <appendix id="contrib">
  <title>Additional Supplied Modules</title>
@@ -110,6 +110,7 @@ psql -d dbname -f <replaceable>SHAREDIR</>/contrib/<replaceable>module</>.sql
  &pgstatstatements;
  &pgstattuple;
  &pgtrgm;
+ &pgupgrade;
  &seg;
  &contrib-spi;
  &sslinfo;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 57f3af2c13d..dc2044b77cf 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/filelist.sgml,v 1.67 2010/02/22 11:47:30 heikki Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/filelist.sgml,v 1.68 2010/05/12 02:19:11 momjian Exp $ -->
 
 <!entity history    SYSTEM "history.sgml">
 <!entity info       SYSTEM "info.sgml">
@@ -122,6 +122,7 @@
 <!entity pgstatstatements SYSTEM "pgstatstatements.sgml">
 <!entity pgstattuple     SYSTEM "pgstattuple.sgml">
 <!entity pgtrgm          SYSTEM "pgtrgm.sgml">
+<!entity pgupgrade       SYSTEM "pgupgrade.sgml">
 <!entity seg             SYSTEM "seg.sgml">
 <!entity contrib-spi     SYSTEM "contrib-spi.sgml">
 <!entity sslinfo         SYSTEM "sslinfo.sgml">
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 00000000000..01919c64470
--- /dev/null
+++ b/doc/src/sgml/pgupgrade.sgml
@@ -0,0 +1,441 @@
+<!-- $PostgreSQL: pgsql/doc/src/sgml/pgupgrade.sgml,v 1.1 2010/05/12 02:19:11 momjian Exp $ -->
+
+<sect1 id="pgupgrade">
+ <title>pg_upgrade</title>
+
+ <indexterm zone="pgupgrade">
+  <primary>pg_upgrade</primary>
+ </indexterm>
+
+ <para>
+  <application>pg_upgrade</> (formerly called pg_migrator) allows data
+  stored in Postgres data files to be migrated to a later Postgres
+  major version without the data dump/reload typically required for
+  major version upgrades, e.g. from 8.4.7 to the current major release
+  of Postgres.  It is not required for minor version upgrades, e.g.
+  9.0.1 -> 9.0.4.
+ </para>
+
+ <sect2>
+  <title>Supported Versions</title>
+
+  <para>
+   pg_upgrade supports upgrades from 8.3.X and later to the current
+   major release of Postgres, including snapshot and alpha releases.
+   pg_upgrade also supports upgrades from EnterpriseDB's Postgres Plus
+   Advanced Server.
+  </para>
+
+ </sect2>
+
+ <sect2>
+  <title>Upgrade Steps</title>
+
+  <orderedlist>
+   <listitem>
+    <para>
+     Optionally move the old cluster
+    </para>
+ 
+    <para>
+     If you are using a version-specific PostgreSQL install directory, e.g.
+     /opt/PostgreSQL/8.4, you do not need to move the old cluster. The
+     one-click installers all use version-specific install directories.
+    </para>
+ 
+    <para>    
+     If your PostgreSQL install directory is not version-specific, e.g.
+     /usr/local/pgsql, it is necessary to move the current Postgres install
+     directory so it does not interfere with the new Postgres installation.
+     Once the current Postgres server is shut down, it is safe to rename the
+     Postgres install directory; assuming the old directory is
+     /usr/local/pgsql, you can do:
+ 
+<programlisting>
+mv /usr/local/pgsql /usr/local/pgsql.old
+</programlisting>
+     to rename the directory.
+    </para>
+ 
+    <para>
+     If you are using tablespaces and migrating to 8.4 or earlier, there must
+     be sufficient directory permissions to allow pg_upgrade to rename each
+     tablespace directory to add a ".old" suffix.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     For PostgreSQL source installs, build the new PostgreSQL version
+    </para>
+ 
+    <para>
+     Build the new Postgres source with configure flags that are compatible
+     with the old cluster. pg_upgrade will check pg_controldata to make
+     sure all settings are compatible before starting the upgrade.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Install the new Postgres binaries
+    </para>
+ 
+    <para>
+     Install the new server's binaries and support files. You can use the
+     same port numbers for both clusters, typically 5432, because the old and
+     new clusters will not be running at the same time.
+    </para>
+ 
+    <para>
+     For source installs, if you wish to install the new server in a custom
+     location, use 'prefix':
+ 
+<programlisting>
+gmake prefix=/usr/local/pgsql.new install
+</programlisting>
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Initialize the new PostgreSQL cluster
+    </para>
+ 
+    <para>
+     Initialize the new cluster using initdb. Again, use compatible initdb
+     flags that match the old cluster (pg_upgrade will check that too.) Many
+     prebuilt installers do this step automatically. There is no need to
+     start the new cluster.
+    </para>
+ 
+    <para>
+     If migrating EnterpriseDB's Postgres Plus Advanced Server, you must:
+     <itemizedlist>
+      <listitem>
+       <para>
+        <emphasis>not</> install <literal>sample tables and procedures/functions</>
+        in the new server
+       </para>
+      </listitem>
+      <listitem>
+       <para>
+        delete the empty <literal>edb</> schema in the <literal>enterprisedb</> database
+       </para>
+      </listitem>
+      <listitem>
+       <para>
+        copy dbserver/lib/pgmemcache.so from the old server
+        to the new server (AS8.3 to AS8.3R2 migrations only)
+       </para>
+      </listitem>
+     </itemizedlist>
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Install custom shared object files (or DLLs)
+    </para>
+ 
+    <para>
+     Install any custom shared object files (or DLLs) used by the old cluster
+     into the new cluster, e.g. pgcrypto.so, whether they are from /contrib
+     or some other source. Do not install the schema definitions, e.g.
+     pgcrypto.sql --- these will be migrated from the old cluster.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Adjust authentication
+    </para>
+ 
+    <para>
+     pg_upgrade will connect to the old and new servers several times,
+     so you might want to set authentication to <literal>trust</> in
+     <filename>pg_hba.conf</>, or if using <literal>md5</> authentication,
+     use a <filename>pgpass</> file to avoid being prompted repeatedly
+     for a password.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Stop both servers
+    </para>
+ 
+    <para>
+     Make sure both database servers are stopped using on Unix, e.g.:
+ 
+<programlisting>
+pg_ctl --pgdata /opt/PostgreSQL/8.4 stop
+pg_ctl --pgdata /opt/PostgreSQL/8.5 stop
+</programlisting>
+ 
+     or on Windows
+ 
+<programlisting>
+NET STOP postgresql-8.4
+NET STOP postgresql-9.0
+</programlisting>
+ 
+     or
+ 
+<programlisting>
+NET STOP pgsql-8.3  (different service name)
+</programlisting>
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Run pg_upgrade
+ 
+     Always run the pg_upgrade binary in the new server, not the old one.
+     pg_upgrade requires the specification of the old and new cluster's
+     PGDATA and executable (/bin) directories. You can also specify separate
+     user and port values, and whether you want the data linked instead of
+     copied (the default). If you use linking, the migration will be much
+     faster (no data copying), but you will no longer be able to access your
+     old cluster once you start the new cluster after the upgrade. See
+     pg_upgrade --help for a full list of options.
+    </para>
+ 
+    <para>
+     For Windows users, you must be logged into an administrative account, and
+     then start a shell as the 'postgres' user and set the proper path:
+ 
+<programlisting>
+RUNAS /USER:postgres "CMD.EXE"
+SET PATH=%PATH%;C:\Program Files\PostgreSQL\8.5\bin;
+</programlisting>
+ 
+     and then run pg_upgrade with quoted directories, e.g.:
+ 
+<programlisting>
+pg_upgrade.exe
+        --old-datadir "C:/Program Files/PostgreSQL/8.4/data"
+        --new-datadir "C:/Program Files/PostgreSQL/8.5/data"
+        --old-bindir "C:/Program Files/PostgreSQL/8.4/bin"
+        --new-bindir "C:/Program Files/PostgreSQL/8.5/bin"
+</programlisting>
+ 
+     Once started, pg_upgrade will verify the two clusters are compatible
+     and then do the migration. You can use pg_upgrade <option>--check</>
+     to perform only the checks, even if the old server is still
+     running. pg_upgrade <option>--check</> will also outline any
+     manual adjustments you will need to make after the migration.
+    </para>
+ 
+    <para>
+     Obviously, no one should be accessing the clusters during the migration.
+    </para>
+ 
+    <para>
+     If an error occurs while restoring the database schema, pg_upgrade will
+     exit and you will have to revert to the old cluster as outlined in step
+     #15 below. To try pg_upgrade again, you will need to modify the old
+     cluster so the pg_upgrade schema restore succeeds. If the problem is a
+     /contrib module, you might need to uninstall the /contrib module from
+     the old cluster and install it in the new cluster after the migration,
+     assuming the module is not being used to store user data.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Restore <filename>pg_hba.conf</>
+    </para>
+ 
+    <para>
+     If you modified <filename>pg_hba.conf</> to use <literal>trust</>,
+     restore its original authentication settings.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Post-Migration processing
+    </para>
+ 
+    <para>
+     If any post-migration processing is required, pg_upgrade will issue
+     warnings as it completes. It will also generate script files that must
+     be run by the administrator. The script files will connect to each
+     database that needs post-migration processing. Each script should be
+     run using:
+ 
+<programlisting>
+psql --username postgres --file script.sql postgres
+</programlisting>
+ 
+     The scripts can be run in any order and can be deleted once they have
+     been run.
+    </para>
+ 
+    <para>
+     In general it is unsafe to access tables referenced in rebuild scripts
+     until the rebuild scripts have run to completion; doing so could yield
+     incorrect results or poor performance. Tables not referenced in rebuild
+     scripts can be accessed immediately.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Statistics
+    </para>
+ 
+    <para>
+     Because optimizer statistics are not transferred by pg_upgrade, you will
+     be instructed to run a command to regenerate that information at the end
+     of the migration.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Delete old cluster
+    </para>
+ 
+    <para>
+     Once you are satisfied with the upgrade, you can delete the old
+     cluster's data directories by running the script mentioned when
+     pg_upgrade completes. You will need to manually delete the old install
+     directories, e.g. /bin, /share.
+    </para>
+   </listitem>
+ 
+   <listitem>
+    <para>
+     Reverting to old cluster
+    </para>
+ 
+    <para>
+     If, after running pg_upgrade, you wish to revert to the old cluster,
+     there are several options.
+    </para>
+ 
+    <para>
+     If you ran pg_upgrade with <option>--check</>, no modifications
+     were made to the old cluster and you can re-use it anytime.
+    </para>
+ 
+    <para>
+     If you ran pg_upgrade with <option>--link</>, the data files
+     are shared between the old and new cluster. If you started
+     the new cluster, the new server has written to those shared
+     files and it is unsafe to use the old cluster.
+    </para>
+ 
+    <para>
+     If you ran pg_upgrade <emphasis>without</>_ <option>--link</>
+     or did not start the new server, the old cluster was not
+     modified except that an <literal>.old</> suffix was appended
+     to <filename>$PGDATA/global/pg_control</> and perhaps tablespace
+     directories. To reuse the old cluster, remove the ".old"
+     suffix from <filename>$PGDATA/global/pg_control</>. and, if
+     migrating to 8.4 or earlier, remove the tablespace directories
+     created by the migration and remove the ".old" suffix from
+     the tablespace directory names; then you can restart the old
+     cluster.
+    </para>
+ 
+   </listitem>
+  </orderedlist>
+ 
+  </sect2>
+ 
+  <sect2>
+  <title>Limitations In Migrating <emphasis>from</> PostgreSQL 8.3</title>
+ 
+ 
+  <para>
+   pg_upgrade will not work for a migration from 8.3 if a user column
+   is defined as:
+   <itemizedlist>
+    <listitem>
+     <para>
+      a <type>tsquery</> data type
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      data type <type>name</> and is not the first column
+     </para>
+    </listitem>
+   </itemizedlist>
+  </para>
+
+  <para>
+   You must drop any such columns and migrate them manually.
+  </para>
+ 
+  <para>
+   pg_upgrade will require a table rebuild if:
+   <itemizedlist>
+    <listitem>
+     <para>
+      a user column is of data type tsvector
+     </para>
+    </listitem>
+   </itemizedlist>
+  </para>
+ 
+  <para>
+   pg_upgrade will require a reindex if:
+   <itemizedlist>
+    <listitem>
+     <para>
+      an index is of type hash or gin
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      an index uses <function>bpchar_pattern_ops</>
+     </para>
+    </listitem>
+   </itemizedlist>
+  </para>
+ 
+  <para>
+   Also, the default datetime storage format changed to integer after
+   Postgres 8.3. pg_upgrade will check that the datetime storage format
+   used by the old and new clusters match. Make sure your new cluster is
+   built with the configure flag <option>--disable-integer-datetimes</>.
+  </para>
+ 
+  <para>
+   For Windows users, note that due to different integer datetimes settings
+   used by the one-click installer and the MSI installer, it is only
+   possible to upgrade from version 8.3 of the one-click distribution to
+   version 8.4 of the one-click distribution. It is not possible to upgrade
+   from the MSI installer to the one-click installer.
+  </para>
+ 
+  <para>  
+   All failure, rebuild, and reindex cases will be reported by pg_upgrade
+   if they affect your installation; post-migration scripts to rebuild
+   tables and indexes will be automatically generated. 
+  </para>
+ 
+  <para>
+   For deployment testing, create a schema-only copy of the old cluster,
+   insert dummy data, and migrate that. 
+  </para>
+ 
+  <para>  
+   If you want to use link mode and you don't want your old cluster
+   to be modified when the new cluster is started, make a copy of the
+   old cluster and migrate that with link mode. To make a valid copy
+   of the old cluster, use <application>rsync</> to create a dirty
+   copy of the old cluster while the server is running, then shut down
+   the old server and run rsync again to update the copy with any
+   changes to make it consistent.
+  </para>
+ 
+ </sect2>
+ 
+</sect1>
+