Re: WIP : change tablespace for a database

Tom Lane a écrit :
> Guillaume Lelarge <guillaume(at)lelarge(dot)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(at)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 relations
Schema | 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.org
http://dalibo.com