Hello all,

I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra. What do you guys recommend on RAID Levels for this Database. Which does Postgres recommend, and with which do Postgres run very good or in the best way?

What Backup Strategy do you think would be the best. Dump the DB once a week or work with the WAL`s? For your info, the data which is feeded to the database is available and could be feeded again but it would maybe take a couple of days. So what would be a solution to bring up the huge DB in about one day after a crash. This would be the target.

Thanks for a feedback and Cheers Thomas

On Mon, Jun 30, 2008 at 02:30:23PM +0200, Thomas Bräutigam wrote:

> I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra.

Thats plenty of data.

> What do you guys recommend on RAID Levels for this Database. Which
> does Postgres recommend, and with which do Postgres run very good or
> in the best way?

Avoid RAID5. Go for RAID10.

BTW: I wonder how much the used filesystem affects Postgres' performance...

> What Backup Strategy do you think would be the best. Dump the DB once
> a week or work with the WAL`s? For your info, the data which is feeded
> to the database is available and could be feeded again but it would
> maybe take a couple of days. So what would be a solution to bring up
> the huge DB in about one day after a crash. This would be the target.

How much writing activity is on the DB? WALs are probably the way to go.
How often you backup the whole DB space (as a starting point for
replaying WALs) depends on how much changes the DB gets. And how long it
will take to restore the DB space and the WALs afterwards.

HTH,

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Hi Tino,

Why avoid RAID5? I though that would be good? Can you explain this?

Writing in the DB, I add about 5-10 GB of data a day. There is a lot of writing activity going on in the database every day.
What do you recommend how often I should backup the complete database?

Cheers Thomas


-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailtogsql-admin-owner@postgresql..org] On Behalf Of Tino Schwarze
Sent: Montag, 30. Juni 2008 14:53
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Recommended RAID for Postgres

On Mon, Jun 30, 2008 at 02:30:23PM +0200, Thomas Bräutigam wrote:

> I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra.

Thats plenty of data.

> What do you guys recommend on RAID Levels for this Database. Which
> does Postgres recommend, and with which do Postgres run very good or
> in the best way?

Avoid RAID5. Go for RAID10.

BTW: I wonder how much the used filesystem affects Postgres' performance...

> What Backup Strategy do you think would be the best. Dump the DB once
> a week or work with the WAL`s? For your info, the data which is feeded
> to the database is available and could be feeded again but it would
> maybe take a couple of days. So what would be a solution to bring up
> the huge DB in about one day after a crash. This would be the target.

How much writing activity is on the DB? WALs are probably the way to go.
How often you backup the whole DB space (as a starting point for replaying WALs) depends on how much changes the DB gets. And how long it will take torestore the DB space and the WALs afterwards.

HTH,

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Mon, Jun 30, 2008 at 03:15:34PM +0200, Thomas Bräutigam wrote:

> Why avoid RAID5? I though that would be good? Can you explain this?

RAID5 has a lot of disadvantages, especially in terms of performance.
..oO(There was a link posted recently...)

> Writing in the DB, I add about 5-10 GB of data a day. There is a lot of writing activity going on in the database every day.
> What do you recommend how often I should backup the complete database?

What storage subsystem do you use? This sounds like you want something
like snapshots or similar to do backup. How long do you expect a 1-1.5
TB backup to run? I don't know. My biggest DBs are like 110 GB
(compressed dump), 300-400 GB on disk and they are difficult to handle
already.

Maybe other Postgres admins have more experience with such big DBs.

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Montag, 30. Juni 2008 Thomas Bräutigam wrote:
> Why avoid RAID5?

RAID5 has very poor WRITE performance compared to RAID10, while READ is
nearly the same speed.

If you do not have a "branded" server like HP or IBM, I can recommend
the Areca RAID controllers http://www.areca.com.tw/ their driver is
included in Linux kernel, and they are pretty damn fast. Change the
onboard RAM to 2GB, with the SAS 16 or 24 port controller this is the
way to have fun.

On their website is also benchmarks about different RAID levels, so you
can compare them.

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net Key-ID: 1C1209B4

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4-svn0 (GNU/Linux)

iD8DBQBIaP6HzhSR9xwSCbQRAuxJAKDmpWlJ4bxodLtk1vuNHY 6KVhMC9QCeIqK2
3lDlr5ojn/JW7doMb7UeIv0=
=qA+U
-----END PGP SIGNATURE-----

On Mon, Jun 30, 2008 at 3:30 PM, Thomas Bräutigam <
thomas.braeutigam@nexustelecom.com> wrote:

> Hello all,
>
> I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra. What do you guys
> recommend on RAID Levels for this Database. Which does Postgres recommend,
> and with which do Postgres run very good or in the best way?
>

We are running one ~2 TB database, mainly read-only stuff except for at
batch import process every night. We are using RAID5 (RAID10 would require
too many hard disk drives).


> What Backup Strategy do you think would be the best. Dump the DB once a
> week or work with the WAL`s? For your info, the data which is feeded to the
> database is available and could be feeded again but it would maybe take a
> couple of days. So what would be a solution to bring up the huge DB in about
> one day after a crash. This would be the target.
>

I'd say that backup (using filesystem tools) the database once a week or
fortnight and archive wals everyday. That's what we are doing and it's been
working just fine.

Regards

Mikko