| 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: | Whole Thread | Raw Message | 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
| 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 |