2

I'm currently running a OS X Lion Server system which ships with a built-in and not-upgradable PostgreSQL version. After years of usage I've finnaly decided to leave the built-in version and install an indipendent version. I disabled the built-in installation and downloaded the installer from EDB and followed the wizard. After many issues reguarding encoding and locales, I've finally managed how to setup a DB with no locale and UTF8 encoding. I issued the following command:

initdb -D /path/to/data --no-locale --encoding=UTF8

If I connect using pgAdminIII I get no problems. The command show client_encoding; displays UNICODE as the encoding used by pgAdminIII (the default installation gave me a SQL_ASCII encoding and that's why I run the initdb command).

The problem is that I'm not able to connect to PostgreSQL using psql. Whatever I pass to it, I get the following error:

psql: invalid connection option "client_encoding"

I've searched through the Internet but found nothing that solves my problem (for example issuing env PGCLIENTENCODING=UTF8 and adding client_encoding=UTF8 to postgresql.conf).

otool -L /Library/PostgreSQL/9.4/bin/psql returns:

/Library/PostgreSQL/9.4/bin/psql:
    @loader_path/../lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.7.0)
    @loader_path/../lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
    @loader_path/../lib/libedit.0.dylib (compatibility version 1.0.0, current version 1.48.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 169.3.0)

Anyone can help me to figure it out?

Many thanks to all Pietro

UPDATE

I forgot to logout and then login after editing the bash profile. The suggestion made by Daniel Vérité was right. I just edited the DYLD_LIBRARY_PATH env variable in /etc/profile in order to make it visible at a global level and not only from the interactive shell.

I added the following line to /etc/profile:

export DYLD_LIBRARY_PATH='/Library/PostgreSQL/9.4/lib'

I hope this helps others.

Daniel, Thank you a lot

| improve this question | |
  • It could be that you're running the pre-installed psql instead of the new one. Invoke commands with their full paths to be sure. – Daniel Vérité Nov 6 '15 at 13:55
  • @DanielVérité I cleaned up the original binaries in /usr/bin and updated the symlink to the new Postgres' binaries path, so I'm sure to invoke the 9.4.5 installation. – pietrop Nov 6 '15 at 14:10
  • So the next step would be to do do the same with libpq. Apparently the new psql links dynamically to the old libpq. In Unix this can be dealt with the help of LD_LIBRARY_PATH. Not sure about MacOS. – Daniel Vérité Nov 6 '15 at 14:17
  • 2
    First check if that's the problem. It looks that otool -L /path/to/psql would be able to tell. – Daniel Vérité Nov 6 '15 at 14:38
  • 2
    This error comes from libpq. As this option was added in 2011, it makes sense if you're using a newer psql with an older libpq. Setting DYLD_LIBRARY_PATH to /Library/PostgreSQL/9.4/lib could work, or if not google around for other tweaks to the same effect. – Daniel Vérité Nov 6 '15 at 16:20
2

client_encoding is a libpq connection option that was added circa 2011 and will not be recognized by older versions.

The error:

psql: invalid connection option "client_encoding"

comes from dynamically linking a newer psql with an older libpq, for instance the one that ships with the system.

On Mac OS X, this can be worked around by setting DYLD_LIBRARY_PATH (the equivalent of linux's LD_LIBRARY_PATH) to the path of the newer PostgreSQL installation.

Per comments and update, it's confirmed to work when setting this variable globally in /etc/profile

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.