Re: WIP : change tablespace for a database

From: Guillaume Lelarge <guillaume(at)lelarge(dot)info>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WIP : change tablespace for a database
Date: 2008-10-24 22:02:25
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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;
postgres=# \c db1
psql (8.4devel)
You are now connected to database "db1".
db1=# create tablespace ts1
db1-# location '/home/guillaume/postgresql_tblspc';
db1=# create table t1(id int4);
db1=# insert into t1 values (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
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?



In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Decibel! 2008-10-24 22:29:41 Handling NULL records in plpgsql
Previous Message Tim Keitt 2008-10-24 21:33:27 SPI cursor functions