Re: [GENERAL] psql color hostname prompt

[dabicho]

On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford
 wrote:
> On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte 
> wrote:
>>
>> Hi Cal:
>>
>> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand  wrote:
>> ...
>> > 2)  %M vs shell call
>> > %M on when connected to the local machine displays the string "[local]"
>> > which I didn't like.  I wanted a real hostname to show no matter which
>> > client/server pair I was using.  Zero chance for mistaken commands on
>> > the
>> > wrong host.  Many times we ssh to a remote server, then run psql
>> > locally.
>>
>>
>> That can be done with a named pipe ;->  ( or with an alias / function
>> using getopt to parse the options before forwarding them to psql ).
>> But, which just \sets $hostname in a var and uses it. )  Anyway, the
>> problem with this is that if you do \connect to another. You could do
>> something similar to this using only psql/psqlrc tricks:
>>
>> cdrs=> \set fecha `date`
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:23:22 CEST 2016
>>
>> Here you would use your script instead of fecha, and interpolate it
>> using %:fecha: in the prompt.
>>
>> And now the second step of the trick:
>> cdrs=> \set recalc '\\set fecha `date`'
>> cdrs=> \echo :recalc
>> \set fecha `date`
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:07 CEST 2016
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:16 CEST 2016
>>
>> Now you can use :recalc if you do connect to have the prompt updated.
>>
>> Anyway, TIMTOWTDI.
>>
>> > But again, I think the more elegant approach is to alter the %M logic.
>> > Any thoughts?
>>
>> At risk of being redundant, not altering %M, another %x better.
>>
>
> The various hacks appear to not deal with the fact that there may be
> multiple instances of postgresql running on different TCP ports or Unix
> connections nor with the fact that the local connection may, in fact, be a
> pooler and not a direct connection to the database.
>
> As long as we're into hack-land, I'll offer one.
>
> First, you abuse the custom variables feature in postgresql.conf and add
> something like:
> serverinfo.name = 'steve_test'
>
> Now you can read that info from any client:
> select current_setting('serverinfo.name');
> current_setting
> -
> steve_test
>
> Next you update .psqlrc with something along the lines of:
>
> select current_setting('serverid.name') as server_name;
> \gset
> \set PROMPT1 'You are connected to ' :server_name '/%/%R%# '
>
> Then when you start psql you will see:
>
>  server_name
> -
> steve_test
> (1 row)
>
> psql (9.4.7)
> Type "help" for help.
>
> You are connected to steve_test/steve=>
>
> On the plus side, the custom GUC setting is available to any client, not
> just psql. It also handles multiple PostgreSQL instances and connections
> that are actually routed through a pooler.
>
> On the down side, it is a hack. The method is not in any way guaranteed to
> be future-proof. It leaves an ugly bit of server output at psql startup. It
> requires ensuring that all servers have the variable set appropriately. You
> need to avoid colliding with a custom GUC used by an extension. But perhaps
> it is useful.
>
> -Steve
>
>
>
>

When you connect through ssh, aren't  a set of system environment variables set?
I think you could use them if available as a first cheap option.
(availability may depend on the ssh server/OS  or may not be
available if you change into another user environment after remote
login though).

I just checked, and from the info page for openssh
SSH_CONNECTIONIdentifies the client and server ends of the con‐
   nection.  The variable contains four space-sepa‐
   rated values: client IP address, client port num‐
   ber, server IP address, and server port number.

Just my 2 cents.

Sorry if the reply came in twice.


-- 
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] psql color hostname prompt

[dabicho]

On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford
 wrote:
> On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte 
> wrote:
>>
>> Hi Cal:
>>
>> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand  wrote:
>> ...
>> > 2)  %M vs shell call
>> > %M on when connected to the local machine displays the string "[local]"
>> > which I didn't like.  I wanted a real hostname to show no matter which
>> > client/server pair I was using.  Zero chance for mistaken commands on
>> > the
>> > wrong host.  Many times we ssh to a remote server, then run psql
>> > locally.
>>
>>
>> That can be done with a named pipe ;->  ( or with an alias / function
>> using getopt to parse the options before forwarding them to psql ).
>> But, which just \sets $hostname in a var and uses it. )  Anyway, the
>> problem with this is that if you do \connect to another. You could do
>> something similar to this using only psql/psqlrc tricks:
>>
>> cdrs=> \set fecha `date`
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:23:22 CEST 2016
>>
>> Here you would use your script instead of fecha, and interpolate it
>> using %:fecha: in the prompt.
>>
>> And now the second step of the trick:
>> cdrs=> \set recalc '\\set fecha `date`'
>> cdrs=> \echo :recalc
>> \set fecha `date`
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:07 CEST 2016
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:16 CEST 2016
>>
>> Now you can use :recalc if you do connect to have the prompt updated.
>>
>> Anyway, TIMTOWTDI.
>>
>> > But again, I think the more elegant approach is to alter the %M logic.
>> > Any thoughts?
>>
>> At risk of being redundant, not altering %M, another %x better.
>>
>
> The various hacks appear to not deal with the fact that there may be
> multiple instances of postgresql running on different TCP ports or Unix
> connections nor with the fact that the local connection may, in fact, be a
> pooler and not a direct connection to the database.
>
> As long as we're into hack-land, I'll offer one.
>
> First, you abuse the custom variables feature in postgresql.conf and add
> something like:
> serverinfo.name = 'steve_test'
>
> Now you can read that info from any client:
> select current_setting('serverinfo.name');
> current_setting
> -
> steve_test
>
> Next you update .psqlrc with something along the lines of:
>
> select current_setting('serverid.name') as server_name;
> \gset
> \set PROMPT1 'You are connected to ' :server_name '/%/%R%# '
>
> Then when you start psql you will see:
>
>  server_name
> -
> steve_test
> (1 row)
>
> psql (9.4.7)
> Type "help" for help.
>
> You are connected to steve_test/steve=>
>
> On the plus side, the custom GUC setting is available to any client, not
> just psql. It also handles multiple PostgreSQL instances and connections
> that are actually routed through a pooler.
>
> On the down side, it is a hack. The method is not in any way guaranteed to
> be future-proof. It leaves an ugly bit of server output at psql startup. It
> requires ensuring that all servers have the variable set appropriately. You
> need to avoid colliding with a custom GUC used by an extension. But perhaps
> it is useful.
>
> -Steve
>
>
>
>

When you connect through ssh, aren't  a set of system environment variables set?
I think you could use them if available as a first cheap option.
(those variables may depend on the ssh server/OS  or would not be
available if you change into another user environment after remote
login though).

I just checked, and from the info page for openssh
SSH_CONNECTIONIdentifies the client and server ends of the con‐
   nection.  The variable contains four space-sepa‐
   rated values: client IP address, client port num‐
   ber, server IP address, and server port number.

Just my 2 cents.


-- 
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] pg_xlog is getting bigger

[dabicho]

 I don't see anything obvious. Putting this back on the list, where
 it should have stayed all along. Maybe someone else has an idea;
 I've only seen such behavior when there were archiving problems
 which were showing up in the server log.

 -Kevin


 --
Perhaps it cannot delete old log files because some file system error?
Maybe permissions or a corrupted file somewhere?

[GENERAL] Question on Wal time lines

[dabicho]

For restoring a database from wal files, if I omit a target on the
recovery.conf file, can I make it so the database continues the time line
instead of starting one?
Or is there a tool to pick the most recent time line from a bunch of wal
files?

thankyou.

Re: [GENERAL] Question on Wal time lines

[dabicho]

El may 3, 2011 5:59 p.m., John R Pierce pie...@hogranch.com escribió:

 On 05/03/11 3:07 PM, dabicho wrote:


 For restoring a database from wal files, if I omit a target on the
recovery.conf file, can I make it so the database continues the time line
instead of starting one?
 Or is there a tool to pick the most recent time line from a bunch of wal
files?

 thankyou.


 you need to playback all the wal files from when you started the base
backup.  of course, you need that base backup, too.

I did that.
I restored the database, put the recovery file in place along with previous
wal files and the last wall files, and after start up there was a new time
line.
Am I missing something?
This is postgres 9.0




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