pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAdditive backup and restore?
3+ messages / 3 participants
[nested] [flat]
* Additive backup and restore?
@ 2023-05-08 10:24 Age Apache <age.apache@gmail.com>
2023-05-08 11:02 ` Re: Additive backup and restore? Andreas Kretschmer <andreas@a-kretschmer.de>
2023-05-08 13:36 ` Re: Additive backup and restore? Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Age Apache @ 2023-05-08 10:24 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Additive backup and restore?
2023-05-08 10:24 Additive backup and restore? Age Apache <age.apache@gmail.com>
@ 2023-05-08 11:02 ` Andreas Kretschmer <andreas@a-kretschmer.de>
1 sibling, 0 replies; 3+ messages in thread
From: Andreas Kretschmer @ 2023-05-08 11:02 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Consider table partitioning. You can detach, save and delete partitions, and you can restore and attach partitions.
On 8 May 2023 12:24:06 CEST, Age Apache <age.apache@gmail.com> 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 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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Additive backup and restore?
2023-05-08 10:24 Additive backup and restore? Age Apache <age.apache@gmail.com>
@ 2023-05-08 13:36 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron @ 2023-05-08 13:36 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-05-08 13:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-08 10:24 Additive backup and restore? Age Apache <age.apache@gmail.com>
2023-05-08 11:02 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2023-05-08 13:36 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox