[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] How to migrate from PGSQL 9.3 to 9.6
From: John R Pierce <pierce () hogranch ! com>
Date: 2016-11-30 20:11:39
Message-ID: 14f171c7-4e2a-cb40-b0fb-29f52e244c34 () hogranch ! com
[Download RAW message or body]
On 11/30/2016 10:27 AM, Sinclair, Ian D (Ian) wrote:
>
> The actual upgrade will be that I have an existing server running 9.3
> on RHEL 6.2. We'll have to do a backup of the database, then deploy a
> new OVA to get to RHEL 7 with PG 9.6, then restore the database. Are
> there any specific steps that I'll have to execute in the scripts that
> will restore the database to get the 9.3 data to work with 9.6?
>
> Since all my users will be getting to 9.6 from a new system
> deployment, not by any sort of partial upgrade, do I need to worry
> about trying to get a correct version of the setup script with upgrade
> handling? (Other than whatever I need for the answer above.)
>
the upgrade scripts, like pg_upgrade, are for in place upgrades (same
server, postgres X.Y to X.newer).
if the old server will still be running when you have the new one ready,
what I already said, configure the old server to allow the postgres user
on the new server to access it over the network, then, from the postgres
user on the new server, do this..
pg_dumpall -h oldserver | psql
this assumes the new version of postgres is installed, initialized, and
running, but otherwise untouched
My personal preference for a full backup dump-n-restore goes something
like this...
dump script, run as the postgres system user...
DD=$(date +%F)
PP=/path/of/where/to/put/dumps
pg_dumpall -g -f $PP/pg_dumpall.globals-$DD.sql
for db in $(psql -tc "select datname from pg_database where not
datistemplate"); do
pg_dump -Fc --schema-only -f $PP/pgdump.$db.$DD.dump -d $db
done
this creates a globals dump in plain sql, then a compressed dump of each
non-system database.
to restore these on a new cluster...
psql -f $PP/pg_dumpall.globals-2016-11-15.sql
for db in $PP/*.dump; do
pg_restore -d postgres --clean --create $db
done
but this is more for regular backups, the advantage of the compressed
format is pg_restore has a lot more options during database restore.
--
john r pierce, recycling bits in santa cruz
[Attachment #3 (text/html)]
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">On 11/30/2016 10:27 AM, Sinclair, Ian D
(Ian) wrote:<br>
</div>
<blockquote
cite="mid:773E6ECA62A012488950CF4AD529F80546E9E409@AZ-US1EXMB05.global.avaya.com"
type="cite">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">The
actual upgrade will be that I have an existing server running
9.3 on RHEL 6.2. We’ll have to do a backup of the database,
then deploy a new OVA to get to RHEL 7 with PG 9.6, then
restore the database. Are there any specific steps that I’ll
have to execute in the scripts that will restore the database
to get the 9.3 data to work with 9.6?<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> \
</o:p></span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Since
all my users will be getting to 9.6 from a new system
deployment, not by any sort of partial upgrade, do I need to
worry about trying to get a correct version of the setup
script with upgrade handling? (Other than whatever I need for
the answer above.)<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> \
</o:p></span></p> </blockquote>
<br>
<p>the upgrade scripts, like pg_upgrade, are for in place upgrades
(same server, postgres X.Y to X.newer).</p>
<p>if the old server will still be running when you have the new one
ready, what I already said, configure the old server to allow the
postgres user on the new server to access it over the network,
then, from the postgres user on the new server, do this..</p>
<p> pg_dumpall -h oldserver | psql</p>
<p>this assumes the new version of postgres is installed,
initialized, and running, but otherwise untouched<br>
</p>
<p>My personal preference for a full backup dump-n-restore goes
something like this...</p>
<p>dump script, run as the postgres system user...</p>
<p> DD=$(date +%F)<br>
PP=/path/of/where/to/put/dumps<br>
pg_dumpall -g -f $PP/pg_dumpall.globals-$DD.sql<br>
for db in $(psql -tc "select datname from pg_database where not
datistemplate"); do <br>
pg_dump -Fc --schema-only -f $PP/pgdump.$db.$DD.dump -d $db<br>
done<br>
</p>
<p>this creates a globals dump in plain sql, then a compressed dump
of each non-system database.</p>
<p>to restore these on a new cluster...</p>
<p> psql -f $PP/pg_dumpall.globals-2016-11-15.sql<br>
for db in $PP/*.dump; do<br>
pg_restore -d postgres --clean --create $db<br>
done</p>
<p>but this is more for regular backups, the advantage of the
compressed format is pg_restore has a lot more options during
database restore.</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<pre class="moz-signature" cols="72">--
john r pierce, recycling bits in santa cruz</pre>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic