pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Move tables to tablespace
3+ messages / 3 participants
[nested] [flat]

* Move tables to tablespace
@ 2008-10-20 14:10  Campbell, Lance <lance@illinois.edu>
  0 siblings, 2 replies; 3+ messages in thread

From: Campbell, Lance @ 2008-10-20 14:10 UTC (permalink / raw)
  To: pgsql-admin

I would like to have an SQL statement that would move all of the tables
and indexes found within a particular schema to a particular tablespace.
Is there a way I can do this?

 

Thanks,

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu <http://webservices.uiuc.edu/; 

 

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Move tables to tablespace
@ 2008-10-20 18:34  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Campbell, Lance <lance@illinois.edu>
  1 sibling, 0 replies; 3+ messages in thread

From: Scott Marlowe @ 2008-10-20 18:34 UTC (permalink / raw)
  To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin

On Mon, Oct 20, 2008 at 8:10 AM, Campbell, Lance <lance@illinois.edu> wrote:
> I would like to have an SQL statement that would move all of the tables and
> indexes found within a particular schema to a particular tablespace.  Is
> there a way I can do this?

I think you'll have to write a script (either external or in plpgsql)
to do this.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Move tables to tablespace
@ 2008-10-21 09:25  praveen <praveen.k@renaissance-it.com>
  parent: Campbell, Lance <lance@illinois.edu>
  1 sibling, 0 replies; 3+ messages in thread

From: praveen @ 2008-10-21 09:25 UTC (permalink / raw)
  To: Campbell, Lance <lance@illinois.edu>; pgsql-admin

Yes you can do this using following commands.
For tables:-
select ' alter table '||tablename||' set tablespace <new tablespace name>;' from pg_tables where schemaname='<mention schema name here>' and tableowner='< mention table owner here >';

For index :-
select ' alter index '||indexname||' set tablespace <new tablespace name>;' from pg_indexes where schemaname='<mention schema name here>';

execute above commands in sql prompt and copy the output of these command and paste again in sql prompt.
  ----- Original Message ----- 
  From: Campbell, Lance 
  To: pgsql-admin@postgresql.org 
  Sent: Monday, October 20, 2008 7:40 PM
  Subject: [ADMIN] Move tables to tablespace


  I would like to have an SQL statement that would move all of the tables and indexes found within a particular schema to a particular tablespace.  Is there a way I can do this?

   

  Thanks,

   

  Lance Campbell

  Project Manager/Software Architect

  Web Services at Public Affairs

  University of Illinois

  217.333.0382

  http://webservices.uiuc.edu

   

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2008-10-21 09:25 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-10-20 14:10 Move tables to tablespace Campbell, Lance <lance@illinois.edu>
2008-10-20 18:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-21 09:25 ` praveen <praveen.k@renaissance-it.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox