Thread: moving tables
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.
Current server info:
- 4 dual-core AMD Opteron 2212 processors, 2010.485 MHz
- 64GB RAM
- 16 67GB RAID 1 drives and 1 464GB RAID 10 drive (all ext3) on 2 volumes.
- Linux 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
Thank you,
Midge
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 drivesthey will be reconfigured, at which point I'll need to move everything from the 2nd volume to the aforementioned 12logical 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 foundanything 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 -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
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
Last night I created directories and moved files as outlined in Josh's very helpful reply to my original request. All seemed okay until we unmounted the drives from the first volume. I got the following error (with oid differences) whenever I tried to access any of the tables that were not originally on the 2nd volume raid 10:
ERROR: could not open file "pg_tblspc/18505/PG_9.0_201008051/99644466/99645029": No such file or directory
When I looked at the files in the linked directories on the raid 10, it appeared that the oid (18505 in the above error) was missing. After we remounted the drives so that access could be restored, it occurred to me that I should have altered the tablespaces to match the move to the 2nd volume. Would that have dealt with the error I saw?
On further reflection, it seems that the best course of action would be to have only the one tablespace on the existing raid 10 drive that resides on the 2nd volume. Then the first volume can be reconfigured into one raid 10 and I could move everything to it and the 2nd volume can physically be removed for use in another server that I can configure as a hot standby.
Does this plan make sense? Any comments or suggestions are welcome.
Thanks,
Midge
----- Original Message -----From: Josh BerkusSent: Wednesday, June 20, 2012 5:28 PMSubject: Re: [PERFORM] moving tablesOn 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
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance