Postgres Wal Full

On 3 March 2022 08:46:45 CET, pgdba pgdba <postgresdba12@outlook.com> wrote:

Hi ,

I have a problem from pg_wal. I am using postgresql version 11 and taking backup and writing archive_command in postgresql.conf but did not archive wal and my disk ise full from pg_wal. I research why is my wal is full and dont found anything could you help me please ?

Check if your archive_command works. Read the log.

--
2ndQuadrant - The PostgreSQL Support Company

At Thu, 3 Mar 2022 07:46:45 +0000, pgdba pgdba <postgresdba12@outlook.com> wrote in

I have a problem from pg_wal. I am using postgresql version 11 and taking backup and writing archive_command in postgresql.conf but did not archive wal and my disk ise full from pg_wal. I research why is my wal is full and dont found anything could you help me please ?

The immediate cause of the WAL retension is the archive failure.
PostgreSQL, while archiving is enabled, won't remove a WAL file until
it is successfully archived.

You will see the lines like the following in sergver log if archive
failure happened.

2022-03-03 17:15:21.314 JST LOG: archive command failed with exit code 1

If the archive command emitted some error messages, it can be caught
in the server log together. The follwoing error is emitted by the
archive_command "cp %p /archive/%f".

cp: cannot create regular file '/archive/000000010000000000000001': No such file or directory

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hi, thanks your feedback but not have same error in my log file. I am using streaming replication and have 1 master and 2 slave nodes in async. master node different size file wal from slave nodes such as;
[root@master1 pg_wal]# ls | wc -l
6400
[root@slave2 pg_wal]# ls | wc -l
332
[root@slave3 pg_wal]# ls | wc -l
344

wal_keep_segments = 512
max_wal_size = 2GB
min_wal_size = 1GB
data size : 290G
cpu:8
ram:16
________________________________
Gönderen: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Gönderildi: 3 Mart 2022 Perşembe 11:22
Kime: postgresdba12@outlook.com <postgresdba12@outlook.com>
Bilgi: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Konu: Re: Postgres Wal Full

At Thu, 3 Mar 2022 07:46:45 +0000, pgdba pgdba <postgresdba12@outlook.com> wrote in

I have a problem from pg_wal. I am using postgresql version 11 and taking backup and writing archive_command in postgresql.conf but did not archive wal and my disk ise full from pg_wal. I research why is my wal is full and dont found anything could you help me please ?

The immediate cause of the WAL retension is the archive failure.
PostgreSQL, while archiving is enabled, won't remove a WAL file until
it is successfully archived.

You will see the lines like the following in sergver log if archive
failure happened.

2022-03-03 17:15:21.314 JST LOG: archive command failed with exit code 1

If the archive command emitted some error messages, it can be caught
in the server log together. The follwoing error is emitted by the
archive_command "cp %p /archive/%f".

cp: cannot create regular file '/archive/000000010000000000000001': No such file or directory

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

On Thu, Mar 3, 2022 at 2:17 PM pgdba pgdba <postgresdba12@outlook.com> wrote:

Hi, thanks your feedback but not have same error in my log file. I am using streaming replication and have 1 master and 2 slave nodes in async. master node different size file wal from slave nodes such as;
[root@master1 pg_wal]# ls | wc -l
6400
[root@slave2 pg_wal]# ls | wc -l
332
[root@slave3 pg_wal]# ls | wc -l
344

wal_keep_segments = 512
max_wal_size = 2GB
min_wal_size = 1GB
data size : 290G
cpu:8
ram:16

Few things to check when WAL file grows: 1) archive failures 2)
inactive replication slots (use select * from pg_replication_slots;)
3) infrequent checkpoints (use select * from pg_control_checkpoint; to
know the last checkpoint time)4) high write (WAL-generating)
workloads.

Regards,
Bharath Rupireddy.