SEO

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

--
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:
> 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

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/s...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

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/s...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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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
>