[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: best migration solution
From: "Georg H." <georg-h () silentrunner ! de>
Date: 2024-04-25 15:08:22
Message-ID: 10fbed51-a2f5-46a4-8382-e47cf32c3b47 () silentrunner ! de
[Download RAW message or body]
Hello Markus,
keep it simple. Use a restored backup of the source db or this db itself
and then
Am 25.04.2024 um 09:55 schrieb Zwettler Markus (OIZ):
>
> we have to migrate from hosted PG12 to containerized PG16 on private
> cloud.
>
> some of the installed PG12 extensions are not offered on the
> containerized PG16, eg. PostGIS related extensions like pg_routing and
> ogr_fdw.
>
> some of these extensions are not needed anymore. some of these
> extensions were installed in their own schema.
>
> we also need to change the database names and most role names due to
> external requirements.
>
> I came up with this solution.
>
> dump all roles with pg_dumpall.
>
> edit this dumpfile and
>
> * exclude roles not needed
>
drop roles not needed
>
> * change required role names
>
rename the required roles to their new names
then dump the roles
> dump all required databases with pg_dump
>
> * in plain text
> * exclude all schemas not needed
>
drop all schemas not needed as well as any extension that does not exist
on the target and those that have own schemas (maybe they should not be
installed before the dump is imported)
then take a pg_dump just of the database(s)
> *
>
>
> edit this dump file and
>
> * exclude any "create extension" command for not existing extensions
> * change all required role names on permissions and ownerships
>
> any missings?
> any better solutions?
>
> I wonder whether a plain text dump could lead to conversion problems
> or something similar?
>
when the roles and db-dump are imported, install the missing extensions.
To take the dumps use the binaries of the target version
kind regards and good luck
Georg
[Attachment #3 (text/html)]
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hello Markus,</p>
<p>keep it simple. Use a restored backup of the source db or this db
itself and then<br>
</p>
<div class="moz-cite-prefix">Am 25.04.2024 um 09:55 schrieb Zwettler
Markus (OIZ):<br>
</div>
<blockquote type="cite"
cite="mid:GV0P278MB0099D9368CEBEBF304405F778B172@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator"
content="Microsoft Word 15 (filtered medium)">
<style>@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Arial",sans-serif;
mso-fareast-language:EN-US;}p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
font-size:11.0pt;
font-family:"Arial",sans-serif;
mso-fareast-language:EN-US;}span.E-MailFormatvorlage37
{mso-style-type:personal-compose;
font-family:"Arial",sans-serif;
font-variant:normal !important;
color:windowtext;
text-transform:none;
position:relative;
top:0pt;
mso-text-raise:0pt;
letter-spacing:0pt;
mso-ligatures:none;
mso-number-form:default;
mso-number-spacing:default;
mso-stylistic-set:0;
mso-contextual-alternates:no;
font-weight:normal;
font-style:normal;
text-decoration:none none;
vertical-align:baseline;}.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}div.WordSection1
{page:WordSection1;}ol
{margin-bottom:0cm;}ul
{margin-bottom:0cm;}</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">we
have to migrate from hosted PG12 to containerized PG16 on
private cloud.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">some
of the installed PG12 extensions are not offered on the
containerized PG16, eg. PostGIS related extensions like
pg_routing and ogr_fdw.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">some
of these extensions are not needed anymore. some of these
extensions were installed in their own schema.<br>
<br>
we also need to change the database names and most role
names due to external requirements.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">I
came up with this solution.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">dump
all roles with pg_dumpall.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">edit
this dumpfile and<o:p></o:p></span></p>
<ul style="margin-top:0cm" type="disc">
<li class="MsoListParagraph"
style="margin-left:0cm;mso-list:l3 level1 lfo3"><span
style="font-size:10.0pt" lang="EN-US">exclude roles not
needed</span></li>
</ul>
</div>
</blockquote>
drop roles not needed<br>
<blockquote type="cite"
cite="mid:GV0P278MB0099D9368CEBEBF304405F778B172@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM">
<div class="WordSection1">
<ul style="margin-top:0cm" type="disc">
<li class="MsoListParagraph"
style="margin-left:0cm;mso-list:l3 level1 lfo3"><span
style="font-size:10.0pt" lang="EN-US">change required role
names</span></li>
</ul>
</div>
</blockquote>
<p>rename the required roles to their new names <br>
</p>
<p>then dump the roles<br>
</p>
<blockquote type="cite"
cite="mid:GV0P278MB0099D9368CEBEBF304405F778B172@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">dump
all required databases with pg_dump
<o:p></o:p></span></p>
<ul style="margin-top:0cm" type="disc">
<li class="MsoListParagraph"
style="margin-left:0cm;mso-list:l2 level1 lfo4"><span
style="font-size:10.0pt" lang="EN-US">in plain text<o:p></o:p></span></li>
<li class="MsoListParagraph"
style="margin-left:0cm;mso-list:l2 level1 lfo4"><span
style="font-size:10.0pt" lang="EN-US">exclude all schemas
not needed</span></li>
</ul>
</div>
</blockquote>
<p>drop all schemas not needed as well as any extension that does
not exist on the target and those that have own schemas (maybe
they should not be installed before the dump is imported) <br>
</p>
<p>then take a pg_dump just of the database(s)<br>
</p>
<blockquote type="cite"
cite="mid:GV0P278MB0099D9368CEBEBF304405F778B172@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM">
<div class="WordSection1">
<ul style="margin-top:0cm" type="disc">
<li class="MsoListParagraph"
style="margin-left:0cm;mso-list:l2 level1 lfo4"><span
style="font-size:10.0pt" lang="EN-US"><o:p></o:p></span><br>
</li>
</ul>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">edit
this dump file and<o:p></o:p></span></p>
<ul style="margin-top:0cm" type="disc">
<li class="MsoListParagraph"
style="margin-left:0cm;mso-list:l2 level1 lfo4"><span
style="font-size:10.0pt" lang="EN-US">exclude any "create
extension" command for not existing extensions<o:p></o:p></span></li>
<li class="MsoListParagraph"
style="margin-left:0cm;mso-list:l2 level1 lfo4"><span
style="font-size:10.0pt" lang="EN-US">change all required
role names on permissions and ownerships<o:p></o:p></span></li>
</ul>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">any
missings? <br>
any better solutions?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt" lang="EN-US">I
wonder whether a plain text dump could lead to conversion
problems or something similar?<br>
<br>
<o:p></o:p></span></p>
</div>
</blockquote>
<p>when the roles and db-dump are imported, install the missing
extensions.</p>
<p>To take the dumps use the binaries of the target version<br>
</p>
<p>kind regards and good luck</p>
<p>Georg<br>
</p>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic