Postgres Pro
Downloads
Home   >   mailing lists

Thread: best migration solution

best migration solution

From
"Zwettler Markus (OIZ)"
Date:

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
  • change required role names

 

dump all required databases with pg_dump

  • in plain text
  • exclude all schemas not needed

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?

 

Re: best migration solution

From
Kashif Zeeshan
Date:


On Thu, Apr 25, 2024 at 12:55 PM Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch> wrote:

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
  • change required role names

 

dump all required databases with pg_dump

  • in plain text
  • exclude all schemas not needed

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?

Hi

This solution is ok and should work.

Regards
Kashif Zeeshan
Bitnine Global

 

 

I wonder whether a plain text dump could lead to conversion problems or something similar?

 

Re: best migration solution

From
Ron Johnson
Date:
On Thu, Apr 25, 2024 at 3:55 AM Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch> wrote:

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
  • change required role names

 

dump all required databases with pg_dump

  • in plain text
  • exclude all schemas not needed

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?

 
How big of a database?

Editing a giant SQL file in vim is painful.

I'd do this, which is conceptually similar to your plan:
* pg_dump -Fd
* pg_restore --list
* Edit the generated list
* pg_restore --use-list=edited_list

 I wonder whether a plain text dump could lead to conversion problems or something similar?


Maybe, if the collations are different between the source and destination.

Re: best migration solution

From
"Georg H."
Date:

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):
@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;}

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