On Mon, Mar 3, 2014 at 2:40 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote: > > On 03/03/2014 05:22 PM, Tom Lane wrote: > > Stephen Frost <sfrost@snowman.net> writes: > ... > >> ISTR the discussion going something along the lines of "we'd have to WAL > >> log the entire table to do that, and if we have to do that, what's the > >> point?". > > IIRC, the reason you'd have to do that is to make the table contents > > appear on slave servers. If you don't consider replication then it might > > seem easier. > So switch on logging and then perform CLUSTER/VACUUM FULL ? > > Should this work, or is something extra needed ? >
Today I do something like that:
1) create unlogged table tmp_foo ...
2) populate 'tmp_foo' table (ETL scripts or whatever)
3) start transaction
4) lock table tmp_foo in access exclusive mode
5) update pg_class set relpersistence = 'p' where oid = 'tmp_foo':regclass
6) drop table foo; -- the old foo table
7) alter table tmp_foo rename to foo;
8) end transaction
9) run pg_repack in table 'foo'
I know it's very ugly, but works... and works for standbys too... :-)
