Thread: Wal keep segment
Hi,
I have "wal_keep_segment" set as 256.
But I could see that around 700 files are present inside pg_wal directory.
I thought after reaching 256, wal writer will start overwriting from 1st.
What is the upper limit of these number of wal files in the directory?
Thanks and regards
Raj kumar
Wal_keep_segments is a MINIMUM setting value, meaning the database is guaranteed to keep at least that many WAL segments.If you have replication setup and it’s fallen behind or broken, then WAL segments can rapidly accumulate on a fairlybusy server. I suspect without having your details that something of this nature is occurring. — Jay Sent from my iPad > On Apr 25, 2020, at 8:09 AM, Raj kumar <rajkumar820999@gmail.com> wrote: > > > Hi, > > I have "wal_keep_segment" set as 256. > But I could see that around 700 files are present inside pg_wal directory. > > I thought after reaching 256, wal writer will start overwriting from 1st. > > What is the upper limit of these number of wal files in the directory? > > Thanks and regards > Raj kumar
Hi John,
Thank you.
On Sat, 25 Apr 2020, 17:45 John Scalia, <jayknowsunix@gmail.com> wrote:
Wal_keep_segments is a MINIMUM setting value, meaning the database is guaranteed to keep at least that many WAL segments. If you have replication setup and it’s fallen behind or broken, then WAL segments can rapidly accumulate on a fairly busy server. I suspect without having your details that something of this nature is occurring.
—
Jay
Sent from my iPad
> On Apr 25, 2020, at 8:09 AM, Raj kumar <rajkumar820999@gmail.com> wrote:
>
>
> Hi,
>
> I have "wal_keep_segment" set as 256.
> But I could see that around 700 files are present inside pg_wal directory.
>
> I thought after reaching 256, wal writer will start overwriting from 1st.
>
> What is the upper limit of these number of wal files in the directory?
>
> Thanks and regards
> Raj kumar
On Sat, Apr 25, 2020, 08:13 Raj kumar <rajkumar820999@gmail.com> wrote:
Hi John,Thank you.On Sat, 25 Apr 2020, 17:45 John Scalia, <jayknowsunix@gmail.com> wrote:Wal_keep_segments is a MINIMUM setting value, meaning the database is guaranteed to keep at least that many WAL segments. If you have replication setup and it’s fallen behind or broken, then WAL segments can rapidly accumulate on a fairly busy server. I suspect without having your details that something of this nature is occurring.
—
Jay
Sent from my iPad
> On Apr 25, 2020, at 8:09 AM, Raj kumar <rajkumar820999@gmail.com> wrote:
>
>
> Hi,
>
> I have "wal_keep_segment" set as 256.
> But I could see that around 700 files are present inside pg_wal directory.
>
> I thought after reaching 256, wal writer will start overwriting from 1st.
>
> What is the upper limit of these number of wal files in the directory?
>
> Thanks and regards
> Raj kumar
I would also see if you have a replication slot in use, and verify that it actually should be in use.
Don.
Hi Don,
edb=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_l
sn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+----------
---+---------------------
(0 rows)
edb=#
I am using wal_keep_segments for streaming replication and not replication slots.
max_replication_slots is provided as 10 inside postgresql.conf, but no replication slots are actually used, as you can see them from the below queries.
wal_keep_segments = 256 # in logfile segments; 0 disables
max_replication_slots = 10 # max number of replication slots
edb=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_l
sn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+----------
---+---------------------
(0 rows)
edb=#
edb=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+---------------------------------
pid | 19445
backend_xmin |
state | streaming
sent_lsn | 1B3/A6000000
write_lsn | 1B3/A6000000
flush_lsn | 1B3/A6000000
replay_lsn | 1B3/A6000000
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
-[ RECORD 1 ]----+---------------------------------
pid | 19445
backend_xmin |
state | streaming
sent_lsn | 1B3/A6000000
write_lsn | 1B3/A6000000
flush_lsn | 1B3/A6000000
replay_lsn | 1B3/A6000000
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
Thanks and Regards,
Raj Kumar
On Sat, Apr 25, 2020 at 6:59 PM Don Seiler <don@seiler.us> wrote:
On Sat, Apr 25, 2020, 08:13 Raj kumar <rajkumar820999@gmail.com> wrote:Hi John,Thank you.On Sat, 25 Apr 2020, 17:45 John Scalia, <jayknowsunix@gmail.com> wrote:Wal_keep_segments is a MINIMUM setting value, meaning the database is guaranteed to keep at least that many WAL segments. If you have replication setup and it’s fallen behind or broken, then WAL segments can rapidly accumulate on a fairly busy server. I suspect without having your details that something of this nature is occurring.
—
Jay
Sent from my iPad
> On Apr 25, 2020, at 8:09 AM, Raj kumar <rajkumar820999@gmail.com> wrote:
>
>
> Hi,
>
> I have "wal_keep_segment" set as 256.
> But I could see that around 700 files are present inside pg_wal directory.
>
> I thought after reaching 256, wal writer will start overwriting from 1st.
>
> What is the upper limit of these number of wal files in the directory?
>
> Thanks and regards
> Raj kumarI would also see if you have a replication slot in use, and verify that it actually should be in use.Don.