1 <!-- doc/src/sgml/pgupgrade.sgml -->
4 <title>pg_upgrade</title>
6 <indexterm zone="pgupgrade">
7 <primary>pg_upgrade</primary>
11 <application>pg_upgrade</> (formerly called <application>pg_migrator</>) allows data
12 stored in <productname>PostgreSQL</> data files to be migrated to a later <productname>PostgreSQL</>
13 major version without the data dump/reload typically required for
14 major version upgrades, e.g. from 8.4.7 to the current major release
15 of <productname>PostgreSQL</>. It is not required for minor version upgrades, e.g. from
20 <application>pg_upgrade</> works because, though new features are
21 regularly added to PostgreSQL major releases, the internal data storage
22 format rarely changes. <application>pg_upgrade</> does its best to
23 make sure the old and new clusters are binary-compatible, e.g. by
24 checking for compatible compile-time settings. It is important that
25 any external modules are also binary compatible, though this cannot
26 be checked by <application>pg_upgrade</>.
30 <title>Supported Versions</title>
33 pg_upgrade supports upgrades from 8.3.X and later to the current
34 major release of <productname>PostgreSQL</>, including snapshot and alpha releases.
41 <title><application>pg_upgrade</> Options</title>
44 <application>pg_upgrade</application> accepts the following command-line arguments:
49 <term><option>-b</option> <replaceable>old_bindir</></term>
50 <term><option>--old-bindir</option> <replaceable>OLDBINDIR</></term>
51 <listitem><para>specify the old cluster executable directory</para></listitem>
55 <term><option>-B</option> <replaceable>new_bindir</></term>
56 <term><option>--new-bindir</option> <replaceable>NEWBINDIR</></term>
57 <listitem><para>specify the new cluster executable directory</para></listitem>
61 <term><option>-c</option></term>
62 <term><option>--check</option></term>
63 <listitem><para>check clusters only, don't change any data</para></listitem>
67 <term><option>-d</option> <replaceable>old_datadir</></term>
68 <term><option>--old-datadir</option> <replaceable>OLDDATADIR</></term>
69 <listitem><para>specify the old cluster data directory</para></listitem>
73 <term><option>-D</option> <replaceable>new_datadir</></term>
74 <term><option>--new-datadir</option> <replaceable>NEWDATADIR</></term>
75 <listitem><para>specify the new cluster data directory</para></listitem>
79 <term><option>-g</option></term>
80 <term><option>--debug</option></term>
81 <listitem><para>enable debugging</para></listitem>
85 <term><option>-G</option> <replaceable>debug_filename</></term>
86 <term><option>--debugfile</option> <replaceable>DEBUGFILENAME</></term>
87 <listitem><para>output debugging activity to file</para></listitem>
91 <term><option>-k</option></term>
92 <term><option>--link</option></term>
93 <listitem><para>link instead of copying files to new cluster</para></listitem>
97 <term><option>-l</option> <replaceable>log_filename</></term>
98 <term><option>--logfile</option> <replaceable>LOGFILENAME</></term>
99 <listitem><para>log session activity to file</para></listitem>
103 <term><option>-p</option> <replaceable>old_portnum</></term>
104 <term><option>--old-port</option> <replaceable>portnum</></term>
105 <listitem><para>specify the old cluster port number</para></listitem>
109 <term><option>-P</option> <replaceable>new_portnum</></term>
110 <term><option>--new-port</option> <replaceable>portnum</></term>
111 <listitem><para>specify the new cluster port number</para></listitem>
115 <term><option>-u</option> <replaceable>username</></term>
116 <term><option>--user</option> <replaceable>username</></term>
117 <listitem><para>clusters superuser</para></listitem>
121 <term><option>-v</option></term>
122 <term><option>--verbose</option></term>
123 <listitem><para>enable verbose output</para></listitem>
127 <term><option>-V</option></term>
128 <term><option>--version</option></term>
129 <listitem><para>display version information, then exit</para></listitem>
133 <term><option>-?</option></term>
134 <term><option>-h</option></term>
135 <term><option>--help</option></term>
136 <listitem><para>show help, then exit</para></listitem>
145 <title>Upgrade Steps</title>
148 <step performance="optional">
149 <title>Optionally move the old cluster</title>
152 If you are using a version-specific installation directory, e.g.
153 <filename>/opt/PostgreSQL/8.4</>, you do not need to move the old cluster. The
154 one-click installers all use version-specific installation directories.
158 If your installation directory is not version-specific, e.g.
159 <filename>/usr/local/pgsql</>, it is necessary to move the current PostgreSQL install
160 directory so it does not interfere with the new <productname>PostgreSQL</> installation.
161 Once the current <productname>PostgreSQL</> server is shut down, it is safe to rename the
162 PostgreSQL installation directory; assuming the old directory is
163 <filename>/usr/local/pgsql</>, you can do:
166 mv /usr/local/pgsql /usr/local/pgsql.old
168 to rename the directory.
173 <title>For source installs, build the new version</title>
176 Build the new PostgreSQL source with <command>configure</> flags that are compatible
177 with the old cluster. <application>pg_upgrade</> will check <command>pg_controldata</> to make
178 sure all settings are compatible before starting the upgrade.
183 <title>Install the new PostgreSQL binaries</title>
186 Install the new server's binaries and support files. You can use the
187 same port numbers for both clusters, typically 5432, because the old and
188 new clusters will not be running at the same time.
192 For source installs, if you wish to install the new server in a custom
193 location, use the <literal>prefix</literal> variable:
196 gmake prefix=/usr/local/pgsql.new install
202 <title>Install pg_upgrade</title>
205 Install <application>pg_upgrade</> and
206 <application>pg_upgrade_support</> in the new PostgreSQL cluster
211 <title>Initialize the new PostgreSQL cluster</title>
214 Initialize the new cluster using <command>initdb</command>.
215 Again, use compatible <command>initdb</command>
216 flags that match the old cluster. Many
217 prebuilt installers do this step automatically. There is no need to
218 start the new cluster.
223 <title>Install custom shared object files</title>
226 Install any custom shared object files (or DLLs) used by the old cluster
227 into the new cluster, e.g. <filename>pgcrypto.so</filename>, whether they are from <filename>contrib</filename>
228 or some other source. Do not install the schema definitions, e.g.
229 <filename>pgcrypto.sql</>, because these will be migrated from the old cluster.
234 <title>Adjust authentication</title>
237 <command>pg_upgrade</> will connect to the old and new servers several times,
238 so you might want to set authentication to <literal>trust</> in
239 <filename>pg_hba.conf</>, or if using <literal>md5</> authentication,
240 use a <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">)
241 to avoid being prompted repeatedly for a password.
246 <title>Stop both servers</title>
249 Make sure both database servers are stopped using on Unix, e.g.:
252 pg_ctl -D /opt/PostgreSQL/8.4 stop
253 pg_ctl -D /opt/PostgreSQL/9.0 stop
259 NET STOP postgresql-8.4
260 NET STOP postgresql-9.0
266 NET STOP pgsql-8.3 (<productname>PostgreSQL</> 8.3 and older used a different service name)
272 <title>Run <application>pg_upgrade</></title>
275 Always run the <application>pg_upgrade</> binary in the new server, not the old one.
276 <application>pg_upgrade</> requires the specification of the old and new cluster's
277 data and executable (<filename>bin</>) directories. You can also specify separate
278 user and port values, and whether you want the data linked instead of
279 copied (the default). If you use linking, the migration will be much
280 faster (no data copying), but you will no longer be able to access your
281 old cluster once you start the new cluster after the upgrade. See
282 <literal>pg_upgrade --help</> for a full list of options.
286 For Windows users, you must be logged into an administrative account, and
287 then start a shell as the <literal>postgres</> user and set the proper path:
290 RUNAS /USER:postgres "CMD.EXE"
291 SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.0\bin;
294 and then run <application>pg_upgrade</> with quoted directories, e.g.:
298 --old-datadir "C:/Program Files/PostgreSQL/8.4/data"
299 --new-datadir "C:/Program Files/PostgreSQL/9.0/data"
300 --old-bindir "C:/Program Files/PostgreSQL/8.4/bin"
301 --new-bindir "C:/Program Files/PostgreSQL/9.0/bin"
304 Once started, <command>pg_upgrade</> will verify the two clusters are compatible
305 and then do the migration. You can use <command>pg_upgrade --check</>
306 to perform only the checks, even if the old server is still
307 running. <command>pg_upgrade --check</> will also outline any
308 manual adjustments you will need to make after the migration.
312 Obviously, no one should be accessing the clusters during the migration.
316 If an error occurs while restoring the database schema, <command>pg_upgrade</> will
317 exit and you will have to revert to the old cluster as outlined in <xref linkend="pgupgrade-step-revert">
318 below. To try <command>pg_upgrade</command> again, you will need to modify the old
319 cluster so the pg_upgrade schema restore succeeds. If the problem is a
320 contrib module, you might need to uninstall the contrib module from
321 the old cluster and install it in the new cluster after the migration,
322 assuming the module is not being used to store user data.
327 <title>Restore <filename>pg_hba.conf</></title>
330 If you modified <filename>pg_hba.conf</> to use <literal>trust</>,
331 restore its original authentication settings.
336 <title>Post-migration processing</title>
339 If any post-migration processing is required, pg_upgrade will issue
340 warnings as it completes. It will also generate script files that must
341 be run by the administrator. The script files will connect to each
342 database that needs post-migration processing. Each script should be
346 psql --username postgres --file script.sql postgres
349 The scripts can be run in any order and can be deleted once they have
355 In general it is unsafe to access tables referenced in rebuild scripts
356 until the rebuild scripts have run to completion; doing so could yield
357 incorrect results or poor performance. Tables not referenced in rebuild
358 scripts can be accessed immediately.
364 <title>Statistics</title>
367 Because optimizer statistics are not transferred by <command>pg_upgrade</>, you will
368 be instructed to run a command to regenerate that information at the end
374 <title>Delete old cluster</title>
377 Once you are satisfied with the upgrade, you can delete the old
378 cluster's data directories by running the script mentioned when
379 <command>pg_upgrade</command> completes. You can also delete the
380 old installation directories
381 (e.g. <filename>bin</>, <filename>share</>).
385 <step id="pgupgrade-step-revert" performance="optional">
386 <title>Reverting to old cluster</title>
389 If, after running <command>pg_upgrade</command>, you wish to revert to the old cluster,
390 there are several options:
395 If you ran <command>pg_upgrade</command>
396 with <option>--check</>, no modifications were made to the old
397 cluster and you can re-use it anytime.
403 If you ran <command>pg_upgrade</command>
404 with <option>--link</>, the data files are shared between the
405 old and new cluster. If you started the new cluster, the new
406 server has written to those shared files and it is unsafe to
414 ran <command>pg_upgrade</command> <emphasis>without</> <option>--link</>
415 or did not start the new server, the old cluster was not
416 modified except that an <literal>.old</> suffix was appended
417 to <filename>$PGDATA/global/pg_control</> and perhaps
418 tablespace directories. To reuse the old cluster, remove
419 the <filename>.old</> suffix
420 from <filename>$PGDATA/global/pg_control</>. and, if migrating
421 to 8.4 or earlier, remove the tablespace directories created
422 by the migration and remove the <filename>.old</> suffix from
423 the tablespace directory names; then you can restart the old
435 <title>Limitations in Migrating <emphasis>from</> PostgreSQL 8.3</title>
438 Upgrading from PostgreSQL 8.3 has additional restrictions not present
439 when upgrading from later PostgreSQL releases. For example,
440 pg_upgrade will not work for a migration from 8.3 if a user column
445 a <type>tsquery</> data type
450 data type <type>name</> and is not the first column
457 You must drop any such columns and migrate them manually.
461 pg_upgrade will require a table rebuild if:
465 a user column is of data type <type>tsvector</type>
472 pg_upgrade will require a reindex if:
476 an index is of type hash or GIN
481 an index uses <function>bpchar_pattern_ops</>
488 Also, the default datetime storage format changed to integer after
489 <productname>PostgreSQL</> 8.3. pg_upgrade will check that the datetime storage format
490 used by the old and new clusters match. Make sure your new cluster is
491 built with the configure flag <option>--disable-integer-datetimes</>.
495 For Windows users, note that due to different integer datetimes settings
496 used by the one-click installer and the MSI installer, it is only
497 possible to upgrade from version 8.3 of the one-click distribution to
498 version 8.4 or later of the one-click distribution. It is not
499 possible to upgrade from the MSI installer to the one-click installer.
508 <application>pg_upgrade</> does not support migration of databases
509 containing these <type>reg*</> OID-referencing system data types:
510 <type>regproc</>, <type>regprocedure</>, <type>regoper</>,
511 <type>regoperator</>, <type>regclass</>, <type>regconfig</>, and
512 <type>regdictionary</>. (<type>regtype</> can be migrated.)
516 All failure, rebuild, and reindex cases will be reported by
517 <application>pg_upgrade</> if they affect your installation;
518 post-migration scripts to rebuild tables and indexes will be
519 generated automatically.
523 For deployment testing, create a schema-only copy of the old cluster,
524 insert dummy data, and migrate that.
528 If you want to use link mode and you don't want your old cluster
529 to be modified when the new cluster is started, make a copy of the
530 old cluster and migrate that with link mode. To make a valid copy
531 of the old cluster, use <command>rsync</> to create a dirty
532 copy of the old cluster while the server is running, then shut down
533 the old server and run <command>rsync</> again to update the copy with any
534 changes to make it consistent.