To: Jean-Armel Luce <jaluce06@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: rsync and streaming replication
References: 
 <CALnckSpEi-6JjfEFLUcp13nScHbbcywFcmhn51iK2sj_eXbEkQ@mail.gmail.com>
From: Jerry Sievers <gsievers19@comcast.net>
Date: Sat, 12 Nov 2011 14:21:22 -0500
In-Reply-To: 
 <CALnckSpEi-6JjfEFLUcp13nScHbbcywFcmhn51iK2sj_eXbEkQ@mail.gmail.com>
	(Jean-Armel Luce's message of "Sat\,
	12 Nov 2011 15\:15\:56 +0100")
Message-ID: <87pqgxf8p9.fsf@comcast.net>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Jean-Armel Luce <jaluce06@gmail.com> writes:

> Hi all,
>
> I am using Postgres 9.0.3 in production with Slony 2.0.6 for replication.
> I am currently testing the streaming replication with 9.0.3.? My database contains 100 GBytes of data (6000 tables).
>
> I have 1 master node and 3 slave nodes. The 1st slave node is in the same datacenter as the master node. The 2 other
> slave nodes are in a 2nd distant datacenter.
>
> Say node 1 is the master node, node 2 is the local slave node, and nodes 3 and 4 are the slave nodes in the distant
> datacenter.
>
> After installation of the streaming replication, I have checked that all the files have been copied from the master to
> the slaves and that files have the same size and modification date on each node.
> When I send update requests, the streaming replication works perfectly; I am able to replicate many thousands of
> updates per second.
> I have updated, inserted and deleted nearly 70% of the rows in all the tables (mainly update requests), and
> replication is working perfectly.
>
> However; when I want to promote node 3 as the master, I need to rsync all the logs and databases from node 3 to node
> 4, node 1 and node 2.

That is a popular misconception.

Assuming you've got your WAL repositories well organized and your
standbys are configure to follow latest timeline...

You should be able to repoint the existing standbys and even the
demoted master just by creating or changing recovery.conf as needed
and restarting all standbys.

I did a talk about that at Pg-East 2010 based on Pg 8.4.  It all still
seems to work now with streaming replication and hot-standby as well.

HTH

> Even if I use rsync -a (as suggested in the wiki http://wiki.postgresql.org/wiki/Streaming_Replication), it takes a
> long time (nearly 2 hours for a distant slave).
> It looks that all database files do not have the same modification date in the master node and in the slave nodes, so
> the rsync copies quite all the database from the new master to the slaves.
>
> At first, I was suspecting vacuum process for modifying the files not simultaneously in the master and the slaves.
> When I check the dates? last autoanalyze and last_autovacuum fields in pg_stat_user_tables, they are diffent than the
> mtime of the files; so it looks autovacuum is not responsible of that.
>
> Please, could you help me to understand why it is so long to rsync the data from the new master to the other slaves ?
> Did I miss anything ?
> Any idea ?
>
> Jean-Armel
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144