On 5/8/23 05:24, Age Apache wrote:
I am designing a database for a web application. In the near future I will
require past data for Audit, Security and Analysis purpose. But storing
all that data will increase the database size. What are some well known
approaches to archiving data for later use without increasing the database
size?
One approach I am thinking of is compressed additive backup
Additive?
Why not one compressed archive file per month per table?
and restore i.e. copy the rows of the tables that will be required later
and store them in a compressed form, and then delete those rows from the
table. And when the data is required then restore them from the backup
files in an additive way.
Is there an easy way to achieve this, say via pg_dump?
If the tables are partitioned on the relevant date field, then archiving old
data will be "trivially" easy.
If not, then it'll still be "easy":
- COPY TO a file
- compress it
- store it somewhere.
Reverse to "de-archive" the data.
--
Born in Arizona, moved to Babylonia.
