pg-hostname on YugabyteDB compiled and installed directly on the server
Tags: yugabytedb, postgres, distributed, sql
Published: 2023-08-07 15:08:55

`pg-hostname` is a very simple extension, useful, especially on YugabyteDB that runs on multiple nodes. 

Without extensions, seeing on which host I am connected is limited:
```sql
yugabyte=# select inet_server_addr();
 inet_server_addr
------------------
 172.17.0.2
(1 row)

yugabyte=# show listen_addresses ;
 listen_addresses
------------------
 172.17.0.2
(1 row)
```
I have two problems with that. First, it is not easy to get the hostname from it. Second, if I start `yugabyted --advertise_address=0.0.0.0` to listen on all interfaces, I get no useful information:
```sql
yugabyte=# select inet_server_addr();
 inet_server_addr 
------------------
 127.0.0.1
(1 row)

yugabyte=# show listen_addresses ;
 listen_addresses 
------------------
 0.0.0.0
(1 row)
```

## Compile and install the extension on the YugabyteDB node

In this series I show many way to build a PostgreSQL extension. It is probably better to do tht in a container and deploy the files to the YugabyteDB nodes but here, I take an easy way: compile it directly on the YugabyteDB node. I still need to install `postgresql11-devel`. I'm running this on a YugabyteDB node that I've started with `docker run -it yugabytedb/yugabyte bash`

He is how I build the extension:
```sh

# PostgreSQL devel packages for PostgreSQL 11:
dnf update -y
dnf groupinstall -y 'Development Tools'
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf install epel-release -y redhat-rpm-config
dnf --enablerepo=powertools install perl-IPC-Run -y
dnf -qy module disable postgresql
dnf install -y postgresql11-devel
dnf clean all
export PATH="/usr/pgsql-11/bin:${PATH}"

# build pg-hostname extension
git clone https://github.com/theory/pg-hostname.git /var/tmp/pg-hostname
cd /var/tmp/pg-hostname
make

# install in YB_HOME instead of /usr/pgsql-11
make -n install | sed -e "s /usr/pgsql-11 $YB_HOME/postgres " | sh -x

```
The last command generated the installation commands (`make -n`) and changes the destination to be the YugabyteDB directory instead of the PostgreSQL one.

I can cleanup - not needed:
```sh
cd $YB_HOME
dnf remove -y postgresql11-devel
dnf remove perl-IPC-Run -y
dnf groupremove -y 'Development Tools'
dnf clean all
rm -rf /var/tmp/pg-hostname
```

## Test the extension

I start YugabyteDB:
```sh

[root@390b812d3aa0 pg-hostname]# python3 /usr/local/bin/yugabyted start

Starting yugabyted...
✅ YugabyteDB Started
✅ UI ready
✅ Data placement constraint successfully verified

⚠ WARNINGS:
- ntp/chrony package is missing for clock synchronization. For centos 7, we recommend installing either ntp or chrony package and for centos 8, we recommend installing chrony package.
- Cluster started in an insecure mode without authentication and encryption enabled. For non-production use only, not to be used without firewalls blocking the internet traffic.
Please review the 'Quick start for Linux' docs and rerun the start command: https://docs.yugabyte.com/preview/quick-start/linux/


+----------------------------------------------------------------------------------------------------------+
|                                                yugabyted                                                 |
+----------------------------------------------------------------------------------------------------------+
| Status              : Running.                                                                           |
| Replication Factor  : 1                                                                                  |
| YugabyteDB UI       : http://172.17.0.2:15433                                                            |
| JDBC                : jdbc:postgresql://172.17.0.2:5433/yugabyte?user=yugabyte&password=yugabyte         |
| YSQL                : bin/ysqlsh -h 172.17.0.2  -U yugabyte -d yugabyte                                  |
| YCQL                : bin/ycqlsh 172.17.0.2 9042 -u cassandra                                            |
| Data Dir            : /root/var/data                                                                     |
| Log Dir             : /root/var/logs                                                                     |
| Universe UUID       : ba7e7ffa-35a3-4f60-8e0b-055bcec3e644                                               |
+----------------------------------------------------------------------------------------------------------+
🚀 YugabyteDB started successfully! To load a sample dataset, try 'yugabyted demo'.
🎉 Join us on Slack at https://www.yugabyte.com/slack
👕 Claim your free t-shirt at https://www.yugabyte.com/community-rewards/

[root@390b812d3aa0 pg-hostname]#
```

I test the extension:
```sql

[root@390b812d3aa0 pg-hostname]# ysqlsh -h $(hostname)

ysqlsh (11.2-YB-2.19.0.0-b0)
Type "help" for help.

yugabyte=# create extension hostname;
CREATE EXTENSION


yugabyte=# select hostname();

   hostname
--------------
 390b812d3aa0
(1 row)

yugabyte=#
```

This is really simple. Note that this is limited to extensions that do not have strong dependencies on GLIBC version because I've compiled with the OS version but run it with the YugabyteDB version of GLIBC. 

The commands above should run for many simple extensions that are build with `make` and  installed with `make install`. 

Note that compiling on a production server may not be a good idea. You can do it on an ephemeral container and copy the files (those displayed my `make -n`) to all nodes. But to test an extension in a lab, that's really simple.