Monday, July 17, 2017

Listing Queries Runing in PostgreSQL DBMS

To see which queries are being executed by a user in PostgreSQL DBMS, one may use the following query,


SELECT 
       usename, application_name, state, query 
FROM
       pg_stat_activity 
WHERE 
       usename='my_user_name';
where my_user_name should be actual username being queried about.

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

Sunday, May 7, 2017

Mounting Logical Volume Management (LVM) Volumes

I have two hard drives from an old Linux machine. These hard drives were under one Logical Volume Management volume. On another Linux host, I found these tools were very useful to get the data out.

  • Scan LVM volumes

    sudo lvscan

  • Activate LVM volumes

    modprobe dm-mod
    vgchange -ay

  • List and remove mapped LVM devices

    dmsetup ls
    dmsetup remove <device>

    where an example of "device" can be VolGroup00-LogVol01

Troubleshooting

When you see an error message like the following when you try to mount a volume,


# mount /dev/VolGroup00/LogVol00 mnt/
  mount: /dev/mapper/VolGroup00-LogVol00 is write-protected, mounting read-only
  mount: /dev/mapper/VolGroup00-LogVol00: can't read superblock

One solution that has worked for me many times is to remove the mapped LVM devices using the command we discussed in the above. For instance,

# dmsetup remove VolGroup00-LogVol00

If you perform a lvscan, you will find that the volume is inactive,

# lvscan
  inactive          '/dev/VolGroup00/LogVol00' [459.53 GiB] inherit

To activate the volume, we can run the following

vgchange -ay

Now you shall see that the volume is active,

# lvscan
  ACTIVE            '/dev/VolGroup00/LogVol00' [459.53 GiB] inherit

We can now mount the volume,

# mount /dev/VolGroup00/LogVol00 mnt/


Thursday, May 4, 2017

Windows Explorer Appears to Get Stuck at Canceling Copying Files

When I tried to cancel copying a large set of files at a Windows 10 system, it appears that Windows got stuck at canceling it forever. Below I saw,

which shows this "Canceling - 10% complete" indicator forever even after I closed the window. When I tried to copy a different file, this indicator stayed. This is actually not that the Windows tried to cancel copying forever. It is instead that Windows Explorer somehow kept some internal canceling state.

At present, the solution to address this are a few. The following two appear to work.
  • Reboot the system. This is a heavy-handed solution.
  • Terminate the Windows Explorer process, and restart the Windows Explorer process.

Monday, May 1, 2017

Finding Encrypted Files or Folders on Windows NTFS Partitions

Windows NTFS supports Windows Encrypting File System (EFS),which means some files or folders can be encrypted. In more than one occasion, I have lost access to files or folders while I copied them to external hard drives. Perhaps, one strategy is to list files or folders that are encrypted, and we can then decide what to do with them. This can be easily achieved on Windows Command Prompt via the following command,

cipher /S:D:\ /H | findstr "^E"

In the above example, we are looking for encrypted files on drive D:\. Command cipher lists all files and folders, and indicates whether the files or the folders are encrypted. The switch /H is to instruct cipher to look for hidden or system files as well. Command findstr allows us to use Regular Expression, and In "^E", "^" indicates the beginning of a line and "E" indicates that the file or the folder is encrypted.