Execute commands in single-user mode

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 fast

BTW, it's always good form to state the exact O/S and version of
PostgreSQL, regardless of whether you think it may apply.

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_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
andreas@visena.com
www.visena.com
<https://www.visena.com&gt;

--
*Melvin Davidson*
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 <mailto: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 startup
sleep 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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-postgres

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 startup
sleep 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
andreas@visena.com
www.visena.com
<https://www.visena.com&gt;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På søndag 10. januar 2016 kl. 16:53:54, skrev Tom Lane <tgl@sss.pgh.pa.us
<mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På søndag 10. januar 2016 kl. 22:38:05, skrev Jim Nasby <
Jim.Nasby@BlueTreble.com <mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

On 1/10/16 3:44 PM, Andreas Joseph Krogh wrote:

It might be about time to come up with an extension that's a replacement
for large objects.

What would it take to fund such an extension?

Time and/or money.

It would "have to" support:
- Migrate existing LOs away from pg_largeobject
- Proper driver-support (JDBC)

Would probably be possible to extend JDBC (looks like that's what the
current support does).

- Possible to exclude from pg_dump

That'd be up to the extension dump control semantics.

- Support pg_upgrade

And -hackers should agree on the goal to ultimately being merged into
core and replace pg_largeobject.

Well, there's a reason I suggested an extension. I think it's very
unlikely -hackers would want to add another LO format to the database.
Now-a-days, it's generally preferred to do most things as extensions,
and only incorporate things in the backend that really can't be done
with an extension.

If this theoretical new replacement for LOs took the world by storm and
everyone was using it, maybe it'd be a different thing. The xml and JSON
types are examples of that; they started life as add-ons and were
eventually pulled in because they became extremely popular.
--
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general