-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathmigrate-from-older-versions.html
More file actions
137 lines (136 loc) · 16.8 KB
/
Copy pathmigrate-from-older-versions.html
File metadata and controls
137 lines (136 loc) · 16.8 KB
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
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>Upgrade PostgreSQL on Windows — Dump/Restore and pg_upgrade Guide</title>
<meta name="description" content="How to upgrade PostgreSQL on Windows 10 and 11. Safe dump and restore method, pg_upgrade in-place upgrade, post-migration checks and version comparison.">
<link rel="canonical" href="https://postgre-sql.github.io/migrate-from-older-versions.html">
<meta name="last-modified" content="2026-06-08">
<meta property="og:title" content="Upgrade PostgreSQL on Windows — Dump/Restore and pg_upgrade Guide">
<meta property="og:description" content="How to upgrade PostgreSQL on Windows 10 and 11. Safe dump and restore method, pg_upgrade in-place upgrade, post-migration checks and version comparison.">
<meta property="og:type" content="article">
<meta property="og:url" content="https://postgre-sql.github.io/migrate-from-older-versions.html">
<meta property="og:image" content="https://postgre-sql.github.io/og-image.svg">
<meta name="twitter:card" content="summary_large_image">
<link rel="icon" href="/favicon.svg">
<link rel="stylesheet" href="/style.css">
</head>
<body>
<nav class="site-nav"><div class="wrap"><div class="nav-inner">
<a href="/index.html" class="nav-logo"><div class="nav-logo-mark">Pg</div><span class="nav-logo-name">PostgreSQL</span><span class="nav-logo-sub">for Windows</span></a>
<div class="nav-links">
<a href="/index.html" id="nl-home">Home</a>
<a href="/download-windows.html" id="nl-dl">Download</a>
<a href="/offline-installer.html" id="nl-off">Offline</a>
<a href="/configure-windows-service.html" id="nl-svc">Service</a>
<a href="/fix-path-issues.html" id="nl-path">PATH fix</a>
<a href="/pgadmin-windows.html" id="nl-pgadmin">pgAdmin</a>
<a href="/postgresql-windows-faq.html" id="nl-faq">FAQ</a>
</div>
<a href="/download-windows.html" class="nav-cta"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2.5" stroke-linecap="round" stroke-linejoin="round"><path d="M21 15v4a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2v-4"/><polyline points="7 10 12 15 17 10"/><line x1="12" y1="15" x2="12" y2="3"/></svg> Download</a>
</div></div></nav>
<div class="page-hero"><div class="wrap">
<nav class="breadcrumb"><a href="/index.html">home</a> <span>/</span> <span>migrate-from-older-versions</span></nav>
<span class="page-tag">Upgrade guide</span>
<h1>Upgrade PostgreSQL on Windows — <span>dump/restore & pg_upgrade guide</span></h1>
<p class="page-lead">Two methods to upgrade PostgreSQL on Windows: the safe dump/restore path and the faster pg_upgrade in-place upgrade. Covers pre-checks, execution and post-migration validation.</p>
</div></div>
<div class="wrap"><div class="content-layout">
<main><div class="callout callout-warn"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="1.8" stroke-linecap="round" stroke-linejoin="round"><path d="M10.29 3.86L1.82 18a2 2 0 0 0 1.71 3h16.94a2 2 0 0 0 1.71-3L13.71 3.86a2 2 0 0 0-3.42 0z"/><line x1="12" y1="9" x2="12" y2="13"/><line x1="12" y1="17" x2="12.01" y2="17"/></svg><span>Always back up your databases before any upgrade. A failed upgrade without a backup can result in data loss.</span></div>
<div class="section" aria-labelledby="methods-h2"><span class="section-label">Two approaches</span><h2 id="methods-h2">Upgrade methods compared</h2>
<div class="tbl-wrap"><table><thead><tr><th>Method</th><th>Downtime</th><th>Risk</th><th>Best for</th></tr></thead><tbody>
<tr><td><strong>Dump / restore</strong></td><td>Minutes to hours</td><td>Low</td><td>Most users — safest path</td></tr>
<tr><td><strong>pg_upgrade</strong></td><td>Minutes (large DBs)</td><td>Medium</td><td>Large databases where dump is too slow</td></tr>
</tbody></table></div>
</div>
<div class="section" aria-labelledby="dump-h2"><span class="section-label">Method A — recommended</span><h2 id="dump-h2">Dump and restore (safest path)</h2><ul class="steps">
<li class="step"><div class="step-n">1</div><div><h3>Dump all databases from old version</h3>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">cmd.exe</span></div><div class="term-body"><div><span class="t-cm"># Dump all databases to a file:</span></div><div><span class="t-p">C:\></span> <span class="t-c">pg_dumpall -U postgres -f C:\backup\all_dbs.sql</span></div><div><span class="t-o">Password:</span></div><div><span class="t-cm"># Wait for completion (can take minutes to hours)</span></div></div></div>
</div></li>
<li class="step"><div class="step-n">2</div><div><h3>Install the new PostgreSQL version</h3><p>Download and run the new installer. Install to a different directory (e.g. <code>C:\Program Files\PostgreSQL8</code>). Use a different port (e.g. 5433) during setup to avoid conflict with the old version still running.</p></div></li>
<li class="step"><div class="step-n">3</div><div><h3>Restore to the new version</h3>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">cmd.exe</span></div><div class="term-body"><div><span class="t-cm"># Connect to new instance (port 5433) and restore:</span></div><div><span class="t-p">C:\></span> <span class="t-c">psql -U postgres -p 5433 -f C:\backup\all_dbs.sql</span></div><div><span class="t-o">Password:</span></div></div></div>
</div></li>
<li class="step"><div class="step-n">4</div><div><h3>Verify data and switch ports</h3><p>Connect to the new instance, verify your data looks correct, then stop the old service, change the new service port to 5432, and start it. Update any connection strings in your applications.</p></div></li>
<li class="step"><div class="step-n">5</div><div><h3>Uninstall the old version</h3><p>Once everything is confirmed working, uninstall the old PostgreSQL version from Settings → Apps.</p></div></li>
</ul></div>
<div class="section" aria-labelledby="pgupgrade-h2"><span class="section-label">Method B</span><h2 id="pgupgrade-h2">pg_upgrade (in-place upgrade)</h2>
<p>pg_upgrade copies or hard-links data files from the old cluster to the new one. It is faster than dump/restore for large databases but more complex to execute correctly.</p>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">cmd.exe — Administrator</span></div><div class="term-body"><div><span class="t-cm"># Run pg_upgrade from the new version bin directory:</span></div><div><span class="t-p">C:\></span> <span class="t-c">"C:\Program Files\PostgreSQL\18\bin\pg_upgrade.exe"</span></div><div><span class="t-c"> -b "C:\Program Files\PostgreSQL\16\bin"</span></div><div><span class="t-c"> -B "C:\Program Files\PostgreSQL\18\bin"</span></div><div><span class="t-c"> -d "C:\Program Files\PostgreSQL\16\data"</span></div><div><span class="t-c"> -D "C:\Program Files\PostgreSQL\18\data"</span></div><div><span class="t-c"> --check</span></div><div><span class="t-cm"># Run without --check to perform the actual upgrade</span></div></div></div>
<div class="callout callout-warn"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="1.8" stroke-linecap="round" stroke-linejoin="round"><path d="M10.29 3.86L1.82 18a2 2 0 0 0 1.71 3h16.94a2 2 0 0 0 1.71-3L13.71 3.86a2 2 0 0 0-3.42 0z"/><line x1="12" y1="9" x2="12" y2="13"/><line x1="12" y1="17" x2="12.01" y2="17"/></svg><span>Stop both PostgreSQL services before running pg_upgrade. The --check flag runs a dry run without modifying data.</span></div>
</div>
<div class="section" aria-labelledby="post-h2"><span class="section-label">Post-upgrade</span><h2 id="post-h2">Post-migration checks</h2>
<div class="term"><div class="term-bar"><div class="term-dot" style="background:#ff5f56"></div><div class="term-dot" style="background:#ffbd2e"></div><div class="term-dot" style="background:#27c93f"></div><span class="term-label">cmd.exe</span></div><div class="term-body"><div><span class="t-cm"># Check new version:</span></div><div><span class="t-p">C:\></span> <span class="t-c">psql -U postgres -c "SELECT version();"</span></div><div><span class="t-v">PostgreSQL 18.3...</span></div><div></div><div><span class="t-cm"># List all databases:</span></div><div><span class="t-p">C:\></span> <span class="t-c">psql -U postgres -l</span></div><div></div><div><span class="t-cm"># Run ANALYZE to update statistics:</span></div><div><span class="t-p">C:\></span> <span class="t-c">vacuumdb -U postgres --all --analyze</span></div></div></div>
</div>
<div class="section" aria-labelledby="faq-h2"><span class="section-label">FAQ</span><h2 id="faq-h2">Migration questions</h2><div class="faq">
<div class="faq-item"><details><summary>Can I upgrade from PostgreSQL 14 directly to 18?</summary><div class="faq-ans">Yes. Both methods (dump/restore and pg_upgrade) support skipping major versions. pg_upgrade can go from any supported older version directly to the new one. The dump/restore method always works regardless of version gap.</div></details></div>
<div class="faq-item"><details><summary>How long does the upgrade take?</summary><div class="faq-ans">Dump/restore time scales with database size — expect 10-30 minutes per 10 GB for typical workloads. pg_upgrade with hard-linking is nearly instant regardless of size, but requires additional steps and carries more risk.</div></details></div>
<div class="faq-item"><details><summary>Do my extensions need to be upgraded too?</summary><div class="faq-ans">After upgrading, run <code>ALTER EXTENSION extension_name UPDATE;</code> for each extension in each database. Or use <code>vacuumdb --all --analyze</code> which also handles extension updates in recent versions.</div></details></div>
</div></div>
<div class="cta-banner"><div><h2>Need to back up first?</h2><p>pg_dump and pg_restore guide for Windows.</p></div><a href="/pg-dump-restore-windows.html" class="btn-white"><svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2.5" stroke-linecap="round" stroke-linejoin="round"><path d="M21 15v4a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2v-4"/><polyline points="7 10 12 15 17 10"/><line x1="12" y1="15" x2="12" y2="3"/></svg>Backup & restore</a></div>
<div style="margin-bottom:32px"><span class="section-label">Related guides</span><div class="rel-grid"><a href="/pg-dump-restore-windows.html" class="rel-card"><div class="rel-title">Backup & restore →</div><div class="rel-sub">pg_dump guide</div></a><a href="/download-windows.html" class="rel-card"><div class="rel-title">Download →</div><div class="rel-sub">get new version</div></a><a href="/configure-windows-service.html" class="rel-card"><div class="rel-title">Windows service →</div><div class="rel-sub">service management</div></a></div></div></main>
<aside class="content-sidebar"><div class="sb-card"><div class="version-badge-label">Latest stable</div><div class="version-badge-val">18.3</div><div class="version-badge-sub">Released May 2026</div></div><div class="sb-card"><div class="sb-card-title">Install & setup</div><ul class="sb-links"><li><a href="/download-windows.html">Download PostgreSQL</a></li><li><a href="/offline-installer.html">Offline installer</a></li><li><a href="/configure-windows-service.html">Windows service</a></li><li><a href="/fix-path-issues.html">Fix PATH / psql</a></li><li><a href="/pgadmin-windows.html">pgAdmin 4</a></li><li><a href="/install-postgresql-windows-server.html">Windows Server</a></li></ul></div><div class="sb-card"><div class="sb-card-title">Configuration</div><ul class="sb-links"><li><a href="/postgresql-config-windows.html">postgresql.conf</a></li><li><a href="/postgresql-port-5432.html">Port 5432</a></li><li><a href="/postgresql-allow-remote-connections.html">Remote connections</a></li><li><a href="/postgresql-password-windows.html">Reset password</a></li></ul></div><div class="sb-card"><div class="sb-card-title">Tools & help</div><ul class="sb-links"><li><a href="/psql-commands-windows.html">psql commands</a></li><li><a href="/pg-dump-restore-windows.html">Backup & restore</a></li><li><a href="/postgresql-python-windows.html">Python / psycopg2</a></li><li><a href="/odbc-driver-x64-x86.html">ODBC driver</a></li><li><a href="/postgresql-not-starting-windows.html">Service not starting</a></li><li><a href="/postgresql-uninstall-windows.html">Uninstall</a></li><li><a href="/postgresql-windows-faq.html">FAQ</a></li></ul></div></aside>
</div></div>
<footer class="site-footer"><div class="wrap">
<div class="footer-inner">
<div>
<div class="footer-logo"><div class="footer-logo-mark">Pg</div><span class="footer-logo-name">PostgreSQL for Windows</span></div>
<p class="footer-tagline">Unofficial Windows guide for PostgreSQL — download, install, configure, troubleshoot.</p>
</div>
<div>
<div class="footer-col-title">Install & setup</div>
<ul class="footer-links">
<li><a href="/download-windows.html">Download PostgreSQL</a></li>
<li><a href="/offline-installer.html">Offline installer</a></li>
<li><a href="/configure-windows-service.html">Windows service</a></li>
<li><a href="/fix-path-issues.html">Fix PATH / psql</a></li>
<li><a href="/pgadmin-windows.html">pgAdmin 4</a></li>
</ul>
</div>
<div>
<div class="footer-col-title">Configuration</div>
<ul class="footer-links">
<li><a href="/postgresql-config-windows.html">postgresql.conf</a></li>
<li><a href="/postgresql-port-5432.html">Port 5432</a></li>
<li><a href="/postgresql-allow-remote-connections.html">Remote connections</a></li>
<li><a href="/postgresql-password-windows.html">Reset password</a></li>
<li><a href="/install-postgresql-windows-server.html">Windows Server</a></li>
</ul>
</div>
<div>
<div class="footer-col-title">Tools & help</div>
<ul class="footer-links">
<li><a href="/psql-commands-windows.html">psql commands</a></li>
<li><a href="/pg-dump-restore-windows.html">Backup & restore</a></li>
<li><a href="/postgresql-not-starting-windows.html">Service not starting</a></li>
<li><a href="/postgresql-uninstall-windows.html">Uninstall</a></li>
<li><a href="/postgresql-windows-faq.html">FAQ</a></li>
</ul>
</div>
</div>
<div class="footer-bottom">
<p>Not affiliated with the PostgreSQL Global Development Group. Unofficial community guide.</p>
<p><strong>Affiliate disclosure:</strong> Download links may be partner links.</p>
</div>
</div></footer>
<div class="cookie-bar" id="cookieBar" style="display:none">
<p>We use Google Analytics for anonymous traffic data. <a href="/privacy.html">Privacy policy</a></p>
<div class="cookie-btns"><button class="cookie-accept" id="cookieAccept">Accept</button><button class="cookie-decline" id="cookieDecline">Decline</button></div>
</div>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-MHLW57MR8Q"></script>
<script>
window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments)}
gtag('consent','default',{analytics_storage:'denied',ad_storage:'denied'});
gtag('js',new Date());gtag('config','G-MHLW57MR8Q',{anonymize_ip:true,send_page_view:false});
function grantGA(){gtag('consent','update',{analytics_storage:'granted'});gtag('event','page_view',{page_location:location.href,page_title:document.title})}
document.addEventListener('DOMContentLoaded',function(){
var bar=document.getElementById('cookieBar');
var c=localStorage.getItem('pg_consent');
if(!c){bar.style.display='flex'}else if(c==='1'){grantGA()}
document.getElementById('cookieAccept').addEventListener('click',function(){localStorage.setItem('pg_consent','1');grantGA();bar.style.display='none'});
document.getElementById('cookieDecline').addEventListener('click',function(){localStorage.setItem('pg_consent','0');bar.style.display='none'});
});
</script>
<script type="application/ld+json">{"@context":"https://schema.org","@type":"HowTo","name":"Upgrade PostgreSQL on Windows","description":"How to upgrade PostgreSQL on Windows using dump/restore or pg_upgrade.","step":[{"@type":"HowToStep","position":1,"name":"Dump databases","text":"pg_dumpall -U postgres -f backup.sql"},{"@type":"HowToStep","position":2,"name":"Install new version","text":"Run new PostgreSQL installer on a different port"},{"@type":"HowToStep","position":3,"name":"Restore","text":"psql -U postgres -p 5433 -f backup.sql"},{"@type":"HowToStep","position":4,"name":"Verify and switch","text":"Verify data, stop old service, update port, start new service"}]}</script>
<script>var el=document.getElementById("nl-home");if(el)el.classList.add("active");</script>
</body></html>