pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStreaming replication and WAL archiving
4+ messages / 3 participants
[nested] [flat]
* Streaming replication and WAL archiving
@ 2015-02-10 22:21 David F. Skoll <dfs@roaringpenguin.com>
2015-02-11 09:04 ` Re: Streaming replication and WAL archiving Greg Clough <greg.clough@cwtdigital.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: David F. Skoll @ 2015-02-10 22:21 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Streaming replication and WAL archiving
2015-02-10 22:21 Streaming replication and WAL archiving David F. Skoll <dfs@roaringpenguin.com>
@ 2015-02-11 09:04 ` Greg Clough <greg.clough@cwtdigital.co.uk>
2015-02-11 14:27 ` Re: Streaming replication and WAL archiving David F. Skoll <dfs@roaringpenguin.com>
0 siblings, 1 reply; 4+ messages in thread
From: Greg Clough @ 2015-02-11 09:04 UTC (permalink / raw)
To: David F. Skoll <dfs@roaringpenguin.com>; +Cc: pgsql-admin
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_kiYn1CBmHRh6DoZX1WwETayC0rma88wNedREVrYPrjvkpQLcBPe...]
<https://twitter.com/cwtdigital; [image:
https://ci5.googleusercontent.com/proxy/q4GOyacafkMnvs7TLpy3rOcwVK_uxMoECtqVEVpwmxiLShfkGNOZGisnwu4o...]
<https://www.facebook.com/CWTdigital; [image:
https://ci4.googleusercontent.com/proxy/BVh2CnIvbQ_Rp5LFVzt4p72zUvhG3u7ksBMtlWRjbWjXL-DXIFEXXZJCYWMl...]
<https://www.linkedin.com/company/cwt-digital; [image:
https://ci4.googleusercontent.com/proxy/QJJKpRXCvrkfCat5ycimzE7Lve3Pp9wFZWL5eBF5ELDkpl_gA8UVOc-R2p29...]
<https://plus.google.com/s/CWT%20Digital; [image:
https://ci4.googleusercontent.com/proxy/X2ddbFYXE1hmnS4LGzoCr-Pl8fpK6yoO9R-jjF3__nbZkbuCmQR2LD3-Ts3d...]
<http://instagram.com/cwtdigital;
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
>
Attachments:
image005.png (image/png, 699B) download
view image
image002.png (image/png, 610B) download
view image
image004.png (image/png, 584B) download
view image
image006.png (image/png, 688B) download
view image
image003.png (image/png, 483B) download
view image
* Re: Streaming replication and WAL archiving
2015-02-10 22:21 Streaming replication and WAL archiving David F. Skoll <dfs@roaringpenguin.com>
2015-02-11 09:04 ` Re: Streaming replication and WAL archiving Greg Clough <greg.clough@cwtdigital.co.uk>
@ 2015-02-11 14:27 ` David F. Skoll <dfs@roaringpenguin.com>
2015-02-11 14:37 ` Re: Streaming replication and WAL archiving John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David F. Skoll @ 2015-02-11 14:27 UTC (permalink / raw)
To: pgsql-admin; +Cc: Greg Clough <greg.clough@cwtdigital.co.uk>
On Wed, 11 Feb 2015 09:04:44 +0000
Greg Clough <greg.clough@cwtdigital.co.uk> wrote:
> Have you investigated the "Replication Slots" feature that's new in
> 9.4. It sounds like what you want:
Yes, it is exactly what I want... thanks!
Now to plan the upgrade from 9.1 to 9.4... it's not exactly trivial.
The database is about 400GB which is large enough to be unwieldy and it
powers our anti-spam service... that means we can't be down for very
long. :( Time to re-familiarize myself with pg_upgrade...
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Streaming replication and WAL archiving
2015-02-10 22:21 Streaming replication and WAL archiving David F. Skoll <dfs@roaringpenguin.com>
2015-02-11 09:04 ` Re: Streaming replication and WAL archiving Greg Clough <greg.clough@cwtdigital.co.uk>
2015-02-11 14:27 ` Re: Streaming replication and WAL archiving David F. Skoll <dfs@roaringpenguin.com>
@ 2015-02-11 14:37 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: John Scalia @ 2015-02-11 14:37 UTC (permalink / raw)
To: David F. Skoll <dfs@roaringpenguin.com>; pgsql-admin; +Cc: Greg Clough <greg.clough@cwtdigital.co.uk>
I've had some issues with pg_upgrade and 9.4. For my test systems, as I didn't really have the resources to diagnose what was going on, I just used a pg_dumpall to unload the 9.3.3
instance and then just ran psql to load the output from pg_dumpall.
The more wonderful part of installing different minor versions of postgresql, is that most O/S packages put the binaries and the default data directories in slightly different
areas, so they can co-exist, just not necessarily run at the same time without specifying different connect ports.
On 2/11/2015 9:27 AM, David F. Skoll wrote:
> On Wed, 11 Feb 2015 09:04:44 +0000
> Greg Clough <greg.clough@cwtdigital.co.uk> wrote:
>
>> Have you investigated the "Replication Slots" feature that's new in
>> 9.4. It sounds like what you want:
> Yes, it is exactly what I want... thanks!
>
> Now to plan the upgrade from 9.1 to 9.4... it's not exactly trivial.
> The database is about 400GB which is large enough to be unwieldy and it
> powers our anti-spam service... that means we can't be down for very
> long. :( Time to re-familiarize myself with pg_upgrade...
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-02-11 14:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-02-10 22:21 Streaming replication and WAL archiving David F. Skoll <dfs@roaringpenguin.com>
2015-02-11 09:04 ` Greg Clough <greg.clough@cwtdigital.co.uk>
2015-02-11 14:27 ` David F. Skoll <dfs@roaringpenguin.com>
2015-02-11 14:37 ` John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox