PITR and moving objects between table spaces

From: Glen Parker <glenebob(at)nwlink(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PITR and moving objects between table spaces
Date: 2006-12-12 00:51:06
Message-ID: 457DFCFA.3050706@nwlink.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Gurus,

I hope I can make this clear somehow... Anyway... This all involves PG
8.1.4 on a 64-bit FC5 box.

Select version() says "PostgreSQL 8.1.4 on x86_64-redhat-linux-gnu,
compiled by GCC x86_64-redhat-linux-gcc (GCC) 4.1.0 20060304 (Red Hat
4.1.0-3)".

I guess the best question I can see is, under what circumstances is the
directory name in pg_tablespace actually used?

I have a scenario where I want to restore from a PITR backup, into an
alternate location on the same machine it came from, while the original
database is still up and running. I have one alternate table space.

It goes like this. First I expand the base archive into an alternate
location, then expand the table space archive(s) into alternate
location(s). Then I recreate the links under pg_tblspc. I then fiddle
a little bit with config files and run postgres on the new alternate
location. Everthing goes fine, the database rolls forward, and then
postgres quits (because I give it an SQL file for stdin). Great.

But now I have a problem. What if I move objects from the main
tablespace to the alternate one, such as indexes, between the time of
the backup and the restore? During the restore/recovery, the
pg_tablespace table is out of date. If the tablespace directory listed
there was used in copying files, I'd have a big fat mess involving a
badly broken production database.

Hopefully that all makes sense...

-Glen

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-12-12 00:53:43 Re: forcing compression of text field
Previous Message Jeff Davis 2006-12-12 00:04:38 Re: out of memory error on 3 table join