Thursday, June 22, 2017

PostgreSQL on Windows: psql complains "no equivalent in encoding"

I am running a PostgreSQL 9.6 server instance on Windows 10 host. When I issue a query via psql on the Windows 10 host, I encounter the following error message,

ERROR:  character with byte sequence 0xd0 0x9c in encoding "UTF8" 
has no equivalent in encoding "WIN1252"

When I query the client_encoding, I get the encoding indeed as WIN1252, shown as follows,

mydb=> show client_encoding;
 client_encoding
-----------------
 WIN1252
(1 row)

I find that the issue disappears and the display of query results appears to be fine. The following is an example to set client_encoding,

mydb=> SET client_encoding = 'UTF8';
SET

Sunday, June 18, 2017

Windows Defender Interferes with PostgreSQL on Windows 10

I am running a PostgreSQL database server on a Windows 10 host for development. I noticed that Windows Defender sometimes interferes with PostgreSQL and results the PostgreSQL services being terminated.

More specifically,  when you have a database transaction running and the Windows Defender starts to scan, the Windows Defender would label PostgreSQL transaction log as a threat as illustrated in the screenshot captured below. It shows that Windows Defender labels a PostgreSQL transaction log as a "Exploit:HTML/IframeRef", which is clearly a false alarm. Windows Defender would next quarantines the threat and the transaction log becomes inaccessible to the PostgreSQL service.


If you examine services in the Windows 10 host, you will see that the PostgreSQL service is terminated as illustrated in the screenshot below. At this time, any query to the PostgreSQL database results in failure.


The solution is to exclude the PostgreSQL transaction log directory from Windows Defender's scan. Microsoft explains how this can be done in this page.

Tuesday, June 13, 2017

PosgreSQL on Windows: could not find a "psql" to execute

I installed PostgreSQL 9.6.3 on a Windows host. After I had run batch file pg_env.bat on the host, I encountered the following error message when I ran psql

could not find a "psql" to execute
psql: could not find own program executable

It turns out that the quotation marks for path of the PostgreSQL binary from the batch file pg_env.bat is the culprit. For instance, the pg_env.bat batch file on my Windows host has the following content,

@ECHO OFF
REM The script sets environment variables helpful for PostgreSQL

@SET PATH="C:\Program Files\PostgreSQL\9.6\bin";%PATH%
@SET PGDATA=L:\stackoverflow\Data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5432
@SET PGLOCALEDIR=C:\Program Files\PostgreSQL\9.6\share\locale

To correct the problem, we need to remove the quotation marks from the "SET PATH" line, i.e., to change the file to the following,

@ECHO OFF
REM The script sets environment variables helpful for PostgreSQL

@SET PATH=C:\Program Files\PostgreSQL\9.6\bin;%PATH%
@SET PGDATA=L:\stackoverflow\Data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5432
@SET PGLOCALEDIR=C:\Program Files\PostgreSQL\9.6\share\locale