diff options
author | Bruce Momjian <bruce@momjian.us> | 2010-05-12 02:19:11 +0000 |
---|---|---|
committer | Bruce Momjian <bruce@momjian.us> | 2010-05-12 02:19:11 +0000 |
commit | c2e9b2f288185a8569f6391ea250c7eeafa6c14b (patch) | |
tree | 408e8eb0c0aacaf177602789c02d7a416bbd59e1 /doc/src | |
parent | 28e1742217716076da0700094a369eae5766974c (diff) | |
download | postgresql-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.sgml | 3 | ||||
-rw-r--r-- | doc/src/sgml/filelist.sgml | 3 | ||||
-rw-r--r-- | doc/src/sgml/pgupgrade.sgml | 441 |
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> + |