Re: Execute commands in single-user mode

På søndag 10. januar 2016 kl. 16:53:54, skrev Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>:
Andreas Joseph Krogh <andreas(at)visena(dot)com> writes:
> P�� s��ndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>:
> Andreas Joseph Krogh <andreas(at)visena(dot)com> writes:
>>> Then I have to execute the command:
>>> alter table pg_largeobject set tablespace some_tablespace;

>  Why do you think you need single-user mode for that?

> Because of this?
> ERROR:  permission denied: "pg_largeobject" is a system catalog

What that means is that you need allow_system_table_mods to be set.
It's a postmaster-start-time GUC, but still just a GUC.

So, assuming that you want to use psql to enter your commands,
you'd do something like this:

1. Add allow_system_table_mods=on to postgresql.conf.
2. Restart postmaster.
3. Issue ALTER TABLE commands.
4. Remove allow_system_table_mods setting from postgresql.conf.
5. Restart postmaster.

Prudence would suggest that you also alter pg_hba.conf to prevent
anyone but yourself from connecting to the postmaster while it's
in this state.  But that's optional.

regards, tom lane
 
Aha, thanks!
 
pg_largeobject being a system-relation does quite make sense to me, but that's
another discussion. I know there has been some discussions in the past about
making it a non system-relation but it never got anywhere AFAIK.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>