Thread: Streaming replication and WAL archiving

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.

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.

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
whatwas 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
binariesand 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.
>
>

On Wed, 11 Feb 2015 09:37:46 -0500
John Scalia <jayknowsunix@gmail.com> wrote:

> 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.

Dump/restore is not an option for me.  It would mean many hours of
downtime which is unacceptable.  I can barely get away with two hours
of downtime and that would have to be in the wee hours of Sunday
morning.  People tend to get very cranky when their email stops flowing!

> 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.

I use Debian and the Debian packages are very good at allowing concurrent
installation of different versions, so that's a plus.

Regards,

David.

On Feb 11, 2015, at 7:45 AM, David F. Skoll <dfs@roaringpenguin.com> wrote:
>
> Dump/restore is not an option for me.  It would mean many hours of
> downtime which is unacceptable.  I can barely get away with two hours
> of downtime and that would have to be in the wee hours of Sunday
> morning.  People tend to get very cranky when their email stops flowing!

I don't remember the beginning of the thread. Did you consider Slony? It's more configuration, but it would reduce
downtimeto almost 0. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On Wed, 11 Feb 2015 08:04:43 -0700
Scott Ribe <scott_ribe@elevated-dev.com> wrote:

> I don't remember the beginning of the thread. Did you consider Slony?
> It's more configuration, but it would reduce downtime to almost 0.

We have looked at Slony in the past, but AFAIK it cannot replicate
tables that lack a primary key and we have a few of those.  Also
AFAIK, slony requires an initial sync of the databases with
dump/restore.  Finally our database is quite busy and very
write-heavy, so I worry about the performance impact of all the Slony
triggers.

Regards,

David.

Le 11 févr. 2015 16:42, "David F. Skoll" <dfs@roaringpenguin.com> a écrit :
>
> On Wed, 11 Feb 2015 08:04:43 -0700
> Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>
> > I don't remember the beginning of the thread. Did you consider Slony?
> > It's more configuration, but it would reduce downtime to almost 0.
>
> We have looked at Slony in the past, but AFAIK it cannot replicate
> tables that lack a primary key and we have a few of those.

This is a good reason to avoid slony but...

>  Also
> AFAIK, slony requires an initial sync of the databases with
> dump/restore.  Finally our database is quite busy and very
> write-heavy, so I worry about the performance impact of all the Slony
> triggers.
>

This is definitely wrong. Slony can do the initial sync by itself (ie without dump/restore).