How to get hostname in postgresql?

Question

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

Answer 1

You need to execute following query to get the hostname:

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

Answer 2

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 3

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

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

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;