1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>pg_upgrade</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="pgtesttiming.html" title="pg_test_timing" /><link rel="next" href="pgwaldump.html" title="pg_waldump" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center"><span class="application">pg_upgrade</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgtesttiming.html" title="pg_test_timing">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="reference-server.html" title="PostgreSQL Server Applications">Up</a></td><th width="60%" align="center">PostgreSQL Server Applications</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pgwaldump.html" title="pg_waldump">Next</a></td></tr></table><hr /></div><div class="refentry" id="PGUPGRADE"><div class="titlepage"></div><a id="id-1.9.5.13.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle"><span class="application">pg_upgrade</span></span></h2><p>pg_upgrade — upgrade a <span class="productname">PostgreSQL</span> server instance</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><div class="cmdsynopsis"><p id="id-1.9.5.13.4.1"><code class="command">pg_upgrade</code> <code class="option">-b</code> <em class="replaceable"><code>oldbindir</code></em> [<code class="option">-B</code> <em class="replaceable"><code>newbindir</code></em>] <code class="option">-d</code> <em class="replaceable"><code>oldconfigdir</code></em> <code class="option">-D</code> <em class="replaceable"><code>newconfigdir</code></em> [<em class="replaceable"><code>option</code></em>...]</p></div></div><div class="refsect1" id="id-1.9.5.13.5"><h2>Description</h2><p>
3 <span class="application">pg_upgrade</span> (formerly called <span class="application">pg_migrator</span>) allows data
4 stored in <span class="productname">PostgreSQL</span> data files to be upgraded to a later <span class="productname">PostgreSQL</span>
5 major version without the data dump/restore typically required for
6 major version upgrades, e.g., from 12.14 to 13.10 or from 14.9 to 15.5.
7 It is not required for minor version upgrades, e.g., from 12.7 to 12.8
10 Major PostgreSQL releases regularly add new features that often
11 change the layout of the system tables, but the internal data storage
12 format rarely changes. <span class="application">pg_upgrade</span> uses this fact
13 to perform rapid upgrades by creating new system tables and simply
14 reusing the old user data files. If a future major release ever
15 changes the data storage format in a way that makes the old data
16 format unreadable, <span class="application">pg_upgrade</span> will not be usable
17 for such upgrades. (The community will attempt to avoid such
20 <span class="application">pg_upgrade</span> does its best to
21 make sure the old and new clusters are binary-compatible, e.g., by
22 checking for compatible compile-time settings, including 32/64-bit
23 binaries. It is important that
24 any external modules are also binary compatible, though this cannot
25 be checked by <span class="application">pg_upgrade</span>.
27 <span class="application">pg_upgrade</span> supports upgrades from 9.2.X and later to the current
28 major release of <span class="productname">PostgreSQL</span>, including snapshot and beta releases.
29 </p><div class="warning"><h3 class="title">Warning</h3><p>
30 Upgrading a cluster causes the destination to execute arbitrary code of the
31 source superusers' choice. Ensure that the source superusers are trusted
33 </p></div></div><div class="refsect1" id="id-1.9.5.13.6"><h2>Options</h2><p>
34 <span class="application">pg_upgrade</span> accepts the following command-line arguments:
36 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-b</code> <em class="replaceable"><code>bindir</code></em><br /></span><span class="term"><code class="option">--old-bindir=</code><em class="replaceable"><code>bindir</code></em></span></dt><dd><p>the old PostgreSQL executable directory;
37 environment variable <code class="envar">PGBINOLD</code></p></dd><dt><span class="term"><code class="option">-B</code> <em class="replaceable"><code>bindir</code></em><br /></span><span class="term"><code class="option">--new-bindir=</code><em class="replaceable"><code>bindir</code></em></span></dt><dd><p>the new PostgreSQL executable directory;
38 default is the directory where <span class="application">pg_upgrade</span> resides;
39 environment variable <code class="envar">PGBINNEW</code></p></dd><dt><span class="term"><code class="option">-c</code><br /></span><span class="term"><code class="option">--check</code></span></dt><dd><p>check clusters only, don't change any data</p></dd><dt><span class="term"><code class="option">-d</code> <em class="replaceable"><code>configdir</code></em><br /></span><span class="term"><code class="option">--old-datadir=</code><em class="replaceable"><code>configdir</code></em></span></dt><dd><p>the old database cluster configuration directory; environment
40 variable <code class="envar">PGDATAOLD</code></p></dd><dt><span class="term"><code class="option">-D</code> <em class="replaceable"><code>configdir</code></em><br /></span><span class="term"><code class="option">--new-datadir=</code><em class="replaceable"><code>configdir</code></em></span></dt><dd><p>the new database cluster configuration directory; environment
41 variable <code class="envar">PGDATANEW</code></p></dd><dt><span class="term"><code class="option">-j <em class="replaceable"><code>njobs</code></em></code><br /></span><span class="term"><code class="option">--jobs=<em class="replaceable"><code>njobs</code></em></code></span></dt><dd><p>number of simultaneous connections and processes/threads to use
42 </p></dd><dt><span class="term"><code class="option">-k</code><br /></span><span class="term"><code class="option">--link</code></span></dt><dd><p>use hard links instead of copying files to the new
43 cluster</p></dd><dt><span class="term"><code class="option">-N</code><br /></span><span class="term"><code class="option">--no-sync</code></span></dt><dd><p>
44 By default, <code class="command">pg_upgrade</code> will wait for all files
45 of the upgraded cluster to be written safely to disk. This option
46 causes <code class="command">pg_upgrade</code> to return without waiting, which
47 is faster, but means that a subsequent operating system crash can leave
48 the data directory corrupt. Generally, this option is
49 useful for testing but should not be used on a production
51 </p></dd><dt><span class="term"><code class="option">-o</code> <em class="replaceable"><code>options</code></em><br /></span><span class="term"><code class="option">--old-options</code> <em class="replaceable"><code>options</code></em></span></dt><dd><p>options to be passed directly to the
52 old <code class="command">postgres</code> command; multiple
53 option invocations are appended</p></dd><dt><span class="term"><code class="option">-O</code> <em class="replaceable"><code>options</code></em><br /></span><span class="term"><code class="option">--new-options</code> <em class="replaceable"><code>options</code></em></span></dt><dd><p>options to be passed directly to the
54 new <code class="command">postgres</code> command; multiple
55 option invocations are appended</p></dd><dt><span class="term"><code class="option">-p</code> <em class="replaceable"><code>port</code></em><br /></span><span class="term"><code class="option">--old-port=</code><em class="replaceable"><code>port</code></em></span></dt><dd><p>the old cluster port number; environment
56 variable <code class="envar">PGPORTOLD</code></p></dd><dt><span class="term"><code class="option">-P</code> <em class="replaceable"><code>port</code></em><br /></span><span class="term"><code class="option">--new-port=</code><em class="replaceable"><code>port</code></em></span></dt><dd><p>the new cluster port number; environment
57 variable <code class="envar">PGPORTNEW</code></p></dd><dt><span class="term"><code class="option">-r</code><br /></span><span class="term"><code class="option">--retain</code></span></dt><dd><p>retain SQL and log files even after successful completion
58 </p></dd><dt><span class="term"><code class="option">-s</code> <em class="replaceable"><code>dir</code></em><br /></span><span class="term"><code class="option">--socketdir=</code><em class="replaceable"><code>dir</code></em></span></dt><dd><p>directory to use for postmaster sockets during upgrade;
59 default is current working directory; environment
60 variable <code class="envar">PGSOCKETDIR</code></p></dd><dt><span class="term"><code class="option">-U</code> <em class="replaceable"><code>username</code></em><br /></span><span class="term"><code class="option">--username=</code><em class="replaceable"><code>username</code></em></span></dt><dd><p>cluster's install user name; environment
61 variable <code class="envar">PGUSER</code></p></dd><dt><span class="term"><code class="option">-v</code><br /></span><span class="term"><code class="option">--verbose</code></span></dt><dd><p>enable verbose internal logging</p></dd><dt><span class="term"><code class="option">-V</code><br /></span><span class="term"><code class="option">--version</code></span></dt><dd><p>display version information, then exit</p></dd><dt><span class="term"><code class="option">--clone</code></span></dt><dd><p>
62 Use efficient file cloning (also known as <span class="quote">“<span class="quote">reflinks</span>”</span> on
63 some systems) instead of copying files to the new cluster. This can
64 result in near-instantaneous copying of the data files, giving the
65 speed advantages of <code class="option">-k</code>/<code class="option">--link</code> while
66 leaving the old cluster untouched.
68 File cloning is only supported on some operating systems and file
69 systems. If it is selected but not supported, the
70 <span class="application">pg_upgrade</span> run will error. At present, it
71 is supported on Linux (kernel 4.5 or later) with Btrfs and XFS (on
72 file systems created with reflink support), and on macOS with APFS.
73 </p></dd><dt><span class="term"><code class="option">--copy</code></span></dt><dd><p>
74 Copy files to the new cluster. This is the default. (See also
75 <code class="option">--link</code>, <code class="option">--clone</code>,
76 <code class="option">--copy-file-range</code>, and <code class="option">--swap</code>.)
77 </p></dd><dt><span class="term"><code class="option">--copy-file-range</code></span></dt><dd><p>
78 Use the <code class="function">copy_file_range</code> system call for efficient
79 copying. On some file systems this gives results similar to
80 <code class="option">--clone</code>, sharing physical disk blocks, while on others
81 it may still copy blocks, but do so via an optimized path. At present,
82 it is supported on Linux and FreeBSD.
83 </p></dd><dt><span class="term"><code class="option">--no-statistics</code></span></dt><dd><p>
84 Do not restore statistics from the old cluster into the new cluster.
85 </p></dd><dt><span class="term"><code class="option">--set-char-signedness=</code><em class="replaceable"><code>option</code></em></span></dt><dd><p>
86 Manually set the default char signedness of new clusters. Possible values
87 are <code class="literal">signed</code> and <code class="literal">unsigned</code>.
89 In the C language, the default signedness of the <code class="type">char</code> type
90 (when not explicitly specified) varies across platforms. For example,
91 <code class="type">char</code> defaults to <code class="type">signed char</code> on x86 CPUs but
92 to <code class="type">unsigned char</code> on ARM CPUs.
94 Starting from <span class="productname">PostgreSQL</span> 18, database clusters
95 maintain their own default char signedness setting, which can be used to
96 ensure consistent behavior across platforms with different default char
97 signedness. By default, <span class="application">pg_upgrade</span> preserves
98 the char signedness setting when upgrading from an existing cluster.
99 However, when upgrading from <span class="productname">PostgreSQL</span> 17 or
100 earlier, <span class="application">pg_upgrade</span> adopts the char signedness
101 of the platform on which it was built.
103 This option allows you to explicitly set the default char signedness for
104 the new cluster, overriding any inherited values. There are two specific
105 scenarios where this option is relevant:
106 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
107 If you are planning to migrate to a different platform after the upgrade,
108 you should not use this option. The default behavior is right in this case.
109 Instead, perform the upgrade on the original platform without this flag,
110 and then migrate the cluster afterward. This is the recommended and safest
112 </p></li><li class="listitem"><p>
113 If you have already migrated the cluster to a platform with different
114 char signedness (for example, from an x86-based system to an ARM-based
115 system), you should use this option to specify the signedness matching
116 the original platform's default char signedness. Additionally, it's
117 essential not to modify any data files between migrating data files and
118 running <code class="command">pg_upgrade</code>. <code class="command">pg_upgrade</code>
119 should be the first operation that starts the cluster on the new platform.
120 </p></li></ul></div><p>
121 </p></dd><dt><span class="term"><code class="option">--swap</code></span></dt><dd><p>
122 Move the data directories from the old cluster to the new cluster.
123 Then, replace the catalog files with those generated for the new
124 cluster. This mode can outperform <code class="option">--link</code>,
125 <code class="option">--clone</code>, <code class="option">--copy</code>, and
126 <code class="option">--copy-file-range</code>, especially on clusters with many
129 However, this mode creates many garbage files in the old cluster, which
130 can prolong the file synchronization step if
131 <code class="option">--sync-method=syncfs</code> is used. Therefore, it is
132 recommended to use <code class="option">--sync-method=fsync</code> with
133 <code class="option">--swap</code>.
135 Additionally, once the file transfer step begins, the old cluster will
136 be destructively modified and therefore will no longer be safe to
137 start. See <a class="xref" href="pgupgrade.html#PGUPGRADE-STEP-REVERT" title="Reverting to old cluster">Step 17</a> for details.
138 </p></dd><dt><span class="term"><code class="option">--sync-method=</code><em class="replaceable"><code>method</code></em></span></dt><dd><p>
139 When set to <code class="literal">fsync</code>, which is the default,
140 <code class="command">pg_upgrade</code> will recursively open and synchronize all
141 files in the upgraded cluster's data directory. The search for files
142 will follow symbolic links for the WAL directory and each configured
145 On Linux, <code class="literal">syncfs</code> may be used instead to ask the
146 operating system to synchronize the whole file systems that contain the
147 upgraded cluster's data directory, its WAL files, and each tablespace.
148 See <a class="xref" href="runtime-config-error-handling.html#GUC-RECOVERY-INIT-SYNC-METHOD">recovery_init_sync_method</a> for information
149 about the caveats to be aware of when using <code class="literal">syncfs</code>.
151 This option has no effect when <code class="option">--no-sync</code> is used.
152 </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>show help, then exit</p></dd></dl></div><p>
153 </p></div><div class="refsect1" id="id-1.9.5.13.7"><h2>Usage</h2><p>
154 These are the steps to perform an upgrade
155 with <span class="application">pg_upgrade</span>:
156 </p><div class="note"><h3 class="title">Note</h3><p>
157 The steps to upgrade <a class="glossterm" href="glossary.html#GLOSSARY-LOGICAL-REPLICATION-CLUSTER"><em class="glossterm"><a class="glossterm" href="glossary.html#GLOSSARY-LOGICAL-REPLICATION-CLUSTER" title="Logical replication cluster">logical replication clusters</a></em></a>
158 are not covered here;
159 refer to <a class="xref" href="logical-replication-upgrade.html" title="29.13. Upgrade">Section 29.13</a> for details.
160 </p></div><div class="procedure"><ol class="procedure" type="1"><li class="step"><p class="title"><strong>Optionally move the old cluster</strong></p><p>
161 If you are using a version-specific installation directory, e.g.,
162 <code class="filename">/opt/PostgreSQL/18</code>, you do not need to move the old cluster. The
163 graphical installers all use version-specific installation directories.
165 If your installation directory is not version-specific, e.g.,
166 <code class="filename">/usr/local/pgsql</code>, it is necessary to move the current PostgreSQL install
167 directory so it does not interfere with the new <span class="productname">PostgreSQL</span> installation.
168 Once the current <span class="productname">PostgreSQL</span> server is shut down, it is safe to rename the
169 PostgreSQL installation directory; assuming the old directory is
170 <code class="filename">/usr/local/pgsql</code>, you can do:
172 </p><pre class="programlisting">
173 mv /usr/local/pgsql /usr/local/pgsql.old
175 to rename the directory.
176 </p></li><li class="step"><p class="title"><strong>For source installs, build the new version</strong></p><p>
177 Build the new PostgreSQL source with <code class="command">configure</code> flags that are compatible
178 with the old cluster. <span class="application">pg_upgrade</span> will check <code class="command">pg_controldata</code> to make
179 sure all settings are compatible before starting the upgrade.
180 </p></li><li class="step"><p class="title"><strong>Install the new PostgreSQL binaries</strong></p><p>
181 Install the new server's binaries and support
182 files. <span class="application">pg_upgrade</span> is included in a default installation.
184 For source installs, if you wish to install the new server in a custom
185 location, use the <code class="literal">prefix</code> variable:
187 </p><pre class="programlisting">
188 make prefix=/usr/local/pgsql.new install
189 </pre></li><li class="step"><p class="title"><strong>Initialize the new PostgreSQL cluster</strong></p><p>
190 Initialize the new cluster using <code class="command">initdb</code>.
191 Again, use compatible <code class="command">initdb</code>
192 flags that match the old cluster. Many
193 prebuilt installers do this step automatically. There is no need to
194 start the new cluster.
195 </p></li><li class="step"><p class="title"><strong>Install extension shared object files</strong></p><p>
196 Many extensions and custom modules, whether from
197 <code class="filename">contrib</code> or another source, use shared object
198 files (or DLLs), e.g., <code class="filename">pgcrypto.so</code>. If the old
199 cluster used these, shared object files matching the new server binary
200 must be installed in the new cluster, usually via operating system
201 commands. Do not load the schema definitions, e.g., <code class="command">CREATE
202 EXTENSION pgcrypto</code>, because these will be duplicated from
203 the old cluster. If extension updates are available,
204 <span class="application">pg_upgrade</span> will report this and create
205 a script that can be run later to update them.
206 </p></li><li class="step"><p class="title"><strong>Copy custom full-text search files</strong></p><p>
207 Copy any custom full text search files (dictionary, synonym,
208 thesaurus, stop words) from the old to the new cluster.
209 </p></li><li class="step"><p class="title"><strong>Adjust authentication</strong></p><p>
210 <code class="command">pg_upgrade</code> will connect to the old and new servers several
211 times, so you might want to set authentication to <code class="literal">peer</code>
212 in <code class="filename">pg_hba.conf</code> or use a <code class="filename">~/.pgpass</code> file
213 (see <a class="xref" href="libpq-pgpass.html" title="32.16. The Password File">Section 32.16</a>).
214 </p></li><li class="step"><p class="title"><strong>Stop both servers</strong></p><p>
215 Make sure both database servers are stopped using, on Unix, e.g.:
217 </p><pre class="programlisting">
218 pg_ctl -D /opt/PostgreSQL/12 stop
219 pg_ctl -D /opt/PostgreSQL/18 stop
222 or on Windows, using the proper service names:
224 </p><pre class="programlisting">
225 NET STOP postgresql-12
226 NET STOP postgresql-18
229 Streaming replication and log-shipping standby servers must be
230 running during this shutdown so they receive all changes.
231 </p></li><li class="step"><p class="title"><strong>Prepare for standby server upgrades</strong></p><p>
232 If you are upgrading standby servers using methods outlined in section <a class="xref" href="pgupgrade.html#PGUPGRADE-STEP-REPLICAS" title="Upgrade streaming replication and log-shipping standby servers">Step 11</a>, verify that the old standby
233 servers are caught up by running <span class="application">pg_controldata</span>
234 against the old primary and standby clusters. Verify that the
235 <span class="quote">“<span class="quote">Latest checkpoint location</span>”</span> values match in all clusters.
236 Also, make sure <code class="varname">wal_level</code> is not set to
237 <code class="literal">minimal</code> in the <code class="filename">postgresql.conf</code> file on the
239 </p></li><li class="step"><p class="title"><strong>Run <span class="application">pg_upgrade</span></strong></p><p>
240 Always run the <span class="application">pg_upgrade</span> binary of the new server, not the old one.
241 <span class="application">pg_upgrade</span> requires the specification of the old and new cluster's
242 data and executable (<code class="filename">bin</code>) directories. You can also specify
243 user and port values, and whether you want the data files linked, cloned, or swapped
244 instead of the default copy behavior.
246 If you use link mode, the upgrade will be much faster (no file
247 copying) and use less disk space, but you will not be able to access
249 once you start the new cluster after the upgrade. Link mode also
250 requires that the old and new cluster data directories be in the
251 same file system. (Tablespaces and <code class="filename">pg_wal</code> can be on
252 different file systems.)
253 Clone mode provides the same speed and disk space advantages but
254 does not cause the old cluster to be unusable once the new cluster
255 is started. Clone mode also requires that the old and new data
256 directories be in the same file system. This mode is only available
257 on certain operating systems and file systems.
258 Swap mode may be the fastest if there are many relations, but you will not
259 be able to access your old cluster once the file transfer step begins.
260 Swap mode also requires that the old and new cluster data directories be
261 in the same file system.
263 Setting <code class="option">--jobs</code> to 2 or higher allows pg_upgrade to
264 process multiple databases and tablespaces in parallel. A good starting
265 point is the number of CPU cores on the machine. This option can
266 substantially reduce the upgrade time for multi-database and
267 multi-tablespace servers.
269 For Windows users, you must be logged into an administrative account,
270 and then run <span class="application">pg_upgrade</span> with quoted
273 </p><pre class="programlisting">
275 --old-datadir "C:/Program Files/PostgreSQL/12/data"
276 --new-datadir "C:/Program Files/PostgreSQL/18/data"
277 --old-bindir "C:/Program Files/PostgreSQL/12/bin"
278 --new-bindir "C:/Program Files/PostgreSQL/18/bin"
281 Once started, <code class="command">pg_upgrade</code> will verify the two clusters are compatible
282 and then do the upgrade. You can use <code class="command">pg_upgrade --check</code>
283 to perform only the checks, even if the old server is still
284 running. <code class="command">pg_upgrade --check</code> will also outline any
285 manual adjustments you will need to make after the upgrade. If you
286 are going to be using link, clone, copy-file-range, or swap mode, you
287 should use the option <code class="option">--link</code>, <code class="option">--clone</code>,
288 <code class="option">--copy-file-range</code>, or <code class="option">--swap</code> with
289 <code class="option">--check</code> to enable mode-specific checks.
290 <code class="command">pg_upgrade</code> requires write permission in the current directory.
292 Obviously, no one should be accessing the clusters during the
293 upgrade. <span class="application">pg_upgrade</span> defaults to running servers
294 on port 50432 to avoid unintended client connections.
295 You can use the same port number for both clusters when doing an
296 upgrade because the old and new clusters will not be running at the
297 same time. However, when checking an old running server, the old
298 and new port numbers must be different.
300 If an error occurs while restoring the database schema, <code class="command">pg_upgrade</code> will
301 exit and you will have to revert to the old cluster as outlined in <a class="xref" href="pgupgrade.html#PGUPGRADE-STEP-REVERT" title="Reverting to old cluster">Step 17</a>
302 below. To try <code class="command">pg_upgrade</code> again, you will need to modify the old
303 cluster so the pg_upgrade schema restore succeeds. If the problem is a
304 <code class="filename">contrib</code> module, you might need to uninstall the <code class="filename">contrib</code> module from
305 the old cluster and install it in the new cluster after the upgrade,
306 assuming the module is not being used to store user data.
307 </p></li><li class="step" id="PGUPGRADE-STEP-REPLICAS"><p class="title"><strong>Upgrade streaming replication and log-shipping standby servers</strong></p><p>
308 If you used link mode and have Streaming Replication (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION" title="26.2.5. Streaming Replication">Section 26.2.5</a>) or Log-Shipping (see <a class="xref" href="warm-standby.html" title="26.2. Log-Shipping Standby Servers">Section 26.2</a>) standby servers, you can follow these steps to
309 quickly upgrade them. You will not be running <span class="application">pg_upgrade</span> on
310 the standby servers, but rather <span class="application">rsync</span> on the primary.
311 Do not start any servers yet.
313 If you did <span class="emphasis"><em>not</em></span> use link mode, do not have or do not
314 want to use <span class="application">rsync</span>, or want an easier solution, skip
315 the instructions in this section and simply recreate the standby
316 servers once <span class="application">pg_upgrade</span> completes and the new primary
318 </p><div class="procedure"><ol class="procedure" type="1"><li class="step"><p class="title"><strong>Install the new PostgreSQL binaries on standby servers</strong></p><p>
319 Make sure the new binaries and support files are installed on all
321 </p></li><li class="step"><p class="title"><strong>Make sure the new standby data directories do <span class="emphasis"><em>not</em></span> exist</strong></p><p>
322 Make sure the new standby data directories do <span class="emphasis"><em>not</em></span>
323 exist or are empty. If <span class="application">initdb</span> was run, delete
324 the standby servers' new data directories.
325 </p></li><li class="step"><p class="title"><strong>Install extension shared object files</strong></p><p>
326 Install the same extension shared object files on the new standbys
327 that you installed in the new primary cluster.
328 </p></li><li class="step"><p class="title"><strong>Stop standby servers</strong></p><p>
329 If the standby servers are still running, stop them now using the
331 </p></li><li class="step"><p class="title"><strong>Save configuration files</strong></p><p>
332 Save any configuration files from the old standbys' configuration
333 directories you need to keep, e.g., <code class="filename">postgresql.conf</code>
334 (and any files included by it), <code class="filename">postgresql.auto.conf</code>,
335 <code class="literal">pg_hba.conf</code>, because these will be overwritten
336 or removed in the next step.
337 </p></li><li class="step"><p class="title"><strong>Run <span class="application">rsync</span></strong></p><p>
338 When using link mode, standby servers can be quickly upgraded using
339 <span class="application">rsync</span>. To accomplish this, from a directory on
340 the primary server that is above the old and new database cluster
341 directories, run this on the <span class="emphasis"><em>primary</em></span> for each standby
344 </p><pre class="programlisting">
345 rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster new_cluster remote_dir
348 where <code class="option">old_cluster</code> and <code class="option">new_cluster</code> are relative
349 to the current directory on the primary, and <code class="option">remote_dir</code>
350 is <span class="emphasis"><em>above</em></span> the old and new cluster directories
351 on the standby. The directory structure under the specified
352 directories on the primary and standbys must match. Consult the
353 <span class="application">rsync</span> manual page for details on specifying the
354 remote directory, e.g.,
356 </p><pre class="programlisting">
357 rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/12 \
358 /opt/PostgreSQL/18 standby.example.com:/opt/PostgreSQL
361 You can verify what the command will do using
362 <span class="application">rsync</span>'s <code class="option">--dry-run</code> option. While
363 <span class="application">rsync</span> must be run on the primary for at least one
364 standby, it is possible to run <span class="application">rsync</span> on an upgraded
365 standby to upgrade other standbys, as long as the upgraded standby
366 has not been started.
368 What this does is to record the links created by
369 <span class="application">pg_upgrade</span>'s link mode that connect files in the
370 old and new clusters on the primary server. It then finds matching
371 files in the standby's old cluster and creates links for them in the
372 standby's new cluster. Files that were not linked on the primary
373 are copied from the primary to the standby. (They are usually
374 small.) This provides rapid standby upgrades. Unfortunately,
375 <span class="application">rsync</span> needlessly copies files associated with
376 temporary and unlogged tables because these files don't normally
377 exist on standby servers.
379 If you have tablespaces, you will need to run a similar
380 <span class="application">rsync</span> command for each tablespace directory, e.g.:
382 </p><pre class="programlisting">
383 rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_12_201909212 \
384 /vol1/pg_tblsp/PG_18_202307071 standby.example.com:/vol1/pg_tblsp
387 If you have relocated <code class="filename">pg_wal</code> outside the data
388 directories, <span class="application">rsync</span> must be run on those directories
390 </p></li><li class="step"><p class="title"><strong>Configure streaming replication and log-shipping standby servers</strong></p><p>
391 Configure the servers for log shipping. (You do not need to run
392 <code class="function">pg_backup_start()</code> and <code class="function">pg_backup_stop()</code>
393 or take a file system backup as the standbys are still synchronized
394 with the primary.) If the old primary is prior to version 17.0, then no
395 slots on the primary are copied to the new standby, so all the slots on
396 the old standby must be recreated manually. If the old primary is
397 version 17.0 or later, then only logical slots on the primary are copied
398 to the new standby, but other slots on the old standby are not copied,
399 so must be recreated manually.
400 </p></li></ol></div></li><li class="step"><p class="title"><strong>Restore <code class="filename">pg_hba.conf</code></strong></p><p>
401 If you modified <code class="filename">pg_hba.conf</code>, restore its original settings.
402 It might also be necessary to adjust other configuration files in the new
403 cluster to match the old cluster, e.g., <code class="filename">postgresql.conf</code>
404 (and any files included by it), <code class="filename">postgresql.auto.conf</code>.
405 </p></li><li class="step"><p class="title"><strong>Start the new server</strong></p><p>
406 The new server can now be safely started, and then any
407 <span class="application">rsync</span>'ed standby servers.
408 </p></li><li class="step"><p class="title"><strong>Post-upgrade processing</strong></p><p>
409 If any post-upgrade processing is required, pg_upgrade will issue
410 warnings as it completes. It will also generate script files that must
411 be run by the administrator. The script files will connect to each
412 database that needs post-upgrade processing. Each script should be
415 </p><pre class="programlisting">
416 psql --username=postgres --file=script.sql postgres
419 The scripts can be run in any order and can be deleted once they have
421 </p><div class="caution"><h3 class="title">Caution</h3><p>
422 In general it is unsafe to access tables referenced in rebuild scripts
423 until the rebuild scripts have run to completion; doing so could yield
424 incorrect results or poor performance. Tables not referenced in rebuild
425 scripts can be accessed immediately.
426 </p></div></li><li class="step"><p class="title"><strong>Statistics</strong></p><p>
427 Unless the <code class="option">--no-statistics</code> option is specified,
428 <code class="command">pg_upgrade</code> will transfer most optimizer statistics
429 from the old cluster to the new cluster. However, some statistics may
430 not be transferred, such as those created explicitly with <a class="xref" href="sql-createstatistics.html" title="CREATE STATISTICS"><span class="refentrytitle">CREATE STATISTICS</span></a> or custom statistics added by an
433 Because not all statistics are transferred by
434 <code class="command">pg_upgrade</code>, you will be instructed to run commands to
435 regenerate that information at the end of the upgrade. You might need to
436 set connection parameters to match your new cluster.
439 <code class="command">vacuumdb --all --analyze-in-stages --missing-stats-only</code>
440 to quickly generate minimal optimizer statistics for relations without
441 any. Then, use <code class="command">vacuumdb --all --analyze-only</code> to ensure
442 all relations have updated cumulative statistics for triggering vacuum and
443 analyze. For both commands, the use of <code class="option">--jobs</code> can speed
445 If <code class="varname">vacuum_cost_delay</code> is set to a non-zero
446 value, this can be overridden to speed up statistics generation
447 using <code class="envar">PGOPTIONS</code>, e.g., <code class="literal">PGOPTIONS='-c
448 vacuum_cost_delay=0' vacuumdb ...</code>.
449 </p></li><li class="step"><p class="title"><strong>Delete old cluster</strong></p><p>
450 Once you are satisfied with the upgrade, you can delete the old
451 cluster's data directories by running the script mentioned when
452 <code class="command">pg_upgrade</code> completes. (Automatic deletion is not
453 possible if you have user-defined tablespaces inside the old data
454 directory.) You can also delete the old installation directories
455 (e.g., <code class="filename">bin</code>, <code class="filename">share</code>).
456 </p></li><li class="step" id="PGUPGRADE-STEP-REVERT"><p class="title"><strong>Reverting to old cluster</strong></p><p>
457 If, after running <code class="command">pg_upgrade</code>, you wish to revert to the old cluster,
458 there are several options:
460 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
461 If the <code class="option">--check</code> option was used, the old cluster
462 was unmodified; it can be restarted.
463 </p></li><li class="listitem"><p>
464 If neither <code class="option">--link</code> nor <code class="option">--swap</code> was
465 used, the old cluster was unmodified; it can be restarted.
466 </p></li><li class="listitem"><p>
467 If the <code class="option">--link</code> option was used, the data
468 files might be shared between the old and new cluster:
470 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
471 If <code class="command">pg_upgrade</code> aborted before linking started,
472 the old cluster was unmodified; it can be restarted.
473 </p></li><li class="listitem"><p>
474 If you did <span class="emphasis"><em>not</em></span> start the new cluster, the old
475 cluster was unmodified except that, when linking started, a
476 <code class="literal">.old</code> suffix was appended to
477 <code class="filename">$PGDATA/global/pg_control</code>. To reuse the old
478 cluster, remove the <code class="filename">.old</code> suffix from
479 <code class="filename">$PGDATA/global/pg_control</code>; you can then restart
481 </p></li><li class="listitem"><p>
482 If you did start the new cluster, it has written to shared files
483 and it is unsafe to use the old cluster. The old cluster will
484 need to be restored from backup in this case.
485 </p></li></ul></div></li><li class="listitem"><p>
486 If the <code class="option">--swap</code> option was used, the old cluster might
487 be destructively modified:
489 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
490 If <code class="command">pg_upgrade</code> aborts before reporting that the
491 old cluster is no longer safe to start, the old cluster was
492 unmodified; it can be restarted.
493 </p></li><li class="listitem"><p>
494 If <code class="command">pg_upgrade</code> has reported that the old cluster
495 is no longer safe to start, the old cluster was destructively
496 modified. The old cluster will need to be restored from backup in
498 </p></li></ul></div><p>
499 </p></li></ul></div></li></ol></div></div><div class="refsect1" id="id-1.9.5.13.8"><h2>Environment</h2><p>
500 Some environment variables can be used to provide defaults for command-line options:
502 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="envar">PGBINOLD</code></span></dt><dd><p>
503 The old PostgreSQL executable directory; option
504 <code class="option">-b</code>/<code class="option">--old-bindir</code>.
505 </p></dd><dt><span class="term"><code class="envar">PGBINNEW</code></span></dt><dd><p>
506 The new PostgreSQL executable directory; option
507 <code class="option">-B</code>/<code class="option">--new-bindir</code>.
508 </p></dd><dt><span class="term"><code class="envar">PGDATAOLD</code></span></dt><dd><p>
509 The old database cluster configuration directory; option
510 <code class="option">-d</code>/<code class="option">--old-datadir</code>.
511 </p></dd><dt><span class="term"><code class="envar">PGDATANEW</code></span></dt><dd><p>
512 The new database cluster configuration directory; option
513 <code class="option">-D</code>/<code class="option">--new-datadir</code>.
514 </p></dd><dt><span class="term"><code class="envar">PGPORTOLD</code></span></dt><dd><p>
515 The old cluster port number; option
516 <code class="option">-p</code>/<code class="option">--old-port</code>.
517 </p></dd><dt><span class="term"><code class="envar">PGPORTNEW</code></span></dt><dd><p>
518 The new cluster port number; option
519 <code class="option">-P</code>/<code class="option">--new-port</code>.
520 </p></dd><dt><span class="term"><code class="envar">PGSOCKETDIR</code></span></dt><dd><p>
521 Directory to use for postmaster sockets during upgrade; option
522 <code class="option">-s</code>/<code class="option">--socketdir</code>.
523 </p></dd><dt><span class="term"><code class="envar">PGUSER</code></span></dt><dd><p>
524 Cluster's install user name; option
525 <code class="option">-U</code>/<code class="option">--username</code>.
526 </p></dd></dl></div><p>
527 </p></div><div class="refsect1" id="id-1.9.5.13.9"><h2>Notes</h2><p>
528 <span class="application">pg_upgrade</span> creates various working files, such
529 as schema dumps, stored within <code class="filename">pg_upgrade_output.d</code> in
530 the directory of the new cluster. Each run creates a new subdirectory named
531 with a timestamp formatted as per ISO 8601
532 (<code class="literal">%Y%m%dT%H%M%S</code>), where all its generated files are
534 <code class="filename">pg_upgrade_output.d</code> and its contained files will be
535 removed automatically if <span class="application">pg_upgrade</span> completes
536 successfully; but in the event of trouble, the files there may provide
537 useful debugging information.
539 <span class="application">pg_upgrade</span> launches short-lived postmasters in
540 the old and new data directories. Temporary Unix socket files for
541 communication with these postmasters are, by default, made in the current
542 working directory. In some situations the path name for the current
543 directory might be too long to be a valid socket name. In that case you
544 can use the <code class="option">-s</code> option to put the socket files in some
545 directory with a shorter path name. For security, be sure that that
546 directory is not readable or writable by any other users.
547 (This is not supported on Windows.)
549 All failure, rebuild, and reindex cases will be reported by
550 <span class="application">pg_upgrade</span> if they affect your installation;
551 post-upgrade scripts to rebuild tables and indexes will be
552 generated automatically. If you are trying to automate the upgrade
553 of many clusters, you should find that clusters with identical database
554 schemas require the same post-upgrade steps for all cluster upgrades;
555 this is because the post-upgrade steps are based on the database
556 schemas, and not user data.
558 For deployment testing, create a schema-only copy of the old cluster,
559 insert dummy data, and upgrade that.
561 <span class="application">pg_upgrade</span> does not support upgrading of databases
562 containing table columns using these <code class="type">reg*</code> OID-referencing system data types:
563 </p><table border="0" summary="Simple list" class="simplelist"><tr><td><code class="type">regcollation</code></td></tr><tr><td><code class="type">regconfig</code></td></tr><tr><td><code class="type">regdictionary</code></td></tr><tr><td><code class="type">regnamespace</code></td></tr><tr><td><code class="type">regoper</code></td></tr><tr><td><code class="type">regoperator</code></td></tr><tr><td><code class="type">regproc</code></td></tr><tr><td><code class="type">regprocedure</code></td></tr></table><p>
564 (<code class="type">regclass</code>, <code class="type">regrole</code>, and <code class="type">regtype</code> can be upgraded.)
566 If you want to use link mode and you do not want your old cluster
567 to be modified when the new cluster is started, consider using the clone mode.
568 If that is not available, make a copy of the
569 old cluster and upgrade that in link mode. To make a valid copy
570 of the old cluster, use <code class="command">rsync</code> to create a dirty
571 copy of the old cluster while the server is running, then shut down
572 the old server and run <code class="command">rsync --checksum</code> again to update the
573 copy with any changes to make it consistent. (<code class="option">--checksum</code>
574 is necessary because <code class="command">rsync</code> only has file modification-time
575 granularity of one second.) You might want to exclude some
576 files, e.g., <code class="filename">postmaster.pid</code>, as documented in <a class="xref" href="continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP" title="25.3.4. Making a Base Backup Using the Low Level API">Section 25.3.4</a>. If your file system supports
577 file system snapshots or copy-on-write file copies, you can use that
578 to make a backup of the old cluster and tablespaces, though the snapshot
579 and copies must be created simultaneously or while the database server
581 </p></div><div class="refsect1" id="id-1.9.5.13.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="app-initdb.html" title="initdb"><span class="refentrytitle"><span class="application">initdb</span></span></a>, <a class="xref" href="app-pg-ctl.html" title="pg_ctl"><span class="refentrytitle"><span class="application">pg_ctl</span></span></a>, <a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle"><span class="application">pg_dump</span></span></a>, <a class="xref" href="app-postgres.html" title="postgres"><span class="refentrytitle"><span class="application">postgres</span></span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgtesttiming.html" title="pg_test_timing">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="reference-server.html" title="PostgreSQL Server Applications">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pgwaldump.html" title="pg_waldump">Next</a></td></tr><tr><td width="40%" align="left" valign="top"><span class="application">pg_test_timing</span> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_waldump</span></td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob