Обсуждение: multiple hot standby streaming replication scenario with "rotating" the primary server

Hi,
I'm trying to set up at least 3 servers using hot standby streaming
replication. I'd like to have one primary and 2 secondary (on 2
different locations in case of a desaster in the server room).
A primary
B secondary 1
C secondary 2 (on a different location that A and B)

Are the following actions in case of recovering to any of the standby
servers (B or C) correct ?

1. primary A crashes/maintenance or whatever
2. creating the trigger file on B brings this server to life.
3. stop server on C
4. make base backup on B and install it on C and A (if already available)
5. change primary_conninfo in recovery.conf on A,C to reflect new primary B
6. start server on A,C

These actions should all be automated, since I have no redundancy
between step 1 and 6, so in case something happens to the just
switched over new primary, I have no up to date server to bring up
from standby. Making base backup takes ~10 mins, installing probably
also about 10 mins, so I have 20 minutes, where nothing should happen
to B :-(

But maybe I'm also missing something.

regards
Gerhard

Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:

> I'm trying to set up at least 3 servers using hot standby
> streaming replication. I'd like to have one primary and 2
> secondary (on 2 different locations in case of a desaster in the
> server room).
> A primary
> B secondary 1
> C secondary 2 (on a different location that A and B)
>
> Are the following actions in case of recovering to any of the
> standby servers (B or C) correct ?
>
> 1. primary A crashes/maintenance or whatever
> 2. creating the trigger file on B brings this server to life.
> 3. stop server on C
> 4. make base backup on B and install it on C and A (if already
>    available)
> 5. change primary_conninfo in recovery.conf on A,C to reflect new
>    primary B
> 6. start server on A,C
>
> These actions should all be automated, since I have no redundancy
> between step 1 and 6, so in case something happens to the just
> switched over new primary, I have no up to date server to bring up
> from standby. Making base backup takes ~10 mins, installing
> probably also about 10 mins, so I have 20 minutes, where nothing
> should happen to B :-(
>
> But maybe I'm also missing something.

The main thing I would look at is how you do step 4.  Proper use of
rsync (using a daemon) directly from B to C and A (over the top of
what they have from before the crash) should be optimal.  We saw two
orders of magnitude improvement in backup time over a slow WAN doing
this.  YMMV

-Kevin

On Thu, Apr 7, 2011 at 11:40 AM, Gerhard Hintermayer
<gerhard.hintermayer@gmail.com> wrote:
> Hi,
> I'm trying to set up at least 3 servers using hot standby streaming
> replication. I'd like to have one primary and 2 secondary (on 2
> different locations in case of a desaster in the server room).
> A primary
> B secondary 1
> C secondary 2 (on a different location that A and B)
>
> Are the following actions in case of recovering to any of the standby
> servers (B or C) correct ?
>
> 1. primary A crashes/maintenance or whatever
> 2. creating the trigger file on B brings this server to life.
> 3. stop server on C
> 4. make base backup on B and install it on C and A (if already available)
> 5. change primary_conninfo in recovery.conf on A,C to reflect new primary B
> 6. start server on A,C
>
I managed to change 4. to use rsync, which is really faster that
zip/transfer/extract, even though index files have a large impact on
the transfered data volume. (my db is ~ 7GB on disk, and even though I
made minimal changes, approx 2.5 GB of data is transfered when making
a new base backup with rsyncing over the existing data dir. tI roughly
takes 10 min to rsync (over 100Mbit LAN).

Unfortunately I had to insert
2.1 reindex database [for all databases] after creating the trigger
file on the new dedicated primary, which takes another 10-20 minutes
to make any queries to the DB working (since I heavyly depend on
indices. So I have ~ 30 mins to get my new primary up and ready for
production :-(
Is there anything how I can speedup things.

I know that I have some large tables, where mos't of the reindex time
is, spent, so reindex these few tables later would be an option, but
that special treatment has to be maintained for new tables, so I'd
prefer a more generic way.

regards
Gerhard

Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:

> Unfortunately I had to insert 2.1 reindex database [for all
> databases] after creating the trigger file on the new dedicated
> primary

Why?

-Kevin

Because tests & docs say so:
http://www.postgresql.org/docs/9.0/static/continuous-archiving.html#CONTINUOUS-ARCHIVING-CAVEATS
;-) Docs say that this might be fixed sometime.

Also when I try a SELECT statement on the new primary, which makes use
of an index, I get an empty result, even though the tuple is in the
table (by just listing all tuples and see if it is there)

Gerhard

On Mon, Apr 11, 2011 at 6:09 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:
>
>> Unfortunately I had to insert 2.1 reindex database [for all
>> databases] after creating the trigger file on the new dedicated
>> primary
>
> Why?
>
> -Kevin
>

Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:

> Because tests & docs say so:
>
http://www.postgresql.org/docs/9.0/static/continuous-archiving.html#CONTINUOUS-ARCHIVING-CAVEATS

I asked because I didn't remember any mention of hashed indexes.  I
still don't know for sure that you have any, much less that you have
so many that it makes sense to reindex the entire database.

Or are you saying you've seen a problem with indexes of other types?

-Kevin

On Mon, Apr 11, 2011 at 7:25 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:
>
>> Because tests & docs say so:
>>
> http://www.postgresql.org/docs/9.0/static/continuous-archiving.html#CONTINUOUS-ARCHIVING-CAVEATS
>
> I asked because I didn't remember any mention of hashed indexes.  I
> still don't know for sure that you have any, much less that you have
> so many that it makes sense to reindex the entire database.
>
> Or are you saying you've seen a problem with indexes of other types?
>
> -Kevin
>

I have e.g. a table with:

            Table "public.auftrag_l1"
   Column   |          Type          | Modifiers
------------+------------------------+-----------
 a_nr       | integer                |
 sub_nr     | integer                |
 ch_nr_ofm  | real                   |
 case_nr    | integer                |
 datum      | date                   |
 zeit       | time without time zone |
 dauer      | integer                |
 print      | text                   |
 format     | character(1)           |
 gramm      | real                   |
 brand      | text                   |
 outrate    | smallint               |
 reliab     | smallint               |
 tech       | character varying(3)   |
 gramm_dekl | real                   |
 ch_nr_fl   | real                   |
 ch_nr      | real                   |
 fuell_gew  | real                   |
 p_code     | text                   |
Indexes:
    "idx_auftrag_l1" hash (a_nr)

when I do query this table on the newly awakened primary with
something like a_nr=1234, I get an empty result, but when I query all
tuples and manually search for the one with a_nr=1234, I see that the
tuple is there.
Seeing this and reading the docs about the caveats I see no other
solution as to REINDEX all of my DB's :-(
I have lot's of tables with primary key a_nr as above (though I know
it is not reflected in the table definition, but should result in the
same result, i.e. a_nr integer PRIMARY KEY would create an index to
lookup the table).

Gerhard

Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:

> I have e.g. a table with:

> Indexes:
>     "idx_auftrag_l1" hash (a_nr)

Any *hash* index will need to be rebuilt.  Like that one.

> Seeing this and reading the docs about the caveats I see no other
> solution as to REINDEX all of my DB's :-(

As the docs say, you need to REINDEX your *hash* indexes.  Most
people don't use those because they aren't WAL-logged, which can
lead to various problems (including this one) and because there seem
to be very few circumstances were they perform better than btree
indexes, which don't have such a problem.

If you switched to btree indexes where you currently have hash
indexes, the REINDEX would be totally unneeded.

> I have lot's of tables with primary key a_nr as above (though I
> know it is not reflected in the table definition, but should
> result in the same result, i.e. a_nr integer PRIMARY KEY would
> create an index to lookup the table).

I think a PRIMARY KEY constraint normally builds a btree index,
which wouldn't have this problem.

-Kevin

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. Should play in the
lottery this week :-)  Will check this tomorrow and recreate the
affected hash indices. As the history of this db is quite long, I
really just can't remember how this happened. Thanks for pointing out
this.

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 ?

Gerhard

On Mon, Apr 11, 2011 at 7:55 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:
>
>> I have e.g. a table with:
>
>> Indexes:
>>     "idx_auftrag_l1" hash (a_nr)
>
> Any *hash* index will need to be rebuilt.  Like that one.
>
>> Seeing this and reading the docs about the caveats I see no other
>> solution as to REINDEX all of my DB's :-(
>
> As the docs say, you need to REINDEX your *hash* indexes.  Most
> people don't use those because they aren't WAL-logged, which can
> lead to various problems (including this one) and because there seem
> to be very few circumstances were they perform better than btree
> indexes, which don't have such a problem.
>
> If you switched to btree indexes where you currently have hash
> indexes, the REINDEX would be totally unneeded.
>
>> I have lot's of tables with primary key a_nr as above (though I
>> know it is not reflected in the table definition, but should
>> result in the same result, i.e. a_nr integer PRIMARY KEY would
>> create an index to lookup the table).
>
> I think a PRIMARY KEY constraint normally builds a btree index,
> which wouldn't have this problem.
>
> -Kevin
>

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

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
>

This should of course be rsync -a ... . Much better now :-)

Sorry for this extra round.


On Tue, Apr 12, 2011 at 2:32 PM, Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:
> 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
>>
>

Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:

> This should of course be rsync -a ... . Much better now :-)

Yeah, you need to pick the right options.  The other important thing
is to use a daemon, but from the :: in the remote target
specification, it appears you're already on that.  :-)

-Kevin