Re: strange behavior of WAL files

it gets cleaned up for me.

turn archiving on, simulate success using /bin/true
turn archiving off, simulate success using /bin/false
generate wals by some DMLS.

postgres(at)go:~/pgsql/data/pg_wal$ grep wal_size ../postgresql.conf
max_wal_size = 100MB
min_wal_size = 80MB
postgres(at)go:~/pgsql/data/pg_wal$ grep archive_ ../postgresql.conf | grep -v
"^#"
archive_mode = on # enables archiving; off, on, or always
archive_command = '/bin/true' # command to use to archive a
logfile segment

# fake failed archiving
postgres(at)go:~/pgsql/data/pg_wal$ sed -i -e 's,/bin/true,/bin/false,'
../postgresql.conf
postgres(at)go:~/pgsql/data/pg_wal$ grep archive_ ../postgresql.conf | grep -v
"^#"
archive_mode = on # enables archiving; off, on, or always
archive_command = '/bin/false' # command to use to archive a
logfile segment

postgres(at)go:~/pgsql/data/pg_wal$ stoppg
waiting for server to shut down.... done
server stopped
postgres(at)go:~/pgsql/data/pg_wal$ startpg
waiting for server to start.... done
server started

postgres(at)go:~/pgsql/data/pg_wal$ ls
0000000100000000000000B1 0000000100000000000000B3
0000000100000000000000B5 archive_status
0000000100000000000000B2 0000000100000000000000B4 0000000100000000000000B6

# generate wals, switch_wal, check is wal files incresed as achiving failing
postgres(at)go:~/pgsql/data/pg_wal$ for i in {1..10}; do psql -c 'insert into
t select x from generate_series(1, 100) x; delete from t; select
pg_switch_wal();'; sleep 1; done
pg_switch_wal
---------------
0/B1004690
(1 row)

pg_switch_wal
---------------
0/B2002F68
(1 row)

pg_switch_wal
---------------
0/B3003098
(1 row)

pg_switch_wal
---------------
0/B4003068
(1 row)

pg_switch_wal
---------------
0/B50039B8
(1 row)

pg_switch_wal
---------------
0/B60030E0
(1 row)

pg_switch_wal
---------------
0/B7002F68
(1 row)

pg_switch_wal
---------------
0/B8003078
(1 row)

pg_switch_wal
---------------
0/B9004128
(1 row)

pg_switch_wal
---------------
0/BA003048
(1 row)

postgres(at)go:~/pgsql/data/pg_wal$ ls
0000000100000000000000B1 0000000100000000000000B4
0000000100000000000000B7 0000000100000000000000BA
0000000100000000000000B2 0000000100000000000000B5
0000000100000000000000B8 0000000100000000000000BB
0000000100000000000000B3 0000000100000000000000B6
0000000100000000000000B9 archive_status

postgres(at)go:~/pgsql/data/pg_wal$ pg_controldata -D ~/pgsql/data | grep
REDO
Latest checkpoint's REDO location: 0/B9000028
Latest checkpoint's REDO WAL file: 0000000100000000000000B9

# enable success archiving, old wals should get recycled and not appear
again.
postgres(at)go:~/pgsql/data/pg_wal$ sed -i -e 's,/bin/false,/bin/true,'
../postgresql.conf
postgres(at)go:~/pgsql/data/pg_wal$ stoppg
waiting for server to shut down....... done
server stopped
postgres(at)go:~/pgsql/data/pg_wal$ startpg
waiting for server to start.... done
server started

postgres(at)go:~/pgsql/data/pg_wal$ ls
0000000100000000000000B1 0000000100000000000000B4
0000000100000000000000B7 0000000100000000000000BA archive_status
0000000100000000000000B2 0000000100000000000000B5
0000000100000000000000B8 0000000100000000000000BB
0000000100000000000000B3 0000000100000000000000B6
0000000100000000000000B9 0000000100000000000000BC
postgres(at)go:~/pgsql/data/pg_wal$ pg_controldata -D ~/pgsql/data | grep REDO
Latest checkpoint's REDO location: 0/BC000028
Latest checkpoint's REDO WAL file: 0000000100000000000000BC

postgres(at)go:~/pgsql/data/pg_wal$ for i in {1..5}; do psql -c 'insert into t
select x from generate_series(1, 100) x; delete from t; select
pg_switch_wal();'; sleep 1; done
pg_switch_wal
---------------
0/BC004150
(1 row)

pg_switch_wal
---------------
0/BD003068
(1 row)

pg_switch_wal
---------------
0/BE003070
(1 row)

pg_switch_wal
---------------
0/BF003098
(1 row)

pg_switch_wal
---------------
0/C0004170
(1 row)

# old wals cleaned up.
postgres(at)go:~/pgsql/data/pg_wal$ ls
0000000100000000000000C0 0000000100000000000000C2
0000000100000000000000C4 archive_status
0000000100000000000000C1 0000000100000000000000C3 0000000100000000000000C5

it seems to work fine in this basic test.

Hence I said, i did not see that earlier.
FYI, i tested this on pg13, i have not worked on pg9.6

On Fri, 4 Jun 2021 at 21:36, Atul Kumar <akumar14871(at)gmail(dot)com> wrote:

> hi Tom,
>
> Please check my findings below
>
> older
> -rw------- 1 enterprisedb enterprisedb 16777216 Jun 2 02:47
> 00000001000036CF000000A4
> -rw------- 1 enterprisedb enterprisedb 16777216 Jun 2 02:45
> 00000001000036CF000000A3
> -rw------- 1 enterprisedb enterprisedb 16777216 Jun 2 02:44
> 00000001000036CF000000A5
>
> please note that above files are of June 2nd and once it is archived
> it will be recycled with same name with current timestamp, check
> below:
>
> newer
>
> -rw------- 1 enterprisedb enterprisedb 16777216 Jun 4 08:19
> 00000001000036CF000000A0
> -rw------- 1 enterprisedb enterprisedb 16777216 Jun 4 08:20
> 00000001000036CF000000A1
> -rw------- 1 enterprisedb enterprisedb 16777216 Jun 4 08:22
> 00000001000036CF000000A2
> drwx------ 2 enterprisedb enterprisedb 311296 Jun 4 08:22 archive_status
> -rw------- 1 enterprisedb enterprisedb 16777216 Jun 4 08:23
> 00000001000036CF000000A3
> -rw------- 1 enterprisedb enterprisedb 16777216 Jun 4 08:23
> 00000001000036CF000000A4
>
>
>
> the file names ending with A3 and A4 are the files that got generated
> with same name with the latest timestamp.
>
>
>
> So that's why I called it strange behavior, please suggest your opinion.
>
>
>
>
>
> Regards,
> Atul
>
>
>
>
>
>
>
>
>
>
> On 6/4/21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Atul Kumar <akumar14871(at)gmail(dot)com> writes:
> >> once old WAL files of pg_xlog directory are archived in
> >> '/nfslogs/wal/' directory then these WAL files are getting generated
> >> with the same name in pg_xlog directory.
> >
> > Are you sure you are describing the behavior accurately?
> >
> > What I would expect to happen, once an old WAL file has been archived
> > and the server knows its contents are no longer needed, is for the
> > WAL file to be "recycled" by renaming it to have a name that's in-the-
> > future in the WAL name series, whereupon it will wait its turn to be
> > reused by future WAL writes. On most filesystems the rename as such
> > doesn't change the file's mod time, so you'll see files that seem
> > to be in-the-future according to their names, but have old timestamps.
> >
> > (There's a limit on how many future WAL files we'll tee up this way,
> > so it's possible that an old one would just get deleted instead.
> > But the steady-state behavior is to just rotate them around.)
> >
> > regards, tom lane
> >
>
>
>

--
Thanks,
Vijay
Mumbai, India