Skip site navigation (1) Skip section navigation (2)

Re: moving tables

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Josh Berkus *EXTERN*" <josh(at)agliodbs(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: moving tables
Date: 2012-06-21 08:34:38
Message-ID: D960CB61B694CF459DCFB4B0128514C20808D4EB@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-performance
Josh Berkus wrote:
> On 6/20/12 3:27 PM, Midge Brown wrote:
>> I need to move a postgres 9.0 database -- with tables, indexes, and wals associated with 16
>> tablespaces on 12 logical drives -- to an existing raid 10 drive in another volume on the same server.
>> Once I get the data off the initial 12 drives they will be reconfigured, at which point I'll need to
>> move everything from the 2nd volume to the aforementioned 12 logical drives on the first volume. This
>> is being done both to free up the 2nd volume and to better utilize raid 10.
>>
>> I checked around and found a way to create sql statements to alter the public tablespaces and
>> indexes, but I haven't found anything that provides information about moving the numerous associated
>> config files, log files, etc.
>>
>> ANY comments, suggestions, or direction to existing documentation would be greatly appreciated.

> 1. back everything up.
> 
> 2. create a bunch of directories on the RAID10 to match the existing
> tablespaces (they won't be mounts, but Postgres doesn't care about that).
> 
> 3. shut down postgres
> 
> 4. copy all your files to the new directories
> 
> 5. change your mount points which were in use by the old tablespaces to
> symlinks which point at the new diretories
> 
> 6. start postgres back up from the new location

Shouldn't you also

7. UPDATE spclocation in pg_tablespace ?

Yours,
Laurenz Albe

In response to

pgsql-performance by date

Next:From: Andy HalsallDate: 2012-06-21 20:07:01
Subject: Can I do better than this heapscan and sort?
Previous:From: Craig RingerDate: 2012-06-21 02:45:41
Subject: Re: index-only scan is missing the INCLUDE feature

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group