aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
authorBruce Momjian <bruce@momjian.us>2010-05-12 02:19:11 +0000
committerBruce Momjian <bruce@momjian.us>2010-05-12 02:19:11 +0000
commitc2e9b2f288185a8569f6391ea250c7eeafa6c14b (patch)
tree408e8eb0c0aacaf177602789c02d7a416bbd59e1 /doc/src
parent28e1742217716076da0700094a369eae5766974c (diff)
downloadpostgresql-c2e9b2f288185a8569f6391ea250c7eeafa6c14b.tar.gz
postgresql-c2e9b2f288185a8569f6391ea250c7eeafa6c14b.zip
Add pg_upgrade to /contrib; will be in 9.0 beta2.
Add documentation. Supports migration from PG 8.3 and 8.4.
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/contrib.sgml3
-rw-r--r--doc/src/sgml/filelist.sgml3
-rw-r--r--doc/src/sgml/pgupgrade.sgml441
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>
+