From: "Zwettler Markus (OIZ)" <Markus.Zwettler@zuerich.ch>
To: PG-General Mailing List <pgsql-general@postgresql.org>
Subject: question on plain pg_dump file usage
Thread-Topic: question on plain pg_dump file usage
Thread-Index: AdsI/Dm3z5gVcpZ8Qe6aBjkJQGcfRw==
Date: Tue, 17 Sep 2024 12:22:14 +0000
Message-ID: <GV0P278MB009945F8EA0D8D6B2CF9D91D8B612@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM>
Accept-Language: de-DE, en-US
Content-Language: de-DE
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?KMYTuLAM5ZH86TzZr5FxNlDem+GEtdDtdfSVB3V3WIP1ImdafRpRrvl8wyry?=
 =?us-ascii?Q?d5te6Qbrcj73Aqn5V5Pgw7vKzRv3eDipE4uxKMtyIO4si8Y4+tgU1nfnIXKd?=
 =?us-ascii?Q?X6wgPTqbi0sHqnzuXilVUKo0w6o2Fal3mMG84MgMg+RbIMptKksMgGoE95sk?=
 =?us-ascii?Q?wswmjZCeY205GHpq0ZxGOejHu4gzRF6mxYQk9tdIj6CM7zI21dct+Hs5tDBw?=
 =?us-ascii?Q?qfybwuuHs/XNhXYEUYeto+gWgkL/2hYm9VvyVg8Jdxk2msGlKkh5PjImxJ6V?=
 =?us-ascii?Q?zg4wWM4Jd4p3MqoUuCs8jEFhvk1wTYs8IxxO0LemTxKLEerpzjqqf8dkg1T2?=
 =?us-ascii?Q?ZNc/cwEJy+h4bnBJ9OFsIWfYLmX4ZYNGsJNRIlS57g3BDTl7KXpU7bDChpH9?=
 =?us-ascii?Q?2YK3lHyFsZ+Q/ETxEDrbm30pVG9h7Jt5MArGiNW5uKlsiUn4BwY0OLhHvWUU?=
 =?us-ascii?Q?rp0xtPySU014VLMNoYJbUvSgozmpsWV9h4ff5d3QyLQbkbL67iE3pDibxVwy?=
 =?us-ascii?Q?luFGk8joe/1vSfMYtBJVekAzDJ82VUyRrGL0GPIW0v0L+zZH6TwD4iGnddqG?=
 =?us-ascii?Q?1G1aN2xjFfBDHT4z2ynpRYX9ZLsUizpU84cLmhRUHQABaoGrlwB1eDsdDJ60?=
 =?us-ascii?Q?m0lJnJoAzpH6EqRKemo351Jh/YpnPFUYJ17B4J8dAidZmNzcwQzHroyBqBwI?=
 =?us-ascii?Q?hQw4Ekjm15UvpP4Fa1ZeGsTIlWKkoaWomXjcq2wE/DCB+vNGHy5yMJB3CNqn?=
 =?us-ascii?Q?f1K9jWyQPIMf/AXSn1yqXAKvVHirsa3sjem9EAYD0H4xk17osjTrs8lzqiwB?=
 =?us-ascii?Q?t0KSQ+FqdNSZs1bCC+5A6SJpdgTFGovG4qq0PAjwBjBcqB1eS8cBsR3pYz5H?=
 =?us-ascii?Q?24HVvfxsv/AmnvU1VaYFMTE6x3tz4C4Xonx1codWMoh2iX1hm9fzY3rT/B37?=
 =?us-ascii?Q?mbbdrN8yn8o/2yyfRqexDWGNOdklAj3EWK+e0T09yGU6XbntydAwqBmedjwx?=
 =?us-ascii?Q?+Or19rrVCPbQ5vVoZIMXRqVp4msxCrwgZ1xA1aYsrFdApDCfmyawzTeuHsvm?=
 =?us-ascii?Q?WuNyVpuzD+OaQIcp+UNKBUp5xPz3CtXXkTyDYGxLbSej7NIwiAL3TmGcca2I?=
 =?us-ascii?Q?XcQam0GYph6zLqdlVT7ZY+HmH7PL5WOjf75yaOYQmy10CBTsVIzzd25yfcmq?=
 =?us-ascii?Q?Dyci1Z28u4vOVSqH/FzSNQEuBgJvMTkd2tlQv4FPTkvR0ajlra1hUVg1lqqU?=
 =?us-ascii?Q?T9og+oiyUuTv653c4+PxXRIrxuk0O8J51nP/OefSkQHUwBhN+s4AB+dF+KJy?=
 =?us-ascii?Q?BYftCkIEsKCvLg6/3a0uL9SFAzTb2Bz2l6FlnMS8wcbZ5PcsS+DJynLOgb0E?=
 =?us-ascii?Q?EKey2idvXk0fhNJGD8QhdeXlZ9b2O6sAI6iXFnXgjnv87CTgm80T3pK7H6Mx?=
 =?us-ascii?Q?riTDkMvkCgaT0N3myzRW+ztJhB1NJ2qwAEnWXcQvhS3fvTpB2/zhN9RTYMQX?=
 =?us-ascii?Q?oTHSjiSyh0MN8sgALj5vj8xolOuh1KqPmcRhDd97ol5fmPLWk3mbDoR+RfgJ?=
 =?us-ascii?Q?6btoFFPue6LavwgxFm/Jl5tYJ1eZ4HDO+h179co6?=
Content-Type: multipart/alternative;
	boundary="_000_GV0P278MB009945F8EA0D8D6B2CF9D91D8B612GV0P278MB0099CHEP_"
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: e984326a-4e90-4bef-604b-08dcd7135cdc
X-MS-Exchange-CrossTenant-originalarrivaltime: 17 Sep 2024 12:22:14.1800
 (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: Im8Lj0NLip0wX3GhND8Cv4G/DGOk/Dna4UsOcBoZbVhW2WpVtQ47/7foUMTTyXGNnxccfYSY7oOKTC/mrYPH08rfXMSycQ7V92pIYAH9DiA=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: ZR0P278MB1629
X-OriginatorOrg: zuerich.ch
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/GV0P278MB009945F8EA0D8D6B2CF9D91D8B612%40GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_GV0P278MB009945F8EA0D8D6B2CF9D91D8B612GV0P278MB0099CHEP_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have to do an out-of-place Postgres migration from PG12 to PG16 using:

pg_dump -F p -f dump.sql ...
sed -i "s/old_name/new_name/g"
psql -f dump.sql ...

Both databases are on UTF-8.

I wonder if there could be character set conversion errors here, as the dat=
a is temporarily written to a plain text file.

Thanks, Markus


--_000_GV0P278MB009945F8EA0D8D6B2CF9D91D8B612GV0P278MB0099CHEP_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:11.0pt;
	font-family:"Arial",sans-serif;
	mso-ligatures:standardcontextual;
	mso-fareast-language:EN-US;}
span.E-MailFormatvorlage35
	{mso-style-type:personal-compose;
	font-family:"Arial",sans-serif;
	font-variant:normal !important;
	color:windowtext;
	text-transform:none;
	position:relative;
	top:0pt;
	mso-text-raise:0pt;
	letter-spacing:0pt;
	mso-ligatures:none;
	mso-number-form:default;
	mso-number-spacing:default;
	mso-stylistic-set:0;
	mso-contextual-alternates:no;
	font-weight:normal;
	font-style:normal;
	text-decoration:none none;
	vertical-align:baseline;}
.MsoChpDefault
	{mso-style-type:export-only;
	mso-fareast-language:EN-US;}
.MsoPapDefault
	{mso-style-type:export-only;
	margin-bottom:10.0pt;
	line-height:115%;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
	{page:WordSection1;}
/* List Definitions */
@list l0
	{mso-list-id:-120;
	mso-list-type:simple;
	mso-list-template-ids:-2036700426;}
@list l0:level1
	{mso-level-style-link:Listennummer;
	mso-level-tab-stop:18.0pt;
	mso-level-number-position:left;
	margin-left:18.0pt;
	text-indent:-18.0pt;}
@list l1
	{mso-list-id:-119;
	mso-list-type:simple;
	mso-list-template-ids:1072705328;}
@list l1:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:18.0pt;
	mso-level-number-position:left;
	margin-left:18.0pt;
	text-indent:-18.0pt;
	font-family:Symbol;}
@list l2
	{mso-list-id:99691344;
	mso-list-type:hybrid;
	mso-list-template-ids:-435887208 -755725786 134676483 134676485 134676481 =
134676483 134676485 134676481 134676483 134676485;}
@list l2:level1
	{mso-level-number-format:bullet;
	mso-level-style-link:Aufz\00E4hlungszeichen;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Symbol;}
@list l2:level2
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:"Courier New";}
@list l2:level3
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Wingdings;}
@list l2:level4
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Symbol;}
@list l2:level5
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:"Courier New";}
@list l2:level6
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Wingdings;}
@list l2:level7
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Symbol;}
@list l2:level8
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:"Courier New";}
@list l2:level9
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Wingdings;}
ol
	{margin-bottom:0cm;}
ul
	{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"DE-CH" link=3D"blue" vlink=3D"purple" style=3D"word-wrap:brea=
k-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none">I have to do an out-of-place Postgres migration from PG12 to=
 PG16 using:<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none">pg_dump -F p -f dump.sql &#8230;<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none">sed -i &quot;s/old_name/new_name/g&quot;<o:p></o:p></span></=
p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none">psql -f dump.sql &#8230;<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none">Both databases are on UTF-8.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none">I wonder if there could be character set conversion errors h=
ere, as the data is temporarily written to a plain text file.<o:p></o:p></s=
pan></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none">Thanks, Markus<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US" style=3D"font-size:10.0pt;mso-l=
igatures:none"><o:p>&nbsp;</o:p></span></p>
</div>
</body>
</html>

--_000_GV0P278MB009945F8EA0D8D6B2CF9D91D8B612GV0P278MB0099CHEP_--