Re: GSoC proposal - "make an unlogged table logged"

On Mon, Mar 3, 2014 at 2:40 PM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>
> On 03/03/2014 05:22 PM, Tom Lane wrote:
> > Stephen Frost <sfrost(at)snowman(dot)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... :-)

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello