Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints

Hi,

While testing the v8 patches in a hot-standby setup, it was observed the
master is crashing with the below error;

2021-12-16 19:32:47.757 +04 [101483] PANIC: could not fsync file
"pg_tblspc/16385/PG_15_202112111/16386/16391": No such file or directory
2021-12-16 19:32:48.917 +04 [101482] LOG: checkpointer process (PID
101483) was terminated by signal 6: Aborted

Parameters configured at master:
wal_level = hot_standby
max_wal_senders = 3
hot_standby = on
max_standby_streaming_delay= -1
wal_consistency_checking='all'
max_wal_size= 10GB
checkpoint_timeout= 1d
log_min_messages=debug1

Test Case:
create tablespace tab1 location
'/home/edb/PGsources/postgresql/inst/bin/test1';
create tablespace tab location
'/home/edb/PGsources/postgresql/inst/bin/test';
create database test tablespace tab;
\c test
create table t( a int PRIMARY KEY,b text);
CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS 'select
array_agg(md5(g::text))::text from generate_series(1, 256) g';
insert into t values (generate_series(1,100000), large_val());
alter table t set tablespace tab1 ;
\c postgres
create database test1 template test;
\c test1
alter table t set tablespace tab;
\c postgres
alter database test1 set tablespace tab1;

--cancel the below command
alter database test1 set tablespace pg_default; --press ctrl+c
\c test1
alter table t set tablespace tab1;

Log file attached for reference.

Thanks.
--
Regards,
Neha Sharma

On Thu, Dec 16, 2021 at 4:17 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> On Thu, Dec 16, 2021 at 12:15 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >
> > On Thu, Dec 2, 2021 at 07:19:50PM +0530, Dilip Kumar wrote:
> > From the patch:
> >
> > > Currently, CREATE DATABASE forces a checkpoint, then copies all the
> files,
> > > then forces another checkpoint. The comments in the createdb() function
> > > explain the reasons for this. The attached patch fixes this problem by
> making
> > > create database completely WAL logged so that we can avoid the
> checkpoints.
> > >
> > > This can also be useful for supporting the TDE. For example, if we
> need different
> > > encryption for the source and the target database then we can not
> re-encrypt the
> > > page data if we copy the whole directory. But with this patch, we are
> copying
> > > page by page so we have an opportunity to re-encrypt the page before
> copying that
> > > to the target database.
> >
> > Uh, why is this true? Why can't we just copy the heap/index files 8k at
> > a time and reencrypt them during the file copy, rather than using shared
> > buffers?
>
> Hi Bruce,
>
> Yeah, you are right that if we copy in 8k block then we can re-encrypt
> the page, but in the current system, we are not copying block by
> block. So the main effort for this patch is not only for TDE but to
> get rid of the checkpoint we are forced to do before and after create
> database. So my point is that in this patch since we are copying page
> by page we get an opportunity to re-encrypt the page. I agree that if
> the re-encryption would have been the main goal of this patch then
> true we can copy files in 8k blocks and re-encrypt those blocks, that
> time even if we have to access some page data for re-encryption (like
> nonce) then also we can do it, but that is not the main objective.
>
> --
> Regards,
> Dilip Kumar
> EnterpriseDB: http://www.enterprisedb.com
>
>
>