1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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>19.6. Upgrading a PostgreSQL Cluster</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="server-shutdown.html" title="19.5. Shutting Down the Server" /><link rel="next" href="preventing-server-spoofing.html" title="19.7. Preventing Server Spoofing" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">19.6. Upgrading a <span class="productname">PostgreSQL</span> Cluster</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="server-shutdown.html" title="19.5. Shutting Down the Server">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime.html" title="Chapter 19. Server Setup and Operation">Up</a></td><th width="60%" align="center">Chapter 19. Server Setup and Operation</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.6 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="preventing-server-spoofing.html" title="19.7. Preventing Server Spoofing">Next</a></td></tr></table><hr /></div><div class="sect1" id="UPGRADING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.6. Upgrading a <span class="productname">PostgreSQL</span> Cluster</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="upgrading.html#UPGRADING-VIA-PGDUMPALL">19.6.1. Upgrading Data via <span class="application">pg_dumpall</span></a></span></dt><dt><span class="sect2"><a href="upgrading.html#UPGRADING-VIA-PG-UPGRADE">19.6.2. Upgrading Data via <span class="application">pg_upgrade</span></a></span></dt><dt><span class="sect2"><a href="upgrading.html#UPGRADING-VIA-REPLICATION">19.6.3. Upgrading Data via Replication</a></span></dt></dl></div><a id="id-1.6.6.9.2" class="indexterm"></a><a id="id-1.6.6.9.3" class="indexterm"></a><p>
This section discusses how to upgrade your database data from one
<span class="productname">PostgreSQL</span> release to a newer one.
</p><p>
Current <span class="productname">PostgreSQL</span> version numbers consist of a
major and a minor version number. For example, in the version number 10.1,
the 10 is the major version number and the 1 is the minor version number,
meaning this would be the first minor release of the major release 10. For
releases before <span class="productname">PostgreSQL</span> version 10.0, version
numbers consist of three numbers, for example, 9.5.3. In those cases, the
major version consists of the first two digit groups of the version number,
e.g., 9.5, and the minor version is the third number, e.g., 3, meaning this
would be the third minor release of the major release 9.5.
</p><p>
Minor releases never change the internal storage format and are always
compatible with earlier and later minor releases of the same major version
number. For example, version 10.1 is compatible with version 10.0 and
version 10.6. Similarly, for example, 9.5.3 is compatible with 9.5.0,
9.5.1, and 9.5.6. To update between compatible versions, you simply
replace the executables while the server is down and restart the server.
The data directory remains unchanged — minor upgrades are that
simple.
</p><p>
For <span class="emphasis"><em>major</em></span> releases of <span class="productname">PostgreSQL</span>, the
internal data storage format is subject to change, thus complicating
upgrades. The traditional method for moving data to a new major version
is to dump and restore the database, though this can be slow. A
faster method is <a class="xref" href="pgupgrade.html" title="pg_upgrade"><span class="refentrytitle"><span class="application">pg_upgrade</span></span></a>. Replication methods are
also available, as discussed below.
(If you are using a pre-packaged version
of <span class="productname">PostgreSQL</span>, it may provide scripts to
assist with major version upgrades. Consult the package-level
documentation for details.)
</p><p>
New major versions also typically introduce some user-visible
incompatibilities, so application programming changes might be required.
All user-visible changes are listed in the release notes (<a class="xref" href="release.html" title="Appendix E. Release Notes">Appendix E</a>); pay particular attention to the section
labeled "Migration". Though you can upgrade from one major version
to another without upgrading to intervening versions, you should read
the major release notes of all intervening versions.
</p><p>
Cautious users will want to test their client applications on the new
version before switching over fully; therefore, it's often a good idea to
set up concurrent installations of old and new versions. When
testing a <span class="productname">PostgreSQL</span> major upgrade, consider the
following categories of possible changes:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Administration</span></dt><dd><p>
The capabilities available for administrators to monitor and control
the server often change and improve in each major release.
</p></dd><dt><span class="term">SQL</span></dt><dd><p>
Typically this includes new SQL command capabilities and not changes
in behavior, unless specifically mentioned in the release notes.
</p></dd><dt><span class="term">Library API</span></dt><dd><p>
Typically libraries like <span class="application">libpq</span> only add new
functionality, again unless mentioned in the release notes.
</p></dd><dt><span class="term">System Catalogs</span></dt><dd><p>
System catalog changes usually only affect database management tools.
</p></dd><dt><span class="term">Server C-language API</span></dt><dd><p>
This involves changes in the backend function API, which is written
in the C programming language. Such changes affect code that
references backend functions deep inside the server.
</p></dd></dl></div><div class="sect2" id="UPGRADING-VIA-PGDUMPALL"><div class="titlepage"><div><div><h3 class="title">19.6.1. Upgrading Data via <span class="application">pg_dumpall</span></h3></div></div></div><p>
One upgrade method is to dump data from one major version of
<span class="productname">PostgreSQL</span> and restore it in another — to do
this, you must use a <span class="emphasis"><em>logical</em></span> backup tool like
<span class="application">pg_dumpall</span>; file system
level backup methods will not work. (There are checks in place that prevent
you from using a data directory with an incompatible version of
<span class="productname">PostgreSQL</span>, so no great harm can be done by
trying to start the wrong server version on a data directory.)
</p><p>
It is recommended that you use the <span class="application">pg_dump</span> and
<span class="application">pg_dumpall</span> programs from the <span class="emphasis"><em>newer</em></span>
version of
<span class="productname">PostgreSQL</span>, to take advantage of enhancements
that might have been made in these programs. Current releases of the
dump programs can read data from any server version back to 9.2.
</p><p>
These instructions assume that your existing installation is under the
<code class="filename">/usr/local/pgsql</code> directory, and that the data area is in
<code class="filename">/usr/local/pgsql/data</code>. Substitute your paths
appropriately.
</p><div class="procedure"><ol class="procedure" type="1"><li class="step"><p>
If making a backup, make sure that your database is not being updated.
This does not affect the integrity of the backup, but the changed
data would of course not be included. If necessary, edit the
permissions in the file <code class="filename">/usr/local/pgsql/data/pg_hba.conf</code>
(or equivalent) to disallow access from everyone except you.
See <a class="xref" href="client-authentication.html" title="Chapter 21. Client Authentication">Chapter 21</a> for additional information on
access control.
</p><p>
<a id="id-1.6.6.9.11.5.1.2.1" class="indexterm"></a>
To back up your database installation, type:
</p><pre class="screen">
<strong class="userinput"><code>pg_dumpall > <em class="replaceable"><code>outputfile</code></em></code></strong>
</pre><p>
</p><p>
To make the backup, you can use the <span class="application">pg_dumpall</span>
command from the version you are currently running; see <a class="xref" href="backup-dump.html#BACKUP-DUMP-ALL" title="26.1.2. Using pg_dumpall">Section 26.1.2</a> for more details. For best
results, however, try to use the <span class="application">pg_dumpall</span>
command from <span class="productname">PostgreSQL</span> 15.6,
since this version contains bug fixes and improvements over older
versions. While this advice might seem idiosyncratic since you
haven't installed the new version yet, it is advisable to follow
it if you plan to install the new version in parallel with the
old version. In that case you can complete the installation
normally and transfer the data later. This will also decrease
the downtime.
</p></li><li class="step"><p>
Shut down the old server:
</p><pre class="screen">
<strong class="userinput"><code>pg_ctl stop</code></strong>
</pre><p>
On systems that have <span class="productname">PostgreSQL</span> started at boot time,
there is probably a start-up file that will accomplish the same thing. For
example, on a <span class="systemitem">Red Hat Linux</span> system one
might find that this works:
</p><pre class="screen">
<strong class="userinput"><code>/etc/rc.d/init.d/postgresql stop</code></strong>
</pre><p>
See <a class="xref" href="runtime.html" title="Chapter 19. Server Setup and Operation">Chapter 19</a> for details about starting and
stopping the server.
</p></li><li class="step"><p>
If restoring from backup, rename or delete the old installation
directory if it is not version-specific. It is a good idea to
rename the directory, rather than
delete it, in case you have trouble and need to revert to it. Keep
in mind the directory might consume significant disk space. To rename
the directory, use a command like this:
</p><pre class="screen">
<strong class="userinput"><code>mv /usr/local/pgsql /usr/local/pgsql.old</code></strong>
</pre><p>
(Be sure to move the directory as a single unit so relative paths
remain unchanged.)
</p></li><li class="step"><p>
Install the new version of <span class="productname">PostgreSQL</span> as
outlined in <a class="xref" href="install-procedure.html" title="17.4. Installation Procedure">Section 17.4</a>.
</p></li><li class="step"><p>
Create a new database cluster if needed. Remember that you must
execute these commands while logged in to the special database user
account (which you already have if you are upgrading).
</p><pre class="programlisting">
<strong class="userinput"><code>/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data</code></strong>
</pre><p>
</p></li><li class="step"><p>
Restore your previous <code class="filename">pg_hba.conf</code> and any
<code class="filename">postgresql.conf</code> modifications.
</p></li><li class="step"><p>
Start the database server, again using the special database user
account:
</p><pre class="programlisting">
<strong class="userinput"><code>/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data</code></strong>
</pre><p>
</p></li><li class="step"><p>
Finally, restore your data from backup with:
</p><pre class="screen">
<strong class="userinput"><code>/usr/local/pgsql/bin/psql -d postgres -f <em class="replaceable"><code>outputfile</code></em></code></strong>
</pre><p>
using the <span class="emphasis"><em>new</em></span> <span class="application">psql</span>.
</p></li></ol></div><p>
The least downtime can be achieved by installing the new server in
a different directory and running both the old and the new servers
in parallel, on different ports. Then you can use something like:
</p><pre class="programlisting">
pg_dumpall -p 5432 | psql -d postgres -p 5433
</pre><p>
to transfer your data.
</p></div><div class="sect2" id="UPGRADING-VIA-PG-UPGRADE"><div class="titlepage"><div><div><h3 class="title">19.6.2. Upgrading Data via <span class="application">pg_upgrade</span></h3></div></div></div><p>
The <a class="xref" href="pgupgrade.html" title="pg_upgrade"><span class="refentrytitle"><span class="application">pg_upgrade</span></span></a> module allows an installation to
be migrated in-place from one major <span class="productname">PostgreSQL</span>
version to another. Upgrades can be performed in minutes,
particularly with <code class="option">--link</code> mode. It requires steps similar to
<span class="application">pg_dumpall</span> above, e.g., starting/stopping the server,
running <span class="application">initdb</span>. The <span class="application">pg_upgrade</span> <a class="link" href="pgupgrade.html" title="pg_upgrade">documentation</a> outlines the necessary steps.
</p></div><div class="sect2" id="UPGRADING-VIA-REPLICATION"><div class="titlepage"><div><div><h3 class="title">19.6.3. Upgrading Data via Replication</h3></div></div></div><p>
It is also possible to use logical replication methods to create a standby
server with the updated version of <span class="productname">PostgreSQL</span>.
This is possible because logical replication supports
replication between different major versions of
<span class="productname">PostgreSQL</span>. The standby can be on the same computer or
a different computer. Once it has synced up with the primary server
(running the older version of <span class="productname">PostgreSQL</span>), you can
switch primaries and make the standby the primary and shut down the older
database instance. Such a switch-over results in only several seconds
of downtime for an upgrade.
</p><p>
This method of upgrading can be performed using the built-in logical
replication facilities as well as using external logical replication
systems such as <span class="productname">pglogical</span>,
<span class="productname">Slony</span>, <span class="productname">Londiste</span>, and
<span class="productname">Bucardo</span>.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="server-shutdown.html" title="19.5. Shutting Down the Server">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime.html" title="Chapter 19. Server Setup and Operation">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="preventing-server-spoofing.html" title="19.7. Preventing Server Spoofing">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.5. Shutting Down the Server </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 19.7. Preventing Server Spoofing</td></tr></table></div></body></html>
|
