public inbox for [email protected]  

help / color / mirror / Atom feed

best migration solution
2+ messages / 2 participants
[nested] [flat]

* best migration solution
@ 2024-04-25 07:55  Zwettler Markus (OIZ) <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Zwettler Markus (OIZ) @ 2024-04-25 07:55 UTC (permalink / raw)
  To: [email protected] <[email protected]>

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?




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: best migration solution
@ 2024-04-25 13:14  Ron Johnson <[email protected]>
  parent: Zwettler Markus (OIZ) <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Ron Johnson @ 2024-04-25 13:14 UTC (permalink / raw)
  To: pgsql-general

On Thu, Apr 25, 2024 at 3:55 AM Zwettler Markus (OIZ) <
[email protected]> 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.


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-04-25 13:14 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-25 07:55 best migration solution Zwettler Markus (OIZ) <[email protected]>
2024-04-25 13:14 ` Ron Johnson <[email protected]>

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