Thanks for your answer @andresrcs. I searched for an actual file named libpq.so.rh-postgresql12-5 (seems obvious today, thanks to your answer) and found it (the third entry):
$ sudo find / -name libpq.so*
/usr/lib64/libpq.so.5
/usr/lib64/libpq.so.5.5
/opt/rh/rh-postgresql12/root/usr/lib64/libpq.so.rh-postgresql12-5
/opt/rh/rh-postgresql12/root/usr/lib64/libpq.so.rh-postgresql12-5.12
/opt/rh/rh-postgresql12/root/usr/lib64/libpq.so
Also, I checked if any of my environment variables had that path defined:
$ set | grep /usr/lib64
LIBPQ_DIR=/opt/rh/rh-postgresql12/root/usr/lib64/
PKG_CONFIG_PATH=/opt/rh/rh-postgresql12/root/usr/lib64/pkgconfig
Obviously, I did that when reading documentation about troubleshooting RPostgres. Then it struck me and I exported the LIBPQ_DIR value to the LD_LIBRARY_PATH environment variable and it worked!
Solution:
export LD_LIBRARY_PATH=$LIBPQ_DIR
Problem solved!