summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/upgrading.html
blob: b4f2321b4e893fd0404a7d0c0afa61ad622dd435 (plain)
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.7 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 &gt; <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.7,
      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.7 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 19.7. Preventing Server Spoofing</td></tr></table></div></body></html>