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.