Re: Too many .history file in pg_xlog takes lots of space
Hi Michael, Alvaro, Tom: Really appreciate yours help, this is an invalid report, and I'm sorry for that. After I examine restart script, I found we generate recovery.conf every time, and this cause lost of timeline. Thanks. 2018-03-14 23:29 GMT+08:00 Tom Lane : > Alvaro Herrera writes: > > 彭昱傑 wrote: > >> Every time I restart postgre server, it generates a new history file: > > > That's strange -- it shouldn't happen ... sounds like you're causing a > > crash each time you restart. Are you using immediate mode in shutdown > > maybe? If so, don't; use fast mode instead. > > I'm confused by this report too. Plain crashes shouldn't result in > forking a new timeline. To check, I tried "-m immediate", as well as > "kill -9 postmaster", and neither of those resulted in a new .history file > on restart. I wonder if the OP's restart process involves calling > pg_resetxlog or something like that (which would be risky as heck). > > regards, tom lane >
Re: Too many .history file in pg_xlog takes lots of space
Alvaro Herrera writes: > 彭昱傑 wrote: >> Every time I restart postgre server, it generates a new history file: > That's strange -- it shouldn't happen ... sounds like you're causing a > crash each time you restart. Are you using immediate mode in shutdown > maybe? If so, don't; use fast mode instead. I'm confused by this report too. Plain crashes shouldn't result in forking a new timeline. To check, I tried "-m immediate", as well as "kill -9 postmaster", and neither of those resulted in a new .history file on restart. I wonder if the OP's restart process involves calling pg_resetxlog or something like that (which would be risky as heck). regards, tom lane
Re: Too many .history file in pg_xlog takes lots of space
彭昱傑 wrote: > My postgre version is 9.4.9, and I face a space issue. Latest in 9.4 is 9.4.17, so you're missing about two years of bug fixes. > Every time I restart postgre server, it generates a new history file: That's strange -- it shouldn't happen ... sounds like you're causing a crash each time you restart. Are you using immediate mode in shutdown maybe? If so, don't; use fast mode instead. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Too many .history file in pg_xlog takes lots of space
On Wed, Mar 14, 2018 at 02:12:47PM +0800, 彭昱傑 wrote: > It's useful information for me. Once archived, there is no need to keep them in the data folder as if needed at recovery the startup process would look for timeline history files where necessary if it needs to do a timeline jump. > I will examine my restart script, and study point-in-time-recovery. > Also remove unused history file. At the same time, the backend makes little effort to remove past timeline history files, and those are just a couple of bytes, which accumulate, so after a couple of hundreds of failovers you could bloat the data folder. Why not making their removal more aggressive at each restart point created? You don't need any history files older than the current timeline recovery is processing, so we could make the removal policy more aggressive. -- Michael signature.asc Description: PGP signature
Re: Too many .history file in pg_xlog takes lots of space
Thank you. It's useful information for me. I will examine my restart script, and study point-in-time-recovery. Also remove unused history file. 2018-03-14 13:56 GMT+08:00 Laurenz Albe : > 彭昱傑 wrote: > > My postgre version is 9.4.9, and I face a space issue. > > > > Every time I restart postgre server, it generates a new history file: > > 156A.history => 156B.history > > > > Now it takes a lot of space about 800MB (5787 history file): > > [...] > > Is file 1570.history important? > > A new history file is created when a new timeline is opened, > which happens after point-in-time-recovery or promotion of > a physical standby server. > > There must be something weird in the way you start PostgreSQL. > Examine the start script, maybe you can fix the problem. > > These files are only necessary for point-in-time-recovery, > so you don't have to retain them any longer than you retain > your WAL archives. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
Re: Too many .history file in pg_xlog takes lots of space
彭昱傑 wrote: > My postgre version is 9.4.9, and I face a space issue. > > Every time I restart postgre server, it generates a new history file: > 156A.history => 156B.history > > Now it takes a lot of space about 800MB (5787 history file): > [...] > Is file 1570.history important? A new history file is created when a new timeline is opened, which happens after point-in-time-recovery or promotion of a physical standby server. There must be something weird in the way you start PostgreSQL. Examine the start script, maybe you can fix the problem. These files are only necessary for point-in-time-recovery, so you don't have to retain them any longer than you retain your WAL archives. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
