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 1s00i7-008qWR-UZ for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 15:08:32 +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 1s00i6-007xji-Kd for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 15:08:30 +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 1s00i6-007xij-48 for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 15:08:30 +0000 Received: from mout.kundenserver.de ([212.227.17.24]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s00i2-004bOS-O4 for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 15:08:28 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=silentrunner.de; s=s1-ionos; t=1714057703; x=1714662503; i=georg-h@silentrunner.de; bh=eSfWu5ui9z2ehgKX7ZK2czX8SoekxKvpk5Cfsln7wtE=; h=X-UI-Sender-Class:Content-Type:Message-ID:Date:MIME-Version: Subject:To:References:From:In-Reply-To:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=pkh50I791e4XPf9BVBNEki6pKoNEPglcZngw9/tVHF3ZPh40bImEIuE1PM26UVCQ kEBUQDPHMBJ77WJ4ZQUMdHTV1bNBCumcfqa9yYirnOx4vlk9X3T307Zg0ccEXx7EC qM0UVrzpYv3/qQmrUSzwaJD33lQNus9ezdJ0Kv81FgaYHSggZ27SPt6dFlnnK16iY JdeDOx9/kNJpekSyAMVHzk3o8g3TNtShAQqQcBiWYbKXM2YehYVpSKSuNwrX+WChw 4D2twPk2ERVypLtpupH43bDIFd1STi4W+W8C3iWL7XYOx03rx2tnTywFJueEC1Lhu /1O0gJw3ergiOHSzHw== X-UI-Sender-Class: 55c96926-9e95-11ee-ae09-1f7a4046a0f6 Received: from [192.168.0.150] ([79.221.75.9]) by mrelayeu.kundenserver.de (mreue109 [212.227.15.183]) with ESMTPSA (Nemesis) id 1N5mOb-1sjxUK11fH-017GhM; Thu, 25 Apr 2024 17:08:23 +0200 Content-Type: multipart/alternative; boundary="------------8gobp0WSb6THP1S6wjbAxbrc" Message-ID: <10fbed51-a2f5-46a4-8382-e47cf32c3b47@silentrunner.de> Date: Thu, 25 Apr 2024 17:08:22 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: best migration solution To: Markus.Zwettler@zuerich.ch, pgsql-general@lists.postgresql.org References: Content-Language: de-DE From: "Georg H." In-Reply-To: X-Provags-ID: V03:K1:KBgTjaQtLkMwZXBoWamZ878Uc0gSpsXTqqyjes32m1Lc0FJzU6O Xn01LR7yGQn8GfBvpNuPhn0Pf9wrzhGJqBX34VyLaswP7CHsPQgExyQY6W2V3DVpMjqWviH GtE3eU+2WYwqz6JjbAEGillM1vnPyfKh3pheYdq0b5k7OKv7/HqiYYsZQe6ZmmsuHDyowhX UzEsRbNx0vW5n/3D5lvZQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:T9MRsEkB5Dc=;aq2zprc6hcMemyxdj6525Hq8mcp Vd/MK+CHZt/I6QeDjeUUk+w3VXvSrg4mHX90okGI5g9aPk2kqTN2L3myK6jvC0W3p/pfVj0Kr kQu/EWnU0ctfsCwBvP+J0LK1e8dUYqvLLLHhjwkWHDCgJtwlNttwJ6q3Nb0y9f8iZrLXX9oN8 UmmpN0oaD5Jv5EISmGycSFGwVyKHMbIxmV/SOCrijnkFdVaP7rH0Z/nsWmTjiMCUN3gjcr1Dj sPtGXOlNxOENMMZWmnlF5yCl9q+AMaApjRdAP08CmJYyHAEols0OSMfs63MQ8X/LrCaP7EHbw hpp24952upY30u+qbq+Ag9SXzzMjvbQEqyPcHsrz3KipUe9mt4OPwc41hgzxsunFhAAUPSCug nME8BhtmQBzfTH401GJ2rmH6oCp/YheSOyqwfjIop4Cltb8fMZoYju19hRgjuUwY9+0zXWVfk WgY91rnB9UBb9fSwtwyBeO0FUzz8N/4T9tK1IspCjMMY3QnH0uTk/PrRLzvWHhCUKe6EoGLJt nJwbRJ1KRAXRdjpI5xsPAbTLE0Yq3fYOe6Mx1gTIGt+X2QkWIE7SRKT/GRNdZgyeSQ5riP5Fb bT4fFWP9xL5TsT3XT1KhctSRTQNdEVElf2ygkgXO/MsAKEXs49WfIpvnDzEDOrurkQplKWotX TltpiNd3WScl+Gno4H9R8kqiouKH0HhZYQBTrnucHy3h6y528WaHggcezjVtR+D+Prq2eGuVF bD1y5QLdJX+IxnT3KyccYgM5Q0GVcvpzWzIma4RABWb/jSbs0Xe6MM= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------8gobp0WSb6THP1S6wjbAxbrc Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable 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 --------------8gobp0WSb6THP1S6wjbAxbrc Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

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.

=C2=A0

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.

=C2=A0

=C2=A0

I came up with this solution.

=C2=A0

dump all roles with pg_dumpall.

edit this dumpfile and

  • exclude roles not needed
drop roles not needed
  • change required ro= le names

rename the required roles to their new names

then dump the roles

=C2=A0

dump all required databases with pg_dump

  • in plain text=
  • exclude all schema= s 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 "creat= e extension" command for not existing extensions
  • change all require= d role names on permissions and ownerships

=C2=A0

=C2=A0

any missings?
any better solutions?

=C2=A0

=C2=A0

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

--------------8gobp0WSb6THP1S6wjbAxbrc--