Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVXA7-000Tf9-3h for pgsql-general@arkaria.postgresql.org; Mon, 14 Aug 2023 12:59:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qVXA5-004MYd-S3 for pgsql-general@arkaria.postgresql.org; Mon, 14 Aug 2023 12:59:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVXA5-004MYN-5h for pgsql-general@lists.postgresql.org; Mon, 14 Aug 2023 12:59:09 +0000 Received: from tamriel.snowman.net ([70.109.60.50]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVXA2-0005vz-DG for pgsql-general@lists.postgresql.org; Mon, 14 Aug 2023 12:59:08 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id F39B25F7B7; Mon, 14 Aug 2023 08:58:59 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1692017939; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=+fUiwBFtepTofUag+YCsoQArNNvXjG9GD3A8C3Lbb8E=; b=E53dqa2THTh3XOGz5y5XA8hYSxAhz7wGA9jB4+B/A4jzhntmvLBQq/e5iqsy7USDpMab66 uIX0rsF3K/b+uHPj1CiQ3TJGVZUc7oR3ZaLFzL5tWqwfYmXQUOHWAHok3Cfp8smBQ7LH91 WXkzOkljmWYK0Gt/fSknqCMExHY6j3k= Date: Mon, 14 Aug 2023 08:58:59 -0400 From: Stephen Frost To: Mario Diangelo Cc: pgsql-general@lists.postgresql.org Subject: Re: Best strategy to perform individual incremental backups Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="9SKbllZQjzG4kaDF" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --9SKbllZQjzG4kaDF Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Mario Diangelo (watisditnou@msn.com) wrote: > We use different Windows servers with Postgresql and looking for the best= strategy to create daily incremental backups to a (managed) volume. We wan= t to achieve this with scripts and run them daily within the TaskSchedular.= Point in time recovery seems to work only for the whole cluster but we wan= t to be able to restore just one database on a cluster witch contains more = databases. Restoring of file-based backups of any kind (PITR or just regularly restoring a backup) requires restoring the entire cluster. It's not possible to restore an individual database using that method. If you want to do per-database restores, you'd need to use pg_dump to export the data and then pg_restore to restore it (note that this takes a lot more time, generally, since indexes have to be rebuilt, constraints have to be re-validated, etc). > It seems to start with WAL files and a full backup is necessary. We don= =E2=80=99t want to shutdown a production server so a full backup with pg_ba= sebackup seems to be the way (i could be wrong). We want to minimize the ch= ance of dataloss so archivemode will be set to on (for production environme= nt). pg_basebackup is a decent tool on Windows for doing cluster backups. It does not support per-database backup or restore since that isn't something that PostgreSQL really supports. If you want to be able to independently backup/restore things in a given cluster, the best answer really is to put those into a different cluster instead, which also means you'll get PITR too, which is certainly a good thing to have. > Postgresql.conf > wal_level =3D replica > archive_mode =3D on > archive_command =3D 'copy "%p" "D:\\backupfolder\\walfiles\\%f"' # Windows This is actually a really bad approach when it comes to an archive command because 'copy' wasn't written with the considerations of PostgreSQL in mind. While the documentation does say that the above is an example and not a recommendation, it really doesn't go into the details of why that is and it starts with copy not (afaik) sync'ing the copied file to actual disk before returning success, meaning that a crash will leave you with missing WAL files and no way to perform PITR through those. You could possibly get around that with xcopy /j, but there's other problems- PG will, for various reasons, archive the same file multiple times, which you could maybe get around with xcopy /j /w, but the /j doesn't sync the metadata afaik, which might be an issue, and it definitely doesn't check to see if the file being copied is identical to the one already in the archive (which generally shouldn't happen anyway ... unless you've misconfigured something). There's also no validation that the WAL file being copied is going into the correct repository location if you've got many PostgreSQL clusters and that can lead to corruption in the repository, among a host of other just really good checks to have to make sure backups and archiving are all working properly. > Does anyone have a (powershell) script that we can use to create the incr= emental backups on Windows and can point us the right settings to achieve t= his? Do you keep the first full backup, do you replace them once a week/mon= th and then delete the wall files? I=E2=80=99m wondering how other PostgreS= QL on Windows admins perform this.=C2=A0Is a third party tool the only way = to perfom such a backup?=C2=A0For the record, at the time being a daily ful= l backup is executed for all individual databases.=C2=A0 pg_basebackup and pg_receivewal can be used to do backup on Windows and to do WAL archiving in a better way than using the archive_command and copy, though that doesn't give you an incremental backup option currently (perhaps one day in the future it will... though pg_basebackup is still a very long way from a proper backup solution for PG). Perhaps with PITR, you won't really need incremental backups though? Depends a lot on how active your cluster is. The bigger and more active it is, frankly, the more you might want to simply consider moving it off of Windows. Thanks, Stephen --9SKbllZQjzG4kaDF Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmTaJREACgkQ7WyKOINH ZFVHlQ/+I4CsflcH/JYhvJIBL7bZh5A+J0qlg/JFSErg90Qr6dCMJxgaEUrAufGK g8//771ZLoPZ32RbPlIKdxQJx+X+pLw3P1BtWG92gBeyOB//1InDKJWIWf9agesB j41Tgzekazog1hIRVdAq5KtuyVN4UBZzI/TNPzJDXOJrxjy1xgM8jwjy2c+SocCO NDCqPfrfK77OJtFWgvL8VPXKHfyr+B0YhJdBQ5oZs7IO4hvQGcISt2MhVWcB3THK JnqDC/HQqM7QN31SAWtGOO/r7qR2G0ew/8wCtQMXNw7gzPPb2kYU6JJIWo5DuXbn 75xZv3j+i44hAJu9hqunr7TfxcNdEQ22FdWcbwztpxzAmQ16eRMMjhuunfF6MWo0 m3pJVBI5iT5IfWWY9wSDB/NYncFQpfiWC6nSk197XY0MIA4rSoieWWs422se2xc3 Jckd2lWx/fn8AFnuX4flR0pe+Ae91xOTY339uum43ZM3Fq5l+JR3F1mYJljeCGFu MSfu/59k7TfwlGAsOH0ww/DXdJjKEjAumGl3MAXdm8bZGBlQGgIhhBhdYk4t4joU R+lmz7Bzk9EXlqAA/l3knX/hcG+KvGeR65xG+lELWKcBpAuzYeT/fCwThDRRSDNo 967o3J+ahsWIMkHpz56sAgTZ4VoysaWaE//u1Iy0B3jii6xOqtc= =AXe5 -----END PGP SIGNATURE----- --9SKbllZQjzG4kaDF--