Обсуждение: data volume resizing and large objects
Hello All
I recently ran out of disk space on the volume hosting my default tablespace. As it turned out, the sysad didnt allocate all of the disk to the volume when he installed it, so he resized the volume and restarted the database. The server came up fine so I assumed all was well ( the server would not start before the resize ), however now when i get failures whenever I try to insert lobs via my application.
My application is written in perl and uses DBD::Pg and DBI to access the database. The failure occurs when i attempt to call lo_creat:
$dbh->func($mode, 'lo_creat');
lo_creat fails with:
ERROR: large object <oid> already exists
This leads me to believe that my database lost its list of existing lob oids and is experiencing collisions but I am not sure where to go from here. I could really use some additional insight into what is happening here and perhaps some direction in how to remedy the problem.
TIA
-bill
I recently ran out of disk space on the volume hosting my default tablespace. As it turned out, the sysad didnt allocate all of the disk to the volume when he installed it, so he resized the volume and restarted the database. The server came up fine so I assumed all was well ( the server would not start before the resize ), however now when i get failures whenever I try to insert lobs via my application.
My application is written in perl and uses DBD::Pg and DBI to access the database. The failure occurs when i attempt to call lo_creat:
$dbh->func($mode, 'lo_creat');
lo_creat fails with:
ERROR: large object <oid> already exists
This leads me to believe that my database lost its list of existing lob oids and is experiencing collisions but I am not sure where to go from here. I could really use some additional insight into what is happening here and perhaps some direction in how to remedy the problem.
TIA
-bill
"beer@cmu.edu" <beer@cmu.edu> writes: > lo_creat fails with: > ERROR: large object <oid> already exists Well, does it? What PG version is this? I'm suspicious that it's pre-8.1 and the problem is that your OID counter has wrapped around to a region that is already pretty densely populated with large objects. 8.1 and up are able to pick unused OIDs but earlier versions just fail on collisions. regards, tom lane
Tom
You hit the nail on the head actually! Further delving into the issue revealed that but I hadnt had a chance to post.
We recently used copy to put some lobs back into the database, about 70k of them. All of these lobs were assigned sequential oids incremented by 1. We then wrapped around and ended up in that block of oids again.
The irony of all of this is we have been trying to migrate to 8.1 for months now but have not been able to do so due to infrastructure issues beyond our control.
Is there anyway to force 8.0 to start at a specific value? That would at least get us back up and running.
Thanks
-bill
You hit the nail on the head actually! Further delving into the issue revealed that but I hadnt had a chance to post.
We recently used copy to put some lobs back into the database, about 70k of them. All of these lobs were assigned sequential oids incremented by 1. We then wrapped around and ended up in that block of oids again.
The irony of all of this is we have been trying to migrate to 8.1 for months now but have not been able to do so due to infrastructure issues beyond our control.
Is there anyway to force 8.0 to start at a specific value? That would at least get us back up and running.
Thanks
-bill
On Jan 4, 2008 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"beer@cmu.edu" <beer@cmu.edu> writes:Well, does it?
> lo_creat fails with:
> ERROR: large object <oid> already exists
What PG version is this? I'm suspicious that it's pre-8.1 and the
problem is that your OID counter has wrapped around to a region that
is already pretty densely populated with large objects. 8.1 and up
are able to pick unused OIDs but earlier versions just fail on
collisions.
regards, tom lane
"beer@cmu.edu" <beer@cmu.edu> writes: > Is there anyway to force 8.0 to start at a specific value? That would at > least get us back up and running. You're in luck: 8.0's pg_resetxlog has the -o switch, so just shut down (cleanly), use pg_resetxlog to put the OID counter where you want it, restart. regards, tom lane
beer@cmu.edu wrote: > Is there anyway to force 8.0 to start at a specific value? That would at > least get us back up and running. Stop the system (cleanly) and use pg_resetxlog -o. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Thank you both!! That got me back up and running ( for now ). Hopefully this will give me enough enough time to finish the migration next week.
Thanks again!!
Thanks again!!
On Jan 4, 2008 4:21 PM, Alvaro Herrera < alvherre@commandprompt.com> wrote:
beer@cmu.edu wrote:Stop the system (cleanly) and use pg_resetxlog -o.
> Is there anyway to force 8.0 to start at a specific value? That would at
> least get us back up and running.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support