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
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
/messages/by-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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 that directory.
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:
/messages/by-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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general