Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1esrUA-00052G-Pr for pgsql-admin@arkaria.postgresql.org; Mon, 05 Mar 2018 14:53:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1esrU9-0002M6-2d for pgsql-admin@arkaria.postgresql.org; Mon, 05 Mar 2018 14:53:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1esrU8-0002Lx-LJ for pgsql-admin@lists.postgresql.org; Mon, 05 Mar 2018 14:53:04 +0000 Received: from mr28p48im-tydg10113601.me.com ([17.110.88.45]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1esrU4-0003RI-GJ for pgsql-admin@lists.postgresql.org; Mon, 05 Mar 2018 14:53:04 +0000 Received: from process-dkim-sign-daemon.mr28p48im-tydg10113601.me.com by mr28p48im-tydg10113601.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) id <0P5400100HB4R000@mr28p48im-tydg10113601.me.com> for pgsql-admin@lists.postgresql.org; Mon, 05 Mar 2018 14:52:57 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=04042017; t=1520261577; bh=pIMfY4IPEBGcGPa+fzzSaNPQ1O1BLL2i2TArW4tPmKI=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=dyeDyNBA2UA1Q749+BqAppDz+V+ZGiuVt4NovCKPBCP/iuZJYMCrhO+TyEOijI5GU Ad62sU4zrx4pzpG/AqIoAyYXW5TmHdUcJXZDeeRXcXCf7B63G9yhi+NQ5hSltnGzWN BJ2j3t51cf3TiNRsrzDG9MDBZjh5pfiB5HZnVVVgpw1AfCe/qsBJKRdT5ByhMH2FQw J6ZCBWwkHajdy7imk9XZdOhxHQ0ieO1DYrIBwyWioSEWFn6C9+t490q1gODuT6Tx3W EWozyiq/E1i79NP6Pxrrf48J9J4TmNSq5T8x2StAIxmoONh0yDhXilKQLMc8zTqzVQ TxGPMSGbisVAQ== Received: from icloud.com ([127.0.0.1]) by mr28p48im-tydg10113601.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) with ESMTPSA id <0P54000FZHC6RD40@mr28p48im-tydg10113601.me.com>; Mon, 05 Mar 2018 14:52:56 +0000 (GMT) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2018-03-05_06:,, signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1015 suspectscore=0 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1803050178 From: Rui DeSousa Message-id: Content-type: multipart/alternative; boundary="Apple-Mail=_E7576867-30C9-4103-9265-AF80B14EB345" MIME-version: 1.0 (Mac OS X Mail 11.2 \(3445.5.20\)) Subject: Re: Reliable WAL file shipping over unreliable network Date: Mon, 05 Mar 2018 09:52:53 -0500 In-reply-to: <20180305111521.GV2416@tamriel.snowman.net> Cc: scott ribe , Mark Kirkwood , Dianne Skoll , pgsql-admin@lists.postgresql.org To: Stephen Frost References: <9DDA6F85-077E-4F8D-8708-632CC2907D3C@icloud.com> <06dd8b81-8b1a-ae5c-8ddd-1556b5946176@catalyst.net.nz> <6056907B-551F-4ED0-804B-0FCACD39EB23@icloud.com> <988AB20C-9FDE-4197-BF3F-C54D80C2ED78@elevated-dev.com> <20180302180911.GO2416@tamriel.snowman.net> <20180304150640.GR2416@tamriel.snowman.net> <3D9CC2FB-57E1-45B2-B5FC-1B214F5DB068@icloud.com> <20180305111521.GV2416@tamriel.snowman.net> X-Mailer: Apple Mail (2.3445.5.20) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_E7576867-30C9-4103-9265-AF80B14EB345 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Mar 5, 2018, at 6:15 AM, Stephen Frost wrote: >=20 > 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=E2=80=99t 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=E2=80=99m generating 1/2TB of WALs a = day. The real problem is that applying the WALs is single threaded =E2=80= =94 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 =E2=80=94 obviously I=E2=80=99m = 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=E2=80=99m not sure = what filesystem you=E2=80=99re using but the one I use sync to disk = every 30 seconds. The original poster stated that rsync wasn=E2=80=99t even an option and = it not even using it. I=E2=80=99m not here trying to push a backup = solution or anything else; I was just trying give some simple advice to = the given problem.= --Apple-Mail=_E7576867-30C9-4103-9265-AF80B14EB345 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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=E2=80=99= 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=E2=80=99m generating 1/2TB of = WALs a day.  The real problem is that applying the WALs is single = threaded =E2=80=94 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 =E2=80=94 obviously I=E2=80=99m 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=E2=80=99m = not sure what filesystem you=E2=80=99re using but the one I use sync to = disk every 30 seconds.

The original poster stated that rsync wasn=E2=80=99t even an = option and it not even using it.  I=E2=80=99m not here trying to = push a backup solution or anything else; I was just trying give some = simple advice to the given problem.
= --Apple-Mail=_E7576867-30C9-4103-9265-AF80B14EB345--