Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fsVCa-0006GK-VA for pgsql-admin@arkaria.postgresql.org; Wed, 22 Aug 2018 15:37:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fsVCY-0008Ox-K4 for pgsql-admin@arkaria.postgresql.org; Wed, 22 Aug 2018 15:37:42 +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 1fsVCY-0008Oq-Cp for pgsql-admin@lists.postgresql.org; Wed, 22 Aug 2018 15:37:42 +0000 Received: from mail.thelabyrinth.net ([45.56.70.56]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fsVCQ-0003CI-96 for pgsql-admin@postgresql.org; Wed, 22 Aug 2018 15:37:41 +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 BAF0654682; Wed, 22 Aug 2018 15:37:31 +0000 (UTC) Subject: Re: pgbackrest: backup from standby To: Craig James Cc: pgsql-admin@postgresql.org References: 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= Message-ID: <404af85d-01ea-562a-0186-afef862696d2@pgmasters.net> Date: Wed, 22 Aug 2018 11:37:30 -0400 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 8/20/18 9:42 PM, Craig James wrote: > > One possibility: Does pgbackrest support chained standby servers? > > primary ----> standby #1 ----> standby #2 This won't matter to pgBackRest and Postgres certainly supports it. > If so, we could do this: > > 1. Set up standby #1 and #2 on AWS, but no S3 yet > 2. Prepare config for S3, but don't deploy > 3. Failover to standby #1, making it the primary > 4. Start the S3 backup Or combine my earlier plan with your plan to allow you to keep your repo and have uninterrupted PITR: Before the migration: 1) Stop backups 2) Sync the NFS repo to S3 using rsync/s3fs or aws cli During the migration: 3) Failover to new AWS primary but leave archive_command disabled on new primary (archive_command = false) 4) Resync the NFS repo to S3 to get new WAL segments 5) Enable archive command on AWS primary 6) Perform a new backup directly to S3 (just in case was mistake was made syncing the repo) Depending on your WAL volume you can add some syncs in there to reduce the time when the new primary is not archiving, maybe after step #2 and again before step #3. This leaves you with your old backups and PITR capability while only copying data local->S3 once. Regards, -- -David david@pgmasters.net