Lists: | pgsql-hackers |
---|
From: | "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | CLONE TABLE DATA TO <new_table> |
Date: | 2012-01-13 10:56:31 |
Message-ID: | C4DAC901169B624F933534A26ED7DF310861B2BC@JENMAIL01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hello,
I wonder if it would be possible to have a fast table clone function
(data only) while copying the corresponding data files
instead of using the "CREATE TABLE AS" way.
pg_upgrade seems to have such a mechanisms, though it requires to first
stop the server...
This would of course require to lock the complete table and ensure that
all latest changes are flushed to the plates.
I don't know how are the plan about switching from UNLOGGED to LOGGED
tables, but I guess this might be required to start logging the table
only after the copy.
Background: I have daily tables with hourly imports which may contain >
100 Mio rows and require 7 indices on them.
In order to improve import performances, I first do a copy of the active
table, import new data and rebuild the indexes.
Thanks for your great job,
Marc Mamin
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: CLONE TABLE DATA TO <new_table> |
Date: | 2012-01-13 13:12:16 |
Message-ID: | CA+TgmoaGC4LG3Haut7d6tgKm17HhB2SbA73J-JG2nps3_b1Hpg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Fri, Jan 13, 2012 at 5:56 AM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
> I wonder if it would be possible to have a fast table clone function (data
> only) while copying the corresponding data files
>
> instead of using the "CREATE TABLE AS" way.
>
> pg_upgrade seems to have such a mechanisms, though it requires to first stop
> the server...
>
> This would of course require to lock the complete table and ensure that all
> latest changes are flushed to the plates.
I think it would be possible to implement this. In fact, it could
probably be done as a contrib module.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From: | "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "Robert Haas" <robertmhaas(at)gmail(dot)com> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: CLONE TABLE DATA TO <new_table> |
Date: | 2012-01-13 13:25:22 |
Message-ID: | C4DAC901169B624F933534A26ED7DF310861B2C3@JENMAIL01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
This would be great, but I can't C :-(
Marc
> -----Original Message-----
> From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
> Sent: Freitag, 13. Januar 2012 14:12
> To: Marc Mamin
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] CLONE TABLE DATA TO <new_table>
>
> On Fri, Jan 13, 2012 at 5:56 AM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de>
> wrote:
> > I wonder if it would be possible to have a fast table clone function
> (data
> > only) while copying the corresponding data files
> >
> > instead of using the "CREATE TABLE AS" way.
> >
> > pg_upgrade seems to have such a mechanisms, though it requires to
> first stop
> > the server...
> >
> > This would of course require to lock the complete table and ensure
> that all
> > latest changes are flushed to the plates.
>
> I think it would be possible to implement this. In fact, it could
> probably be done as a contrib module.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company