Re: Additive backup and restore?

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.