Обсуждение: Orphan TOAST object
Hi all,
I wonder whether any of you can help me out with this problem. We were performed a routine "lazy" VACUUM in order to reassign frozen XIDs and prevent data-loss.
After the VACUUM completed successfully, the command "SELECT datname, age(datfrozenxid) FROM pg_database" still showed an excess of 1,800,000,000 transactions from the cutoff XID of some table meaning that either the VACUUM somehow failed or else missed out a table.
Just to make sure, we re-ran the VACUUM but the result in the end was the same. We then decided to find out which table was causing this problem by running the following query:
select relname from pg_class where relfrozenxid = (select datfrozenxid from pg_database where datname = 'CDR')
Since datfrozenxid in pg_database stores the oldest XID, using this query we were able to home on the database object which was allegedly being missed by the VACUUM. The query returned a TOAST object: pg_toast_35027430. This was puzzling; as far as I know pg_toasts objects can't be vacuumed directly b
ut only when vacuuming their parent. This means that somehow this pg_toast object was orphaned, fact confirmed by the following query:
select relname from pg_class a where relname like 'pg_toast_3%' and relkind = 't' and not exists (
select 1 from pg_class b where a.oid = b.reltoastrelid and relkind = 'r')
To get vacuum the TOAST object we created a temporary table foo (col1 char(1)) and assigned its reltoastrelid (up till now set to 0) to pg_toast_35027430's OID and then vacuumed foo. The plan worked and immediately age(datfrozenxid) in pg_database reflected a much younger XID.
We then decided to get rid of pg_toast_35027430 by dropping foo. Foo disappeared but pg_toast_35027430 persisted. I'd like to get rid of it because in a few months' time we will bump into the same problem again. Does anyone have any idea how this can be removed manually without causing any unwarranted damage to the system catalogue?
Also, can one un-vacuumed database object cause dataloss in other unrelated tabled which have otherwise been VACUUMed and their respective FrozenXIDs reassigned?
Thank you in advance,
James.
"James Farrugia" <james.farrugia@gmail.com> writes:
> I wonder whether any of you can help me out with this problem.
What PG version is this?
> To get vacuum the TOAST object we created a temporary table foo (col1
> char(1)) and assigned its reltoastrelid (up till now set to 0) to
> pg_toast_35027430's OID and then vacuumed foo. The plan worked and
> immediately age(datfrozenxid) in pg_database reflected a much younger XID.
> We then decided to get rid of pg_toast_35027430 by dropping foo. Foo
> disappeared but pg_toast_35027430 persisted.
Well, yeah, because you didn't create a pg_depend link.
We have seen a couple prior reports of toast tables not going away when
their parent was dropped, but nobody's been able to create a
reproducible case yet. The most likely idea is probably that pg_depend
got corrupted somehow, causing the toast table not to get found by DROP.
Can you find any rows in pg_depend having objid equal to the OID of
pg_toast_35027430? Try reindexing pg_depend and then see if you find
any.
regards, tom lane
"James Farrugia" <james.farrugia@gmail.com> writes:
> I'm running 8.2.1.
You really need to update to 8.2.latest. There are several known
data-corruption problems in 8.2.1, and it seems possible that one of
them ate the pg_depend row you needed.
> I cleanly forgot about pg_depend!
> Even after re-indexing I wasn't able to find an entry in pg_depend having
> the TOAST's OID. I guess that by creating foo again and linking
> pg_toast_xxx with foo in pg_depend by hand i can make it go away.
Yeah, that's probably the cleanest recovery strategy.
regards, tom lane
Hi Tom,
First of all thanks for the immediate replies!
Was actually waiting for the right moment to upgrade to 8.3 but migrating a live 1Tb database is a bit daunting especially if you have never done it before (as in my case). If I'm not mistaken i can upgrade to the latest minor version without having to dump and restore so I'll do that.
One last thing...can we run into data-loss problems with successfully vacuumed tables even if there is one unvacuumed database object; what would have happened if I ignored to vacuum that rogue pg_toast (which was the only unvacuumed object within the entire database)?
Thanks again.
James
James
On 5/11/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"James Farrugia" <james.farrugia@gmail.com> writes:
> I'm running 8.2.1.
You really need to update to 8.2.latest. There are several known
data-corruption problems in 8.2.1, and it seems possible that one of
them ate the pg_depend row you needed.
> I cleanly forgot about pg_depend!
> Even after re-indexing I wasn't able to find an entry in pg_depend having
> the TOAST's OID. I guess that by creating foo again and linking
> pg_toast_xxx with foo in pg_depend by hand i can make it go away.
Yeah, that's probably the cleanest recovery strategy.
regards, tom lane
Hi James, On Mon, May 12, 2008 at 09:25:34AM +0200, James Farrugia wrote: > First of all thanks for the immediate replies! > Was actually waiting for the right moment to upgrade to 8.3 but migrating a > live 1Tb database is a bit daunting especially if you have never done it > before (as in my case). If I'm not mistaken i can upgrade to the latest > minor version without having to dump and restore so I'll do that. You need to check the release notes of the particulra minor release you are upgrading to. Some minor releases also require a dump/restore cycle because of bugfixes. You're lucky: 8.2.7 doesn't require dump/restore in your case, the release notes say: "A dump/restore is not required for those running 8.2.X" > One last thing...can we run into data-loss problems with successfully > vacuumed tables even if there is one unvacuumed database object; what would > have happened if I ignored to vacuum that rogue pg_toast (which was the only > unvacuumed object within the entire database)? At a certain point, the server would have switched to "emergency mode" and wouldn't have accepted any commands any more. See the description here: http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND HTH, Tino. -- "What we resist, persists." (Zen saying) www.craniosacralzentrum.de www.forteego.de
Hi Tino,
That was what we suspected and in fact didn't want to run any unnecessary risks. Thanks again.
James.
On 5/12/08, Tino Schwarze <postgresql@tisc.de> wrote:
Hi James,
On Mon, May 12, 2008 at 09:25:34AM +0200, James Farrugia wrote:
> First of all thanks for the immediate replies!
> Was actually waiting for the right moment to upgrade to 8.3 but migrating a
> live 1Tb database is a bit daunting especially if you have never done it
> before (as in my case). If I'm not mistaken i can upgrade to the latest
> minor version without having to dump and restore so I'll do that.
You need to check the release notes of the particulra minor release you
are upgrading to. Some minor releases also require a dump/restore cycle
because of bugfixes. You're lucky: 8.2.7 doesn't require dump/restore in
your case, the release notes say:
"A dump/restore is not required for those running 8.2.X"
> One last thing...can we run into data-loss problems with successfully
> vacuumed tables even if there is one unvacuumed database object; what would
> have happened if I ignored to vacuum that rogue pg_toast (which was the only
> unvacuumed object within the entire database)?
At a certain point, the server would have switched to "emergency mode"
and wouldn't have accepted any commands any more. See the description
here:
http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
HTH,
Tino.
--
"What we resist, persists." (Zen saying)
www.craniosacralzentrum.de
www.forteego.de
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
"James Farrugia" <james.farrugia@gmail.com> writes:
> One last thing...can we run into data-loss problems with successfully
> vacuumed tables even if there is one unvacuumed database object; what would
> have happened if I ignored to vacuum that rogue pg_toast (which was the only
> unvacuumed object within the entire database)?
The database would have shut down when you got to the
2-billion-transactions mark. So you'd have had to solve the problem
sooner or later anyway.
regards, tom lane
Problem has now been solved. Thanks a lot for all your help.
On 5/12/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"James Farrugia" <james.farrugia@gmail.com> writes:
> One last thing...can we run into data-loss problems with successfully
> vacuumed tables even if there is one unvacuumed database object; what would
> have happened if I ignored to vacuum that rogue pg_toast (which was the only
> unvacuumed object within the entire database)?
The database would have shut down when you got to the
2-billion-transactions mark. So you'd have had to solve the problem
sooner or later anyway.
regards, tom lane