Re: Streaming replication and WAL archiving - Mailing list pgsql-admin

From Greg Clough
Subject Re: Streaming replication and WAL archiving
Date
Msg-id CABZWJif7cJ1xFpQaLUifgAGhgxs3=iyKG2p3wyr4BCUs6Krwpg@mail.gmail.com
Whole thread Raw
In response to Streaming replication and WAL archiving  ("David F. Skoll" <dfs@roaringpenguin.com>)
List pgsql-admin
Hi David,

Have you investigated the "Replication Slots" feature that's new in 9.4.  It sounds like what you want:


Regards.
Greg.

Greg Clough

Database Administrator | CWT Digital

t. 0845 456 0070 w. cwtdigital.com


Units A/B, Level 8 North, New England House, 
New England Street, Brighton, East Sussex, BN1 4GH

https://ci6.googleusercontent.com/proxy/spbuXLCE_kiYn1CBmHRh6DoZX1WwETayC0rma88wNedREVrYPrjvkpQLcBPeuKfSkfkOpDSkuDvye9g2Ps4-BW4s=s0-d-e1-ft#http://www.cwtdigital.com/hosted/twitter.png https://ci5.googleusercontent.com/proxy/q4GOyacafkMnvs7TLpy3rOcwVK_uxMoECtqVEVpwmxiLShfkGNOZGisnwu4oHGtcEK_C-TfrLguFshp1VKC-5zZw6g=s0-d-e1-ft#http://www.cwtdigital.com/hosted/facebook.png https://ci4.googleusercontent.com/proxy/BVh2CnIvbQ_Rp5LFVzt4p72zUvhG3u7ksBMtlWRjbWjXL-DXIFEXXZJCYWMlSd3MshfG4UN0NAuTj1eElX8r5iu2KQ=s0-d-e1-ft#http://www.cwtdigital.com/hosted/linkedin.png https://ci4.googleusercontent.com/proxy/QJJKpRXCvrkfCat5ycimzE7Lve3Pp9wFZWL5eBF5ELDkpl_gA8UVOc-R2p29DTS5DJmfD-FHGg2cjSf4lEuz2Ts=s0-d-e1-ft#http://www.cwtdigital.com/hosted/google.png https://ci4.googleusercontent.com/proxy/X2ddbFYXE1hmnS4LGzoCr-Pl8fpK6yoO9R-jjF3__nbZkbuCmQR2LD3-Ts3deetyHm0L8E-hjyXeRS0YTkcqtNNGkxM=s0-d-e1-ft#http://www.cwtdigital.com/hosted/instagram.png


On 10 February 2015 at 22:21, David F. Skoll <dfs@roaringpenguin.com> wrote:
Hi,

I have a two-database pair with a master and hot-standby.  The hot-standby
is connected to the master via streaming replication, but I also archive
WAL files from master to standby just for safety.

It occurs to me that this is a waste of bandwidth.  I could set
wal_keep_segments high and forget about the archiving, but what
happens if disaster strikes and the backup is down for too long?

I'm wondering if there's any way to write an archive_command script that:

1) Looks at the WAL file to figure out where it is in the transaction stream.

2) Connects to the backup server to figure out where *it* is.

3) If the backup server has already streamed all of the contents of the
to-be-archived WAL file, just throw it away.  Otherwise (or if step 2 failed)
archive the WAL file.

I'm not sure about the timing.  It could be that it's impossible for
all the WAL records to have been streamed already by the time a WAL
file is ready to be archived.  Maybe the archive_command could cheat
and delay for a few seconds to see if the hot_standby can catch up.
But I really would like to find some sort of adaptive way to decide
whether or not a WAL file *really* needs to be archived over to the
hot-standby for safety.

Regards,

David.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Attachment

pgsql-admin by date:

Previous
From: Elías David
Date:
Subject: Re: pg_createcluster failing on Trusty
Next
From: Dave Johansen
Date:
Subject: Postgres 8.4.20 seqfault on RHEL 6.4