How to get hostname in postgresql?

Question

Is there any way where I can get hostname/servername in postgresql like @@ServerName in SQL Server?

• Gaius: Use a variation on this technique to run the OS hostname command serverfault.com/a/901196/58037

Answer 1

You need to execute following query to get the hostname:

SELECT boot_val,reset_val
FROM pg_settings
WHERE name='listen_addresses';;

• Kapil Bhagchandani: I have corrected my answer to get the hostname info directly from postgresql. However it will only display what is configured in postgresql.conf. In order to get accurate hostname of server one must check hostname command on shell.
• user1822: Well, in my case that simply returns localhost ;)
• nate: This worked on my Windows 10 machine. Note I also used this link for help in order to connect pgadmin to the DB on my personal machine: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/…

Answer 2

I use such a solution. No plugins required. Temporary tables are not needed. Only for unix.

select pg_read_file('/etc/hostname') as hostname;

• user1822: This will only work for the database superuser.
• scravy: Since this only works for the superuser, if you're on AWS RDS, you only have an admin account which is not the superuser, so this does not work at all / on rds.
• Quandary: @user1822: Not just only for superusers, but also not on windows.

Answer 3

PostgreSQL doesn't provide a builtin function returning the server's hostname.

A pg_gethostname() function written in C (a wrapper to POSIX's gethostname()) has been contributed on the wiki and made available as an extension on PGXN:

https://wiki.postgresql.org/wiki/Pg_gethostname

https://pgxn.org/dist/hostname/

If you can't install that on the server, or an equivalent in another language supporting calls to the system such as plperlu or plpythonu, you're probably going to be limited to inet_server_addr().

Answer 4

I've solve this issue after creating a temp table and filling it with the result of copy from

-- creating a temporary table and only one column
# CREATE TEMP TABLE tt_cmd (hostname text);
CREATE TABLE
-- filling the column with the result of the command
# COPY tt_cmd FROM PROGRAM 'hostname';
COPY 1
-- checking the result
# SELECT * from tt_cmd ;
+-------------+
| hostname |
+-------------+
| db_hostname |
+-------------+
-- filling the result into a variable
# SELECT * FROM tt_cmd \gset
-- no return
-- checking the result
# \echo :hostname
db_hostname
-- dropping the temporary table
DROP TABLE tt_cmd ;
DROP TABLE
-- the variable is still here with the right result
# \echo :hostname
db_hostname

And I can use the result like this, to check, for example, if I'm running on a production server.

SELECT CASE :'hostname' WHEN 'prod_db_hostname' THEN 'PROD/' ELSE '' END AS sub_dir
\gset

After reading the comments, the answer is right if you're executing on Pg server. That's the case for me. Of course, the hostname result is where you're executing it. Maybe there's a way with HOST variable value using it in psql.

SELECT CASE WHEN :'HOST' ~* '^server_prod_hostname' THEN '/on_prod' ELSE '/offprod' END AS dir;

• Oleg Neumyvakin: \! hostname returns hostname where the psql is running.
• Arty: Your answer is a bit confusing, first you give the wrong advice \! hostname - return client hostname, and then you give the right answer. This command does what is needed - COPY tt_cmd (ret) FROM PROGRAM 'hostname';
• Pct Mtnxt: Sorry, I've check on a server, not a client. So, happy to know that the command is the right anwser.

Answer 5

You can get the host with :'HOST'

Here a full .psqlrc example to change prompt when connecting to a production database

WITH H AS (SELECT :'HOST' h) SELECT CASE WHEN h LIKE '%prod%' THEN 1 ELSE 0 END as is_prod FROM H \gset
\if :is_prod
\set PROMPT1 '%[%033[1m%] %n@%/@%[%033[48;5;093m%]%M%[%033[0m%]%R%[%033[0m%]%# '
\else
\set PROMPT1 '%[%033[1m%] %n@%/@%[%033[48;5;237m%]%M%[%033[0m%]%R%[%033[0m%]%# '
\endif