Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzu2g-007nVo-CK for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 08:01:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rzu2e-004Nme-Up for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 08:01:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzu2e-004NmW-DH for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 08:01:16 +0000 Received: from mail-vs1-xe2d.google.com ([2607:f8b0:4864:20::e2d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rzu2c-004YOU-4k for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 08:01:15 +0000 Received: by mail-vs1-xe2d.google.com with SMTP id ada2fe7eead31-47bfea1df1dso366623137.0 for ; Thu, 25 Apr 2024 01:01:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714032073; x=1714636873; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=pgRF+QWEgD7e4/dIcHUBOcZPN6Lw2OgRx4SKCrsFolY=; b=Sjhowf/6+BQ3IVqKUvbpmQLF7iz/2S9jqP6wVO6J0uHFuEkAfKLEQkPqfeci0HRSpm IfRQh6HJut6voSRWOYEcbP3jT2ftxV6ONO67KT0kly5g2K/hIyzJT9cRj1tzy4Xd3gFf XriGSU+2WcKtWHW/VPzDAckw1UirzCpdaW7QuAZBK5qJ2/j/+L8fvhwZh/ZZ1dxBrSNB KkAs5D4y0bth7kxo1vrE1dhh2xrtA16NjPF5Vi5J4n8tIBhC4URjS+aLT0IRUhesxHZZ hlkMhQULDpoXHJbIKpAjA2F4mWj7mpinTsGoB3ZUf5TLVQrnKXiPVclmZalSxowARozI U+tw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714032073; x=1714636873; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=pgRF+QWEgD7e4/dIcHUBOcZPN6Lw2OgRx4SKCrsFolY=; b=shWs2K4UobhnhDP98x3sSo9uyHgjsmYMh7Axavi25c9Wjgw0NJbYOi64lC7LIiLzwm E0IfYdK+TIOl3Gn1xHmFTe6lL8UtqGfNnPycTTCyzo9I5cSpZR9bZwVjClPauX7s66g7 bhwR1R/jS+hTrIooG7jbc8BnVlaNxbYMdrNKNdcXg4FBsc1gKNWHPg0kt6vTw0Opq31n 8Tor6VTP+wUDUfpez2mkmDXgLuQ8Ol1oTo7UBrcRIEm9zaRCh6nPb41erIaAZdvJSWLw cwAI6x3ZhbuQwOkoKmRj45NpXwE3p/Z1eb671KIZKIdXU3sRhsruWthh/DGk6+RJM08m Nw0A== X-Gm-Message-State: AOJu0YyuZUCWf4un+2Yqm3Qr/Msm+vA8r/TCoTzxXJP/K2Rwy2Apoh5c +9+FiHBkbHYXLBUkfZ51pmJQU7gtt5ai6R1y7Qe4KIrr9gjb0BY9l1prA99Th1m9bZkiFT/Q9PW xq1TCtCraZOvxE9RH+MYa7wR0p4M= X-Google-Smtp-Source: AGHT+IFCOIkfLZ440BSlqe+H+/VRWNtGuW0t0xCdP3vd2ghf0pDtoy0Vyhk9mKSAlBKVQI5r2lHBfGlQgaU6KsTz7P4= X-Received: by 2002:a67:e99a:0:b0:47a:42d8:f017 with SMTP id b26-20020a67e99a000000b0047a42d8f017mr5519638vso.30.1714032073066; Thu, 25 Apr 2024 01:01:13 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Kashif Zeeshan Date: Thu, 25 Apr 2024 13:01:01 +0500 Message-ID: Subject: Re: best migration solution To: "Zwettler Markus (OIZ)" Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000d94e7a0616e730f3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d94e7a0616e730f3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Apr 25, 2024 at 12:55=E2=80=AFPM 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 containerize= d > 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? > > > --000000000000d94e7a0616e730f3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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

we hav= e to migrate from hosted PG12 to containerized PG16 on private cloud.

=C2=A0

some o= f the installed PG12 extensions are not offered on the containerized PG16, = eg. PostGIS related extensions like pg_routing and ogr_fdw.

some o= f these extensions are not needed anymore. some of these extensions were in= stalled in their own schema.

we also need to change the database names and most role names due to extern= al requirements.

=C2=A0

=C2=A0

I came= up with this solution.

=C2=A0

dump a= ll roles with pg_dumpall.

edit t= his dumpfile and

  • exclude roles not needed
  • ch= ange required role names

=C2=A0

dump a= ll required databases with pg_dump

  • in plain text
  • exclude all s= chemas not needed

edit t= his dump file and

  • exclude any "create e= xtension" command for not existing extensions
  • change all required role = names on permissions and ownerships

=C2=A0

=C2=A0

any mi= ssings?
any better solutions?

Hi

This solution is ok and should work.

Regards
Kashif Zeeshan
Bitnine Global

=C2=A0

=C2=A0

I wond= er whether a plain text dump could lead to conversion problems or something= similar?

=C2=A0

--000000000000d94e7a0616e730f3--