How do I archive an old data in postgresql

Question

0.00/5 (No votes)

See more:

As I'm currently expecting for somebody to advice me on the process which I'm gonna take forward for DB archiving.

I've database(DB-1) which has 2 very large tables, one table having 25 GB of data and another is 20 GB of data. which cause major performance issues even i have indexes.

What I have tried:

So, we can considered to archive the old data with the below process,

1. Clone a new database(DB-2) from existing database(DB-1).
2. Delete an old data from DB-1, so it will have only the last 2 years records.In case If I need old data can connect DB-2.
3. Every month should move an old data from DB-1 to DB-2, and delete
the moved records from DB-1.

Thanks,

Posted 10-Jul-19 23:25pm

King Fisher

Updated 10-Jul-19 23:29pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-07-10T23:29:00

Solution 1

Quote:
I've database(DB-1) which has 2 very large tables, one table having 25 GB of data and another is 20 GB of data. which cause major performance issues even i have indexes.

Seems, your question is not about archiving old data, but how to "shrink database"?

Have you seen this: PostgreSQL: Documentation: 11: 24.1. Routine Vacuuming[^] ?
This might be helpful too: Optimize and Improve PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX - Atlassian Documentation[^]

Posted 10-Jul-19 23:29pm

Maciej Los

Updated 10-Jul-19 23:44pm

v3

Comments

King Fisher 11-Jul-19 8:31am

Hi Maciej,
how are you ? Thanks for the reply, I see the autovacuumis on in db. Its not about only shrink database. If I remove the old data, my production db will have only 2 years data, which is less, so there will be a improvement in the system performance.

Maciej Los 11-Jul-19 10:14am

Hi King!
Lot of time i haven't seen you on CP...
Thank you, i'm fine. And how are you?

King Fisher 31-Jul-19 3:21am

Hi Maciej,
yeah, didn't get chance actually.
I'm good, thanks.