public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jakub Wartak <[email protected]>
To: [email protected]
Cc: Kyotaro Horiguchi <[email protected]>
Subject: Re: In-placre persistance change of a relation
Date: Tue, 11 Jan 2022 09:33:55 +0000
Message-ID: <164189363522.31345.3832968263786650864.pgcf@coridan.postgresql.org> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: not tested
I've retested v15 of the patch with everything that came to my mind. The patch passes all my tests (well, there's this just windows / cfbot issue). Patch looks good to me. I haven't looked in-depth at the code, so patch might still need review.
FYI, about potential usage of this patch: the most advanced test that I did was continually bouncing wal_level - it works. So chain of :
1. wal_level=replica->minimal
2. alter table set unlogged and load a lot of data, set logged
3. wal_level=minimal->replica
4. barman incremental backup # rsync(1) just backups changed files in steps 2 and 3 (not whole DB)
5. some other (logged) work
The idea is that when performing mass-alterations to the DB (think nightly ETL/ELT on TB-sized DBs), one could skip backing up most of DB and then just quickly backup only the changed files - during the maintenance window - e.g. thanks to local-rsync barman mode. This is the output of barman show-backups after loading data to unlogged table each such cycle:
mydb 20220110T100236 - Mon Jan 10 10:05:14 2022 - Size: 144.1 GiB - WAL Size: 16.0 KiB
mydb 20220110T094905 - Mon Jan 10 09:50:12 2022 - Size: 128.5 GiB - WAL Size: 80.2 KiB
mydb 20220110T094016 - Mon Jan 10 09:40:17 2022 - Size: 109.1 GiB - WAL Size: 496.3 KiB
And dedupe ratio of the last one: Backup size: 144.1 GiB. Actual size on disk: 36.1 GiB (-74.96% deduplication ratio).
The only thing I've found out that bouncing wal_level also forces max_wal_senders=X -> 0 -> X which in turn requires dropping replication slot for pg_receievewal (e.g. barman receive-wal --create-slot/--drop-slot/--reset). I have tested the restore using barman recover afterwards to backup 20220110T094905 and indeed it worked OK using this patch too.
The new status of this patch is: Needs review
view thread (2+ messages)
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: In-placre persistance change of a relation
In-Reply-To: <164189363522.31345.3832968263786650864.pgcf@coridan.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox