Thread: streaming replication trigger file

Hi all,

I am a bit confused regarding streaming replication.

I have two server: A and B.
I set up replication successfully.
But when the master A goes down, B are not acting as master.

My recovery.conf as follows:

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.91 port=5432 user=postgres'
trigger_file = 'C:\\pg\\stopreplication\\standby.txt' 
restore_command = 'copy \pg\\wal_archive_91\\%f %p' 

When I manually create the C:\\pg\\stopreplication\\standby.txt' file, then it is working. That is, B is becoming the master.
So, my question is, how this trigger file should be created so that B will become master automatically as soon as A goes down?

On 06/16/11 1:31 AM, AI Rumman wrote:
> When I manually create the C:\\pg\\stopreplication\\standby.txt' file,
> then it is working. That is, B is becoming the master.
> So, my question is, how this trigger file should be created so that B
> will become master automatically as soon as A goes down?

you need cluster management software, such as Heartbeat (on linux) or
Veritas Cluster Service (on various operating systems) configured to
detect system failure, and reconfigure the slave to be the master.
This software also generally is used to manage things like the shared IP

really really REALLY important is what to do when the failed original
master is brought back up.  it can NOT be allowed to wake up thinking
its still a master since its lacking any updates since it went down,
instead it has to be reconfigured to be a new slave.

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Hi Rumman,

Can you send me link to "how to create postgres replication on windows."
I tried few hours as per following link, but not succeeded. Which
version of postgres are you using ? I tried using 9.0.
http://www.postgresql.org/docs/8.2/static/high-availability.html

Any type of help would be appreciated.

Regards,
Sanjay Rao

On 6/16/2011 2:01 PM, AI Rumman wrote:
> Hi all,
>
> I am a bit confused regarding streaming replication.
>
> I have two server: A and B.
> I set up replication successfully.
> But when the master A goes down, B are not acting as master.
>
> My recovery.conf as follows:
>
> standby_mode = 'on'
> primary_conninfo = 'host=10.0.0.91 port=5432 user=postgres'
> trigger_file = 'C:\\pg\\stopreplication\\standby.txt'
> restore_command = 'copy \pg\\wal_archive_91\\%f %p'
>
> When I manually create the C:\\pg\\stopreplication\\standby.txt' file,
> then it is working. That is, B is becoming the master.
> So, my question is, how this trigger file should be created so that B
> will become master automatically as soon as A goes down?

Hi,

My main database server was not being replicated on standby server. In
standby's logs I am getting :

2011-06-16 16:22:03 IST FATAL:  database system identifier differs
between the primary and standby
2011-06-16 16:22:03 IST DETAIL:  The primary's identifier is
5618757375637716504, the standby's identifier is 5618757422882348667.

Does anybody have any idea ?


Regards,
Sanjay Rao

On 6/16/2011 2:50 PM, Sanjay Rao wrote:
> Hi Rumman,
>
> Can you send me link to "how to create postgres replication on windows."
> I tried few hours as per following link, but not succeeded. Which
> version of postgres are you using ? I tried using 9.0.
> http://www.postgresql.org/docs/8.2/static/high-availability.html
>
> Any type of help would be appreciated.
>
> Regards,
> Sanjay Rao
>
> On 6/16/2011 2:01 PM, AI Rumman wrote:
>> Hi all,
>>
>> I am a bit confused regarding streaming replication.
>>
>> I have two server: A and B.
>> I set up replication successfully.
>> But when the master A goes down, B are not acting as master.
>>
>> My recovery.conf as follows:
>>
>> standby_mode = 'on'
>> primary_conninfo = 'host=10.0.0.91 port=5432 user=postgres'
>> trigger_file = 'C:\\pg\\stopreplication\\standby.txt'
>> restore_command = 'copy \pg\\wal_archive_91\\%f %p'
>>
>> When I manually create the C:\\pg\\stopreplication\\standby.txt'
>> file, then it is working. That is, B is becoming the master.
>> So, my question is, how this trigger file should be created so that B
>> will become master automatically as soon as A goes down?

I think you should make the base database the same

6. Make a base backup by copying the primary server's data directory to the
standby server.
$ psql -c "SELECT pg_start_backup('label', true)"
$ rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/streaming-replication-trigger-file-tp4494129p4559669.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 16/06/11 18:44, John R Pierce wrote:
> On 06/16/11 1:31 AM, AI Rumman wrote:
>> When I manually create the C:\\pg\\stopreplication\\standby.txt' file,
>> then it is working. That is, B is becoming the master.
>> So, my question is, how this trigger file should be created so that B
>> will become master automatically as soon as A goes down?
>
> you need cluster management software, such as Heartbeat (on linux) or
> Veritas Cluster Service (on various operating systems) configured to
> detect system failure, and reconfigure the slave to be the master. This
> software also generally is used to manage things like the shared IP
>
> really really REALLY important is what to do when the failed original
> master is brought back up. it can NOT be allowed to wake up thinking its
> still a master since its lacking any updates since it went down, instead
> it has to be reconfigured to be a new slave.

Just following on a bit from this..

It seems fairly hard to get that part right - ensuring that the
former-master stays down, and also that automatic scripts don't run off
and convert the wrong one into a new standby either.

Do you mind if I ask how other people are doing this? Are you finding
heartbeat, keepalived or pacemaker better? What sort of ways are you
triggering the fail-over by?

And, how are you avoiding having a former-master come back up thinking
it's still the master.. yet still coping with a situation where, say,
first one machine, then the other, reboot. (thus easily triggering a
failover to one, then it going down and the other coming up and looking
like it's alone in the world)

Thanks!
Toby