Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 29E2F1337B82 for ; 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 ; 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 ; 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 To: Kevin Grittner 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 To: pgsql-admin@postgresql.org, IñigoMartinez Lasala 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 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 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--