I thought this was a good idea, but didn't hear back when I raised it before.
Failing to preserve access method is arguably a bug, reminiscent of CREATE
STATISTICS and 5564c1181. But maybe it's not important to backpatch a fix in
this case, since access methods are still evolving.
https://www.postgresql.org/message-id/20190818193533.GL11185@telsasoft.com
On Sun, Aug 18, 2019 at 02:35:33PM -0500, Justin Pryzby wrote:
> . What do you think about pg_restore --no-tableam; similar to
> --no-tablespaces, it would allow restoring a table to a different AM:
> PGOPTIONS='-c default_table_access_method=zedstore' pg_restore --no-tableam ./pg_dump.dat -d postgres
> Otherwise, the dump says "SET default_table_access_method=heap", which
> overrides any value from PGOPTIONS and precludes restoring to new AM.
...
> . it'd be nice if there was an ALTER TABLE SET ACCESS METHOD, to allow
> migrating data. Otherwise I think the alternative is:
> begin; lock t;
> CREATE TABLE new_t LIKE (t INCLUDING ALL) USING (zedstore);
> INSERT INTO new_t SELECT * FROM t;
> for index; do CREATE INDEX...; done
> DROP t; RENAME new_t (and all its indices). attach/inherit, etc.
> commit;
>
> . Speaking of which, I think LIKE needs a new option for ACCESS METHOD, which
> is otherwise lost.