CLONE TABLE DATA TO <new_table>
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
On Fri, Jan 13, 2012 at 5:56 AM, Marc Mamin <M.Mamin@intershop.de> wrote:
I wonder if it would be possible to have a fast table clone function (data
only) while copying the corresponding data filesinstead 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
This would be great, but I can't C :-(
Marc
Show quoted text
-----Original Message-----
From: Robert Haas [mailto:robertmhaas@gmail.com]
Sent: Freitag, 13. Januar 2012 14:12
To: Marc Mamin
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] CLONE TABLE DATA TO <new_table>On Fri, Jan 13, 2012 at 5:56 AM, Marc Mamin <M.Mamin@intershop.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