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 1rzyvh-008ZXI-4j for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 13:14:25 +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 1rzyvf-006oz7-Eu for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 13:14:23 +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 1rzyve-006oyy-Vs for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 13:14:23 +0000 Received: from mail-ot1-x332.google.com ([2607:f8b0:4864:20::332]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rzyvc-004aWn-Fv for pgsql-general@postgresql.org; Thu, 25 Apr 2024 13:14:21 +0000 Received: by mail-ot1-x332.google.com with SMTP id 46e09a7af769-6eb658ca1ceso655960a34.2 for ; Thu, 25 Apr 2024 06:14:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714050859; x=1714655659; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=63kc5RsHj5AV98siQgmsfeod3bKOz7J6Eab8jdlIcAQ=; b=mvpWTFjpOprh/umq7ij5qVlN8q1257C/hFvd88IjXkb6P7cP95kFTHbKE1Pxurekgr q0bkBY7/+Nov++xNHP3WccdGVOHoPVVGx8RE+EK66RUFoUVc7f0q5ZaBx5QiCVHT883B CuxB4CPr9KQlUwYfaJaEP2to/QbMYPBZpB91NhxM1LRZbNh4ixpAVOxypFi9TWRJzc6q 1qYSAPOL94N/Lx2RF230a3l/dpxYjp3yqge9c/JsEwBuon31P1j/2G75gSLqDiLnaW+p N51SwdmJ+DqbtxiqyCWcL5JoWnIzNyUg/CFqDsgaMrmiwDMyocie7onV3N8o9maRC9mj SXXQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714050859; x=1714655659; h=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=63kc5RsHj5AV98siQgmsfeod3bKOz7J6Eab8jdlIcAQ=; b=j7hwyp+2BmgJIbdL6c04swSwNNtvM2WuLUHcuPdiFcqafkWctsgc8QnHIjyUK7NwWD NZRaYWiQkhSJyknNdnO5Guy7T7yQs4MLRE809wK4AGcuvS8ohh8wuSAcYSSfND5vMxcO gNYl9ggn2Wg7oAyuF6lKIYQBd6GoPOf0iB767XQf+oElXqkwwdqv49zrZBdW5rYyKumR MJF0infWSeuZhIpvdfZjm9+0kf+Osa5ZeHbffsYiplcXhWiP0CDDSvk0sYep5SNgWjAp 63N3lm1Bdj6yLp8MiHVhS5gHHHMihRXfdIir8kENUuRIRpLFAVL4VXtMyA43/NkrxJ10 sC5g== X-Gm-Message-State: AOJu0YznOUNgtZ94yGw2ZK6iISgW70pqWGVWoZvuoJGhvzTyHsZD2pxU 2vwCNj+Mkw37I3iCOUa/WZ8zdAA336Np0Ol/XcKXCv0603F5ZAQ+1yrmKjLj8/dPKx0GdLPyX7e 4fSif9IS9jZoqsS3+9CgZaOyTvcZkuzRz X-Google-Smtp-Source: AGHT+IGf1SLROC243e/y54b9z8fk6nNlf+HMEGCbf2w4bDNSkGnzbAYqQaz95/ertXzv7YyJ8KtNYu2SwLhNiu+l7cg= X-Received: by 2002:a05:6870:218d:b0:22e:13d7:53dc with SMTP id l13-20020a056870218d00b0022e13d753dcmr6527166oae.13.1714050859558; Thu, 25 Apr 2024 06:14:19 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 25 Apr 2024 09:14:08 -0400 Message-ID: Subject: Re: best migration solution To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000009c6cf60616eb9060" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009c6cf60616eb9060 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Apr 25, 2024 at 3:55=E2=80=AFAM 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? > 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=3Dedited_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. --0000000000009c6cf60616eb9060 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Apr 25, 2024 at 3:55=E2=80=AFAM Z= wettler Markus (OIZ) <Mark= us.Zwettler@zuerich.ch> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">

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?

=C2=A0<= /div>
How big of a database?

Editing a giant S= QL file in vim is painful.

I'd do this, which = is conceptually similar to your plan:
* pg_dump -Fd
* p= g_restore=C2=A0--list
* Edit the generated list
* pg_re= store=C2=A0--use-list=3Dedited_list

=C2=A0I 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.

--0000000000009c6cf60616eb9060--