From mosheb@nexperience.com Thu Jan  29 11:57:53 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E92316325C5
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 29 Jan 2009 07:57:53 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 74423-01 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 29 Jan 2009 07:57:47 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from nexperience.com (mail.nexperience.com [212.150.204.129])
	by mail.postgresql.org (Postfix) with ESMTP id C801C63273B
	for <pgsql-admin@postgresql.org>; Thu, 29 Jan 2009 07:57:45 -0400 (AST)
Received: from Internal Mail-Server by PUMA (envelope-from
	mosheb@nexperience.com) with SMTP; 29 Jan 2009 13:58:16 +0200
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C98207.CB0B2489"
Subject: Encoding problem using pg_dumpall
Date: Thu, 29 Jan 2009 13:50:11 +0200
Message-ID: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Encoding problem using pg_dumpall
thread-index: AcmCB73T95Xm8lv2StqyuO7cRrDJsg==
From: "Moshe Ben-Shoham" <mosheb@nexperience.com>
To: <pgsql-general@postgresql.org>,
	<pgsql-admin@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.821 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001, MIME_QP_LONG_LINE=1.819,
	UNPARSEABLE_RELAY=0.001
X-Spam-Level: *
X-Archive-Number: 200901/156
X-Sequence-Number: 31203

This is a multi-part message in MIME format.

------_=_NextPart_001_01C98207.CB0B2489
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

=20

I have a database with encoding UTF-8 installed on Windows, and I try to
dump it using pg_dumpall, on the machine on which the database is
installed. I get the following error message:

=20

C:\Program Files\PostgreSQL\8.3\bin>pg_dumpall -U admint >
c:\temp\dbdump.sql

pg_dump: SQL command failed

pg_dump: Error message from server: ERROR:  character 0xd595 of encoding
"UTF8" has no equivalent in "WIN1252"

pg_dump: The command was: COPY public.cms_history_properties
(structure_id, propertydef_id, property_mapping_id,
property_mapping_type, property_value, publish_tag) TO stdout;

pg_dumpall: pg_dump failed on database "opencms", exiting

=20

it probably means that some UTF-8 characters cannot be represented in
WIN1252 which is the encoding of the output file.=20

=20

Is there any solution to this problem?

=20

Thanks,

Moshe Ben Shoham.





The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you.





 
 
************************************************************************************
This footnote confirms that this email message has been scanned by
PineApp Mail-SeCure for the presence of malicious code, vandals & computer viruses.
************************************************************************************



------_=_NextPart_001_01C98207.CB0B2489
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-microsoft-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=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @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:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</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=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>Hi,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I have a database with encoding UTF-8 installed on =
Windows, and
I try to dump it using pg_dumpall, on the machine on which the database =
is installed.
I get the following error message:<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>C:\Program Files\PostgreSQL\8.3\bin&gt;pg_dumpall =
-U admint
&gt; c:\temp\dbdump.sql<o:p></o:p></p>

<p class=3DMsoNormal>pg_dump: SQL command failed<o:p></o:p></p>

<p class=3DMsoNormal>pg_dump: Error message from server: ERROR:&nbsp; =
character 0xd595
of encoding &quot;UTF8&quot; has no equivalent in =
&quot;WIN1252&quot;<o:p></o:p></p>

<p class=3DMsoNormal>pg_dump: The command was: COPY =
public.cms_history_properties
(structure_id, propertydef_id, property_mapping_id, =
property_mapping_type,
property_value, publish_tag) TO stdout;<o:p></o:p></p>

<p class=3DMsoNormal>pg_dumpall: pg_dump failed on database =
&quot;opencms&quot;,
exiting<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>it probably means that some UTF-8 characters cannot =
be
represented in WIN1252 which is the encoding of the output file. =
<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Is there any solution to this =
problem?<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Thanks,<o:p></o:p></p>

<p class=3DMsoNormal>Moshe Ben Shoham.<o:p></o:p></p>

</div>

</body>

</html>


<BR>
<BR>
The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you.<BR>
<BR>


<BR>
<BR>
 <BR>
 <BR>
************************************************************************************<BR>
This footnote confirms that this email message has been scanned by<BR>
PineApp Mail-SeCure for the presence of malicious code, vandals & computer viruses.<BR>
************************************************************************************<BR>
<BR>
<BR>

------_=_NextPart_001_01C98207.CB0B2489--




From tgl@sss.pgh.pa.us Thu Jan  29 15:16:30 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 64DEB632630;
	Thu, 29 Jan 2009 11:16:30 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 83474-04; Thu, 29 Jan 2009 11:16:25 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id BCFE66325C5;
	Thu, 29 Jan 2009 11:16:25 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n0TFGG3j028818;
	Thu, 29 Jan 2009 10:16:16 -0500 (EST)
To: "Moshe Ben-Shoham" <mosheb@nexperience.com>
cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Encoding problem using pg_dumpall 
In-reply-to: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com> 
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
Comments: In-reply-to "Moshe Ben-Shoham" <mosheb@nexperience.com>
	message dated "Thu, 29 Jan 2009 13:50:11 +0200"
Date: Thu, 29 Jan 2009 10:16:16 -0500
Message-ID: <28817.1233242176@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/158
X-Sequence-Number: 31205

"Moshe Ben-Shoham" <mosheb@nexperience.com> writes:
> C:\Program Files\PostgreSQL\8.3\bin>pg_dumpall -U admint >
> c:\temp\dbdump.sql
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  character 0xd595 of encoding
> "UTF8" has no equivalent in "WIN1252"

Apparently you have WIN1252 set as the default client encoding, probably
via an environment variable or locale setting.  Either get rid of it,
or override it by including "-E UTF8" in the pg_dump command.

(Hmm, actually it looks like pg_dumpall hasn't got a -E switch,
which seems like an oversight.  So you need to fix your locale,
or else use pg_dump directly.)

			regards, tom lane



From magnus@hagander.net Thu Jan  29 17:31:58 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 20E0363261E;
	Thu, 29 Jan 2009 13:31:58 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 36200-02; Thu, 29 Jan 2009 13:31:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from svr2.hagander.net (svr2.hagander.net [88.198.128.226])
	by mail.postgresql.org (Postfix) with ESMTP id D51A76325DC;
	Thu, 29 Jan 2009 13:31:51 -0400 (AST)
Received: from dynamic.hagander.net ([127.0.0.1]) (encrypted and
	authenticated) by svr2.hagander.net (Postfix) with ESMTP id
	96146DCD152; Thu, 29 Jan 2009 18:31:50 +0100 (CET)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by mha-laptop.hagander.net (Postfix) with ESMTP id 2CD631241C5;
	Thu, 29 Jan 2009 18:31:50 +0100 (CET)
Message-ID: <4981E805.7040302@hagander.net>
Date: Thu, 29 Jan 2009 18:31:49 +0100
From: Magnus Hagander <magnus@hagander.net>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Moshe Ben-Shoham <mosheb@nexperience.com>,
	pgsql-general@postgresql.org, pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Encoding problem using pg_dumpall
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
	<28817.1233242176@sss.pgh.pa.us>
In-Reply-To: <28817.1233242176@sss.pgh.pa.us>
X-Enigmail-Version: 0.95.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/159
X-Sequence-Number: 31206

Tom Lane wrote:
> "Moshe Ben-Shoham" <mosheb@nexperience.com> writes:
>> C:\Program Files\PostgreSQL\8.3\bin>pg_dumpall -U admint >
>> c:\temp\dbdump.sql
>> pg_dump: SQL command failed
>> pg_dump: Error message from server: ERROR:  character 0xd595 of encoding
>> "UTF8" has no equivalent in "WIN1252"
> 
> Apparently you have WIN1252 set as the default client encoding, probably
> via an environment variable or locale setting.  Either get rid of it,
> or override it by including "-E UTF8" in the pg_dump command.
> 
> (Hmm, actually it looks like pg_dumpall hasn't got a -E switch,
> which seems like an oversight.  So you need to fix your locale,
> or else use pg_dump directly.)

IIRC, you can't set the windows console to be UTF8. Thus, your option is
to switch to using pg_dump and use -E UTF8 on that one.

//Magnus



From tgl@sss.pgh.pa.us Thu Jan  29 17:40:03 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 758C0632729;
	Thu, 29 Jan 2009 13:40:03 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 46425-01-4; Thu, 29 Jan 2009 13:39:59 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id F4199632730;
	Thu, 29 Jan 2009 13:39:58 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n0THdstC007405;
	Thu, 29 Jan 2009 12:39:54 -0500 (EST)
To: Magnus Hagander <magnus@hagander.net>
cc: Moshe Ben-Shoham <mosheb@nexperience.com>, pgsql-general@postgresql.org,
	pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Encoding problem using pg_dumpall 
In-reply-to: <4981E805.7040302@hagander.net> 
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
	<28817.1233242176@sss.pgh.pa.us> <4981E805.7040302@hagander.net>
Comments: In-reply-to Magnus Hagander <magnus@hagander.net>
	message dated "Thu, 29 Jan 2009 18:31:49 +0100"
Date: Thu, 29 Jan 2009 12:39:54 -0500
Message-ID: <7403.1233250794@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/160
X-Sequence-Number: 31207

Magnus Hagander <magnus@hagander.net> writes:
> Tom Lane wrote:
>> (Hmm, actually it looks like pg_dumpall hasn't got a -E switch,
>> which seems like an oversight.  So you need to fix your locale,
>> or else use pg_dump directly.)

> IIRC, you can't set the windows console to be UTF8.

Ugh.  That seems to raise the priority of having a -E switch quite
a lot.  I'm surprised no one has complained of this before.

I think it should be possible to work around it by setting
PGCLIENTENCODING in the environment, but I dunno how to do that
on Windows.

			regards, tom lane



From magnus@hagander.net Thu Jan  29 17:46:02 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 70743631ADA;
	Thu, 29 Jan 2009 13:46:02 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 53646-01-5; Thu, 29 Jan 2009 13:45:54 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from svr2.hagander.net (svr2.hagander.net [88.198.128.226])
	by mail.postgresql.org (Postfix) with ESMTP id 7BB056327E1;
	Thu, 29 Jan 2009 13:45:27 -0400 (AST)
Received: from dynamic.hagander.net ([127.0.0.1]) (encrypted and
	authenticated) by svr2.hagander.net (Postfix) with ESMTP id
	0AB67DCD149; Thu, 29 Jan 2009 18:45:26 +0100 (CET)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by mha-laptop.hagander.net (Postfix) with ESMTP id 799371241C5;
	Thu, 29 Jan 2009 18:45:25 +0100 (CET)
Message-ID: <4981EB35.5060106@hagander.net>
Date: Thu, 29 Jan 2009 18:45:25 +0100
From: Magnus Hagander <magnus@hagander.net>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Moshe Ben-Shoham <mosheb@nexperience.com>,
	pgsql-general@postgresql.org, pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Encoding problem using pg_dumpall
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
	<28817.1233242176@sss.pgh.pa.us>
	<4981E805.7040302@hagander.net> <7403.1233250794@sss.pgh.pa.us>
In-Reply-To: <7403.1233250794@sss.pgh.pa.us>
X-Enigmail-Version: 0.95.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/161
X-Sequence-Number: 31208

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> Tom Lane wrote:
>>> (Hmm, actually it looks like pg_dumpall hasn't got a -E switch,
>>> which seems like an oversight.  So you need to fix your locale,
>>> or else use pg_dump directly.)
> 
>> IIRC, you can't set the windows console to be UTF8.
> 
> Ugh.  That seems to raise the priority of having a -E switch quite
> a lot.  I'm surprised no one has complained of this before.

Most people use pg_dump. I know I recommend everbody to use pg_dump to
dump the database, because you can use -Fc. Then just use pg_dumpall to
dump the globals, and they normally don't have any non-ascii in them.


> I think it should be possible to work around it by setting
> PGCLIENTENCODING in the environment, but I dunno how to do that
> on Windows.

That's a simple
set PGCLIENTENCODING=UTF8

//Magnus




From michael.monnerie@is.it-management.at Thu Jan  29 19:14:59 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 9DF2F6327B2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 29 Jan 2009 15:14:59 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 53174-02-6
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 29 Jan 2009 15:14:56 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mailsrv1.zmi.at (mailsrv1.zmi.at [212.69.162.198])
	by mail.postgresql.org (Postfix) with ESMTP id 23CBE6327B3
	for <pgsql-admin@postgresql.org>; Thu, 29 Jan 2009 15:14:32 -0400 (AST)
Received: from mailsrv2.i.zmi.at (h081217054243.dyn.cm.kabsi.at
	[81.217.54.243])
	(using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits))
	(Client CN "mailsrv2.i.zmi.at", Issuer "power4u.zmi.at" (not verified))
	by mailsrv1.zmi.at (Postfix) with ESMTP id 471D02C98
	for <pgsql-admin@postgresql.org>; Thu, 29 Jan 2009 20:14:29 +0100 (CET)
Received: from saturn.localnet (saturn.i.zmi.at [10.0.0.2])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client did not present a certificate)
	by mailsrv2.i.zmi.at (Postfix) with ESMTPSA id 8757140016F
	for <pgsql-admin@postgresql.org>; Thu, 29 Jan 2009 20:14:29 +0100 (CET)
From: Michael Monnerie <michael.monnerie@is.it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Encoding problem using pg_dumpall
Date: Thu, 29 Jan 2009 20:14:28 +0100
User-Agent: KMail/1.10.3 (Linux/2.6.27.13-ZMI; KDE/4.1.3; x86_64; ; )
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
	<7403.1233250794@sss.pgh.pa.us> <4981EB35.5060106@hagander.net>
In-Reply-To: <4981EB35.5060106@hagander.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200901292014.29195@zmi.at>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/162
X-Sequence-Number: 31209

On Donnerstag 29 Januar 2009 Magnus Hagander wrote:
> Most people use pg_dump. I know I recommend everbody to use pg_dump
> to dump the database, because you can use -Fc. Then just use
> pg_dumpall to dump the globals, and they normally don't have any
> non-ascii in them.

Why couldn't pg_dumpall get the same behaviour as pg_dump? It could get 
that -Fc, and couldn't it be implementet as "call pg_dump for each db 
and once for the system"? Why is it that different at all?

mfg zmi
-- 
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4




From tgl@sss.pgh.pa.us Fri Jan  30 01:08:05 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id EECC0632A37
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 29 Jan 2009 21:08:05 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 32757-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 29 Jan 2009 21:08:02 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id F340C632487
	for <pgsql-admin@postgresql.org>; Thu, 29 Jan 2009 21:08:02 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n0U16flk014709;
	Thu, 29 Jan 2009 20:06:41 -0500 (EST)
To: Michael Monnerie <michael.monnerie@is.it-management.at>
cc: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Encoding problem using pg_dumpall 
In-reply-to: <200901292014.29195@zmi.at> 
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
	<7403.1233250794@sss.pgh.pa.us> <4981EB35.5060106@hagander.net>
	<200901292014.29195@zmi.at>
Comments: In-reply-to Michael Monnerie <michael.monnerie@is.it-management.at>
	message dated "Thu, 29 Jan 2009 20:14:28 +0100"
Date: Thu, 29 Jan 2009 20:06:41 -0500
Message-ID: <14708.1233277601@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/164
X-Sequence-Number: 31211

Michael Monnerie <michael.monnerie@is.it-management.at> writes:
> Why couldn't pg_dumpall get the same behaviour as pg_dump? It could get 
> that -Fc, and couldn't it be implementet as "call pg_dump for each db 
> and once for the system"? Why is it that different at all?

The -Fc (and -Ft) formats are only designed to hold the contents of a
single database; and pg_restore only knows how to restore into a single
database.

If you feel like fixing that, step right up.

			regards, tom lane



From michael.monnerie@is.it-management.at Fri Jan  30 11:23:58 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 10458632C2F
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 30 Jan 2009 07:23:58 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 74691-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 30 Jan 2009 07:23:54 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mailsrv1.zmi.at (mailsrv1.zmi.at [212.69.162.198])
	by mail.postgresql.org (Postfix) with ESMTP id 3C0C4632C2A
	for <pgsql-admin@postgresql.org>; Fri, 30 Jan 2009 07:23:53 -0400 (AST)
Received: from mailsrv2.i.zmi.at (h081217054243.dyn.cm.kabsi.at
	[81.217.54.243])
	(using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits))
	(Client CN "mailsrv2.i.zmi.at", Issuer "power4u.zmi.at" (not verified))
	by mailsrv1.zmi.at (Postfix) with ESMTP id D4DC9300E
	for <pgsql-admin@postgresql.org>; Fri, 30 Jan 2009 12:23:49 +0100 (CET)
Received: from saturn.localnet (saturn.i.zmi.at [10.0.0.2])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client did not present a certificate)
	by mailsrv2.i.zmi.at (Postfix) with ESMTPSA id 17D4C40016F
	for <pgsql-admin@postgresql.org>; Fri, 30 Jan 2009 12:23:50 +0100 (CET)
From: Michael Monnerie <michael.monnerie@is.it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Encoding problem using pg_dumpall
Date: Fri, 30 Jan 2009 12:23:49 +0100
User-Agent: KMail/1.10.3 (Linux/2.6.27.13-ZMI; KDE/4.1.3; x86_64; ; )
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
	<200901292014.29195@zmi.at> <14708.1233277601@sss.pgh.pa.us>
In-Reply-To: <14708.1233277601@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200901301223.49703@zmi.at>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/167
X-Sequence-Number: 31214

On Freitag 30 Januar 2009 Tom Lane wrote:
> The -Fc (and -Ft) formats are only designed to hold the contents of a
> single database; and pg_restore only knows how to restore into a
> single database.
>
> If you feel like fixing that, step right up.

Sorry, didn't want to step on toes here. I know that sometimes if you 
perfectly know a system you get blind over alternatives, and wanted to 
throw in an idea.

The format of pg_dumpall could be a tar of single pg_dumps as well, 
making pg_dumpall just call pg_dump for each db, putting that output 
into the tar output from pg_dumpall. But again, just an idea, I don't 
mind really. I'm just a sysadmin who wants things as easy as possible. 
Maybe fixing pg_dumpall to include that -E is easier.

mfg zmi
-- 
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4




From enkiduonthenet@gmail.com Sat Jan  31 23:47:58 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 6C82A632690
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 31 Jan 2009 19:47:58 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 81174-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 31 Jan 2009 19:47:55 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from rv-out-0506.google.com (rv-out-0506.google.com
 [209.85.198.236])
	by mail.postgresql.org (Postfix) with ESMTP id 7373663264A
	for <pgsql-admin@postgresql.org>; Sat, 31 Jan 2009 19:47:55 -0400 (AST)
Received: by rv-out-0506.google.com with SMTP id b25so1021467rvf.43
	for <pgsql-admin@postgresql.org>; Sat, 31 Jan 2009 15:47:54 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:content-type
	:content-transfer-encoding;
	bh=0PwGe3UPj3Xfy+v+jVTGRgNbQwMmWS+c0qAw/VoZOHE=;
	b=Zvd/NZLj8rtJYTqd2pl71s+kwO7LgJ+ctaFns5jVhEHr205Wg9qmyq9UV2CUBdKxiE
	hAitXGoqJh7fILzhDv9C+ZBfbkfd/hXHLscbZqdOvVUd2WK80DDi84KjzNiNQvR3bFhS
	iU9hUd4rQjeBhLIozaCAwRU1dUZG4bxn4ZQZk=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type:content-transfer-encoding;
	b=jE5/KYcVD7keXiZuLTOd9/Xi1R+k8v5oEFga6wBxQOOORLGUNM+XDgXzQtp+5m77rW
	07NrxtaVXpcvIk2S7g8GIOds8H5zGcu48D9/Sg6aO1XUf1ZPjje1RTizaipo/qXEzHuE
	3CfmAd9nSIdxW1z9UviePs09wtneBsN/BtouY=
MIME-Version: 1.0
Received: by 10.142.125.9 with SMTP id x9mr1137266wfc.331.1233445674028; Sat,
	31 Jan 2009 15:47:54 -0800 (PST)
In-Reply-To: <200901301223.49703@zmi.at>
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>
	<200901292014.29195@zmi.at> <14708.1233277601@sss.pgh.pa.us>
	<200901301223.49703@zmi.at>
Date: Sun, 1 Feb 2009 12:47:53 +1300
Message-ID: <9adabaf40901311547r1444e0c4m4797da5b30671d63@mail.gmail.com>
Subject: Re: [GENERAL] Encoding problem using pg_dumpall
From: Cliff Pratt <enkiduonthenet@gmail.com>
To: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/173
X-Sequence-Number: 31220

On Sat, Jan 31, 2009 at 12:23 AM, Michael Monnerie
<michael.monnerie@is.it-management.at> wrote:
> On Freitag 30 Januar 2009 Tom Lane wrote:
>> The -Fc (and -Ft) formats are only designed to hold the contents of a
>> single database; and pg_restore only knows how to restore into a
>> single database.
>>
>> If you feel like fixing that, step right up.
>
> Sorry, didn't want to step on toes here. I know that sometimes if you
> perfectly know a system you get blind over alternatives, and wanted to
> throw in an idea.
>
> The format of pg_dumpall could be a tar of single pg_dumps as well,
> making pg_dumpall just call pg_dump for each db, putting that output
> into the tar output from pg_dumpall. But again, just an idea, I don't
> mind really. I'm just a sysadmin who wants things as easy as possible.
> Maybe fixing pg_dumpall to include that -E is easier.
>
You should be able to knock up a simple script in bash, perl or python
to do what you want.

Sort of like:

Use 'psql' to get a list of the databases
Sanitize the list.
Loop through the list doing backup.
At the end zip them all.

With a little bit of trickery you should be able to zip them as you go.

With regards being too close to the system, while this is true, it
also possible to be distant from the system's entrails and not don't
realize the implications and difficulties of your brilliant idea for
enhancement!

Cheers,

Cliff



From milen@radev.net Sun Feb  1 00:59:49 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 281166326EC
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 31 Jan 2009 20:59:49 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 56615-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 31 Jan 2009 20:59:45 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from ciao.gmane.org (main.gmane.org [80.91.229.2])
	by mail.postgresql.org (Postfix) with ESMTP id 1881863264A
	for <pgsql-admin@postgresql.org>; Sat, 31 Jan 2009 20:59:45 -0400 (AST)
Received: from list by ciao.gmane.org with local (Exim 4.43)
	id 1LTQgY-0007Ry-DK
	for pgsql-admin@postgresql.org; Sun, 01 Feb 2009 00:59:42 +0000
Received: from 77.70.61.39 ([77.70.61.39])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-admin@postgresql.org>; Sun, 01 Feb 2009 00:59:42 +0000
Received: from milen by 77.70.61.39 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-admin@postgresql.org>; Sun, 01 Feb 2009 00:59:42 +0000
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-admin@postgresql.org
From: "Milen A. Radev" <milen@radev.net>
Subject: Re: [GENERAL] Encoding problem using pg_dumpall
Date: Sun, 01 Feb 2009 02:59:35 +0200
Lines: 58
Message-ID: <gm2s5m$9g5$1@ger.gmane.org>
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>	
	<200901292014.29195@zmi.at> <14708.1233277601@sss.pgh.pa.us>	
	<200901301223.49703@zmi.at>
	<9adabaf40901311547r1444e0c4m4797da5b30671d63@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@ger.gmane.org
X-Gmane-NNTP-Posting-Host: 77.70.61.39
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
In-Reply-To: <9adabaf40901311547r1444e0c4m4797da5b30671d63@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.599 tagged_above=0 required=5
	tests=RCVD_IN_DNSWL_LOW=-1, RCVD_NUMERIC_HELO=2.599
X-Spam-Level: *
X-Archive-Number: 200901/174
X-Sequence-Number: 31221

Cliff Pratt написа:
[...]
> You should be able to knock up a simple script in bash, perl or python
> to do what you want.
>
> Sort of like:
>
> Use 'psql' to get a list of the databases
> Sanitize the list.
> Loop through the list doing backup.
> At the end zip them all.
>
> With a little bit of trickery you should be able to zip them as you go.
[...]


#!/bin/bash

PSQL=/usr/bin/psql
PG_DUMP=/usr/bin/pg_dump
PG_DUMPALL=/usr/bin/pg_dumpall

if [ $# -eq 0 ] ; then
        BACKUPDIR=/home/db_backup/dumps
elif [ $# -ne 1 ] ; then
        echo -e "Usage: $0 [directory]\n"
        exit 1
else
        BACKUPDIR=$1
fi

if [ ! -d $BACKUPDIR ] ; then
        if ! mkdir $BACKUPDIR ; then
                echo -e "Cannot create backup directory: $BACKUPDIR\n"
                exit 2
        fi
fi


echo Starting at `date "+%Y-%m-%d %H:%M:%S"`
echo -e -n "Dumping globals...\t\t"
$PG_DUMPALL -U postgres --globals-only > $BACKUPDIR/pg_globals.sql
echo "done."

for db in `$PSQL -U postgres -d template1 -t -c "SELECT datname FROM pg_catalog.pg_database WHERE datname "\!"~ 'template(0|1)';"`
do
        echo -n -e "Dumping database $db...\t\t"
        $PG_DUMP -U postgres --format=c $db > $BACKUPDIR/$db.dump
        echo "done."
done
echo Ended at `date "+%Y-%m-%d %H:%M:%S"`


[...]


-- 
Milen A. Radev