A bozo's blog: June 2020

Monday, June 22, 2020

MacOS: How to test netcat

Source: How To Use Netcat to Establish and Test TCP and UDP Connections on a VPS
Terminal #1:

[jerome@jeroboam] > nc -l 4444

Terminal #2:

[jerome@jeroboam] > nc localhost 4444
toto

Then terminal #1 will display toto as well.

Monday, June 15, 2020

PgBouncer: WARNING DNS lookup failed: host.docker.internal: result=0

If you're using PgBouncer in a Docker container and try to connect to the host via host.docker.internal, you may experience this error:

2020-06-15 17:23:46.211 UTC [1] WARNING DNS lookup failed: host.docker.internal: result=0

This fix resolves it: Fix DNS for Docker #8

Monday, June 1, 2020

PostgreSQL: plpgsql_check

Source: plpgsql_check
Cannot get it to compile from Github sources on macOS 10.15.5.
So, retrieve it from pgxn.
Then compile and install it.

[jerome@jeroboam] > make
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -undefined dynamic_lookup -I/usr/local/Cellar/postgresql@9.4/9.4.26/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -I. -I./ -I/usr/local/Cellar/postgresql@9.4/9.4.26/include/server -I/usr/local/Cellar/postgresql@9.4/9.4.26/include/internal -isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.15.sdk   -I/usr/local/opt/gettext/include -I/usr/local/opt/openldap/include -I/usr/local/opt/openssl@1.1/include -I/usr/local/opt/readline/include -I/usr/local/opt/tcl-tk/include  -c -o plpgsql_check.o plpgsql_check.c
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -undefined dynamic_lookup -I/usr/local/Cellar/postgresql@9.4/9.4.26/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -bundle -multiply_defined suppress -o plpgsql_check.so plpgsql_check.o -L/usr/local/Cellar/postgresql@9.4/9.4.26/lib    -L/usr/local/opt/gettext/lib -L/usr/local/opt/openldap/lib -L/usr/local/opt/openssl@1.1/lib -L/usr/local/opt/readline/lib -L/usr/local/opt/tcl-tk/lib -Wl,-dead_strip_dylibs   -bundle_loader /usr/local/Cellar/postgresql@9.4/9.4.26/bin/postgres
[jerome@jeroboam] > make install
/bin/sh /usr/local/Cellar/postgresql@9.4/9.4.26/lib/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql@9.4/9.4.26/lib'
/bin/sh /usr/local/Cellar/postgresql@9.4/9.4.26/lib/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql@9.4/9.4.26/share/extension'
/bin/sh /usr/local/Cellar/postgresql@9.4/9.4.26/lib/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql@9.4/9.4.26/share/extension'
/usr/bin/install -c -m 755  plpgsql_check.so '/usr/local/Cellar/postgresql@9.4/9.4.26/lib/plpgsql_check.so'
/usr/bin/install -c -m 644 plpgsql_check.control '/usr/local/Cellar/postgresql@9.4/9.4.26/share/extension/'
/usr/bin/install -c -m 644 plpgsql_check--1.0.sql '/usr/local/Cellar/postgresql@9.4/9.4.26/share/extension/'
[jerome@jeroboam] >

Then from psql:

crmmbqt=# CREATE EXTENSION plpgsql_check;
CREATE EXTENSION
Time: 317.716 ms

PostgreSQL: ERROR: could not access file "$libdir/plpython2": No such file or directory

After upgrading postgres from 9.4.18 to 9.4.26 on MacOS 10.15.5 with

[jerome@jeroboam] > brew upgrade 'postgresql@9.4'

I then discovered that plpython was gone.

[jerome@jeroboam] > make
touch sql/mbqt_post_api.out
../bin/replace_extension.pl sql/mbqt_post_api.sql
DBG> schema = mbqt_db, extension = mbqt_post_api
DBG> installed version = 1.21, new version = 1.21
CMD> cat sql/mbqt_post_api.sql > sql/mbqt_post_api--1.21.sql
CMD> /usr/bin/install -m 644 sql/mbqt_post_api--1.21.sql sql/mbqt_post_api.control /usr/local/Cellar/postgresql@9.4/9.4.26/share/postgresql@9.4/extension
CMD> psql -Upostgres -h127.0.0.1 -p5432 -dcrmmbqt -q<<END_OF_SQL
\set ON_ERROR_STOP ON
create schema if not exists mbqt_db authorization postgres;
drop extension if exists mbqt_post_api cascade;
create extension mbqt_post_api with schema mbqt_db;
END_OF_SQL
2020-06-01 14:12:47
NOTICE:  schema "mbqt_db" already exists, skipping
Time: 11.778 ms
NOTICE:  extension "mbqt_post_api" does not exist, skipping
Time: 12.900 ms
ERROR:  could not access file "$libdir/plpython2": No such file or directory

And indeed

[jerome@jeroboam] > ll -rt /usr/local/opt/postgresql\@9.4/lib/pl*
-r--r--r--  1 jerome  staff    42K Jun  1 12:41 /usr/local/opt/postgresql@9.4/lib/pltcl.so
-r--r--r--  1 jerome  staff   164K Jun  1 12:41 /usr/local/opt/postgresql@9.4/lib/plpgsql.so
-r--r--r--  1 jerome  staff    84K Jun  1 12:41 /usr/local/opt/postgresql@9.4/lib/plperl.so

I tried

[jerome@jeroboam] > brew reinstall postgresql@9.4 --with-python

But it failed with

Error: invalid option: --with-python

And indeed, as per Remove all options from Homebrew/homebrew-core formulae, they estimated that options in brew are bad from now.
Then I found out: petere / homebrew-postgresql.
So, I used

[jerome@jeroboam] > brew tap petere/postgresql

And

[jerome@jeroboam] > brew reinstall petere/postgresql/postgresql@9.4

which ended up with

configure: error: header file <perl.h> is required for Perl

If reporting this issue please do so at (not Homebrew/brew or Homebrew/core):
  https://github.com/petere/homebrew-postgresql/issues


Error: A newer Command Line Tools release is available.
Update them from Software Update in System Preferences or run:
  softwareupdate --all --install --force

If that doesn't show you an update run:
  sudo rm -rf /Library/Developer/CommandLineTools
  sudo xcode-select --install

Then let's go

[jerome@jeroboam] > sudo rm -rf /Library/Developer/CommandLineTools
[jerome@jeroboam] > sudo xcode-select --install

And finally

[jerome@jeroboam] > brew reinstall petere/postgresql/postgresql@9.4

which ended OK this time


==> Summary
🍺  /usr/local/Cellar/postgresql@9.4/9.4.26: 2,989 files, 41.0MB, built in 2 minutes 8 seconds

And guess what? plpython is back.

[jerome@jeroboam] > ll -rt /usr/local/opt/postgresql\@9.4/lib/pl*
-r--r--r--  1 jerome  staff    42K Jun  1 15:53 /usr/local/opt/postgresql@9.4/lib/pltcl.so
-r--r--r--  1 jerome  staff   108K Jun  1 15:53 /usr/local/opt/postgresql@9.4/lib/plpython2.so
-r--r--r--  1 jerome  staff   164K Jun  1 15:53 /usr/local/opt/postgresql@9.4/lib/plpgsql.so
-r--r--r--  1 jerome  staff    84K Jun  1 15:53 /usr/local/opt/postgresql@9.4/lib/plperl.so