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 1rztwu-007mcS-Tc for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 07:55:21 +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 1rztwt-004HIS-Al for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 07:55:19 +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 1rztws-004HIK-K9 for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 07:55:18 +0000 Received: from mx4.stadt-zuerich.ch ([194.56.33.13]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rztwk-004YLy-H0 for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 07:55:17 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zuerich.ch; i=@zuerich.ch; l=13751; q=dns/txt; s=zuerichch202311; t=1714031710; x=1745567710; h=from:to:subject:date:message-id:mime-version; bh=8qmN/Jzlfx+tB/IK/K1oRz2qRENF1wBdQQyPIkDeddY=; b=OdSoeFq0790yp7b5oTSps3ljp54FQiFiev1ksXiSp3ZIuRHy9nF2EiVA gvyoxMeKMIJ52mpXt8z5WwKTF6WQJr3hLL0hYcmAFBKDjCCWLEkrXvBdt 5En2k/2hdu6rPXhPGJxz17vS8aCI6LjUzqwcWE9C/DGhRDWei0IgyHeOa 9O3DW8MFm2rMwWNFUjKYszvZOWX1rw10ukKozNTn1pkgMp3p6upwpSKz8 XxosEm1EgDXdqKqOg+k4xU2BrH89PTJFakFSrU1X1erSowpDoQQB4GckR rJZXCiKBYVwrZNQey3YyTPP1FEVlxZsv0BTXaQmxipjbfKtgjpSvYtcNV A==; X-IronPort-AV: E=Sophos;i="6.07,228,1708383600"; d="scan'208,217";a="53500740" Received: from unknown (HELO SZHM27370.global.szh.loc) ([10.7.50.50]) by mx4.prm.szh.zone with ESMTP/TLS/AES256-GCM-SHA384; 25 Apr 2024 09:55:07 +0200 Received: from SZHM29233.global.szh.loc (10.7.49.90) by SZHM27370.global.szh.loc (10.7.50.53) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2375.18; Thu, 25 Apr 2024 09:55:07 +0200 Received: from szhm30910.global.szh.loc (172.26.4.38) by SZHM29233.global.szh.loc (10.7.49.93) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2375.18 via Frontend Transport; Thu, 25 Apr 2024 09:55:07 +0200 Received: from ZRZP278CU001.outbound.protection.outlook.com (40.93.85.7) by smtp.stadt-zuerich.ch (172.27.4.38) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.792.3; Thu, 25 Apr 2024 09:55:07 +0200 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=IQnMK+KBxMfI8zeaNaL72JxSQdIlHdukfTeFRaW8PQOLVEkQZ9mt19wULpkm3SWOztBK7mCid8SXOvBJi7DQ52TqTcqsql8uWGTydW1w+XSPj5UzSqNRE8ImURC5Kv+76WVEXgr1/ydxQ9vczJdfbrg1yQRcnT5y5Z/A9rAgwb8YJlIxco6WroUplavYKLX3RQTDMbnYDt1qUha/aZLd9fjv+voLuw9BaczR7LDmw9mPmq8wpH5/VCAuXrsOashWZZBHg/h1lvyJ2QJSrsJ/smQTlzVVKotMyB91bc+tXoFK/mnCQCL5Ou8tBjJFqIMe0f3qVtj9y5NyjmxHanMtjg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=8qmN/Jzlfx+tB/IK/K1oRz2qRENF1wBdQQyPIkDeddY=; b=Gaky+4XilXptqC/VzrNjl0FeLffl95UoL5tBv7Fh68wVw7b76JvQSGNlV/aNQ5iHZzdeU+kbbB5zp9n4xekWn3qYYKcQ3kb81NFce6k/+YxkcrkBFfV2jEg+eZpfprWwMrFY4NVLJi//Q15h2F/ysiS/1JOTDcSefcX9/b541cNFuin0ztm5OJyvznYmk+uJlk2EC5H7U4XOOR4TaCwSeIB8uUAogLOvzmHGWMvA9U6tiMp6N+uwx9WuzTT4pPAcM0BzrSF/e0LkDWvrMjPgiufFvIo2ZqlbCwY1LOi/iv0rN9+myF3JLyI7jPH7LE2X5+9xwz/PPstB7hROUwY4lw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=zuerich.ch; dmarc=pass action=none header.from=zuerich.ch; dkim=pass header.d=zuerich.ch; arc=none Received: from GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM (2603:10a6:710:27::12) by GVAP278MB0836.CHEP278.PROD.OUTLOOK.COM (2603:10a6:710:57::10) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7519.25; Thu, 25 Apr 2024 07:55:05 +0000 Received: from GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM ([fe80::8b41:db4:b90d:454c]) by GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM ([fe80::8b41:db4:b90d:454c%5]) with mapi id 15.20.7519.021; Thu, 25 Apr 2024 07:55:05 +0000 From: "Zwettler Markus (OIZ)" To: "pgsql-general@lists.postgresql.org" Subject: best migration solution Thread-Topic: best migration solution Thread-Index: AdqW41nxf/+Hpk9QREC0SuXKnJsdcg== Date: Thu, 25 Apr 2024 07:55:05 +0000 Message-ID: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=zuerich.ch; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: GV0P278MB0099:EE_|GVAP278MB0836:EE_ x-ms-office365-filtering-correlation-id: 69b32dd6-3db2-4ba6-cb23-08dc64fd04fa x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230031|1800799015|366007|376005|38070700009; x-microsoft-antispam-message-info: =?us-ascii?Q?kNzAge8b6Hib9fDQLY6tOmYbx0TsA6ZgU4zy9lE/rPmvpoNUWeUPyAvbe0ID?= =?us-ascii?Q?QUSj9qCI3eaZzSkd9UIvfycAe84w4rUBtDbPknVWp46GNfv3ujzdonz7lU3z?= =?us-ascii?Q?wkwmxVpFoY/8PSs4rWpB/1N7ND3jdiwquoB0eyjD/SVWkh3rfcCrCHFO6lJp?= =?us-ascii?Q?J+ZVB1XGKT/FH+Xmw21Yu2TL7JPjcOPd69CPLPzw654El9pmQGdtWtXZ2oW9?= =?us-ascii?Q?eUPFT8XOWHdUKn9+enBrrsWcfa5xKI2sDJsWcZgot/64946RL5rierya9gZo?= =?us-ascii?Q?/gRbvNmF4wzuwNYkrwoUL0irBWWgyo1rMfObVC+21rdsFc/AyzBMa4LWxMKJ?= =?us-ascii?Q?J60ZBENqmN1jtJkMG8Hw4pLGid6CFL1z2meyb1uOy2tbgCoCZkbEkF9yD3lv?= =?us-ascii?Q?hd9Axh5tt391puyLQ0fk869dosdjepfxI8IZzZ8JNdqUdkwQgOfQ+jmx6kE4?= =?us-ascii?Q?r5KAJC2MzfEQByysOyKp6jE1HGP5w8gijZIG4BLnRNUUhJdgZkyiqKExBhau?= =?us-ascii?Q?fJvrVmznOeG8cuQpDIXWwHC4wC1FLXyhmVNnJcsBBVyqA1nmd36JIUtjQgFm?= =?us-ascii?Q?RB5SCfgGkbstr7nIM7GUvS5ltJq2RZlrRGeejWCsJwKkKkljNwfjroIRhlyq?= =?us-ascii?Q?95jF+So8ww9MkbfvHqoUdnPjxm3YZxbYKf46TUNj+nj196qwl0kQ7M9MDTao?= =?us-ascii?Q?dHGOxmuCAIG+7sVKd6MnhKv93q9+AImKhcF+Mpdd+M1Xv4scVI9FlVi3IecM?= =?us-ascii?Q?nRxAoI++udxvbHiQFFzjrxuDBO/Gw9SBwvaTZht7pG5ty24ojRddr0xXtExn?= =?us-ascii?Q?nxjo1wbe72BhOBh0I/t0eJ5KAURUvbJjj/psvJl87H0qO4Yw322NiPjk/Meg?= =?us-ascii?Q?x3l5uuFWfzNIoIEvBFottI0dFtqF159kLXfjCgWjDmfXfJNgx+BXvSUt9L0I?= =?us-ascii?Q?cfSgPC5hg8BgjXf9RamCGox/BM0POFuo+mxM7cbNx4nMdMJMw4wnU4/xHmjM?= =?us-ascii?Q?BPFuMCrXybiEjO5nsH6kaBPWlp2InfIpaa0YjanXXDCFlHk3pcBdVpEDejUC?= =?us-ascii?Q?F2tqhqaBNszx9m2slNKd8H4ghTjw0FBOIzQtxj3tkABygINwO8y7ccr8mXDe?= =?us-ascii?Q?JZ7XyzOHDG8+iTLMFcyqO91IzBXg7X2SrfywuTF5GTHRtsVPaKUARff42tZ4?= =?us-ascii?Q?ePEfgTZqutHcdTC9nbu+f2qQwH2z3DcVKSNPbfFjXJ/drxjzW7qBg2T4cGO0?= =?us-ascii?Q?x/g4/wgAxqIXHk9Wl1vUZzNCI1IlP/4QJ0DjcQblp3/sSIV1dc2TV2IsCB6T?= =?us-ascii?Q?FTF7E7wlw1QPEdUGjB99aA3AqtScGWJdPnCva0xXaRXukA=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230031)(1800799015)(366007)(376005)(38070700009);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?jXEfr+8OWW247sXnqjQOvjJTwolpPEEmk2DVcyK2KMEmS2ec5XNW2y9psXFH?= =?us-ascii?Q?ji35dyCUMwa/kzUgnV/4YIyHQWDNLPhOnpivXL/GADQ02N6c/jrELGAk4NYg?= =?us-ascii?Q?ANo347azM6o6ARX+s5YuzHMCTJF/TUPx7IqAUny3rske8JtO8OjztOqhv9id?= =?us-ascii?Q?cvzhud1Ev4ChKC0E/EjVErEHz4KeUxmjXsAW3SxYz7ZvhXSJYru5nqJuK5EU?= =?us-ascii?Q?1+fjvkTp4bgUFhF8BAe0gS77LYT47M8BJIS0jfLJ2UChxP+14p2nJX3NIe1u?= =?us-ascii?Q?j6cdQQwAKVLaO59C/1XhsIZXX8YHKPArtjOKjazilupMPAYziDxy0ze9M4o7?= =?us-ascii?Q?4DZenr8e/blFm2PzTX6wfs1v+yo6mvWA/DIv9CSLrHpkJS+zE2JCbUP2vKgb?= =?us-ascii?Q?Z3x7SGePhB3xgj+kjh5Gv3VL7QVuR2gaJ4DmstfeDZodYMZV4nqffU4hfgVi?= =?us-ascii?Q?fTphyRZLMFZAJzRscHQ8ny9lP95uTiFe6pNT+S19KZHrMcTxOyx0fWT3KhCV?= =?us-ascii?Q?U3IOW8yVpgrbzKKGfzT/vEajNkSj66ZychGHe1lUM/at+gf4JqRSJp/cOd4n?= =?us-ascii?Q?9Rh4gG2fZb0QAIMhN3h2M9kjjD+5xzxBkYaueQYvEuc68j0bl0EO6WhX+X03?= =?us-ascii?Q?bYm+OuBICAhVTZL+sh1nwx5WiB1kVPzEnoSCk/dAYlHKyD7rjzoYBqrd4PSq?= =?us-ascii?Q?taJffekEK6Wvn+u4tHb5LWxTottP6akwY+2czPcqDMomXBV21bwDjfS/G5GN?= =?us-ascii?Q?ZsdML51o6rCGiY+x6Wg5tuF0CU9dwESSLZpqfv3TMOVqIsb2cbvn2tnvVlOp?= =?us-ascii?Q?yKt/gUwE2UephczTn4iJzQ8SUTKm1br47jNSbIkdBdEWogQDPyIXJzoS5Zf+?= =?us-ascii?Q?yq6L1jHF4vrw0HtBHozfyVNKvAVUXv4jkTJVSgMLjAdiaccgcgboWCmwzQIZ?= =?us-ascii?Q?cNgO43tLw7J8esBF0t3SLGeOThOqi/+GbklgK1VYz7UiQTLBk+YPRExoVn58?= =?us-ascii?Q?YAst8v3rGILygv3ARFcDhMdVPcOKzp9fj5OCw24f4P5WzFZd+R5kzISmiYZl?= =?us-ascii?Q?NrqhxZrvAKW2K/u/9SYCy5ww5oW5mpYnKlWCnMfEofGaeq8Qs22RLI5OgtH+?= =?us-ascii?Q?IWNvjxFogxfc7TLDCjLknlsmPVMaBX3LX8mucmlMmKj5rS0GtF/A9jUnkBdx?= =?us-ascii?Q?fuBo7IRkgURlEponre6eLEoHAr4QPJo8MMcRa4fgslNsRtZVOLDGWAtIbwK1?= =?us-ascii?Q?ulTmCDWO4cymv0uf371gBjEWAr/8X3+DRwycPjetXTNcioiZ+fQCdTCFAGfq?= =?us-ascii?Q?32wpUHm8jXdgU9I5/ClfUnfpnWdYjuoouPOchlWhY6fEvAexeLtmf3v1cMQn?= =?us-ascii?Q?GdMiUvtTBZ6kL+RjbkoT4/F9qjg25/1NSdwHd3EzuEUAPE2AWGnigk+UQj/Z?= =?us-ascii?Q?+6kQgFARl6ts5MLE2SSOPhjQCEh0dK/erTyy4o3DgxXGv+R+q8vi7P4eu5nD?= =?us-ascii?Q?CvKlv7XMQQSJiFBXfdJI32Gm3SbeHJTMRSMVUJA5271EmhhaUw9z8FOSqdkC?= =?us-ascii?Q?sXnrl2/fsp9Qdv+iAJANV+eXe64bhfeHDSscdtaP?= Content-Type: multipart/alternative; boundary="_000_GV0P278MB0099D9368CEBEBF304405F778B172GV0P278MB0099CHEP_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 69b32dd6-3db2-4ba6-cb23-08dc64fd04fa X-MS-Exchange-CrossTenant-originalarrivaltime: 25 Apr 2024 07:55:05.2327 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 9f489fcc-e452-4509-8234-6f97b42d0117 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: NCzMwuafhrDnMoZT5BJzAPYHFgLyAqGHm+Q0oWiDnuG9GvivzvkbDLryBw48SBOl7R+RJJfzIfrqhb3RqLzphC4ZmIfj7gl0ZqO0HdDIXlU= X-MS-Exchange-Transport-CrossTenantHeadersStamped: GVAP278MB0836 X-OriginatorOrg: zuerich.ch List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_GV0P278MB0099D9368CEBEBF304405F778B172GV0P278MB0099CHEP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 w= ere installed in their own schema. we also need to change the database names and most role names due to extern= al 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 som= ething similar? --_000_GV0P278MB0099D9368CEBEBF304405F778B172GV0P278MB0099CHEP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

we h= ave 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 extern= al requirements.

 

 

I ca= me up with this solution.

 

dump= all roles with pg_dumpall.

edit= this dumpfile and

  • exclude roles not nee= ded
  • change required role names

 

dump= all required databases with pg_dump

  • in plain text
  • exclud= e all schemas not needed

edit= this dump file and

  • exclude any "cre= ate extension" command for not existing extensions
  • change all require= d role names on permissions and ownerships

 

 

any = missings?
any better solutions?

 

 

I wo= nder whether a plain text dump could lead to conversion problems or somethi= ng similar?

 

--_000_GV0P278MB0099D9368CEBEBF304405F778B172GV0P278MB0099CHEP_--