Обсуждение: WIP : change tablespace for a database
Hi, I'm currently working on a patch for the TODO item : Allow databases to be moved to different tablespaces I already changed the syntax, added some code to move the relations of the specific database to the target tablespace. It works. But I have three issues I would like to discuss. To get the list of relations to move, the user needs to be connected to the database. It seems awkward to launch an ALTER DATABASE statement on the currently open database. I mean, this is what I do know: guillaume@laptop$ psql db1 psql (8.4devel) Type "help" for help. db1=# ALTER DATABASE db1 TABLESPACE ts1; I don't think we can do another way, do you? One other thing, a much worse one. It seems I can't move sys objects. There's this comment in ATExecSetTableSpace function: /** We can never allow moving of shared or nailed-in-cache relations,* because we can't support changing their reltablespacevalues.*/ I do understand we forbid moving a system relation when using the ALTER TABLE SET TABLESPACE statement. But I wonder if it could be done with an ALTER DATABASE TABLESPACE statement. And last issue, when I do my ALTER DATABASE TABLESPACE statement, all relations are moved on the target tablespace, and the dattablespace field (in pg_database catalog) is updated with the new value. When this is done, a few moment after, I get messages telling me PG_VERSION file is not available in the pg_tblspc/<tablespace oid>/<database oid> directory. I know about the set_short_version() function in backend/commands/tablespace.c but I'm not sure I should use this function. In fact, I wonder why this file is not created at the first move of a table. Comments and suggestions welcome! -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
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) 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). > One other thing, a much worse one. It seems I can't move sys objects. The nailed relations have to stay in the DB's default tablespace, yes. This is one of the reasons for my opinion above about what the command's behavior should be. regards, tom lane
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
Guillaume Lelarge <guillaume@lelarge.info> writes: > 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? You certainly need to do *something* about that. But are you sure there aren't any existing record types that will work? Look at CREATE/DROP DATABASE. regards, tom lane
Tom Lane a écrit : > Guillaume Lelarge <guillaume@lelarge.info> writes: >> 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? > > You certainly need to do *something* about that. But are you sure there > aren't any existing record types that will work? Look at CREATE/DROP > DATABASE. > You're right. I found what I needed. I will send my patch in a few moments. Thanks a lot. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com