Re: WIP : change tablespace for a database

Tom Lane a écrit :
> Guillaume Lelarge <guillaume@lelarge.info> writes:
>> To get the list of relations to move, the user needs to be connected to
>> the database.
> 
> Why?  If what you are doing is changing the database's default
> tablespace (which IMHO is what such a command ought to do)

That's exactly what I'm trying to do.

> then
> all you have to do is bulk-copy the per-DB subdirectory from
> the old default tablespace to the new one.  There's no reason to
> think about it at the individual-relation level, and there won't be
> any change to the contents of any catalog in the DB either (only
> its pg_database row will change).
> 

So, I should be doing something like this:
* check various stuff (like permission and the fact that  no-one is connected on the target database)* lock the
database*read the default tablespace dir (AllocateDir, ReadDir)* move each file in it to the target tablespace
(copydir,rmtree)* change the default tablespace in pg_database
 

My current patch works well with this simple script:

guillaume@laptop$ psql postgres
psql (8.4devel)
Type "help" for help.

postgres=# create database db1;
CREATE DATABASE
postgres=# \c db1
psql (8.4devel)
You are now connected to database "db1".
db1=# create tablespace ts1
db1-# location '/home/guillaume/postgresql_tblspc';
CREATE TABLESPACE
db1=# create table t1(id int4);
CREATE TABLE
db1=# insert into t1 values (1);
INSERT 0 1
db1=# \c postgres
psql (8.4devel)
You are now connected to database "postgres".
postgres=# alter database db1 tablespace ts1;
NOTICE:  alter tablespace db1 set tablespace ts1!
NOTICE:  move base/16384 to pg_tblspc/16385/16384
NOTICE:  remove base/16384
ALTER DATABASE
postgres=# \c db1
psql (8.4devel)
You are now connected to database "db1".
db1=# \d        List of relationsSchema | Name | Type  |   Owner
--------+------+-------+-----------public | t1   | table | guillaume
(1 row)
db1=# select datname, dattablespace from pg_database
db1-# where datname='db1';datname | dattablespace
---------+---------------db1     |         16385
(1 row)
db1=# select relname, relfilenode, reltablespace from pg_class
db1-# where relname='t1';relname | relfilenode | reltablespace
---------+-------------+---------------t1      |       16386 |             0
(1 row)

So, it seems to work. I say "seems" because there's no XLOG record that
says I moved all relations from one tablespace to another. Am I right in
thinking I need to insert a new XLOG record? should I create a new one?

Thanks.


-- 
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com