[ADMIN] Database archive solutions
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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] How to check history of sequences and constraints (version 9.1)
Shams Khan writes: > I want to check the last reset valued of sequence and the date of foreign > key constraint when it was created. Is that possible in postgres 9.1 > version?? No, the system doesn't track any such thing. You could possibly set log_statement to record all commands of interest in the postmaster log and then trawl through that data, but it wouldn't be especially easy. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Database archive solutions
On Wed, Nov 21, 2012 at 1:21 AM, Gnanakumar wrote: > 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 > > 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 > > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
Re: [ADMIN] Database archive solutions
> 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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin