SEO

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)

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

iD8DBQFGUyICC0FPBMSS+BIRAvzDAJ9MP7OfRHtM+OZogeILaS 7pUgS9/ACggCfR
bn5YWD1/YAVHwUQLLb7C0tE=
=HwLq
-----END PGP SIGNATURE-----

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/i...blespaces.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/


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

-----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)

iD8DBQFGUywzS9HxQb37XmcRAlI4AKCHKBmWSW5FpLKmiPRvXf ZqWd68RwCfaTOm
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

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)
>
> iD8DBQFGUywzS9HxQb37XmcRAlI4AKCHKBmWSW5FpLKmiPRvXf ZqWd68RwCfaTOm
> 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/


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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)

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

iD8DBQFGUy8yC0FPBMSS+BIRApfKAKCeShumnn2pEQZcGepOuN tqn80kzwCfbCSM
5lQP+1Kr8jJS85OlGCM2ZLg=
=7aGH
-----END PGP SIGNATURE-----

-----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/i...ltertable.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-----

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster