From imartinez@vectorsf.com Mon Dec  20 19:31:03 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id D1AC11337B5B
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 20 Dec 2010 15:31:03 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 17571-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 20 Dec 2010 19:30:57 +0000 (UTC)
X-Greylist: delayed 00:08:11.790214 by SQLgrey-1.7.6
Received: from hermes.vectorsf.com (firewall.vectorsf.com [62.82.80.62])
	by mail.postgresql.org (Postfix) with ESMTP id 34375133616B
	for <pgsql-admin@postgresql.org>; Mon, 20 Dec 2010 15:30:56 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by hermes.vectorsf.com (Postfix) with ESMTP id 7FCD411C0165
	for <pgsql-admin@postgresql.org>; Mon, 20 Dec 2010 20:22:39 +0100 (CET)
X-Virus-Scanned: amavisd-new at hermes.vectorsf.com
Received: from hermes.vectorsf.com ([127.0.0.1])
	by localhost (hermes.vectorsf.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id K98+NPBiKsZe for <pgsql-admin@postgresql.org>;
	Mon, 20 Dec 2010 20:22:39 +0100 (CET)
Received: from [192.168.25.110] (unknown [192.168.25.110])
	(Authenticated sender: imartinez)
	by hermes.vectorsf.com (Postfix) with ESMTPSA id 17E7A11C00BD
	for <pgsql-admin@postgresql.org>; Mon, 20 Dec 2010 20:22:38 +0100 (CET)
Subject: Data duplication when moving datafiles from one server to another.
From: =?ISO-8859-1?Q?I=F1igo?= Martinez Lasala <imartinez@vectorsf.com>
To: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary="=-N9s5KU8TL05Gq3Yvypu9"
Date: Mon, 20 Dec 2010 20:22:40 +0100
Message-ID: <1292872960.20992.8.camel@deimos>
Mime-Version: 1.0
X-Mailer: Evolution 2.30.3 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.209 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_MED=-2.3,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/134
X-Sequence-Number: 36363


--=-N9s5KU8TL05Gq3Yvypu9
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Good evening.

Yesterday we experienced some data duplication in several database
tables after one sysadmin decided to test in a production environment an
rsync script in order to migrate a database from one server to another
one.
Postgresql (8.2) was running in source server and rsync script was
launched from second one. Second one server had a one day old copy of
the same database. Rsync script create a datafile replica in destination
server. 
Our sysadmin swear he didn't launch the script in a reverse way (that
is, from destination to source)... so my question is
How this data duplication could happen? Due to an rsync lock in
checkpoint segment or transaction logs? Or he has mistaken source with
destination server?

Thans in advance.



--=-N9s5KU8TL05Gq3Yvypu9
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.30.3">
</HEAD>
<BODY>
Good evening.<BR>
<BR>
Yesterday we experienced some data duplication in several database tables after one sysadmin decided to test in a production environment an rsync script in order to migrate a database from one server to another one.<BR>
Postgresql (8.2) was running in source server and rsync script was launched from second one. Second one server had a one day old copy of the same database. Rsync script create a datafile replica in destination server. <BR>
Our sysadmin swear he didn't launch the script in a reverse way (that is, from destination to source)... so my question is<BR>
How this data duplication could happen? Due to an rsync lock in checkpoint segment or transaction logs? Or he has mistaken source with destination server?<BR>
<BR>
Thans in advance.<BR>
<BR>
<BR>
</BODY>
</HTML>

--=-N9s5KU8TL05Gq3Yvypu9--




From Kevin.Grittner@wicourts.gov Mon Dec  20 19:43:14 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 7D1821337B68
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 20 Dec 2010 15:43:14 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 79781-07 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 20 Dec 2010 19:43:06 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id C631A1337B5B
	for <pgsql-admin@postgresql.org>; Mon, 20 Dec 2010 15:43:05 -0400 (AST)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Mon, 20 Dec 2010 13:43:05 -0600
Message-Id: <4D0F5D650200002500038840@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Mon, 20 Dec 2010 13:43:01 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-admin@postgresql.org>,
	"=?ISO-8859-15?Q?I=F1igoMartinez=20Lasala?=" <imartinez@vectorsf.com>
Subject: Re: Data duplication when moving datafiles from one server to
 another.
References: <1292872960.20992.8.camel@deimos>
In-Reply-To: <1292872960.20992.8.camel@deimos>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 8bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/135
X-Sequence-Number: 36364

IñigoMartinez Lasala <imartinez@vectorsf.com> wrote:
 
> Yesterday we experienced some data duplication in several database
> tables after one sysadmin decided to test in a production
> environment an rsync script in order to migrate a database from
> one server to another one.
> Postgresql (8.2) was running in source server and rsync script was
> launched from second one. Second one server had a one day old copy
> of the same database. Rsync script create a datafile replica in
> destination server. 
> Our sysadmin swear he didn't launch the script in a reverse way
> (that is, from destination to source)... so my question is
> How this data duplication could happen?
 
Where did this duplication happen -- source or destination?  If in
the source, I would suspect operator error by your sysadmin; I have
never seen rsync cause problems with the source files.  If on the
target, I would suspect that you didn't follow PITR recovery
techniques properly.
 
If you need to follow up on this, please include a few more details,
like the exact PostgreSQL version from `select version();` and the
exact version of the OS.  The exact rsync statement(s) used would be
good to see, too -- maybe from the bash history (or similar) on the
machines.
 
-Kevin



From scott.marlowe@gmail.com Mon Dec  20 20:36:52 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 556041337B98
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 20 Dec 2010 16:36:52 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 28481-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 20 Dec 2010 20:36:45 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ww0-f50.google.com (mail-ww0-f50.google.com
 [74.125.82.50])
	by mail.postgresql.org (Postfix) with ESMTP id 817A81337B96
	for <pgsql-admin@postgresql.org>; Mon, 20 Dec 2010 16:36:44 -0400 (AST)
Received: by wwf26 with SMTP id 26so3417278wwf.19
	for <pgsql-admin@postgresql.org>; Mon, 20 Dec 2010 12:36:43 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:received:in-reply-to
	:references:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=5UmGWpUPtiYPF3SWGJtATxeThPP7uXtyLw04DjCTOvs=;
	b=mR1RQvvUFBNilroKy75G8Bs23OfG96BufBgNbqGWMkUPn20wjqp1++pgwGa1bgBwOL
	kI0aYivVZ+FmOAq5QDqsKbBvk9eNNOalp1DYYAv2zbqixuT5EWGlX1Adm8e838cLqYn6
	J/7Jhrgsx6N5ZBIO+vTalwAWEQiOG1zB+ZRt8=
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
	:cc:content-type:content-transfer-encoding;
	b=KlvwG5PyaF+stDh89/E4pD/Mw/CY/KGOxq2DC1hFcXMoXxTzznTvOFqKPMGL/Top+q
	wACTZ96mCS6xCT7u5ACD9piEYdOiOh8ri5z4SoHIWopEk/JapUE02Xl95kxpR8p85xY5
	QLvHRHAoPJ6vmml+uNYD6/C6lHiqJQD+aQ14Q=
MIME-Version: 1.0
Received: by 10.227.136.132 with SMTP id r4mr2914202wbt.38.1292877403506; Mon,
	20 Dec 2010 12:36:43 -0800 (PST)
Received: by 10.227.180.79 with HTTP; Mon, 20 Dec 2010 12:36:43 -0800 (PST)
In-Reply-To: <1292872960.20992.8.camel@deimos>
References: <1292872960.20992.8.camel@deimos>
Date: Mon, 20 Dec 2010 13:36:43 -0700
Message-ID: <AANLkTimRc9NsSCws6PzMyXrYJnSA=TysB=N2=tX9drM4@mail.gmail.com>
Subject: Re: Data duplication when moving datafiles from one server to
 another.
From: Scott Marlowe <scott.marlowe@gmail.com>
To: =?ISO-8859-1?Q?I=F1igo_Martinez_Lasala?= <imartinez@vectorsf.com>
Cc: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201012/136
X-Sequence-Number: 36365

On Mon, Dec 20, 2010 at 12:22 PM, I=F1igo Martinez Lasala
<imartinez@vectorsf.com> wrote:
> Good evening.
>
> Yesterday we experienced some data duplication in several database tables
> after one sysadmin decided to test in a production environment an rsync
> script in order to migrate a database from one server to another one.
> Postgresql (8.2) was running in source server and rsync script was launch=
ed
> from second one. Second one server had a one day old copy of the same
> database. Rsync script create a datafile replica in destination server.
> Our sysadmin swear he didn't launch the script in a reverse way (that is,
> from destination to source)... so my question is
> How this data duplication could happen? Due to an rsync lock in checkpoin=
t
> segment or transaction logs? Or he has mistaken source with destination
> server?

I use this method all the time to backup big dbs from one machine to anothe=
r:

(on remote machine):
/etc/init.d/postgresql stop

(on main server):
rsync -avl --delete /data/* backupserver:/data/* # takes forever
sudo /etc/init.d/postgresql stop
rsync -avl --delete /data/* backupserver:/data/* # fast, we're just catchin=
g up
sudo /etc/init.d/postgresql start

(on remote machine):
/etc/init.d/postgresql start

You can run such a script at 2 in the morning with a few minutes
downtime without having to jump through a lot of hoops.

So, can you show us what commands your sysadmin / dba actually typed?
My guess is he left out the --delete or left the backup server db up
and running at the time of the rsync.  It needs to be shut down to do
this right.



From imartinez@vectorsf.com Tue Dec  21 07:06:21 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 29E2F1337B82
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 21 Dec 2010 03:06:21 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 39338-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 21 Dec 2010 07:06:13 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from hermes.vectorsf.com (firewall.vectorsf.com [62.82.80.62])
	by mail.postgresql.org (Postfix) with ESMTP id 795CB1337B9A
	for <pgsql-admin@postgresql.org>; Tue, 21 Dec 2010 03:06:12 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by hermes.vectorsf.com (Postfix) with ESMTP id 864E211C0148;
	Tue, 21 Dec 2010 08:06:10 +0100 (CET)
X-Virus-Scanned: amavisd-new at hermes.vectorsf.com
Received: from hermes.vectorsf.com ([127.0.0.1])
	by localhost (hermes.vectorsf.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id fKOcVL8K1nEs; Tue, 21 Dec 2010 08:06:10 +0100 (CET)
Received: from [192.168.25.110] (unknown [192.168.25.110])
	(Authenticated sender: imartinez)
	by hermes.vectorsf.com (Postfix) with ESMTPSA id A36A811C00BD;
	Tue, 21 Dec 2010 08:06:09 +0100 (CET)
Subject: Re: Data duplication when moving datafiles from one server to
 another.
From: =?ISO-8859-1?Q?I=F1igo?= Martinez Lasala <imartinez@vectorsf.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-admin@postgresql.org
In-Reply-To: <4D0F5D650200002500038840@gw.wicourts.gov>
References: <1292872960.20992.8.camel@deimos>
	<4D0F5D650200002500038840@gw.wicourts.gov>
Content-Type: multipart/alternative; boundary="=-Qgl4fkGZBMdO+UWnnAiE"
Date: Tue, 21 Dec 2010 08:06:09 +0100
Message-ID: <1292915169.20992.22.camel@deimos>
Mime-Version: 1.0
X-Mailer: Evolution 2.30.3 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.209 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_MED=-2.3,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/142
X-Sequence-Number: 36371


--=-Qgl4fkGZBMdO+UWnnAiE
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit

Hi Kevin, Scott.

Thank you for your response.

Data duplication happened on source server (that is, on production
environment). This data duplication affected only on some tables, with
some duplicated PKs and altered sequences (sequences value didn't match
"select max()" and we had to fix it manually).
Postgres version is 8.2.7 64bit on Ubuntu Server 8.04 LTS

Rsync command was:
rsync -avt -lHpogDtx /srv/postgresql/8.2/main/
root@destination_server:/srv/postgresql/8.2/main/

and was launched from source server.

Remember: source server was running.

If rsync sentence was launched correctly (I assume yes), could a
temporary lock over a checkpoint segment cause this behaviour?  

We have managed to delete duplicated registers and restore incorrect
sequences, but I don't really know what happened. Since I've been
indirect responsable from this (sysadmin reports me) I need to clarify
the cause. I need an valid explanation for our client. 


-----Original Message-----
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-admin@postgresql.org, IñigoMartinez Lasala
<imartinez@vectorsf.com>
Subject: Re: [ADMIN] Data duplication when moving datafiles from one
server to another.
Date: Mon, 20 Dec 2010 13:43:01 -0600


IñigoMartinez Lasala <imartinez@vectorsf.com> wrote:
 
> Yesterday we experienced some data duplication in several database
> tables after one sysadmin decided to test in a production
> environment an rsync script in order to migrate a database from
> one server to another one.
> Postgresql (8.2) was running in source server and rsync script was
> launched from second one. Second one server had a one day old copy
> of the same database. Rsync script create a datafile replica in
> destination server. 
> Our sysadmin swear he didn't launch the script in a reverse way
> (that is, from destination to source)... so my question is
> How this data duplication could happen?
 
Where did this duplication happen -- source or destination?  If in
the source, I would suspect operator error by your sysadmin; I have
never seen rsync cause problems with the source files.  If on the
target, I would suspect that you didn't follow PITR recovery
techniques properly.
 
If you need to follow up on this, please include a few more details,
like the exact PostgreSQL version from `select version();` and the
exact version of the OS.  The exact rsync statement(s) used would be
good to see, too -- maybe from the bash history (or similar) on the
machines.
 
-Kevin



--=-Qgl4fkGZBMdO+UWnnAiE
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.30.3">
</HEAD>
<BODY>
Hi Kevin, Scott.<BR>
<BR>
Thank you for your response.<BR>
<BR>
Data duplication happened on source server (that is, on production environment). This data duplication affected only on some tables, with some duplicated PKs and altered sequences (sequences value didn't match &quot;select max()&quot; and we had to fix it manually).<BR>
Postgres version is 8.2.7 64bit on Ubuntu Server 8.04 LTS<BR>
<BR>
Rsync command was:<BR>
rsync -avt -lHpogDtx /srv/postgresql/8.2/main/ root@destination_server:/srv/postgresql/8.2/main/<BR>
<BR>
and was launched from source server.<BR>
<BR>
Remember: source server was running.<BR>
<BR>
If rsync sentence was launched correctly (I assume yes), could a temporary lock over a checkpoint segment cause this behaviour?&nbsp; <BR>
<BR>
We have managed to delete duplicated registers and restore incorrect sequences, but I don't really know what happened. Since I've been indirect responsable from this (sysadmin reports me) I need to clarify the cause. I need an valid explanation for our client. <BR>
<BR>
<BR>
-----Original Message-----<BR>
<B>From</B>: Kevin Grittner &lt;<A HREF="mailto:Kevin%20Grittner%20%3cKevin.Grittner@wicourts.gov%3e">Kevin.Grittner@wicourts.gov</A>&gt;<BR>
<B>To</B>: <A HREF="mailto:pgsql-admin@postgresql.org">pgsql-admin@postgresql.org</A>, I&#241;igoMartinez Lasala &lt;<A HREF="mailto:%3d%3fISO-8859-1%3fQ%3fI%3dF1igoMartinez%3f%3d%20Lasala%20%3cimartinez@vectorsf.com%3e">imartinez@vectorsf.com</A>&gt;<BR>
<B>Subject</B>: Re: [ADMIN] Data duplication when moving datafiles from one server to another.<BR>
<B>Date</B>: Mon, 20 Dec 2010 13:43:01 -0600<BR>
<BR>
<PRE>
I&#241;igoMartinez Lasala &lt;<A HREF="mailto:imartinez@vectorsf.com">imartinez@vectorsf.com</A>&gt; wrote:
 
&gt; Yesterday we experienced some data duplication in several database
&gt; tables after one sysadmin decided to test in a production
&gt; environment an rsync script in order to migrate a database from
&gt; one server to another one.
&gt; Postgresql (8.2) was running in source server and rsync script was
&gt; launched from second one. Second one server had a one day old copy
&gt; of the same database. Rsync script create a datafile replica in
&gt; destination server. 
&gt; Our sysadmin swear he didn't launch the script in a reverse way
&gt; (that is, from destination to source)... so my question is
&gt; How this data duplication could happen?
 
Where did this duplication happen -- source or destination?  If in
the source, I would suspect operator error by your sysadmin; I have
never seen rsync cause problems with the source files.  If on the
target, I would suspect that you didn't follow PITR recovery
techniques properly.
 
If you need to follow up on this, please include a few more details,
like the exact PostgreSQL version from `select version();` and the
exact version of the OS.  The exact rsync statement(s) used would be
good to see, too -- maybe from the bash history (or similar) on the
machines.
 
-Kevin
</PRE>
<BR>
</BODY>
</HTML>

--=-Qgl4fkGZBMdO+UWnnAiE--




From Kevin.Grittner@wicourts.gov Tue Dec  21 17:50:36 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 741CA1337B5B
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 21 Dec 2010 13:50:36 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 28992-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 21 Dec 2010 17:50:28 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 3D3BD1337B69
	for <pgsql-admin@postgresql.org>; Tue, 21 Dec 2010 13:50:28 -0400 (AST)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Tue, 21 Dec 2010 11:50:27 -0600
Message-Id: <4D10948002000025000388C3@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Tue, 21 Dec 2010 11:50:24 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "=?ISO-8859-15?Q?I=F1igoMartinez=20Lasala?=" <imartinez@vectorsf.com>
Cc: <pgsql-admin@postgresql.org>
Subject: Re: Data duplication when moving datafiles from one server to
 another.
References: <1292872960.20992.8.camel@deimos>
	<4D0F5D650200002500038840@gw.wicourts.gov>
	<1292915169.20992.22.camel@deimos>
In-Reply-To: <1292915169.20992.22.camel@deimos>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 8bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/149
X-Sequence-Number: 36378

IñigoMartinez Lasala <imartinez@vectorsf.com> wrote:
 
> Data duplication happened on source server (that is, on production
> environment). This data duplication affected only on some tables,
> with some duplicated PKs and altered sequences (sequences value
> didn't match "select max()" and we had to fix it manually).
 
We rsync from production servers all the time, and have never seen
such problems.  Maybe someone else has ideas beyond my long shots
(below).
 
> Postgres version is 8.2.7 64bit on Ubuntu Server 8.04 LTS
 
There have been 12 bug fix releases for the 8.2 major release since
8.2.7.  It's not impossible that you somehow hit a bug which has
been fixed in the last 33 months.
 
http://www.postgresql.org/support/versioning
 
http://www.postgresql.org/docs/8.2/static/release.html
 
> Rsync command was:
> rsync -avt -lHpogDtx /srv/postgresql/8.2/main/
> root@destination_server:/srv/postgresql/8.2/main/
 
Did it run successfully on the first try, or might there have been
an attempt where the target was mis-specified?  A space instead of a
colon between the target server and path might have caused the kind
of corruption you're seeing.
 
Also -- I would avoid using root for such things.  We have remote
root login disabled entirely on our machines, and limit the use of
that login to situations where there is no reasonable alternative.
 
> and was launched from source server.
 
I find it's harder to accidentally damage the remote copy, so I run
rsync from the target where possible.
 
-Kevin