From: Rui DeSousa <rui.desousa@icloud.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: scott ribe <scott_ribe@elevated-dev.com>
Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: Dianne Skoll <dfs@roaringpenguin.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Reliable WAL file shipping over unreliable network
Date: Mon, 05 Mar 2018 09:52:53 -0500
Message-ID: <ABC9F093-7249-498E-901B-6ADB7BBA3B22@icloud.com> (raw)
In-Reply-To: <20180305111521.GV2416@tamriel.snowman.net>
References: <9DDA6F85-077E-4F8D-8708-632CC2907D3C@icloud.com>
<06dd8b81-8b1a-ae5c-8ddd-1556b5946176@catalyst.net.nz>
<6056907B-551F-4ED0-804B-0FCACD39EB23@icloud.com>
<ea674584-85bc-3f63-c182-0d45123bb3eb@catalyst.net.nz>
<988AB20C-9FDE-4197-BF3F-C54D80C2ED78@elevated-dev.com>
<E6AB850C-D05E-405B-8D4E-DE18E128F402@icloud.com>
<20180302180911.GO2416@tamriel.snowman.net>
<BB8E0E71-D65F-409F-B9AD-DDF2237CDF99@icloud.com>
<20180304150640.GR2416@tamriel.snowman.net>
<3D9CC2FB-57E1-45B2-B5FC-1B214F5DB068@icloud.com>
<20180305111521.GV2416@tamriel.snowman.net>
> On Mar 5, 2018, at 6:15 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
> Using replication slots and pg_receivewal can be a good solution if
> you're on a version which supports the combination of both. That
> doesn't mean the proposed archive commands are any good though, just to
> be clear. An issue with pg_receivewal though is that it's
> single-threaded.
I must say this is a bit absurd; I didn’t realize that telling someone not to delete Postgres WAL files from underneath Postgres would require me to provide a complete redundant backup solution.
Pg_receivxlog or the archive_command being single threaded is not an issue; at least not for me and I’m generating 1/2TB of WALs a day. The real problem is that applying the WALs is single threaded — trying to apply a single days worth of WALs takes too long and is one of the reasons I take multiple backups a day to reduce the number of WALs required during a PITR.
The solution you proposed; would not be able to keep up with the rate of backups I issue daily nor is it capable of taking a backups on the replica at least not yet from the presentation I reviewed. I do my backups on replicas at multiple sites with WAL files also being stored at multiple sites. I also do daily restores in a lower environment which take less than 5 minutes to do — obviously I’m making extensive use of snapshots and snapshot replication.
If your archive server is crashing then you have other issues and one should work to remove single points of failure. I’m not sure what filesystem you’re using but the one I use sync to disk every 30 seconds.
The original poster stated that rsync wasn’t even an option and it not even using it. I’m not here trying to push a backup solution or anything else; I was just trying give some simple advice to the given problem.=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui.desousa@icloud.com, sfrost@snowman.net, scott_ribe@elevated-dev.com, mark.kirkwood@catalyst.net.nz, dfs@roaringpenguin.com, pgsql-admin@lists.postgresql.org
Subject: Re: Reliable WAL file shipping over unreliable network
In-Reply-To: <ABC9F093-7249-498E-901B-6ADB7BBA3B22@icloud.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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