Обсуждение: Execute commands in single-user mode
Hi all.
I'm planning to move all my pg_largeobject tables to separate tablespaces and to be able to do that I need to shuddown PG and start in single-user mode, like this:
postgres --single -O -D $PGDATA $DB_NAME
Then I have to execute the command:
alter table pg_largeobject set tablespace some_tablespace;
Is it possible to issue the above ALTER-statement from the command-line so I can start PG in single-user mode AND execute the ALTER-statement from the command-line i a bash-loop?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
Well, you can't combine psql with the postgres startup, but you can issue subsequent commands from bash with the -c option:
EG:postgres --single -O -D $PGDATA $DB_NAME
# give postgres a few seconds to complete startup
sleep 30
psql -U postgres -d your_database -c "alter table pg_largeobject set tablespace some_tablespace;"
pg_ctl stop -d $PGDATA -m fastpsql -U postgres -d your_database -c "alter table pg_largeobject set tablespace some_tablespace;"
On Sun, Jan 10, 2016 at 8:53 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
Hi all.I'm planning to move all my pg_largeobject tables to separate tablespaces and to be able to do that I need to shuddown PG and start in single-user mode, like this:postgres --single -O -D $PGDATA $DB_NAMEThen I have to execute the command:alter table pg_largeobject set tablespace some_tablespace;Is it possible to issue the above ALTER-statement from the command-line so I can start PG in single-user mode AND execute the ALTER-statement from the command-line i a bash-loop?Thanks.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Вложения
På søndag 10. januar 2016 kl. 15:52:12, skrev Melvin Davidson <melvin6925@gmail.com>:
Well, you can't combine psql with the postgres startup, but you can issue subsequent commands from bash with the -c option:EG:
postgres --single -O -D $PGDATA $DB_NAME# give postgres a few seconds to complete startupsleep 30
psql -U postgres -d your_database -c "alter table pg_largeobject set tablespace some_tablespace;"
pg_ctl stop -d $PGDATA -m fast
How can that work?
Starting in single-mode gives you a prompt, so there's no way to issue another command. Even if it was, PG is not accepting connections, so using psql won't work, unless I'm misstaken...
BTW, it's always good form to state the exact O/S and version of PostgreSQL, regardless of whether you think it may apply.
Yea, sorry, it's 9.5.0
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
Sorry, I've never used single user mode,
but here is a better example
#!/bin/bash
echo "******CHANGING TABLESPACES******"
gosu postgres postgres --single -O -D $PGDATA $DB_NAME<<- EOSQL
alter table pg_largeobject set tablespace some_tablespace;
EOSQL
pg_ctl stop -d $PGDATA -m fast
echo ""
echo "******TABLESPACES CHANGED******"
fyi, revised from http://stackoverflow.com/questions/28244869/creating-a-table-in-single-user-mode-in-postgresbut here is a better example
#!/bin/bash
echo "******CHANGING TABLESPACES******"
gosu postgres postgres --single -O -D $PGDATA $DB_NAME<<- EOSQL
alter table pg_largeobject set tablespace some_tablespace;
EOSQL
pg_ctl stop -d $PGDATA -m fast
echo ""
echo "******TABLESPACES CHANGED******"
On Sun, Jan 10, 2016 at 10:01 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På søndag 10. januar 2016 kl. 15:52:12, skrev Melvin Davidson <melvin6925@gmail.com>:Well, you can't combine psql with the postgres startup, but you can issue subsequent commands from bash with the -c option:EG:
postgres --single -O -D $PGDATA $DB_NAME# give postgres a few seconds to complete startupsleep 30
psql -U postgres -d your_database -c "alter table pg_largeobject set tablespace some_tablespace;"
pg_ctl stop -d $PGDATA -m fastHow can that work?Starting in single-mode gives you a prompt, so there's no way to issue another command. Even if it was, PG is not accepting connections, so using psql won't work, unless I'm misstaken...
BTW, it's always good form to state the exact O/S and version of PostgreSQL, regardless of whether you think it may apply.Yea, sorry, it's 9.5.0
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Вложения
På søndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> I'm planning to move all my pg_largeobject tables to separate tablespaces and
> to be able to do that I need to shuddown PG and start in single-user mode, like
> this:
> postgres --single -O -D $PGDATA $DB_NAME
> 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
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På søndag 10. januar 2016 kl. 16:53:54, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> P�� s��ndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
> Andreas Joseph Krogh <andreas@visena.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
Вложения
På søndag 10. januar 2016 kl. 22:38:05, skrev Jim Nasby <Jim.Nasby@BlueTreble.com>:
On 1/10/16 10:01 AM, Andreas Joseph Krogh wrote:
> 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.
BTW, there's some other issues with large objects, notably their use of
OIDs. Lots of LOs can lead to OID depletion. There was a thread about
this recently.
It might be about time to come up with an extension that's a replacement
for large objects.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
What would it take to fund such an extension?
It would "have to" support:
- Migrate existing LOs away from pg_largeobject
- Proper driver-support (JDBC)
- Possible to exclude from pg_dump
- Support pg_upgrade
And -hackers should agree on the goal to ultimately being merged into core and replace pg_largeobject.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963