Debian Codegraph @

doc/src/sgml/ref/pgupgrade.sgml

raw
1 <!--
2 doc/src/sgml/ref/pgupgrade.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="pgupgrade">
7 <indexterm zone="pgupgrade">
8 <primary>pg_upgrade</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle><application>pg_upgrade</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>pg_upgrade</refname>
19 <refpurpose>upgrade a <productname>PostgreSQL</productname> server instance</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <cmdsynopsis>
24 <command>pg_upgrade</command>
25 <arg choice="plain"><option>-b</option></arg>
26 <arg choice="plain"><replaceable>oldbindir</replaceable></arg>
27 <arg choice="opt"><option>-B</option> <replaceable>newbindir</replaceable></arg>
28 <arg choice="plain"><option>-d</option></arg>
29 <arg choice="plain"><replaceable>oldconfigdir</replaceable></arg>
30 <arg choice="plain"><option>-D</option></arg>
31 <arg choice="plain"><replaceable>newconfigdir</replaceable></arg>
32 <arg rep="repeat"><replaceable>option</replaceable></arg>
33 </cmdsynopsis>
34 </refsynopsisdiv>
35
36 <refsect1>
37 <title>Description</title>
38
39 <para>
40 <application>pg_upgrade</application> (formerly called <application>pg_migrator</application>) allows data
41 stored in <productname>PostgreSQL</productname> data files to be upgraded to a later <productname>PostgreSQL</productname>
42 major version without the data dump/restore typically required for
43 major version upgrades, e.g., from 12.14 to 13.10 or from 14.9 to 15.5.
44 It is not required for minor version upgrades, e.g., from 12.7 to 12.8
45 or from 14.1 to 14.5.
46 </para>
47
48 <para>
49 Major PostgreSQL releases regularly add new features that often
50 change the layout of the system tables, but the internal data storage
51 format rarely changes. <application>pg_upgrade</application> uses this fact
52 to perform rapid upgrades by creating new system tables and simply
53 reusing the old user data files. If a future major release ever
54 changes the data storage format in a way that makes the old data
55 format unreadable, <application>pg_upgrade</application> will not be usable
56 for such upgrades. (The community will attempt to avoid such
57 situations.)
58 </para>
59
60 <para>
61 <application>pg_upgrade</application> does its best to
62 make sure the old and new clusters are binary-compatible, e.g., by
63 checking for compatible compile-time settings, including 32/64-bit
64 binaries. It is important that
65 any external modules are also binary compatible, though this cannot
66 be checked by <application>pg_upgrade</application>.
67 </para>
68
69 <para>
70 <application>pg_upgrade</application> supports upgrades from 9.2.X and later to the current
71 major release of <productname>PostgreSQL</productname>, including snapshot and beta releases.
72 </para>
73
74 <warning>
75 <para>
76 Upgrading a cluster causes the destination to execute arbitrary code of the
77 source superusers' choice. Ensure that the source superusers are trusted
78 before upgrading.
79 </para>
80 </warning>
81 </refsect1>
82
83 <refsect1>
84 <title>Options</title>
85
86 <para>
87 <application>pg_upgrade</application> accepts the following command-line arguments:
88
89 <variablelist>
90
91 <varlistentry>
92 <term><option>-b</option> <replaceable>bindir</replaceable></term>
93 <term><option>--old-bindir=</option><replaceable>bindir</replaceable></term>
94 <listitem><para>the old PostgreSQL executable directory;
95 environment variable <envar>PGBINOLD</envar></para></listitem>
96 </varlistentry>
97
98 <varlistentry>
99 <term><option>-B</option> <replaceable>bindir</replaceable></term>
100 <term><option>--new-bindir=</option><replaceable>bindir</replaceable></term>
101 <listitem><para>the new PostgreSQL executable directory;
102 default is the directory where <application>pg_upgrade</application> resides;
103 environment variable <envar>PGBINNEW</envar></para></listitem>
104 </varlistentry>
105
106 <varlistentry>
107 <term><option>-c</option></term>
108 <term><option>--check</option></term>
109 <listitem><para>check clusters only, don't change any data</para></listitem>
110 </varlistentry>
111
112 <varlistentry>
113 <term><option>-d</option> <replaceable>configdir</replaceable></term>
114 <term><option>--old-datadir=</option><replaceable>configdir</replaceable></term>
115 <listitem><para>the old database cluster configuration directory; environment
116 variable <envar>PGDATAOLD</envar></para></listitem>
117 </varlistentry>
118
119 <varlistentry>
120 <term><option>-D</option> <replaceable>configdir</replaceable></term>
121 <term><option>--new-datadir=</option><replaceable>configdir</replaceable></term>
122 <listitem><para>the new database cluster configuration directory; environment
123 variable <envar>PGDATANEW</envar></para></listitem>
124 </varlistentry>
125
126 <varlistentry>
127 <term><option>-j <replaceable class="parameter">njobs</replaceable></option></term>
128 <term><option>--jobs=<replaceable class="parameter">njobs</replaceable></option></term>
129 <listitem><para>number of simultaneous connections and processes/threads to use
130 </para></listitem>
131 </varlistentry>
132
133 <varlistentry>
134 <term><option>-k</option></term>
135 <term><option>--link</option></term>
136 <listitem><para>use hard links instead of copying files to the new
137 cluster</para></listitem>
138 </varlistentry>
139
140 <varlistentry>
141 <term><option>-N</option></term>
142 <term><option>--no-sync</option></term>
143 <listitem>
144 <para>
145 By default, <command>pg_upgrade</command> will wait for all files
146 of the upgraded cluster to be written safely to disk. This option
147 causes <command>pg_upgrade</command> to return without waiting, which
148 is faster, but means that a subsequent operating system crash can leave
149 the data directory corrupt. Generally, this option is
150 useful for testing but should not be used on a production
151 installation.
152 </para>
153 </listitem>
154 </varlistentry>
155
156 <varlistentry>
157 <term><option>-o</option> <replaceable class="parameter">options</replaceable></term>
158 <term><option>--old-options</option> <replaceable class="parameter">options</replaceable></term>
159 <listitem><para>options to be passed directly to the
160 old <command>postgres</command> command; multiple
161 option invocations are appended</para></listitem>
162 </varlistentry>
163
164 <varlistentry>
165 <term><option>-O</option> <replaceable class="parameter">options</replaceable></term>
166 <term><option>--new-options</option> <replaceable class="parameter">options</replaceable></term>
167 <listitem><para>options to be passed directly to the
168 new <command>postgres</command> command; multiple
169 option invocations are appended</para></listitem>
170 </varlistentry>
171
172 <varlistentry>
173 <term><option>-p</option> <replaceable>port</replaceable></term>
174 <term><option>--old-port=</option><replaceable>port</replaceable></term>
175 <listitem><para>the old cluster port number; environment
176 variable <envar>PGPORTOLD</envar></para></listitem>
177 </varlistentry>
178
179 <varlistentry>
180 <term><option>-P</option> <replaceable>port</replaceable></term>
181 <term><option>--new-port=</option><replaceable>port</replaceable></term>
182 <listitem><para>the new cluster port number; environment
183 variable <envar>PGPORTNEW</envar></para></listitem>
184 </varlistentry>
185
186 <varlistentry>
187 <term><option>-r</option></term>
188 <term><option>--retain</option></term>
189 <listitem><para>retain SQL and log files even after successful completion
190 </para></listitem>
191 </varlistentry>
192
193 <varlistentry>
194 <term><option>-s</option> <replaceable>dir</replaceable></term>
195 <term><option>--socketdir=</option><replaceable>dir</replaceable></term>
196 <listitem><para>directory to use for postmaster sockets during upgrade;
197 default is current working directory; environment
198 variable <envar>PGSOCKETDIR</envar></para></listitem>
199 </varlistentry>
200
201 <varlistentry>
202 <term><option>-U</option> <replaceable>username</replaceable></term>
203 <term><option>--username=</option><replaceable>username</replaceable></term>
204 <listitem><para>cluster's install user name; environment
205 variable <envar>PGUSER</envar></para></listitem>
206 </varlistentry>
207
208 <varlistentry>
209 <term><option>-v</option></term>
210 <term><option>--verbose</option></term>
211 <listitem><para>enable verbose internal logging</para></listitem>
212 </varlistentry>
213
214 <varlistentry>
215 <term><option>-V</option></term>
216 <term><option>--version</option></term>
217 <listitem><para>display version information, then exit</para></listitem>
218 </varlistentry>
219
220 <varlistentry>
221 <term><option>--clone</option></term>
222 <listitem>
223 <para>
224 Use efficient file cloning (also known as <quote>reflinks</quote> on
225 some systems) instead of copying files to the new cluster. This can
226 result in near-instantaneous copying of the data files, giving the
227 speed advantages of <option>-k</option>/<option>--link</option> while
228 leaving the old cluster untouched.
229 </para>
230
231 <para>
232 File cloning is only supported on some operating systems and file
233 systems. If it is selected but not supported, the
234 <application>pg_upgrade</application> run will error. At present, it
235 is supported on Linux (kernel 4.5 or later) with Btrfs and XFS (on
236 file systems created with reflink support), and on macOS with APFS.
237 </para>
238 </listitem>
239 </varlistentry>
240
241 <varlistentry>
242 <term><option>--copy</option></term>
243 <listitem>
244 <para>
245 Copy files to the new cluster. This is the default. (See also
246 <option>--link</option>, <option>--clone</option>,
247 <option>--copy-file-range</option>, and <option>--swap</option>.)
248 </para>
249 </listitem>
250 </varlistentry>
251
252 <varlistentry>
253 <term><option>--copy-file-range</option></term>
254 <listitem>
255 <para>
256 Use the <function>copy_file_range</function> system call for efficient
257 copying. On some file systems this gives results similar to
258 <option>--clone</option>, sharing physical disk blocks, while on others
259 it may still copy blocks, but do so via an optimized path. At present,
260 it is supported on Linux and FreeBSD.
261 </para>
262 </listitem>
263 </varlistentry>
264
265 <varlistentry>
266 <term><option>--no-statistics</option></term>
267 <listitem>
268 <para>
269 Do not restore statistics from the old cluster into the new cluster.
270 </para>
271 </listitem>
272 </varlistentry>
273
274 <varlistentry>
275 <term><option>--set-char-signedness=</option><replaceable>option</replaceable></term>
276 <listitem>
277 <para>
278 Manually set the default char signedness of new clusters. Possible values
279 are <literal>signed</literal> and <literal>unsigned</literal>.
280 </para>
281 <para>
282 In the C language, the default signedness of the <type>char</type> type
283 (when not explicitly specified) varies across platforms. For example,
284 <type>char</type> defaults to <type>signed char</type> on x86 CPUs but
285 to <type>unsigned char</type> on ARM CPUs.
286 </para>
287 <para>
288 Starting from <productname>PostgreSQL</productname> 18, database clusters
289 maintain their own default char signedness setting, which can be used to
290 ensure consistent behavior across platforms with different default char
291 signedness. By default, <application>pg_upgrade</application> preserves
292 the char signedness setting when upgrading from an existing cluster.
293 However, when upgrading from <productname>PostgreSQL</productname> 17 or
294 earlier, <application>pg_upgrade</application> adopts the char signedness
295 of the platform on which it was built.
296 </para>
297 <para>
298 This option allows you to explicitly set the default char signedness for
299 the new cluster, overriding any inherited values. There are two specific
300 scenarios where this option is relevant:
301 <itemizedlist>
302 <listitem>
303 <para>
304 If you are planning to migrate to a different platform after the upgrade,
305 you should not use this option. The default behavior is right in this case.
306 Instead, perform the upgrade on the original platform without this flag,
307 and then migrate the cluster afterward. This is the recommended and safest
308 approach.
309 </para>
310 </listitem>
311 <listitem>
312 <para>
313 If you have already migrated the cluster to a platform with different
314 char signedness (for example, from an x86-based system to an ARM-based
315 system), you should use this option to specify the signedness matching
316 the original platform's default char signedness. Additionally, it's
317 essential not to modify any data files between migrating data files and
318 running <command>pg_upgrade</command>. <command>pg_upgrade</command>
319 should be the first operation that starts the cluster on the new platform.
320 </para>
321 </listitem>
322 </itemizedlist>
323 </para>
324 </listitem>
325 </varlistentry>
326
327 <varlistentry>
328 <term><option>--swap</option></term>
329 <listitem>
330 <para>
331 Move the data directories from the old cluster to the new cluster.
332 Then, replace the catalog files with those generated for the new
333 cluster. This mode can outperform <option>--link</option>,
334 <option>--clone</option>, <option>--copy</option>, and
335 <option>--copy-file-range</option>, especially on clusters with many
336 relations.
337 </para>
338 <para>
339 However, this mode creates many garbage files in the old cluster, which
340 can prolong the file synchronization step if
341 <option>--sync-method=syncfs</option> is used. Therefore, it is
342 recommended to use <option>--sync-method=fsync</option> with
343 <option>--swap</option>.
344 </para>
345 <para>
346 Additionally, once the file transfer step begins, the old cluster will
347 be destructively modified and therefore will no longer be safe to
348 start. See <xref linkend="pgupgrade-step-revert"/> for details.
349 </para>
350 </listitem>
351 </varlistentry>
352
353 <varlistentry>
354 <term><option>--sync-method=</option><replaceable>method</replaceable></term>
355 <listitem>
356 <para>
357 When set to <literal>fsync</literal>, which is the default,
358 <command>pg_upgrade</command> will recursively open and synchronize all
359 files in the upgraded cluster's data directory. The search for files
360 will follow symbolic links for the WAL directory and each configured
361 tablespace.
362 </para>
363 <para>
364 On Linux, <literal>syncfs</literal> may be used instead to ask the
365 operating system to synchronize the whole file systems that contain the
366 upgraded cluster's data directory, its WAL files, and each tablespace.
367 See <xref linkend="guc-recovery-init-sync-method"/> for information
368 about the caveats to be aware of when using <literal>syncfs</literal>.
369 </para>
370 <para>
371 This option has no effect when <option>--no-sync</option> is used.
372 </para>
373 </listitem>
374 </varlistentry>
375
376 <varlistentry>
377 <term><option>-?</option></term>
378 <term><option>--help</option></term>
379 <listitem><para>show help, then exit</para></listitem>
380 </varlistentry>
381
382 </variablelist>
383 </para>
384
385 </refsect1>
386
387 <refsect1>
388 <title>Usage</title>
389
390 <para>
391 These are the steps to perform an upgrade
392 with <application>pg_upgrade</application>:
393 </para>
394
395 <note>
396 <para>
397 The steps to upgrade <glossterm linkend="glossary-logical-replication-cluster">logical replication clusters</glossterm>
398 are not covered here;
399 refer to <xref linkend="logical-replication-upgrade"/> for details.
400 </para>
401 </note>
402
403 <procedure>
404 <step performance="optional">
405 <title>Optionally move the old cluster</title>
406
407 <para>
408 If you are using a version-specific installation directory, e.g.,
409 <filename>/opt/PostgreSQL/&majorversion;</filename>, you do not need to move the old cluster. The
410 graphical installers all use version-specific installation directories.
411 </para>
412
413 <para>
414 If your installation directory is not version-specific, e.g.,
415 <filename>/usr/local/pgsql</filename>, it is necessary to move the current PostgreSQL install
416 directory so it does not interfere with the new <productname>PostgreSQL</productname> installation.
417 Once the current <productname>PostgreSQL</productname> server is shut down, it is safe to rename the
418 PostgreSQL installation directory; assuming the old directory is
419 <filename>/usr/local/pgsql</filename>, you can do:
420
421 <programlisting>
422 mv /usr/local/pgsql /usr/local/pgsql.old
423 </programlisting>
424 to rename the directory.
425 </para>
426 </step>
427
428 <step>
429 <title>For source installs, build the new version</title>
430
431 <para>
432 Build the new PostgreSQL source with <command>configure</command> flags that are compatible
433 with the old cluster. <application>pg_upgrade</application> will check <command>pg_controldata</command> to make
434 sure all settings are compatible before starting the upgrade.
435 </para>
436 </step>
437
438 <step>
439 <title>Install the new PostgreSQL binaries</title>
440
441 <para>
442 Install the new server's binaries and support
443 files. <application>pg_upgrade</application> is included in a default installation.
444 </para>
445
446 <para>
447 For source installs, if you wish to install the new server in a custom
448 location, use the <literal>prefix</literal> variable:
449
450 <programlisting>
451 make prefix=/usr/local/pgsql.new install
452 </programlisting></para>
453 </step>
454
455 <step>
456 <title>Initialize the new PostgreSQL cluster</title>
457
458 <para>
459 Initialize the new cluster using <command>initdb</command>.
460 Again, use compatible <command>initdb</command>
461 flags that match the old cluster. Many
462 prebuilt installers do this step automatically. There is no need to
463 start the new cluster.
464 </para>
465 </step>
466
467 <step>
468 <title>Install extension shared object files</title>
469
470 <para>
471 Many extensions and custom modules, whether from
472 <filename>contrib</filename> or another source, use shared object
473 files (or DLLs), e.g., <filename>pgcrypto.so</filename>. If the old
474 cluster used these, shared object files matching the new server binary
475 must be installed in the new cluster, usually via operating system
476 commands. Do not load the schema definitions, e.g., <command>CREATE
477 EXTENSION pgcrypto</command>, because these will be duplicated from
478 the old cluster. If extension updates are available,
479 <application>pg_upgrade</application> will report this and create
480 a script that can be run later to update them.
481 </para>
482 </step>
483
484 <step>
485 <title>Copy custom full-text search files</title>
486
487 <para>
488 Copy any custom full text search files (dictionary, synonym,
489 thesaurus, stop words) from the old to the new cluster.
490 </para>
491 </step>
492
493 <step>
494 <title>Adjust authentication</title>
495
496 <para>
497 <command>pg_upgrade</command> will connect to the old and new servers several
498 times, so you might want to set authentication to <literal>peer</literal>
499 in <filename>pg_hba.conf</filename> or use a <filename>~/.pgpass</filename> file
500 (see <xref linkend="libpq-pgpass"/>).
501 </para>
502 </step>
503
504 <step>
505 <title>Stop both servers</title>
506
507 <para>
508 Make sure both database servers are stopped using, on Unix, e.g.:
509
510 <programlisting>
511 pg_ctl -D /opt/PostgreSQL/12 stop
512 pg_ctl -D /opt/PostgreSQL/&majorversion; stop
513 </programlisting>
514
515 or on Windows, using the proper service names:
516
517 <programlisting>
518 NET STOP postgresql-12
519 NET STOP postgresql-&majorversion;
520 </programlisting>
521 </para>
522
523 <para>
524 Streaming replication and log-shipping standby servers must be
525 running during this shutdown so they receive all changes.
526 </para>
527 </step>
528
529 <step>
530 <title>Prepare for standby server upgrades</title>
531
532 <para>
533 If you are upgrading standby servers using methods outlined in section <xref
534 linkend="pgupgrade-step-replicas"/>, verify that the old standby
535 servers are caught up by running <application>pg_controldata</application>
536 against the old primary and standby clusters. Verify that the
537 <quote>Latest checkpoint location</quote> values match in all clusters.
538 Also, make sure <varname>wal_level</varname> is not set to
539 <literal>minimal</literal> in the <filename>postgresql.conf</filename> file on the
540 new primary cluster.
541 </para>
542 </step>
543
544 <step>
545 <title>Run <application>pg_upgrade</application></title>
546
547 <para>
548 Always run the <application>pg_upgrade</application> binary of the new server, not the old one.
549 <application>pg_upgrade</application> requires the specification of the old and new cluster's
550 data and executable (<filename>bin</filename>) directories. You can also specify
551 user and port values, and whether you want the data files linked, cloned, or swapped
552 instead of the default copy behavior.
553 </para>
554
555 <para>
556 If you use link mode, the upgrade will be much faster (no file
557 copying) and use less disk space, but you will not be able to access
558 your old cluster
559 once you start the new cluster after the upgrade. Link mode also
560 requires that the old and new cluster data directories be in the
561 same file system. (Tablespaces and <filename>pg_wal</filename> can be on
562 different file systems.)
563 Clone mode provides the same speed and disk space advantages but
564 does not cause the old cluster to be unusable once the new cluster
565 is started. Clone mode also requires that the old and new data
566 directories be in the same file system. This mode is only available
567 on certain operating systems and file systems.
568 Swap mode may be the fastest if there are many relations, but you will not
569 be able to access your old cluster once the file transfer step begins.
570 Swap mode also requires that the old and new cluster data directories be
571 in the same file system.
572 </para>
573
574 <para>
575 Setting <option>--jobs</option> to 2 or higher allows pg_upgrade to
576 process multiple databases and tablespaces in parallel. A good starting
577 point is the number of CPU cores on the machine. This option can
578 substantially reduce the upgrade time for multi-database and
579 multi-tablespace servers.
580 </para>
581
582 <para>
583 For Windows users, you must be logged into an administrative account,
584 and then run <application>pg_upgrade</application> with quoted
585 directories, e.g.:
586
587 <programlisting>
588 pg_upgrade.exe
589 --old-datadir "C:/Program Files/PostgreSQL/12/data"
590 --new-datadir "C:/Program Files/PostgreSQL/&majorversion;/data"
591 --old-bindir "C:/Program Files/PostgreSQL/12/bin"
592 --new-bindir "C:/Program Files/PostgreSQL/&majorversion;/bin"
593 </programlisting>
594
595 Once started, <command>pg_upgrade</command> will verify the two clusters are compatible
596 and then do the upgrade. You can use <command>pg_upgrade --check</command>
597 to perform only the checks, even if the old server is still
598 running. <command>pg_upgrade --check</command> will also outline any
599 manual adjustments you will need to make after the upgrade. If you
600 are going to be using link, clone, copy-file-range, or swap mode, you
601 should use the option <option>--link</option>, <option>--clone</option>,
602 <option>--copy-file-range</option>, or <option>--swap</option> with
603 <option>--check</option> to enable mode-specific checks.
604 <command>pg_upgrade</command> requires write permission in the current directory.
605 </para>
606
607 <para>
608 Obviously, no one should be accessing the clusters during the
609 upgrade. <application>pg_upgrade</application> defaults to running servers
610 on port 50432 to avoid unintended client connections.
611 You can use the same port number for both clusters when doing an
612 upgrade because the old and new clusters will not be running at the
613 same time. However, when checking an old running server, the old
614 and new port numbers must be different.
615 </para>
616
617 <para>
618 If an error occurs while restoring the database schema, <command>pg_upgrade</command> will
619 exit and you will have to revert to the old cluster as outlined in <xref linkend="pgupgrade-step-revert"/>
620 below. To try <command>pg_upgrade</command> again, you will need to modify the old
621 cluster so the pg_upgrade schema restore succeeds. If the problem is a
622 <filename>contrib</filename> module, you might need to uninstall the <filename>contrib</filename> module from
623 the old cluster and install it in the new cluster after the upgrade,
624 assuming the module is not being used to store user data.
625 </para>
626 </step>
627
628 <step id="pgupgrade-step-replicas">
629 <title>Upgrade streaming replication and log-shipping standby servers</title>
630
631 <para>
632 If you used link mode and have Streaming Replication (see <xref
633 linkend="streaming-replication"/>) or Log-Shipping (see <xref
634 linkend="warm-standby"/>) standby servers, you can follow these steps to
635 quickly upgrade them. You will not be running <application>pg_upgrade</application> on
636 the standby servers, but rather <application>rsync</application> on the primary.
637 Do not start any servers yet.
638 </para>
639
640 <para>
641 If you did <emphasis>not</emphasis> use link mode, do not have or do not
642 want to use <application>rsync</application>, or want an easier solution, skip
643 the instructions in this section and simply recreate the standby
644 servers once <application>pg_upgrade</application> completes and the new primary
645 is running.
646 </para>
647
648 <procedure>
649
650 <step>
651 <title>Install the new PostgreSQL binaries on standby servers</title>
652
653 <para>
654 Make sure the new binaries and support files are installed on all
655 standby servers.
656 </para>
657 </step>
658
659 <step>
660 <title>Make sure the new standby data directories do <emphasis>not</emphasis> exist</title>
661
662 <para>
663 Make sure the new standby data directories do <emphasis>not</emphasis>
664 exist or are empty. If <application>initdb</application> was run, delete
665 the standby servers' new data directories.
666 </para>
667 </step>
668
669 <step>
670 <title>Install extension shared object files</title>
671
672 <para>
673 Install the same extension shared object files on the new standbys
674 that you installed in the new primary cluster.
675 </para>
676 </step>
677
678 <step>
679 <title>Stop standby servers</title>
680
681 <para>
682 If the standby servers are still running, stop them now using the
683 above instructions.
684 </para>
685 </step>
686
687 <step>
688 <title>Save configuration files</title>
689
690 <para>
691 Save any configuration files from the old standbys' configuration
692 directories you need to keep, e.g., <filename>postgresql.conf</filename>
693 (and any files included by it), <filename>postgresql.auto.conf</filename>,
694 <literal>pg_hba.conf</literal>, because these will be overwritten
695 or removed in the next step.
696 </para>
697 </step>
698
699 <step>
700 <title>Run <application>rsync</application></title>
701
702 <para>
703 When using link mode, standby servers can be quickly upgraded using
704 <application>rsync</application>. To accomplish this, from a directory on
705 the primary server that is above the old and new database cluster
706 directories, run this on the <emphasis>primary</emphasis> for each standby
707 server:
708
709 <programlisting>
710 rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster new_cluster remote_dir
711 </programlisting>
712
713 where <option>old_cluster</option> and <option>new_cluster</option> are relative
714 to the current directory on the primary, and <option>remote_dir</option>
715 is <emphasis>above</emphasis> the old and new cluster directories
716 on the standby. The directory structure under the specified
717 directories on the primary and standbys must match. Consult the
718 <application>rsync</application> manual page for details on specifying the
719 remote directory, e.g.,
720
721 <programlisting>
722 rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/12 \
723 /opt/PostgreSQL/&majorversion; standby.example.com:/opt/PostgreSQL
724 </programlisting>
725
726 You can verify what the command will do using
727 <application>rsync</application>'s <option>--dry-run</option> option. While
728 <application>rsync</application> must be run on the primary for at least one
729 standby, it is possible to run <application>rsync</application> on an upgraded
730 standby to upgrade other standbys, as long as the upgraded standby
731 has not been started.
732 </para>
733
734 <para>
735 What this does is to record the links created by
736 <application>pg_upgrade</application>'s link mode that connect files in the
737 old and new clusters on the primary server. It then finds matching
738 files in the standby's old cluster and creates links for them in the
739 standby's new cluster. Files that were not linked on the primary
740 are copied from the primary to the standby. (They are usually
741 small.) This provides rapid standby upgrades. Unfortunately,
742 <application>rsync</application> needlessly copies files associated with
743 temporary and unlogged tables because these files don't normally
744 exist on standby servers.
745 </para>
746
747 <para>
748 If you have tablespaces, you will need to run a similar
749 <application>rsync</application> command for each tablespace directory, e.g.:
750
751 <programlisting>
752 rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_12_201909212 \
753 /vol1/pg_tblsp/PG_&majorversion;_202307071 standby.example.com:/vol1/pg_tblsp
754 </programlisting>
755
756 If you have relocated <filename>pg_wal</filename> outside the data
757 directories, <application>rsync</application> must be run on those directories
758 too.
759 </para>
760 </step>
761
762 <step>
763 <title>Configure streaming replication and log-shipping standby servers</title>
764
765 <para>
766 Configure the servers for log shipping. (You do not need to run
767 <function>pg_backup_start()</function> and <function>pg_backup_stop()</function>
768 or take a file system backup as the standbys are still synchronized
769 with the primary.) If the old primary is prior to version 17.0, then no
770 slots on the primary are copied to the new standby, so all the slots on
771 the old standby must be recreated manually. If the old primary is
772 version 17.0 or later, then only logical slots on the primary are copied
773 to the new standby, but other slots on the old standby are not copied,
774 so must be recreated manually.
775 </para>
776 </step>
777
778 </procedure>
779
780 </step>
781
782 <step>
783 <title>Restore <filename>pg_hba.conf</filename></title>
784
785 <para>
786 If you modified <filename>pg_hba.conf</filename>, restore its original settings.
787 It might also be necessary to adjust other configuration files in the new
788 cluster to match the old cluster, e.g., <filename>postgresql.conf</filename>
789 (and any files included by it), <filename>postgresql.auto.conf</filename>.
790 </para>
791 </step>
792
793 <step>
794 <title>Start the new server</title>
795
796 <para>
797 The new server can now be safely started, and then any
798 <application>rsync</application>'ed standby servers.
799 </para>
800 </step>
801
802 <step>
803 <title>Post-upgrade processing</title>
804
805 <para>
806 If any post-upgrade processing is required, pg_upgrade will issue
807 warnings as it completes. It will also generate script files that must
808 be run by the administrator. The script files will connect to each
809 database that needs post-upgrade processing. Each script should be
810 run using:
811
812 <programlisting>
813 psql --username=postgres --file=script.sql postgres
814 </programlisting>
815
816 The scripts can be run in any order and can be deleted once they have
817 been run.
818 </para>
819
820 <caution>
821 <para>
822 In general it is unsafe to access tables referenced in rebuild scripts
823 until the rebuild scripts have run to completion; doing so could yield
824 incorrect results or poor performance. Tables not referenced in rebuild
825 scripts can be accessed immediately.
826 </para>
827 </caution>
828 </step>
829
830 <step>
831 <title>Statistics</title>
832
833 <para>
834 Unless the <option>--no-statistics</option> option is specified,
835 <command>pg_upgrade</command> will transfer most optimizer statistics
836 from the old cluster to the new cluster. This does not transfer
837 all statistics, such as those created explicitly with
838 <xref linkend="sql-createstatistics"/>, custom statistics added by
839 an extension, or statistics collected by the cumulative statistics system.
840 </para>
841
842 <para>
843 Because not all statistics are transferred by
844 <command>pg_upgrade</command>, you will be instructed to run commands to
845 regenerate that information at the end of the upgrade. You might need to
846 set connection parameters to match your new cluster.
847 </para>
848
849 <para>
850 First, use
851 <command>vacuumdb --all --analyze-in-stages --missing-stats-only</command>
852 to quickly generate minimal optimizer statistics for relations without
853 any. Then, use <command>vacuumdb --all --analyze-only</command> to ensure
854 all relations have updated cumulative statistics for triggering vacuum and
855 analyze. For both commands, the use of <option>--jobs</option> can speed
856 it up.
857 If <varname>vacuum_cost_delay</varname> is set to a non-zero
858 value, this can be overridden to speed up statistics generation
859 using <envar>PGOPTIONS</envar>, e.g., <literal>PGOPTIONS='-c
860 vacuum_cost_delay=0' vacuumdb ...</literal>.
861 </para>
862 </step>
863
864 <step>
865 <title>Delete old cluster</title>
866
867 <para>
868 Once you are satisfied with the upgrade, you can delete the old
869 cluster's data directories by running the script mentioned when
870 <command>pg_upgrade</command> completes. (Automatic deletion is not
871 possible if you have user-defined tablespaces inside the old data
872 directory.) You can also delete the old installation directories
873 (e.g., <filename>bin</filename>, <filename>share</filename>).
874 </para>
875 </step>
876
877 <step id="pgupgrade-step-revert" performance="optional">
878 <title>Reverting to old cluster</title>
879
880 <para>
881 If, after running <command>pg_upgrade</command>, you wish to revert to the old cluster,
882 there are several options:
883
884 <itemizedlist>
885 <listitem>
886 <para>
887 If the <option>--check</option> option was used, the old cluster
888 was unmodified; it can be restarted.
889 </para>
890 </listitem>
891
892 <listitem>
893 <para>
894 If neither <option>--link</option> nor <option>--swap</option> was
895 used, the old cluster was unmodified; it can be restarted.
896 </para>
897 </listitem>
898
899 <listitem>
900 <para>
901 If the <option>--link</option> option was used, the data
902 files might be shared between the old and new cluster:
903
904 <itemizedlist>
905 <listitem>
906 <para>
907 If <command>pg_upgrade</command> aborted before linking started,
908 the old cluster was unmodified; it can be restarted.
909 </para>
910 </listitem>
911
912 <listitem>
913 <para>
914 If you did <emphasis>not</emphasis> start the new cluster, the old
915 cluster was unmodified except that, when linking started, a
916 <literal>.old</literal> suffix was appended to
917 <filename>$PGDATA/global/pg_control</filename>. To reuse the old
918 cluster, remove the <filename>.old</filename> suffix from
919 <filename>$PGDATA/global/pg_control</filename>; you can then restart
920 the old cluster.
921 </para>
922 </listitem>
923
924 <listitem>
925 <para>
926 If you did start the new cluster, it has written to shared files
927 and it is unsafe to use the old cluster. The old cluster will
928 need to be restored from backup in this case.
929 </para>
930 </listitem>
931
932 </itemizedlist></para>
933 </listitem>
934
935 <listitem>
936 <para>
937 If the <option>--swap</option> option was used, the old cluster might
938 be destructively modified:
939
940 <itemizedlist>
941 <listitem>
942 <para>
943 If <command>pg_upgrade</command> aborts before reporting that the
944 old cluster is no longer safe to start, the old cluster was
945 unmodified; it can be restarted.
946 </para>
947 </listitem>
948
949 <listitem>
950 <para>
951 If <command>pg_upgrade</command> has reported that the old cluster
952 is no longer safe to start, the old cluster was destructively
953 modified. The old cluster will need to be restored from backup in
954 this case.
955 </para>
956 </listitem>
957 </itemizedlist>
958 </para>
959 </listitem>
960 </itemizedlist></para>
961 </step>
962 </procedure>
963
964 </refsect1>
965
966 <refsect1>
967 <title>Environment</title>
968
969 <para>
970 Some environment variables can be used to provide defaults for command-line options:
971
972 <variablelist>
973 <varlistentry>
974 <term><envar>PGBINOLD</envar></term>
975
976 <listitem>
977 <para>
978 The old PostgreSQL executable directory; option
979 <option>-b</option>/<option>--old-bindir</option>.
980 </para>
981 </listitem>
982 </varlistentry>
983
984 <varlistentry>
985 <term><envar>PGBINNEW</envar></term>
986
987 <listitem>
988 <para>
989 The new PostgreSQL executable directory; option
990 <option>-B</option>/<option>--new-bindir</option>.
991 </para>
992 </listitem>
993 </varlistentry>
994
995 <varlistentry>
996 <term><envar>PGDATAOLD</envar></term>
997
998 <listitem>
999 <para>
1000 The old database cluster configuration directory; option
1001 <option>-d</option>/<option>--old-datadir</option>.
1002 </para>
1003 </listitem>
1004 </varlistentry>
1005
1006 <varlistentry>
1007 <term><envar>PGDATANEW</envar></term>
1008
1009 <listitem>
1010 <para>
1011 The new database cluster configuration directory; option
1012 <option>-D</option>/<option>--new-datadir</option>.
1013 </para>
1014 </listitem>
1015 </varlistentry>
1016
1017 <varlistentry>
1018 <term><envar>PGPORTOLD</envar></term>
1019
1020 <listitem>
1021 <para>
1022 The old cluster port number; option
1023 <option>-p</option>/<option>--old-port</option>.
1024 </para>
1025 </listitem>
1026 </varlistentry>
1027
1028 <varlistentry>
1029 <term><envar>PGPORTNEW</envar></term>
1030
1031 <listitem>
1032 <para>
1033 The new cluster port number; option
1034 <option>-P</option>/<option>--new-port</option>.
1035 </para>
1036 </listitem>
1037 </varlistentry>
1038
1039 <varlistentry>
1040 <term><envar>PGSOCKETDIR</envar></term>
1041
1042 <listitem>
1043 <para>
1044 Directory to use for postmaster sockets during upgrade; option
1045 <option>-s</option>/<option>--socketdir</option>.
1046 </para>
1047 </listitem>
1048 </varlistentry>
1049
1050 <varlistentry>
1051 <term><envar>PGUSER</envar></term>
1052
1053 <listitem>
1054 <para>
1055 Cluster's install user name; option
1056 <option>-U</option>/<option>--username</option>.
1057 </para>
1058 </listitem>
1059 </varlistentry>
1060 </variablelist>
1061 </para>
1062 </refsect1>
1063
1064 <refsect1>
1065 <title>Notes</title>
1066
1067 <para>
1068 <application>pg_upgrade</application> creates various working files, such
1069 as schema dumps, stored within <filename>pg_upgrade_output.d</filename> in
1070 the directory of the new cluster. Each run creates a new subdirectory named
1071 with a timestamp formatted as per ISO 8601
1072 (<literal>%Y%m%dT%H%M%S</literal>), where all its generated files are
1073 stored.
1074 <filename>pg_upgrade_output.d</filename> and its contained files will be
1075 removed automatically if <application>pg_upgrade</application> completes
1076 successfully; but in the event of trouble, the files there may provide
1077 useful debugging information.
1078 </para>
1079
1080 <para>
1081 <application>pg_upgrade</application> launches short-lived postmasters in
1082 the old and new data directories. Temporary Unix socket files for
1083 communication with these postmasters are, by default, made in the current
1084 working directory. In some situations the path name for the current
1085 directory might be too long to be a valid socket name. In that case you
1086 can use the <option>-s</option> option to put the socket files in some
1087 directory with a shorter path name. For security, be sure that that
1088 directory is not readable or writable by any other users.
1089 (This is not supported on Windows.)
1090 </para>
1091
1092 <para>
1093 All failure, rebuild, and reindex cases will be reported by
1094 <application>pg_upgrade</application> if they affect your installation;
1095 post-upgrade scripts to rebuild tables and indexes will be
1096 generated automatically. If you are trying to automate the upgrade
1097 of many clusters, you should find that clusters with identical database
1098 schemas require the same post-upgrade steps for all cluster upgrades;
1099 this is because the post-upgrade steps are based on the database
1100 schemas, and not user data.
1101 </para>
1102
1103 <para>
1104 For deployment testing, create a schema-only copy of the old cluster,
1105 insert dummy data, and upgrade that.
1106 </para>
1107
1108 <para>
1109 <application>pg_upgrade</application> does not support upgrading of databases
1110 containing table columns using these <type>reg*</type> OID-referencing system data types:
1111 <simplelist>
1112 <member><type>regcollation</type></member>
1113 <member><type>regconfig</type></member>
1114 <member><type>regdictionary</type></member>
1115 <member><type>regnamespace</type></member>
1116 <member><type>regoper</type></member>
1117 <member><type>regoperator</type></member>
1118 <member><type>regproc</type></member>
1119 <member><type>regprocedure</type></member>
1120 </simplelist>
1121 (<type>regclass</type>, <type>regrole</type>, and <type>regtype</type> can be upgraded.)
1122 </para>
1123
1124 <para>
1125 If you want to use link mode and you do not want your old cluster
1126 to be modified when the new cluster is started, consider using the clone mode.
1127 If that is not available, make a copy of the
1128 old cluster and upgrade that in link mode. To make a valid copy
1129 of the old cluster, use <command>rsync</command> to create a dirty
1130 copy of the old cluster while the server is running, then shut down
1131 the old server and run <command>rsync --checksum</command> again to update the
1132 copy with any changes to make it consistent. (<option>--checksum</option>
1133 is necessary because <command>rsync</command> only has file modification-time
1134 granularity of one second.) You might want to exclude some
1135 files, e.g., <filename>postmaster.pid</filename>, as documented in <xref
1136 linkend="backup-lowlevel-base-backup"/>. If your file system supports
1137 file system snapshots or copy-on-write file copies, you can use that
1138 to make a backup of the old cluster and tablespaces, though the snapshot
1139 and copies must be created simultaneously or while the database server
1140 is down.
1141 </para>
1142
1143 </refsect1>
1144
1145 <refsect1>
1146 <title>See Also</title>
1147
1148 <simplelist type="inline">
1149 <member><xref linkend="app-initdb"/></member>
1150 <member><xref linkend="app-pg-ctl"/></member>
1151 <member><xref linkend="app-pgdump"/></member>
1152 <member><xref linkend="app-postgres"/></member>
1153 </simplelist>
1154 </refsect1>
1155 </refentry>