Re: Streaming replication and WAL archiving

Hi David,

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

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#Replication_slots

Regards.
Greg.

*Greg Clough*

*Database Administrator | CWT Digital*

*t.* 0845 456 0070 *w.* cwtdigital.com <http://www.cwtdigital.com/>

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

[image:
https://ci6.googleusercontent.com/proxy/spbuXLCE_kiYn1CBmHRh6DoZX1WwETayC0rma88wNedREVrYPrjvkpQLcBPeuKfSkfkOpDSkuDvye9g2Ps4-BW4s=s0-d-e1-ft#http://www.cwtdigital.com/hosted/twitter.png]
<https://twitter.com/cwtdigital> [image:
https://ci5.googleusercontent.com/proxy/q4GOyacafkMnvs7TLpy3rOcwVK_uxMoECtqVEVpwmxiLShfkGNOZGisnwu4oHGtcEK_C-TfrLguFshp1VKC-5zZw6g=s0-d-e1-ft#http://www.cwtdigital.com/hosted/facebook.png]
<https://www.facebook.com/CWTdigital> [image:
https://ci4.googleusercontent.com/proxy/BVh2CnIvbQ_Rp5LFVzt4p72zUvhG3u7ksBMtlWRjbWjXL-DXIFEXXZJCYWMlSd3MshfG4UN0NAuTj1eElX8r5iu2KQ=s0-d-e1-ft#http://www.cwtdigital.com/hosted/linkedin.png]
<https://www.linkedin.com/company/cwt-digital> [image:
https://ci4.googleusercontent.com/proxy/QJJKpRXCvrkfCat5ycimzE7Lve3Pp9wFZWL5eBF5ELDkpl_gA8UVOc-R2p29DTS5DJmfD-FHGg2cjSf4lEuz2Ts=s0-d-e1-ft#http://www.cwtdigital.com/hosted/google.png]
<https://plus.google.com/s/CWT%20Digital> [image:
https://ci4.googleusercontent.com/proxy/X2ddbFYXE1hmnS4LGzoCr-Pl8fpK6yoO9R-jjF3__nbZkbuCmQR2LD3-Ts3deetyHm0L8E-hjyXeRS0YTkcqtNNGkxM=s0-d-e1-ft#http://www.cwtdigital.com/hosted/instagram.png]
<http://instagram.com/cwtdigital>

On 10 February 2015 at 22:21, David F. Skoll <dfs(at)roaringpenguin(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>