From: | Fujii Masao <masao(dot)fujii(at)gmail(dot)com> |
---|---|
To: | Andreas Pflug <pgadmin(at)pse-consulting(dot)de> |
Cc: | Scott Mead <scott(dot)lists(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: data loss with pg_standby when doing a controlled failover |
Date: | 2009-04-07 01:55:36 |
Message-ID: | 3f0b79eb0904061855k48b61592w1937b90e5f181275@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
On Mon, Apr 6, 2009 at 11:13 PM, Andreas Pflug
<pgadmin(at)pse-consulting(dot)de> wrote:
> Scott Mead wrote:
>>
>>
>>
>> On Mon, Apr 6, 2009 at 7:37 AM, Andreas Pflug
>> <pgadmin(at)pse-consulting(dot)de <mailto:pgadmin(at)pse-consulting(dot)de>> wrote:
>>
>> Running 8.3.7, I have a warm standby configuration with a
>> archive_timeout of 10min.
>>
>> It's obvious that there's a 10min period where data could be lost
>> if the
>> master fails and the warm standby server has to take over. What's not
>> obvious is that this is true even if the master server is shut down
>> regularly, because it will not write out a last log segment to the
>> archive. As a consequence, when doing a controlled failover (for
>> maintenance purposes or so) all data changed after the last
>> archive copy
>> will be lost.
>> IMHO this should be mentioned in the docs explicitly (I find it quite
>> surprising that data can be lost even if the system is shutdown
>> correctly), or better when shutting down the postmaster should
>> spit all
>> log segments containing all changes when archiving is on so the warm
>> standby server can catch up.
>>
>>
>>
>> You make an excellent point. If you're looking for a way to mitigate
>> this risk, run:
>>
>> select pg_switch_xlog() ;
>>
>> Before shutting down.
> Sort of, unless some other user succeeds to commit a transaction after
> pg_switch_xlog, and before the database ceases operation.
>
> My "graceful failover" procedure now includes this workaround:
> - shutdown server
> - restart server with --listen_addresses='' to prevent other users to
> connect (there are no local users on the server machine)
> - pg_switch_xlog()
> - shutdown finally
> - let the warm server continue
What if new xlogs are generated by autovacuum or bgwriter
between pg_switch_xlog and final shutdown? Those xlogs
can be ignored?
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
From | Date | Subject | |
---|---|---|---|
Next Message | Frans | 2009-04-07 10:23:38 | Re: PostgreSQL 8.3.7: soundex function returns UTF-16 characters |
Previous Message | Tom Lane | 2009-04-06 19:14:06 | Re: BUG #4751: Incorrect pg_dump output when dropping not null in inherited table. |