Re: Data duplication when moving datafiles from one server to another.

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(at)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(dot)Grittner(at)wicourts(dot)gov>
To: pgsql-admin(at)postgresql(dot)org, IñigoMartinez Lasala
<imartinez(at)vectorsf(dot)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(at)vectorsf(dot)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