Skip site navigation (1) Skip section navigation (2)

CLONE TABLE DATA TO <new_table>

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 (view raw or flat)
Thread:
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
	

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2012-01-13 11:09:42
Subject: Re: replay_location indicates incorrect location
Previous:From: Alexander BjörnhagenDate: 2012-01-13 10:30:40
Subject: Re: Standalone synchronous master

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group