public inbox for [email protected]  

help / color / mirror / Atom feed

From: Georg H. <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: best migration solution
Date: Thu, 25 Apr 2024 17:08:22 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <GV0P278MB0099D9368CEBEBF304405F778B172@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM>
References: <GV0P278MB0099D9368CEBEBF304405F778B172@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: best migration solution
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox