Thread: Replication broken. Stale data in slave Database

Replication broken. Stale data in slave Database

From
Alberto Olivares
Date:
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?

Thank you for your answers.

Regards,
Alberto.

Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------




Winner of IHS Jane's ATC Award - Enabling Technology

Re: Replication broken. Stale data in slave Database

From
John Scalia
Date:
Alberto,

I assume you synchronous streaming replication, but have you looked at asynchronous replication just using WAL files? You could have your standby server lag behind by a fixed amount and maybe then you wouldn't have to delete on failover. Without more details, that's the best I can offer.
--
Jay

On Wed, Jun 24, 2015 at 7:43 AM, Alberto Olivares <alberto.olivares@snowflakesoftware.com> wrote:
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?

Thank you for your answers.

Regards,
Alberto.

Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------




Winner of IHS Jane's ATC Award - Enabling Technology

Re: Replication broken. Stale data in slave Database

From
"Gilberto Castillo"
Date:
> 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



Re: Replication broken. Stale data in slave Database

From
Alberto Olivares
Date:
Hi John and Gilberto,

Thanks for your answer.

We have a synchronous streaming replication. Database is PostgreSQL 9.1.15 on linux.

Saludos,
Alberto.

Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------




On 24 June 2015 at 17:07, Gilberto Castillo <gilberto.castillo@etecsa.cu> wrote:

> 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



Winner of IHS Jane's ATC Award - Enabling Technology

Re: Replication broken. Stale data in slave Database

From
"Gilberto Castillo"
Date:
> 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



Re: Replication broken. Stale data in slave Database

From
Alberto Olivares
Date:
Hi Gilberto,

Thanks for your answer.

Do you know how to configure the pg_archivecleanup just in the event the primary database goes down?

Regards,
Alberto.

Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------




On 24 June 2015 at 17:17, Gilberto Castillo <gilberto.castillo@etecsa.cu> wrote:

> 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



Winner of IHS Jane's ATC Award - Enabling Technology

Re: Replication broken. Stale data in slave Database

From
"Gilberto Castillo"
Date:
> 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



Re: Replication broken. Stale data in slave Database

From
Alberto Olivares
Date:
Hi Gilberto,

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

Thanks.

Regards,
Alberto.

Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------




On 24 June 2015 at 17:47, Gilberto Castillo <gilberto.castillo@etecsa.cu> wrote:

> 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



Winner of IHS Jane's ATC Award - Enabling Technology

Re: Replication broken. Stale data in slave Database

From
"Gilberto Castillo"
Date:
> 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



Re: Replication broken. Stale data in slave Database

From
Alberto Olivares
Date:

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

On 24 Jun 2015 5:46 pm, "Gilberto Castillo" <gilberto.castillo@etecsa.cu> wrote:

> 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


Winner of IHS Jane's ATC Award - Enabling Technology

Re: Replication broken. Stale data in slave Database

From
"Gilberto Castillo"
Date:
> 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



Re: Replication broken. Stale data in slave Database

From
Alberto Olivares
Date:

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.

On 24 Jun 2015 6:03 pm, "Gilberto Castillo" <gilberto.castillo@etecsa.cu> wrote:

> 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


Winner of IHS Jane's ATC Award - Enabling Technology

Re: Replication broken. Stale data in slave Database

From
"Gilberto Castillo"
Date:
> 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



Re: Replication broken. Stale data in slave Database

From
Alberto Olivares
Date:

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

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


Winner of IHS Jane's ATC Award - Enabling Technology

Re: Replication broken. Stale data in slave Database

From
"Gilberto Castillo"
Date:
> 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



Re: Replication broken. Stale data in slave Database

From
Alberto Olivares
Date:
Yes, We turn off both databases when there is a problem with the master. But, we need something that automatically clears out the slave database when data is stale because there was a problem in the master database.

Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------




On 24 June 2015 at 19:41, Gilberto Castillo <gilberto.castillo@etecsa.cu> wrote:

> 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



Winner of IHS Jane's ATC Award - Enabling Technology