Thread: Replication broken. Stale data in slave Database

> Hi all,
>
> We have set up a Streaming replication.
>
> Master database serves temporal data being replicated on the Slave
> database.
>
> In the event that we shut down the Master database, the slave database
> contains residual data that we do not need.
>
> Is there any way to trigger a SQL to delete all data from the slave
> database in the event that the replication is broken?
>

What your version PG?


Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba

> Hi John and Gilberto,
>
> Thanks for your answer.
>
> We have a synchronous streaming replication. Database is PostgreSQL 9.1.15
> on linux.


Uhmmmm, see you:
http://www.postgresql.org/docs/current/static/pgarchivecleanup.html

In 9.1 use script what delete for date the wal more old.


Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba

> Hi Gilberto,
>
> Thanks for your answer.
>
> Do you know how to configure the pg_archivecleanup just in the event the
> primary database goes down?


Look  pg_archivecleanup is configured in the slave in version major pg 9.4

It's not your case.


Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba

> Hi Gilberto,
>
> Ups. So, I cannot try to use this solution. Any other ideas?

Use an cron of SO, Monthy. Call an script what delete the old wal.

Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba

Hi,

I thought about creating a cron job but we cannot run it monthly, we need to do it just in case the replication fails because the master database breaks.

Regards,
Alberto

> Hi,
>
> I thought about creating a cron job but we cannot run it monthly, we need
> to do it just in case the replication fails because the master database
> breaks.

You not use failover?
What use for manager the replication?


Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba

Hi,

Yes we have failover. If after a few minutes the master database goes up the replication works again which is fine.

But I need something that deletes all records in the slave database meanwhile master database is down. The reason is because we are sending not updated data when this happens and we prefer deleted what it is in the slave database.

> Hi,
>
> Yes we have failover. If after a few minutes the master database goes up
> the replication works again which is fine.
>
> But I need something that deletes all records in the slave database
> meanwhile master database is down. The reason is because we are sending
> not
> updated data when this happens and we prefer deleted what it is in the
> slave database.

Uhmmmm, use rsync of the folder wal in the master and slave.



Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba

Not sure if I can do that when the master database is shut down

> Not sure if I can do that when the master database is shut down

Yes, In fact both server must be turned off for safety.


> On 24 Jun 2015 6:36 pm, "Gilberto Castillo" <gilberto.castillo@etecsa.cu>
> wrote:
>
>> > Hi,
>> >
>> > Yes we have failover. If after a few minutes the master database goes
>> up
>> > the replication works again which is fine.
>> >
>> > But I need something that deletes all records in the slave database
>> > meanwhile master database is down. The reason is because we are
>> sending
>> > not
>> > updated data when this happens and we prefer deleted what it is in the
>> > slave database.
>>
>> Uhmmmm, use rsync of the folder wal in the master and slave.
>>
>>
>>

Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba