Thread: pgbasebackup is failing after truncate
HI
At present, I have some requirement to truncate the data base to reclaim disk space and at the same time I need to take basebackup.
Seems, truncate is successfully reclaimed the space but pgbasebackup failed and reported the below error.
“could not stat file or directory ./base/16384/25600: Permission denied.
Further observation shows that truncate command reclaiming the space by making empty (not deleted) these files in ./base/16384 folders.
Seems these filename are relfilenode in pg_class table to map filename to table name. Since truncate making empty these files and create new files to map the old table name, pgbasebackup is unable to synch with truncate and pointing to old files.
Also, the old files are not deleted from “base” folder.
Please let me know how to resolve this issue
With best regards,
Ramkumar Yelai
Registered Office: 130, Pandurang Budhkar Marg, Worli, Mumbai 400 018. Telephone +91 22 39677000. Fax +91 22 39677075. Other Offices: Bangalore, Chennai, Gurgaon, Noida, Kolkata, Pune. Corporate Identity number:U99999MH1986PLC093854
On 08/10/2016 11:06 PM, Yelai, Ramkumar wrote: > HI > > At present, I have some requirement to truncate the data base to > reclaim disk space and at the same time I need to take basebackup. To be clear you ran TRUNCATE on tables within the database, correct? If not what was the actual command you used? > > Seems, truncate is successfully reclaimed the space but pgbasebackup > failed and reported the below error. > > “could not stat file or directory ./base/16384/25600: Permission denied. So what user did you run pg_basebackup as and does that user have rights on that file/directory? > > Further observation shows that truncate command reclaiming the space by > making empty (not deleted) these files in ./base/16384 folders. > > Seems these filename are relfilenode in pg_class table to map filename > to table name. Since truncate making empty these files and create new > files to map the old table name, pgbasebackup is unable to synch with > truncate and pointing to old files. > > Also, the old files are not deleted from “base” folder. > > Please let me know how to resolve this issue > > With best regards, > Ramkumar Yelai > > _mailto:ramkumar.yelai@siemens.com_ > _http://www.siemens.co.in/STS_ > > Registered Office: 130, Pandurang Budhkar Marg, Worli, Mumbai 400 018. > Telephone +91 22 39677000. Fax +91 22 39677075. Other Offices: > Bangalore, Chennai, Gurgaon, Noida, Kolkata, Pune. Corporate Identity > number:U99999MH1986PLC093854 > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Aug 10, 2016 at 11:06 PM, Yelai, Ramkumar <ramkumar.yelai@siemens.com> wrote: > HI > > At present, I have some requirement to truncate the data base to reclaim > disk space and at the same time I need to take basebackup. > > Seems, truncate is successfully reclaimed the space but pgbasebackup failed > and reported the below error. > > “could not stat file or directory ./base/16384/25600: Permission denied. Are you on MS Windows? If so, sounds like a possible variant of BUG #14243 Cheers, Jeff
On Sat, Aug 13, 2016 at 12:41 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > On Wed, Aug 10, 2016 at 11:06 PM, Yelai, Ramkumar > <ramkumar.yelai@siemens.com> wrote: >> At present, I have some requirement to truncate the data base to reclaim >> disk space and at the same time I need to take basebackup. >> >> Seems, truncate is successfully reclaimed the space but pgbasebackup failed >> and reported the below error. >> >> “could not stat file or directory ./base/16384/25600: Permission denied. > > Are you on MS Windows? If so, sounds like a possible variant of BUG #14243 That's really the same issue... See here for more details: https://www.postgresql.org/message-id/20160712083220.1426.58667@wrigleys.postgresql.org If you have ideas about making a difference between a real permission error and STATUS_PENDING_DELETE, that would be great. -- Michael
Hi, I have only one database and I ran query with superuser privilege. Seems this issue is related to BUG 14243. Regards, Ramkumar. -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Friday, August 12, 2016 7:37 PM To: Yelai, Ramkumar (CT DD DS AA DF-PD FH ES); pgsql-general@postgresql.org Subject: Re: [GENERAL] pgbasebackup is failing after truncate On 08/10/2016 11:06 PM, Yelai, Ramkumar wrote: > HI > > At present, I have some requirement to truncate the data base to > reclaim disk space and at the same time I need to take basebackup. To be clear you ran TRUNCATE on tables within the database, correct? If not what was the actual command you used? > > Seems, truncate is successfully reclaimed the space but pgbasebackup > failed and reported the below error. > > "could not stat file or directory ./base/16384/25600: Permission denied. So what user did you run pg_basebackup as and does that user have rights on that file/directory? > > Further observation shows that truncate command reclaiming the space > by making empty (not deleted) these files in ./base/16384 folders. > > Seems these filename are relfilenode in pg_class table to map filename > to table name. Since truncate making empty these files and create new > files to map the old table name, pgbasebackup is unable to synch with > truncate and pointing to old files. > > Also, the old files are not deleted from "base" folder. > > Please let me know how to resolve this issue > > With best regards, > Ramkumar Yelai > > _mailto:ramkumar.yelai@siemens.com_ > _http://www.siemens.co.in/STS_ > > Registered Office: 130, Pandurang Budhkar Marg, Worli, Mumbai 400 018. > Telephone +91 22 39677000. Fax +91 22 39677075. Other Offices: > Bangalore, Chennai, Gurgaon, Noida, Kolkata, Pune. Corporate Identity > number:U99999MH1986PLC093854 > > -- Adrian Klaver adrian.klaver@aklaver.com
HI, Thanks for helpful information. Even if there is no way to fix then please let me know how long that file ( pending for deletion ) will be present in thatdirectory. I observe like 1 or 2 hr after it will be deleted. But I am wondering, who is deleting that file. Regards, Ramkumar -----Original Message----- From: Michael Paquier [mailto:michael.paquier@gmail.com] Sent: Tuesday, August 16, 2016 5:51 AM To: Jeff Janes Cc: Yelai, Ramkumar (CT DD DS AA DF-PD FH ES); pgsql-general@postgresql.org Subject: Re: [GENERAL] pgbasebackup is failing after truncate On Sat, Aug 13, 2016 at 12:41 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > On Wed, Aug 10, 2016 at 11:06 PM, Yelai, Ramkumar > <ramkumar.yelai@siemens.com> wrote: >> At present, I have some requirement to truncate the data base to >> reclaim disk space and at the same time I need to take basebackup. >> >> Seems, truncate is successfully reclaimed the space but pgbasebackup >> failed and reported the below error. >> >> “could not stat file or directory ./base/16384/25600: Permission denied. > > Are you on MS Windows? If so, sounds like a possible variant of BUG > #14243 That's really the same issue... See here for more details: https://www.postgresql.org/message-id/20160712083220.1426.58667@wrigleys.postgresql.org If you have ideas about making a difference between a real permission error and STATUS_PENDING_DELETE, that would be great. -- Michael