Re: strange behavior of WAL files - Mailing list pgsql-general
| From | Vijaykumar Jain |
|---|---|
| Subject | Re: strange behavior of WAL files |
| Date | |
| Msg-id | CAM+6J96Xs0RotUghJK6hKAnqv9AwzXFmSvorDGCrcKyYgp2YHA@mail.gmail.com Whole thread Raw |
| In response to | Re: strange behavior of WAL files (Atul Kumar <akumar14871@gmail.com>) |
| List | pgsql-general |
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@go:~/pgsql/data/pg_wal$ grep wal_size ../postgresql.conf
max_wal_size = 100MB
min_wal_size = 80MB
postgres@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@go:~/pgsql/data/pg_wal$ sed -i -e 's,/bin/true,/bin/false,' ../postgresql.conf
postgres@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@go:~/pgsql/data/pg_wal$ stoppg
waiting for server to shut down.... done
server stopped
postgres@go:~/pgsql/data/pg_wal$ startpg
waiting for server to start.... done
server started
postgres@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@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@go:~/pgsql/data/pg_wal$ ls
0000000100000000000000B1 0000000100000000000000B4 0000000100000000000000B7 0000000100000000000000BA
0000000100000000000000B2 0000000100000000000000B5 0000000100000000000000B8 0000000100000000000000BB
0000000100000000000000B3 0000000100000000000000B6 0000000100000000000000B9 archive_status
postgres@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@go:~/pgsql/data/pg_wal$ sed -i -e 's,/bin/false,/bin/true,' ../postgresql.conf
postgres@go:~/pgsql/data/pg_wal$ stoppg
waiting for server to shut down....... done
server stopped
postgres@go:~/pgsql/data/pg_wal$ startpg
waiting for server to start.... done
server started
postgres@go:~/pgsql/data/pg_wal$ ls
0000000100000000000000B1 0000000100000000000000B4 0000000100000000000000B7 0000000100000000000000BA archive_status
0000000100000000000000B2 0000000100000000000000B5 0000000100000000000000B8 0000000100000000000000BB
0000000100000000000000B3 0000000100000000000000B6 0000000100000000000000B9 0000000100000000000000BC
postgres@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@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@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@gmail.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@sss.pgh.pa.us> wrote:
> Atul Kumar <akumar14871@gmail.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
pgsql-general by date: