Database archive solutions

[Gnanakumar]

Hi,

Our application requirement demands archiving of records, so that
performance/speed of the application is not compromised. So, am looking
out/evaluating on various techniques/solutions available for database
archiving, in general, for PostgreSQL.

Few solutions that comes to my mind are:

1) Table partitioning
2) Separate tablespace and/or schema
3) Moving archived records/tables to a different harddisk

Any other suggestions/pointers/solutions are really welcome and appreciated.

NOTE: We're running PostgreSQL v9.1.3 on CentOS5.2

Regards,
Gnanam

[Craig James]

Post by Gnanakumar
Hi,
Our application requirement demands archiving of records, so that
performance/speed of the application is not compromised. So, am looking
out/evaluating on various techniques/solutions available for database
archiving, in general, for PostgreSQL.

Your question isn't specific enough. What does "not compromised" mean?
Every technique for archiving records has some impact on some part of your
system. Is a 0.1% impact too much? A 1% impact? A 10% impact? A factor
of 2 slowdown?

Do you need real-time archiving, a few minutes delay, or once a month
backup?

Do you need a hot standby or records that can be retrieved in a few hours?

You have to describe your needs completely before you can get a meaningful
answer.

Craig

Post by Gnanakumar
1) Table partitioning
2) Separate tablespace and/or schema
3) Moving archived records/tables to a different harddisk
Any other suggestions/pointers/solutions are really welcome and appreciated.
NOTE: We're running PostgreSQL v9.1.3 on CentOS5.2
Regards,
Gnanam
--
http://www.postgresql.org/mailpref/pgsql-admin

[Gnanakumar]

Your question isn't specific enough.  What does "not compromised" mean? 

Every technique for archiving records has some impact on some part of your
system.  Is a 0.1% impact too

much?  A 1% impact?  A 10% impact?  A factor of 2 slowdown?

Sorry if my original question has not clearly conveyed my requirement.
First, let me accept that I'm not a good DBA Expert (am a beginner), however
I'm trying to learn things.
Actually, am finding out various techniques/options *itself* available for
archiving database records. Based on the techniques available, I can
see/assess on how much it could impact our application, so that I can choose
one and adopt that suits our needs. In fact your response was an eye-opener
for me to think further in detail.

Do you need real-time archiving, a few minutes delay, or once a month

backup?

Yes, we're looking for real-time archiving. Once the records are archived,
Users in the application should able to perform search within archives also
after few minutes of time.

Do you need a hot standby or records that can be retrieved in a few hours?

It would be helpful if you can share the techniques/options available for
hot standby also.