From: | Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com> |
---|---|
To: | "Wetmore, Matthew (CTR)" <Matthew(dot)Wetmore(at)express-scripts(dot)com> |
Cc: | Holger Jakobs <holger(at)jakobs(dot)com>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Automate to rename table |
Date: | 2023-08-10 15:49:00 |
Message-ID: | CAJk5AtYsUP-Fo8V0Pg-TF9GhOKASzOsgfYsVHHmddjhvqZDPZg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
@Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com> How exactly?,
In this case, we are not gonna need the old table. That is just kind of
backup only
On Thu, 10 Aug 2023 at 08:27, Wetmore, Matthew (CTR) <
Matthew(dot)Wetmore(at)express-scripts(dot)com> wrote:
> A partitioned table with less than 500M rows will suffer from performance
> issues. Just my $0.02
>
>
>
> *From:* Holger Jakobs <holger(at)jakobs(dot)com>
> *Sent:* Thursday, August 10, 2023 3:12 AM
> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* [EXTERNAL] Re: Automate to rename table
>
>
>
> Creating a partitioned table with a partition by month would be the way to
> go. Then drop the tables you no longer need. This can be done in a cron job
> or by using pg_agent, pg_cron or pg_timetable.
>
>
>
> Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <
> rajeshkumar(dot)dba09(at)gmail(dot)com>:
>
> I have a table A where datas are coming in...and as it gets old (say 30
> days), we would not want this on same table and wants it in another table
> 30days old (A_old) and any new request will be in A table.
>
>
>
>
>
> So, what we are already doing is, created a function to create a table A
> if not exists , then we are renaming the table A to A_old manually. And
> when we execute that function it create a new table A so that new data gets
> inserted. Then after 60 days we drop drop A_old table. Which means we need
> 30 days data in new table, 30 days data in old table and remaining we don't
> need it.
>
>
>
> How do we automate it?
>
>
>
> I am thinking of partioning, but unsure whether it's a complex process
> ..also don't have date column.
>
>
>
> Can we simply create a function to alter table A rename to A_old , and
> schedule to execute this function every 30 days?
>
>
>
> What's the best way?
>
>
>
> On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells(dot)oliver(at)gmail(dot)com>
> wrote:
>
> Wild. What's the use-case here?
>
>
>
> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
> Matthew(dot)Wetmore(at)express-scripts(dot)com> wrote:
>
> Simple CRON entry with a script that runs a psql command
>
>
>
> You can:
>
>
>
> alter table rename
>
> Or
>
> create table x as select * from original table.
>
>
>
> Depending on you DDL needs or permissions/setup
>
>
>
> *From:* Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>
> *Sent:* Wednesday, August 9, 2023 1:39 PM
> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* [EXTERNAL] Automate to rename table
>
>
>
> Hi friends,
>
>
>
> What is the best way to rename the table name automatically every 30
> minutes?
>
>
>
>
>
>
> --
>
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Fiske | 2023-08-10 16:21:46 | Re: Automate to rename table |
Previous Message | Stephen Frost | 2023-08-10 15:41:06 | Re: .history': No such file or directory - a symptom of ? |