I managed to move my indices to btree and now at least data/queries
are consistent after hot standby takeover.
What I'm still worried about is the amount of time rsyncing the
basebackup to another machine (~10 mins over 100MBit LAN).
sent 4669370 bytes received 3287764 bytes 13590.32 bytes/sec
total size is 6503973554 speedup is 817.38
For large table files is see something like "468197128 63%
10.38MB/s 0:00:25" counting up (bytes,percentage)/down(time), but
that data is obviously not transfered. (because total tranfered data
is ~ 3 -4 MByte in the summary).
But when I estimate the ~6,5GB data dir size and the bandwidth of ~
10Mbyte/s, I'll get ~10minutes, so from this point of view it seems
all data is transfered.
This was with absolutely no change of data. I'm just rotating the
streaming replication server and do basebackups to the new slaves.
my basebackup is done via the following ($1 is the parameter for the
server where the basebackup is taken from)
rsync -r --delete --progress ${1}::postgresql-data/
/var/lib/postgresql/9.0/data/
Am I doing something wrong here ? I rsync _over_ the existing data dir.
Gerhard
On Mon, Apr 11, 2011 at 9:06 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:
>
>> Just checked my indices, looks like the only table in all my 5
>> DB's that has a hash index is the one I ran tests on.
>
> Well, actually that's pretty lucky. If you'd tested with other
> indexes, you might have gone live with the broken index. I would
> seriously consider converting the index to btree at this point, to
> simplify life, unless you can show a significant performance benefit
> with the hash index running your actual application mix.
>
>> But the other thing is the huge amount of transfer volume when
>> rsyncing the data dir from the new primary to set up the new
>> replication slaves. But this should go away when I stop using
>> REINDEX DATABASE, right ?
>
> That should make a big difference, yeah.
>
> -Kevin
>