pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOld WAL files under pg_xlog not getting delete
17+ messages / 7 participants
[nested] [flat]
* Old WAL files under pg_xlog not getting delete
@ 2019-01-30 22:58 rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: rammohan ganapavarapu @ 2019-01-30 22:58 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am trying to understand why postgresql not removing WAL logs under
pg_xlog in 9.6, below are my settings
max_wal_size= 1GB
wal_keep_segments=500
pg_xlog/|wc -l
506
pg_xlog]# du -sh .
7.9G .
and i have files from last year Nov
ls -ltr |head
total 8257540
-rw------- 1 postgres postgres 16777216 Nov 21 16:32
0000000100000003000000BD
-rw------- 1 postgres postgres 16777216 Nov 21 16:32
0000000100000001000000C6
-rw------- 1 postgres postgres 16777216 Nov 21 16:33
0000000100000001000000C7
-rw------- 1 postgres postgres 16777216 Nov 21 16:33
0000000100000001000000C8
-rw------- 1 postgres postgres 16777216 Nov 21 16:34
0000000100000001000000C9
-rw------- 1 postgres postgres 16777216 Nov 21 16:34
0000000100000001000000CA
-rw------- 1 postgres postgres 16777216 Nov 21 16:35
0000000100000001000000CB
-rw------- 1 postgres postgres 16777216 Nov 21 16:35
0000000100000001000000CC
-rw------- 1 postgres postgres 16777216 Nov 21 16:36
0000000100000001000000CD
Here is my controldata output
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6620833305040850458
Database cluster state: in production
pg_control last modified: Wed 30 Jan 2019 05:49:44 PM EST
Latest checkpoint location: 3/BC000098
Prior checkpoint location: 3/BA000220
Latest checkpoint's REDO location: 3/BC000060
Latest checkpoint's REDO WAL file: 0000000100000003000000BC
and i dont see postgresql using any of the other files.
sudo lsof |grep pg_xlog
bash 28969 root cwd DIR 202,112
32768 4063264 /opt/pgdata/pg_xlog
postgres 29571 postgres 3u REG 202,112
16777216 4068479 /opt/pgdata/pg_xlog/0000000100000003000000BC
postgres 29573 postgres 3u REG 202,112
16777216 4068479 /opt/pgdata/pg_xlog/0000000100000003000000BC
Any idea why those old files are not getting removed? How can i recycle
them?
Thanks,
Ram
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2019-01-30 23:10 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2019-01-30 23:10 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: pgsql-admin
On Wed, Jan 30, 2019 at 3:58 PM rammohan ganapavarapu
<rammohanganap@gmail.com> wrote:
> I am trying to understand why postgresql not removing WAL logs under pg_xlog in 9.6, below are my settings
>
> wal_keep_segments=500
>
> pg_xlog/|wc -l
> 506
You told PostgreSQL to keep 500 files and it has (the number kept
apparently fluctuates a little bit).
https://www.postgresql.org/docs/11/wal-configuration.html
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
@ 2019-01-30 23:37 ` Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ron @ 2019-01-30 23:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 1/30/19 5:10 PM, David G. Johnston wrote:
> On Wed, Jan 30, 2019 at 3:58 PM rammohan ganapavarapu
> <rammohanganap@gmail.com> wrote:
>> I am trying to understand why postgresql not removing WAL logs under pg_xlog in 9.6, below are my settings
>>
>> wal_keep_segments=500
>>
>> pg_xlog/|wc -l
>> 506
> You told PostgreSQL to keep 500 files and it has (the number kept
> apparently fluctuates a little bit).
>
> https://www.postgresql.org/docs/11/wal-configuration.html
Plus the ".", ".." and archive_status directories, and lost+found if pg_xlog
is it's own partition. That's four of the unknown six. Add a .backup file
or two and all 506 files are accounted for.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
@ 2019-01-31 00:00 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Alvaro Herrera @ 2019-01-31 00:00 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On 2019-Jan-30, Ron wrote:
> Plus the ".", ".." and archive_status directories, and lost+found if pg_xlog
> is it's own partition. That's four of the unknown six. Add a .backup file
> or two and all 506 files are accounted for.
Hmm ... I would recommend to keep the xlog files in a subdir, not the
root of the filesystem. So the lost+found would be, at the most, a
sibling of pg_xlog, not inside it. Otherwise, it seems too easy to get
in trouble if for whatever reason the filesystem is not mounted, your
database starts writing files in it, and the filesystem is mounted
later. Whatever files got written before mounting would be misteriously
"missing", and few people think to unmount the FS to see if they are in
the underlying filesystem.
Then again, if you read this advice then you're safe because next time
this happens to you, you'll remember to unmount to check, so you can
safely ignore me. Go figure.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-01-31 00:11 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2019-01-31 00:11 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Hmm ... I would recommend to keep the xlog files in a subdir, not the
> root of the filesystem.
They are in a subdir (under /opt/pgdata), and the OP seems likely to
have omitted the self/parent hidden directories during his check as
well. Plus per the docs we actually expect 501 so there isn't a need
to account for 6 extra, just 5.
Dave
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
@ 2019-01-31 03:09 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Scott Ribe <scott_ribe@elevated-dev.com>
2019-01-31 03:43 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 3 replies; 17+ messages in thread
From: rammohan ganapavarapu @ 2019-01-31 03:09 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thank you all, but irrespective of count i have why is it keeping old
files? and how is it using those files? Is there any processes that can
flush or remove those files?
On Wed, Jan 30, 2019, 4:11 PM David G. Johnston <david.g.johnston@gmail.com
wrote:
> On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
> > Hmm ... I would recommend to keep the xlog files in a subdir, not the
> > root of the filesystem.
>
> They are in a subdir (under /opt/pgdata), and the OP seems likely to
> have omitted the self/parent hidden directories during his check as
> well. Plus per the docs we actually expect 501 so there isn't a need
> to account for 6 extra, just 5.
>
> Dave
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2019-01-31 03:19 ` Ron <ronljohnsonjr@gmail.com>
2019-01-31 16:22 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2 siblings, 1 reply; 17+ messages in thread
From: Ron @ 2019-01-31 03:19 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 1/30/19 9:09 PM, rammohan ganapavarapu wrote:
> Thank you all, but irrespective of count i have why is it keeping old files?
Because you're telling it to!!
max_wal_size= 1GB
wal_keep_segments=500
> and how is it using those files? Is there any processes that can flush or
> remove those files?
Change the parameter and restart the server...
>
> On Wed, Jan 30, 2019, 4:11 PM David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:
>
> On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera
> <alvherre@2ndquadrant.com <mailto:alvherre@2ndquadrant.com>> wrote:
> > Hmm ... I would recommend to keep the xlog files in a subdir, not the
> > root of the filesystem.
>
> They are in a subdir (under /opt/pgdata), and the OP seems likely to
> have omitted the self/parent hidden directories during his check as
> well. Plus per the docs we actually expect 501 so there isn't a need
> to account for 6 extra, just 5.
>
> Dave
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
@ 2019-01-31 16:22 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 16:37 ` Re: Old WAL files under pg_xlog not getting delete John Scalia <jayknowsunix@gmail.com>
2019-01-31 17:02 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: rammohan ganapavarapu @ 2019-01-31 16:22 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Ron,
I have changed the wal_keep_segments=100 and restarted the postgresql
processes but i still see 500+ files under pg_xlog, i had to run checkpoint
manually to clean.
Ram
On Wed, Jan 30, 2019 at 7:19 PM Ron <ronljohnsonjr@gmail.com> wrote:
> On 1/30/19 9:09 PM, rammohan ganapavarapu wrote:
>
> Thank you all, but irrespective of count i have why is it keeping old
> files?
>
>
> Because you're telling it to!!
>
> max_wal_size= 1GB
> wal_keep_segments=500
>
> and how is it using those files? Is there any processes that can flush or
> remove those files?
>
>
> Change the parameter and restart the server...
>
>
> On Wed, Jan 30, 2019, 4:11 PM David G. Johnston <
> david.g.johnston@gmail.com wrote:
>
>> On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <alvherre@2ndquadrant.com>
>> wrote:
>> > Hmm ... I would recommend to keep the xlog files in a subdir, not the
>> > root of the filesystem.
>>
>> They are in a subdir (under /opt/pgdata), and the OP seems likely to
>> have omitted the self/parent hidden directories during his check as
>> well. Plus per the docs we actually expect 501 so there isn't a need
>> to account for 6 extra, just 5.
>>
>> Dave
>>
>>
> --
> Angular momentum makes the world go 'round.
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 16:22 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2019-01-31 16:37 ` John Scalia <jayknowsunix@gmail.com>
2019-01-31 18:51 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: John Scalia @ 2019-01-31 16:37 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Ram,
Remember that keep_wal_segments is a minimum setting, not a maximum one. It’s telling the database to maintain at least that many segments. Plus, check how you’ve set the archive_command. If that’s not correct, nothing will remove the WAL files.
—
Jay
Sent from my iPhone
> On Jan 31, 2019, at 11:22 AM, rammohan ganapavarapu <rammohanganap@gmail.com> wrote:
>
> Ron,
>
> I have changed the wal_keep_segments=100 and restarted the postgresql processes but i still see 500+ files under pg_xlog, i had to run checkpoint manually to clean.
>
> Ram
>
>> On Wed, Jan 30, 2019 at 7:19 PM Ron <ronljohnsonjr@gmail.com> wrote:
>>> On 1/30/19 9:09 PM, rammohan ganapavarapu wrote:
>>> Thank you all, but irrespective of count i have why is it keeping old files?
>>
>> Because you're telling it to!!
>>
>> max_wal_size= 1GB
>> wal_keep_segments=500
>>
>>> and how is it using those files? Is there any processes that can flush or remove those files?
>>
>> Change the parameter and restart the server...
>>
>>>
>>>> On Wed, Jan 30, 2019, 4:11 PM David G. Johnston <david.g.johnston@gmail.com wrote:
>>>> On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>>>> > Hmm ... I would recommend to keep the xlog files in a subdir, not the
>>>> > root of the filesystem.
>>>>
>>>> They are in a subdir (under /opt/pgdata), and the OP seems likely to
>>>> have omitted the self/parent hidden directories during his check as
>>>> well. Plus per the docs we actually expect 501 so there isn't a need
>>>> to account for 6 extra, just 5.
>>>>
>>>> Dave
>>>>
>>
>> --
>> Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 16:22 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 16:37 ` Re: Old WAL files under pg_xlog not getting delete John Scalia <jayknowsunix@gmail.com>
@ 2019-01-31 18:51 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 19:04 ` Re: Old WAL files under pg_xlog not getting delete Keith <keith@keithf4.com>
2019-01-31 20:59 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: rammohan ganapavarapu @ 2019-01-31 18:51 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Jay,
If there is no max limit on how many files can be on FS at some point it
will fill up disk space right? so you are saying if we dont have correct
archive_command , postgres will do nothing? in my case i did not set
archive_command. But from postgres documentation *"T*he number of WAL
segment files in pg_xlog directory depends on min_wal_size, max_wal_size* and
the amount of WAL generated in previous checkpoint cycles. When old log
segment files are no longer needed, they are removed or recycled (that is,
renamed to become future segments in the numbered sequence)." * This
statement is little confusing me with what you have said: *"**If that’s not
correct, nothing will remove the WAL files.".*
Sorry if i am asking silly questions :(
Thanks,
Ram
On Thu, Jan 31, 2019 at 8:37 AM John Scalia <jayknowsunix@gmail.com> wrote:
> Ram,
>
> Remember that keep_wal_segments is a minimum setting, not a maximum one.
> It’s telling the database to maintain at least that many segments. Plus,
> check how you’ve set the archive_command. If that’s not correct, nothing
> will remove the WAL files.
> —
> Jay
>
> Sent from my iPhone
>
> On Jan 31, 2019, at 11:22 AM, rammohan ganapavarapu <
> rammohanganap@gmail.com> wrote:
>
> Ron,
>
> I have changed the wal_keep_segments=100 and restarted the postgresql
> processes but i still see 500+ files under pg_xlog, i had to run checkpoint
> manually to clean.
>
> Ram
>
> On Wed, Jan 30, 2019 at 7:19 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
>> On 1/30/19 9:09 PM, rammohan ganapavarapu wrote:
>>
>> Thank you all, but irrespective of count i have why is it keeping old
>> files?
>>
>>
>> Because you're telling it to!!
>>
>> max_wal_size= 1GB
>> wal_keep_segments=500
>>
>> and how is it using those files? Is there any processes that can flush or
>> remove those files?
>>
>>
>> Change the parameter and restart the server...
>>
>>
>> On Wed, Jan 30, 2019, 4:11 PM David G. Johnston <
>> david.g.johnston@gmail.com wrote:
>>
>>> On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <alvherre@2ndquadrant.com>
>>> wrote:
>>> > Hmm ... I would recommend to keep the xlog files in a subdir, not the
>>> > root of the filesystem.
>>>
>>> They are in a subdir (under /opt/pgdata), and the OP seems likely to
>>> have omitted the self/parent hidden directories during his check as
>>> well. Plus per the docs we actually expect 501 so there isn't a need
>>> to account for 6 extra, just 5.
>>>
>>> Dave
>>>
>>>
>> --
>> Angular momentum makes the world go 'round.
>>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 16:22 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 16:37 ` Re: Old WAL files under pg_xlog not getting delete John Scalia <jayknowsunix@gmail.com>
2019-01-31 18:51 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2019-01-31 19:04 ` Keith <keith@keithf4.com>
1 sibling, 0 replies; 17+ messages in thread
From: Keith @ 2019-01-31 19:04 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
The archive_command is only for making secondary copies of the WAL files
and doesn't control the files in the pg_xlogs folder itself (pg_wal in
PG10+). Secondary uses could be backups or non-streaming replicas that just
replay the WAL instead of connecting back to the primary.
What they were saying is that if you do have an archive_command set and it
fails to run, postgresql will not remove the WAL files in pg_xlog until the
archive_command successfully runs or is removed. It does this so that your
backups or replicas don't lose WAL files in the time period that the
archive_command is failing.
Keith
On Thu, Jan 31, 2019 at 1:52 PM rammohan ganapavarapu <
rammohanganap@gmail.com> wrote:
> Jay,
>
> If there is no max limit on how many files can be on FS at some point it
> will fill up disk space right? so you are saying if we dont have correct
> archive_command , postgres will do nothing? in my case i did not set
> archive_command. But from postgres documentation *"T*he number of WAL
> segment files in pg_xlog directory depends on min_wal_size, max_wal_size* and
> the amount of WAL generated in previous checkpoint cycles. When old log
> segment files are no longer needed, they are removed or recycled (that is,
> renamed to become future segments in the numbered sequence)." * This
> statement is little confusing me with what you have said: *"**If that’s
> not correct, nothing will remove the WAL files.".*
>
> Sorry if i am asking silly questions :(
>
> Thanks,
> Ram
>
> On Thu, Jan 31, 2019 at 8:37 AM John Scalia <jayknowsunix@gmail.com>
> wrote:
>
>> Ram,
>>
>> Remember that keep_wal_segments is a minimum setting, not a maximum one.
>> It’s telling the database to maintain at least that many segments. Plus,
>> check how you’ve set the archive_command. If that’s not correct, nothing
>> will remove the WAL files.
>> —
>> Jay
>>
>> Sent from my iPhone
>>
>> On Jan 31, 2019, at 11:22 AM, rammohan ganapavarapu <
>> rammohanganap@gmail.com> wrote:
>>
>> Ron,
>>
>> I have changed the wal_keep_segments=100 and restarted the postgresql
>> processes but i still see 500+ files under pg_xlog, i had to run checkpoint
>> manually to clean.
>>
>> Ram
>>
>> On Wed, Jan 30, 2019 at 7:19 PM Ron <ronljohnsonjr@gmail.com> wrote:
>>
>>> On 1/30/19 9:09 PM, rammohan ganapavarapu wrote:
>>>
>>> Thank you all, but irrespective of count i have why is it keeping old
>>> files?
>>>
>>>
>>> Because you're telling it to!!
>>>
>>> max_wal_size= 1GB
>>> wal_keep_segments=500
>>>
>>> and how is it using those files? Is there any processes that can flush
>>> or remove those files?
>>>
>>>
>>> Change the parameter and restart the server...
>>>
>>>
>>> On Wed, Jan 30, 2019, 4:11 PM David G. Johnston <
>>> david.g.johnston@gmail.com wrote:
>>>
>>>> On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <
>>>> alvherre@2ndquadrant.com> wrote:
>>>> > Hmm ... I would recommend to keep the xlog files in a subdir, not the
>>>> > root of the filesystem.
>>>>
>>>> They are in a subdir (under /opt/pgdata), and the OP seems likely to
>>>> have omitted the self/parent hidden directories during his check as
>>>> well. Plus per the docs we actually expect 501 so there isn't a need
>>>> to account for 6 extra, just 5.
>>>>
>>>> Dave
>>>>
>>>>
>>> --
>>> Angular momentum makes the world go 'round.
>>>
>>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 16:22 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 16:37 ` Re: Old WAL files under pg_xlog not getting delete John Scalia <jayknowsunix@gmail.com>
2019-01-31 18:51 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2019-01-31 20:59 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2019-01-31 20:59 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Jan 31, 2019 at 11:52 AM rammohan ganapavarapu
<rammohanganap@gmail.com> wrote:
> But from postgres documentation "The number of WAL segment files in pg_xlog directory depends on min_wal_size, max_wal_size and the amount of WAL generated in previous checkpoint cycles.
You are quoting from the v10+ documentation but using 9.6
You also need to read about archiving[1] since when it is enabled the
rules for WAL retention are modified to meet the needs of archiving,
specifically WAL cannot be removed from the host system until the
host's archiving system knows that the same file exists in the
archive.
[1] https://www.postgresql.org/docs/9.6/continuous-archiving.html#BACKUP-ARCHIVING-WAL
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 16:22 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2019-01-31 17:02 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Ron @ 2019-01-31 17:02 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
"i had to run checkpoint manually to clean."
I see no problem with that. Postgres would have checkpointed sooner or later.
On 1/31/19 10:22 AM, rammohan ganapavarapu wrote:
> Ron,
>
> I have changed the wal_keep_segments=100 and restarted the postgresql
> processes but i still see 500+ files under pg_xlog, i had to run
> checkpoint manually to clean.
>
> Ram
>
> On Wed, Jan 30, 2019 at 7:19 PM Ron <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
> On 1/30/19 9:09 PM, rammohan ganapavarapu wrote:
>> Thank you all, but irrespective of count i have why is it keeping old
>> files?
>
> Because you're telling it to!!
>
> max_wal_size= 1GB
> wal_keep_segments=500
>
>> and how is it using those files? Is there any processes that can
>> flush or remove those files?
>
> Change the parameter and restart the server...
>
>>
>> On Wed, Jan 30, 2019, 4:11 PM David G. Johnston
>> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:
>>
>> On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera
>> <alvherre@2ndquadrant.com <mailto:alvherre@2ndquadrant.com>> wrote:
>> > Hmm ... I would recommend to keep the xlog files in a subdir,
>> not the
>> > root of the filesystem.
>>
>> They are in a subdir (under /opt/pgdata), and the OP seems likely to
>> have omitted the self/parent hidden directories during his check as
>> well. Plus per the docs we actually expect 501 so there isn't a need
>> to account for 6 extra, just 5.
>>
>> Dave
>>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2019-01-31 03:19 ` Scott Ribe <scott_ribe@elevated-dev.com>
2019-01-31 03:43 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2 siblings, 1 reply; 17+ messages in thread
From: Scott Ribe @ 2019-01-31 03:19 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Jan 30, 2019, at 8:09 PM, rammohan ganapavarapu <rammohanganap@gmail.com> wrote:
>
> why is it keeping old files?
Because you configured it to:
wal_keep_segments=500
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Re: Old WAL files under pg_xlog not getting delete Scott Ribe <scott_ribe@elevated-dev.com>
@ 2019-01-31 03:43 ` rammohan ganapavarapu <rammohanganap@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: rammohan ganapavarapu @ 2019-01-31 03:43 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Got it thank you, so it is depends on wal_keep_seg irrespective to age of
the file.
On Wed, Jan 30, 2019, 7:19 PM Scott Ribe <scott_ribe@elevated-dev.com wrote:
> > On Jan 30, 2019, at 8:09 PM, rammohan ganapavarapu <
> rammohanganap@gmail.com> wrote:
> >
> > why is it keeping old files?
>
> Because you configured it to:
>
> wal_keep_segments=500
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2019-01-31 03:43 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 04:18 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2019-01-31 03:43 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Jan 30, 2019 at 8:09 PM rammohan ganapavarapu
<rammohanganap@gmail.com> wrote:
> Thank you all, but irrespective of count i have why is it keeping old files? and how is it using those files? Is there any processes that can flush or remove those files?
Others are answering these questions but I have one of my own. Is
this database being backed up?
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Old WAL files under pg_xlog not getting delete
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Re: Old WAL files under pg_xlog not getting delete Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Re: Old WAL files under pg_xlog not getting delete Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` Re: Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:43 ` Re: Old WAL files under pg_xlog not getting delete David G. Johnston <david.g.johnston@gmail.com>
@ 2019-01-31 04:18 ` rammohan ganapavarapu <rammohanganap@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: rammohan ganapavarapu @ 2019-01-31 04:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Nope, it's non prod db.
On Wed, Jan 30, 2019, 7:44 PM David G. Johnston <david.g.johnston@gmail.com
wrote:
> On Wed, Jan 30, 2019 at 8:09 PM rammohan ganapavarapu
> <rammohanganap@gmail.com> wrote:
> > Thank you all, but irrespective of count i have why is it keeping old
> files? and how is it using those files? Is there any processes that can
> flush or remove those files?
>
> Others are answering these questions but I have one of my own. Is
> this database being backed up?
>
> David J.
>
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2019-01-31 20:59 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-30 22:58 Old WAL files under pg_xlog not getting delete rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-30 23:10 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-30 23:37 ` Ron <ronljohnsonjr@gmail.com>
2019-01-31 00:00 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-31 00:11 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 03:09 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:19 ` Ron <ronljohnsonjr@gmail.com>
2019-01-31 16:22 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 16:37 ` John Scalia <jayknowsunix@gmail.com>
2019-01-31 18:51 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 19:04 ` Keith <keith@keithf4.com>
2019-01-31 20:59 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 17:02 ` Ron <ronljohnsonjr@gmail.com>
2019-01-31 03:19 ` Scott Ribe <scott_ribe@elevated-dev.com>
2019-01-31 03:43 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2019-01-31 03:43 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-31 04:18 ` rammohan ganapavarapu <rammohanganap@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox