Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gSry4-0007k5-Dd for pgsql-admin@arkaria.postgresql.org; Fri, 30 Nov 2018 23:13:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gSry3-0000ze-4J for pgsql-admin@arkaria.postgresql.org; Fri, 30 Nov 2018 23:13:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gSry2-0000zX-Ow for pgsql-admin@lists.postgresql.org; Fri, 30 Nov 2018 23:13:02 +0000 Received: from mail.thelabyrinth.net ([45.56.70.56]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gSry0-0005Vg-7C for pgsql-admin@lists.postgresql.org; Fri, 30 Nov 2018 23:13:01 +0000 Received: from Davids-iMac.local (c-73-39-99-224.hsd1.de.comcast.net [73.39.99.224]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: dsteele) by mail.thelabyrinth.net (Postfix) with ESMTPSA id 0DD6354695; Fri, 30 Nov 2018 23:12:58 +0000 (UTC) Subject: Re: Backup solution over unreliable network To: Achilleas Mantzios , pgsql-admin@lists.postgresql.org References: <20801752-C311-4F70-9D03-08C5E711645C@mac.com> <9956da38-bcb6-6768-d245-887df9e0fb93@matrix.gatewaynet.com> From: David Steele Openpgp: preference=signencrypt Autocrypt: addr=david@pgmasters.net; prefer-encrypt=mutual; keydata= xsFNBFSheh0BEADGNb3c1kOaX5OGUDhqHTwJ3JW7XAllfWLdknpRg+/fihgLPPRnQHZhjq28 nmMLqM+C8TjezT2BgVACeu8qqASnuzN/E5+bAl9hVcgCP4eV/lEIWwVxmhcHqSdeWqWZSBRB tf07fLBnz22+3LwVsESN+fanWxnoqqvE0+hyGnCEDhOBkihgbYHuq4vw1+xgoGN1uDk4NShV El0ZWO90P4e5mOWA2Zsex+8YhGJlfRwMpU69okFCvjpWJKE+ufeJdlrxEpSSKW7SQ0kG1VF7 CvDKpF70TCWkb4Bagb6c7lwZrMjxuBnlIvOlV3RRY67PAaVIFRRvQJeJABOPHp8HnH8cwici 7gwwfWBrZZeAdPqs6VNjANqI9uaDasRnD3yHP6CcHIAeCl5hYnzNTkIqBRlnmD5y8s0VRDLr 6JR3FxscOsvwXPJ+He1RpRlxQDlBboaRFUz8endeQHhGUeHyekQcSYUaz4PX3OJBG0Xi7nr8 EOP6zOeP61z3dL8NuO3QjgPiygvS0qwMEPvOYo52nEGYn08nvFVajI8yxOXFnIwnmpgr5gdq nL9ga72tvSyJQAUx+YB7r1bKjRn/Mlt/fPIS5PXONofiO1K8uX+pzvIX6dnm7ihpFf1XC+uG 5+DKBnU7yS7jCRL52GxWHbuC3mT4tvzqH2b13TLng1Y7Izi+hwARAQABzSJEYXZpZCBTdGVl bGUgPGRhdmlkQHBnbWFzdGVycy5uZXQ+wsF5BBMBCAAjAhsDAh4BAheABQsJCAcDBRUKCQgL BBYCAwEFAlShuEECGQEACgkQn830JKJznPPsLhAAtKkIu0mFfxcVqApg4PNjZKYY4TWeN+I+ go/VKX8QWMadr33t8Sxcgyn48bjBYzgWQXhF4rMcFxDxI3ly5bdE8QKZkhFJJT7N2fBoo9Zm ZOsOvPd+5hz07VZpgNSiTXMAphj8+GSTLbYufm/OUnfpS1eovPtPdYPQpNC3RVHA0gtisz26 NgMLg6QN0iQuITs9N+wiaGtyVIornl4XAY2dM/Hvfr1aDFkukNNr52sc/mGqocG/dKC7dONA 3860y2MLszQcbAE45iEkmwpGkvxyc3i3tvdD3VNlVI1MKJUBnxFPUEI0lVq5WpApFSIU31+j aNunKIcfVxRnM8FTJBsw3RnFRpSsEtiLaZRm5sOZHa9+dy/DWDb+8O1I+momCo88+Jksd6pZ 2Tz9msOiJiTnFsCZgfbbmayH6w8ZyfHuO0DuxScM1UK+6CLC2VVjQFuH6WHqJpaENg5INswS qpUc05iC4i+6dZOe44cviX6LpHuP8vWM0E63CMun0l42yHWy+k6nLvx/fj7t++tF2JIJ92OG n/2MkM5lIKknRg27UjYAspD6f3NSZUMIe6tGyWPTtLOu8zOL5CeRyCHMWwUVDKJSBzlK2mV8 e0EbmtUJFsoNWXWKPKUIxX7JXPN3v523HmOhtyz5SnkGtwEqCZiv/JccYmh+S2IokAuscLNP EJrOwU0EVKF6HQEQALPpUsP67xfcj6NUMyySXSytUzMIVi5XNTyazuNcVfFZzT9QL8wFQkgy i/HOD3tmvdGxz6H8V+v+cszp88gCd8TwClss7u+iPsLlAQQWC4q0WiX4OhwFv2H004iuft8i odjiWDkYEgIFxlPNxQfHFmchkyODIJXQmnzbgsXwGZtJzAxEVh6AgYJqE/Ev/+kTJhJ7703n oIb+PXvafoB5axnU4nf7DuFa9X+q2/ZfzyAXUCKvzDvxb24iUgWFzlWBx0CnI21Kh6aS6hR6 o00D2OBiU+yCpW9eRTi3SuDuslmtCUaZz5V4SHo37ochEYfZ2LYOPsBO3PUMx8T7MDYrmv/x 4W5rdjugz4NXWROStNNLpS74LW20rdrEKMDltph7U2zfjjS663K+CUxoXn/ha2WnzBocoY5o KwdViLln4s0ivOTa4clHJJUsYiAxLShAe2AP+5kJim24tAabbEOsrRTn9RpT7J8jVT/G0YDE glujEt0U04Akkj5NO4G7vJP/AyX5zKZetkp/HFiFBZMkUuXD/KgDc5EM2fpQtaxGGY2fcQqY VHUbbxstxyl5d349M9rok7O90hw9BkP0Q8UEpgmlZ6ftwx+ZfZmBEdQ7HcMHnSPSNJkXt/Vi ozlcrSnkVXzCXvH9DHrIpgenp8btsJ2XyjSdrEu4jAqYeirW+3qRABEBAAHCwV8EGAEIAAkF AlSheh0CGwwACgkQn830JKJznPNmERAApYaQK6i9F6hM2RUD/PqQC45WmzOXK9bDSS9hdk4p Nc5jSW2+FkcMFe1XFG2NyGf1v5KBPxuDTQp35PIwRsL59xNmPICu0ae6zmCPUpxbMIEzb/VU wXqeuNp3cRVll06goDt5w+2LlAtBrxuDjiWxby9LH+vsIUMLWYu+M50pdtOcXOsO1+zyfs3w U6xRZ3PaFDKO1cB68okDFG/cFHnYfY5aETV7LgVBHEOQLwL01U01QC/iqY/aNO4iUH/+PBc0 R6mYlP7IqDQOQVTJ0dXP365thl0Q7c9e2JQSS9wuSv2EEiR//nnZREtLyrQNLwsXE8UFBDe7 uA8Lm2ldmQYpHHKAcorGVTHYJaaus2LjH8tZ1i4RGg4hKMJSHil/5C+Ghl+PmcACVeGaxB0c mJ8+0jyWDkLCDMAXJJyvXRWIONTAxVt5kKg1/iMAq1IQYNd66agnmgxdSvKLPUo0JJ5fhQJp 2bqDDTrINTZg+Rub2HeoOMxYJsMypK+6aY4iVlm2Z7A9SgyQDjGAXzD9+XaezNFeXxfPbjo6 46qA9SycdYhiwaTcTQakiLkZWRIkSjGQwNCK9NktE1fYnXfR0+WaWFduQ4BfM5uta1bt8esB /mLEjVYS9RRAWZ/untJrR9JYGTC4Mu6tcLGZD7385yX3TNM1iaIUGxlKkHHCZCnILyo= Cc: evanbauer@mac.com, Stephen Frost Message-ID: Date: Fri, 30 Nov 2018 18:12:59 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:60.0) Gecko/20100101 Thunderbird/60.3.1 MIME-Version: 1.0 In-Reply-To: <9956da38-bcb6-6768-d245-887df9e0fb93@matrix.gatewaynet.com> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 11/30/18 1:49 PM, Achilleas Mantzios wrote: > > On 30/11/18 8:22 μ.μ., Evan Bauer wrote: >> Achilleas, >> >> I may be over-simplifying your situation, but have you considered >> breaking the problem into two pieces?  First backing up to a local >> drive and then using rsync to move those files over the unreliable >> network to the remote site. >> >> Like other who have responded, I can heartily recommend pgbackrest.  >> But if the network stinks, then I’d break the problem in two and leave >> PostgreSQL out of the network equation. > > Pretty good idea, but : > > 1) those rsync transfers have to be somehow db aware, otherwise lots of > things might break, checksums, order of WALs, etc. There would be the > need to write a whole solution and end up ... getting one of the > established solutions It's actually perfectly OK to rsync a pgBackRest repository. We've already done the hard work of interacting with the database and gotten the backups into a format that can be rsync'd, backed up to tape, with standard enterprise backups tools, etc. It is common to backup the pgBackRest repo or individual backups (with --archive-copy enabled) and we have not seen any issues. BTW, in this context I expect local means in the same data center, not on the database host. > 2) the rsync part would go basically unattended, meaning no smart > software would be taking care of it, monitoring it, sending alerts, etc. > Also we had our issues with rsync in the past with unreliable networks > like getting error messages for which google returns one or no results > (no pgsql stuff, just system scripts) . No wonder more and more PgSQL > backup solutions move away from rsync. I agree that this is a concern -- every process needs to be monitored and if you can avoid the extra step that would be best. But if things start getting complicated it might be the simpler option. Regards, -- -David david@pgmasters.net