Re: [GENERAL] Execute commands in single-user mode

[Jim Nasby]

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

[GENERAL] Execute commands in single-user mode

[Andreas Joseph Krogh]

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
andr...@visena.com 
www.visena.com 
 

Re: [GENERAL] Execute commands in single-user mode

[Andreas Joseph Krogh]

På søndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane >:
Andreas Joseph Krogh  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
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Execute commands in single-user mode

[Melvin Davidson]

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 
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
> andr...@visena.com
> www.visena.com
> 
>



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

Re: [GENERAL] Execute commands in single-user mode

[Melvin Davidson]

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 
wrote:

> På søndag 10. januar 2016 kl. 15:52:12, skrev Melvin Davidson <
> melvin6...@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
> andr...@visena.com
> www.visena.com
> 
>
>



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

Re: [GENERAL] Execute commands in single-user mode

[Tom Lane]

Andreas Joseph Krogh  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

Re: [GENERAL] Execute commands in single-user mode

[Andreas Joseph Krogh]

På søndag 10. januar 2016 kl. 15:52:12, skrev Melvin Davidson <
melvin6...@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
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Execute commands in single-user mode

[Tom Lane]

Andreas Joseph Krogh  writes:
> På søndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane  >:
> Andreas Joseph Krogh  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

Re: [GENERAL] Execute commands in single-user mode

[Andreas Joseph Krogh]

På søndag 10. januar 2016 kl. 16:53:54, skrev Tom Lane >:
Andreas Joseph Krogh  writes:
 > P�� s��ndag 10. januar 2016 kl. 16:40:23, skrev Tom Lane  >:
 > Andreas Joseph Krogh  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
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Execute commands in single-user mode

[Jim Nasby]

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

Re: [GENERAL] Execute commands in single-user mode

[Andreas Joseph Krogh]

På søndag 10. januar 2016 kl. 22:38:05, skrev Jim Nasby <
jim.na...@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
andr...@visena.com 
www.visena.com