Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to move tables in another physial place

How to move tables in another physial place

From
Michelle Konzack
Date:
Hello,

I am using Debian GNU/Linux 4.0 with postgresql 8.2.

Now I have splited my Monster-Table of 580 GByte (historical timeline)
into one table per year (= several 100 now) and use table partitioning.
(Cool for pg_dump since I can dump singel tables and burn it now on CD
or DVD). Then I have reorganized my server and now I have several Raid-1
volumes to use table partitioning.

The tables are now working and my Database is over 80 times faster
because most searches are not over the whole table (580 GByte) but over
a paticular time (mostly 20-40 years).

What I want now is to move tables to another partition.

How can I do this? (best and fastest way)

Greetings
    Michelle Konzack
    Systemadministrator
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSN LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)
Attachment

Re: How to move tables in another physial place

From
"Joshua D. Drake"
Date:
Michelle Konzack wrote:
> Hello,
>
> I am using Debian GNU/Linux 4.0 with postgresql 8.2.
>
> Now I have splited my Monster-Table of 580 GByte (historical timeline)
> into one table per year (= several 100 now) and use table partitioning.
> (Cool for pg_dump since I can dump singel tables and burn it now on CD
> or DVD). Then I have reorganized my server and now I have several Raid-1
> volumes to use table partitioning.
>
> The tables are now working and my Database is over 80 times faster
> because most searches are not over the whole table (580 GByte) but over
> a paticular time (mostly 20-40 years).
>
> What I want now is to move tables to another partition.
>
> How can I do this? (best and fastest way)


You are looking for tablespaces.

http://www.postgresql.org/docs/8.0/interactive/manage-ag-tablespaces.html

>
> Greetings
>     Michelle Konzack
>     Systemadministrator
>     Tamay Dogan Network
>     Debian GNU/Linux Consultant
>
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Re: How to move tables in another physial place

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/22/07 12:01, Michelle Konzack wrote:
> Hello,
>
> I am using Debian GNU/Linux 4.0 with postgresql 8.2.
>
> Now I have splited my Monster-Table of 580 GByte (historical timeline)
> into one table per year (= several 100 now) and use table partitioning.
> (Cool for pg_dump since I can dump singel tables and burn it now on CD
> or DVD). Then I have reorganized my server and now I have several Raid-1
> volumes to use table partitioning.
>
> The tables are now working and my Database is over 80 times faster
> because most searches are not over the whole table (580 GByte) but over
> a paticular time (mostly 20-40 years).
>
> What I want now is to move tables to another partition.
>
> How can I do this? (best and fastest way)

Your subject says "physial place", but then you ask about moving a
table to another partition.

Since partitions are sub-sections of tables, I don't understand what
you are asking.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGUywzS9HxQb37XmcRAlI4AKCHKBmWSW5FpLKmiPRvXfZqWd68RwCfaTOm
0qhxJymuK1WMXcQ9JcFfA1c=
=yTo5
-----END PGP SIGNATURE-----

Re: How to move tables in another physial place

From
Michelle Konzack
Date:
Am 2007-05-22 12:45:23, schrieb Ron Johnson:
> Your subject says "physial place", but then you ask about moving a
> table to another partition.
>
> Since partitions are sub-sections of tables, I don't understand what
> you are asking.

:-)

INSIDE the postgresql I use table partitioning but the whole database
is on ONE PHYSICAL partition (/dev/sdb1) in the moment.

I know tablespace and want to move parts of the Maintable (which is
cutted down in many smaler parts) on another PYSIACAL partitonon of
/dev/sdc1, /dev/sdd1 and /dev/sde1 to reduce the seeks on the drives.

But since the database IS on ONE PYSIACAL partiton I do not know,
HOW TO MOVE some of the smaller tables (without stoping the PostgreSQL
from operating since it is needed 24/7)

Thanks, Greetings and nice Day
    Michelle Konzack
    Systemadministrator
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSN LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)
Attachment

Re: How to move tables in another physial place

From
"Joshua D. Drake"
Date:
Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 05/22/07 12:01, Michelle Konzack wrote:
>> Hello,
>>
>> I am using Debian GNU/Linux 4.0 with postgresql 8.2.
>>
>> Now I have splited my Monster-Table of 580 GByte (historical timeline)
>> into one table per year (= several 100 now) and use table partitioning.
>> (Cool for pg_dump since I can dump singel tables and burn it now on CD
>> or DVD). Then I have reorganized my server and now I have several Raid-1
>> volumes to use table partitioning.
>>
>> The tables are now working and my Database is over 80 times faster
>> because most searches are not over the whole table (580 GByte) but over
>> a paticular time (mostly 20-40 years).
>>
>> What I want now is to move tables to another partition.
>>
>> How can I do this? (best and fastest way)
>
> Your subject says "physial place", but then you ask about moving a
> table to another partition.
>
> Since partitions are sub-sections of tables, I don't understand what
> you are asking.
>

Hard drive partitions.

Joshua D. Drake


> - --
> Ron Johnson, Jr.
> Jefferson LA  USA
>
> Give a man a fish, and he eats for a day.
> Hit him with a fish, and he goes away for good!
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFGUywzS9HxQb37XmcRAlI4AKCHKBmWSW5FpLKmiPRvXfZqWd68RwCfaTOm
> 0qhxJymuK1WMXcQ9JcFfA1c=
> =yTo5
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Re: How to move tables in another physial place

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/22/07 12:58, Michelle Konzack wrote:
> Am 2007-05-22 12:45:23, schrieb Ron Johnson:
>> Your subject says "physial place", but then you ask about moving a
>> table to another partition.
>>
>> Since partitions are sub-sections of tables, I don't understand what
>> you are asking.
>
> :-)
>
> INSIDE the postgresql I use table partitioning but the whole database
> is on ONE PHYSICAL partition (/dev/sdb1) in the moment.
>
> I know tablespace and want to move parts of the Maintable (which is
> cutted down in many smaler parts) on another PYSIACAL partitonon of
> /dev/sdc1, /dev/sdd1 and /dev/sde1 to reduce the seeks on the drives.
>
> But since the database IS on ONE PYSIACAL partiton I do not know,
> HOW TO MOVE some of the smaller tables (without stoping the PostgreSQL
> from operating since it is needed 24/7)

Since PostgreSQL doesn't use raw partitions, your database can't
"own" it.  (Although, obviously, Postgres files might be the only
ones on sdb1.)

Is Maintable composed of smaller tables?

Anyway, this is what you need:
http://www.postgresql.org/docs/8.2/interactive/sql-altertable.html
ALTER TABLE foo SET TABLESPACE bar;


- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGUzWdS9HxQb37XmcRAoSEAKDuKTJi/yFuQcVdWKKzVTPzzJMs4ACfUqzM
Rpd+Xf4/2o1b6mo5xgm8AXQ=
=ASU2
-----END PGP SIGNATURE-----