PostgreSQL Manual Install on Windows 11

 I had the hardest time getting Postgres upgraded from 16 to 17.

Short of a bare metal reload of my Windows 11 playground, I deleted everything!! Files, registry entries and anything that whiffed of ebd and postgres. 

IT TOOK HOURS!!!!!!   

... days    D . :

Afterwards, I did try the installer and was super disappointed that it failed to work. ... and did the cleanup again. grr.

I knew that there might be another way to install the db and after some quick internet searches found this handy article...

https://www.geeksforgeeks.org/postgresql-installing-postgresql-without-admin-rights-on-windows/

Thanks for the help!   : )

Get the Binaries Download

I went to to

https://www.postgresql.org/download/

Clicked on Windows

https://www.postgresql.org/download/windows/

Found, and clicked on "zip archive"

https://www.enterprisedb.com/download-postgresql-binaries

Clicked on Win x86-64

Which put postgresql-17.4-2-windows-x64-binaries.zip in my download directory.

Or in Powershell...

# Set url and destination

$URL = 'https://sbp.enterprisedb.com/getfile.jsp?fileid=1259504'

$output = "d:\downloads\d:\downloads\postgresql-17.4-2-windows-x64-binaries.zip"

# transfer the file with BITS

Start-BitsTransfer -Source $url -Destination $output

Put the files in a useful place

I open the archive, and found the pgsql folder.

I copied the contents of the pgsql folder into my D:\Postgres directory

Or in Powershell...

# Define Zip to work with

$ZipPath = "d:\downloads\postgresql-17.4-2-windows-x64-binaries.zip"

# Define file path as a source or destination

$FilePath = D:\Postgres

# Expand entire zip to a path

Expand-Archive -Path $zipPath -DestinationPath $FilePath -Force

That only kinda does it. The resulting folder is d:\Postgres\pgsql. so bin, doc, lib, and the other dirs are in pgsql. I want them all one level up in d:\Postgres so I did it like this...

# expand a specific folder from a zip to a path

Add-Type -AssemblyName System.IO.Compression.FileSystem

$ZipPath = "d:\downloads\postgresql-17.4-2-windows-x64-binaries.zip"

$Subfolder = "pgsql/"

$FilePath = "D:\Postgres"

$Subfolder = "pgsql/"

$FilePath = "D:\Lesley\bin"

$zip = [System.IO.Compression.ZipFile]::OpenRead($ZipPath)

foreach ($entry in $zip.Entries) {

if ($entry.FullName.StartsWith($Subfolder)){

write-host $entry.FullName

$outPath = Join-Path $FilePath ($entry.FullName.Substring($Subfolder.Length + 1))

write-host ("   item: " + $outPath)

$outDir = Split-Path -Parent $outPath

write-host ("   out dir: " + $outDir )

if (-not (Test-Path -Path $outDir -PathType Container))

New-Item -Path $outDir -ItemType Directory -Force | Out-Null

write-host ("       + New Path: " + $outDir)

}

[System.IO.Compression.ZipFileExtensions]::ExtractToFile($entry, $outPath, $true)

}

}

The resulting dir structure looks like this...

d:\Postgres\

+ bin

+ doc

+ lib

+ ...

A simple drag and drop will also do it.   : )

Make a data directory

I'm picking d:\postgresql\data

new-item -path d:\postgresql -name data -itemtype directory

Make sure to have permissions on the directory tree.

While you can do this from file explorer (It's pretty easy), I (unfortunately) like to do thing from the command ine. It's a preference.   : )

In Powershell...

$FilePath = "D:\Postgresql"

$user = "NT AUTHORITY\NETWORK SERVICE"

$permissions = "FullControl"  # Options: FullControl, ReadAndExecute, Modify,

# Get the folder's current ACL

$acl = Get-Acl $FilePath 

# Create a new access rule

$accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, $permissions, "ContainerInherit,ObjectInherit", "None", "Allow")

# Add the access rule to the ACL

$acl.SetAccessRule($accessRule)

# Apply the updated ACL to the folder

Set-Acl -Path $FilePath -AclObject $acl

# Recursively apply the ACL to subfolders and files

Get-ChildItem -Path $folderPath -Recurse | ForEach-Object {

Set-Acl -Path $_.FullName -AclObject $acl

}

test...

Get-ChildItem -Path $Path -Directory -Recurse | ForEach-Object {

Get-DirectoryPermissionsForUser -DirectoryPath $_.FullName -TargetUser $UserName

$Acl = Get-Acl -Path $DirectoryPath

$AccessRules = $Acl.Access | Where-Object {$_.IdentityReference -like $TargetUser}

Write-Host ""

}

Set the Path

In Settings > System > About > Advanced system settings... open the System Properties dialog

In the System Properties dialog > Advanced tab > Environment Variables button... opens the Environment Variables dialog

In the Environment Variables > system variables, look for, and click on "Path" > Edit button... this open the Edit environment variable dialog. If the your postgres bin's path is missing from the list you may want to add it.  mine looks like this...

Click ok and restart your system when you are ready to do so.

In Powershell, similar to this...

# Get the current PATH : MACHINE

$currentPath = [System.Environment]::GetEnvironmentVariable("Path", [System.EnvironmentVariableTarget]::Machine)

# Define path value to add or remove

$PathValue = "D:\PostgreSQL\bin"  

# ADD a value

$newpath = "$currentPath;$PathValue"

## Apply change

# Set the new PATH : MACHINE

[System.Environment]::SetEnvironmentVariable("Path", $newPath, [System.EnvironmentVariableTarget]::Machine)

# Observe the change

[System.Environment]::GetEnvironmentVariable("Path", [System.EnvironmentVariableTarget]::Machine)

# Restart the computer

restart-computer

Verify the installation

[System.Environment]::GetEnvironmentVariable("Path", [System.EnvironmentVariableTarget]::Machine)

postgres -V

psql -V

Initialize the database

initdb -D D:\PostgreSQL\data -U postgres -E utf8

Afterwards, just to be careful: make a backup of the pg_hba.conf and postgresql.conf. I just copied mine like this...

pg_hba.conf => pg_hba.conf-0Init

postgresql.conf => postgresql.conf-0Init

This makes restarting and resetting changes easier. If restoring these files fails to reset your environment, you can delete the data directory and run the initdb again.

The "-U postgres" is typical, your initial account can be whatever you want. Just remember to know what it is.   : )

Start the server

pg_ctl -D D:\PostgreSQL\data -l logfile start

if you need to stop it for some reason, it's...

pg_ctl -D D:\PostgreSQL\data stop

Access the Database With pgAdmin

Start pgAdmin 

Add a New Server

General>Name: LocalPostgreSQL

Connaction>Host name/address: localhost

Save

LocalPostgreSQL should appear in your Object Explorer and after a few moments you may see graphing similar to the following...

[Optional] Set the postgres password

In Object Explorer  Servers > LocalPostgreSQL > Login/Group Roles > righ click postgres > Properties > definition > Password

Set your password and click Save

if you wanted to do this from the command line then it may look something like this...

psql -U postgres 

ALTER USER postgres WITH PASSWORD 'new_password';

postgres=# \q

Test the Password

To test the password locally you will have to modify the the pg_hba.conf to use passwords. By default the configuration trust local connections.

Modify pg_hba.conf

Find the "host all all trust" line and change it to "host all all md5"

Notice that I also added the local ip ranges to md5 as well. It may not be needed for your implementation.

Save the file.

Stop and restart the db server

pg_ctl -D D:\PostgreSQL\data stop

pg_ctl -D D:\PostgreSQL\data -l logfile start

Restart pgAdmin4 and open the db

Use that new password.   : )

Connection from Other Devices

If you want connectivity and accessibility from another device on your network, you'll have to make sure your firewall allows connections to the port (PostgreSQL defaults to 5432) and add lines to the postgresql.conf and pg_hba.conf to allow the connections. postgresql.conf allow general configuration while pg_hba.conf allows finer control.

Fix the Firewall.

Windows Start > Settings > Privacy & Security > Firewall & network protection > Advanced settings

New Rule

Type of rule: Port

Protocol and Ports: TCP

Specific local Ports: 5432

Action: Allow the connection

Profile: [all]

Name: PostgreSQL in port 5432

 

In Powershell...

New-NetFirewallRule -DisplayName "PostgreSQL in port 5432" -Direction Inbound -LocalPort 5432 -Protocol TCP -Action Allow

This is a little too loose for me, so tighten it up as you can.

Modify data\postgresql.conf to allow the db to listen for connections

listen_addresses = '*'

Notice that you can make this tighter as needed.

Modify the allowed connections in data\pg_hba.conf

host    all             all              192.168.1.0/24         md5

Most home networks only have the one, 192.168.1.0 network. Check yours and add as needed.

Go Test!   : D

You're Done!   : )

Powershell - Setting Up SSH on Windows

A friend was needing to transfer files and wanted to use SFTP.

These notes only slightly have notes for hardening the server from attacks, so for casual use, consider stopping the service when not needed.

 The short answer today,  is...

> add-windowscapability -online -name OpenSSH.Server~~~~0.0.1.0 

> get-service -name 'sshd' | set-service -startup 'Automatic'

> start-service -name sshd

As I never find the short answer sufficient, because I like to test and observe changes...

#

#   check ssh installed state

#

> get-windowscapability -online | where-object name -like 'openssh*'

Name  : OpenSSH.Client~~~~0.0.1.0

State : Installed

Name  : OpenSSH.Server~~~~0.0.1.0

State : NotPresent

#

#   install ssh server

#

> add-windowscapability -online -name OpenSSH.Server~~~~0.0.1.0

Path          :

Online        : True

RestartNeeded : False

#

#   check ssh installed state

#

> get-windowscapability -online | where-object name -like 'openssh*'

Name  : OpenSSH.Client~~~~0.0.1.0

State : Installed

Name  : OpenSSH.Server~~~~0.0.1.0

State : Installed

#

# check ssh service (server) state

#

> get-service -name 'sshd'

Status   Name               DisplayName

------   ----               -----------

Stopped  sshd               OpenSSH SSH Server

#

# set ssh server to start on boot

#

> get-service -name 'sshd' | set-service -startup 'Automatic'

#

# check ssh service state details

#

> get-service -name 'sshd' | select *

UserName            : LocalSystem

Description         : SSH protocol based service to provide secure encrypted communications between two untrusted

                      hosts over an insecure network.

DelayedAutoStart    : False

BinaryPathName      : C:\WINDOWS\System32\OpenSSH\sshd.exe

StartupType         : Automatic

Name                : sshd

RequiredServices    : {}

CanPauseAndContinue : False

CanShutdown         : False

CanStop             : False

DisplayName         : OpenSSH SSH Server

DependentServices   : {}

MachineName         : .

ServiceName         : sshd

ServicesDependedOn  : {}

StartType           : Automatic

ServiceHandle       : Microsoft.Win32.SafeHandles.SafeServiceHandle

Status              : Stopped

ServiceType         : Win32OwnProcess

Site                :

Container           :

#

# start ssh service

#

> start-service -name sshd

WARNING: Waiting for service 'OpenSSH SSH Server (sshd)' to start...

WARNING: Waiting for service 'OpenSSH SSH Server (sshd)' to start...

#

# check ssh service state details

#

> get-service -name 'sshd'

Status   Name               DisplayName

------   ----               -----------

Running  sshd               OpenSSH SSH Server

#

# test

# 

  

* Other odd notes to consider (look this stuff up as needed

*

*   Config and logs

*   default: C:\ProgramData\ssh\sshd_config

*

$sshconfig='C:\ProgramData\ssh\sshd_config'

(get-content $sshconfig).replace("#Port 22", "Port 10022") | set-content $sshconfig

restart-service 'sshd'

> get-nettcpconnection -localport 10022 | select localaddress, localport, state

localaddress localport  State

------------ ---------  -----

::               10022 Listen

0.0.0.0          10022 Listen

#

#   sshd_config settings to consider

#

DenyUsers

AllowUsers lesley

DenyGroups

AllowGroups LesleysToy4\Administrators, LesleysToy4\ssh_users

SyslogFacility Local0

LogLevel Debug3

#

#   stop the service

#

> stop-service 'sshd'

 

Powershell - A Simple Way to Create User Friendly Complex Passwords

Super simple.

I do a lot of password temporary resets. Some of the folks I work with prefer a default password, but I like to new passwords that translate easily when working with clients. 

So, at a minimum I like two words, a symbol, and a number. Something like: !TestPassword42

For my less savvy friends, I created the a code pile similar to the following password generator.


I'm hoping that the format is easy enough to follow. 

• List blocks to work with.
• Putting random list block elements together to create a password.

The list blocks are simple arrays, add, modify or delete elements as you need to each. I made a $Verbs, $Colors, $Nouns, and $Symbols for the basic parts. Then I also added a $Today and I demonstrate a number at the end.

This demonstration creates two outputs. 

1. Creates passwords from $Symbols, $Verbs, $Colors, $Nouns, and a number from 1 to 99.
2. Creates passwords from $Today, $Symbols, $Verbs, $Nouns, and a number from 1 to 99.

By this sample symbols, verbs, colors, nouns, and number you can support 9,022,860 a day.

Or, 3,293,343,900 a year.


You can get a copy of the code on GitHub.

Powershell - Create New Rocky OS Workstation VM

 Cent OS is dead. moving on to Rocky OS.

The initial work was in my blog post Powershell - Create New CentOS Workstation VM

I put the pile of code: https://github.com/LesleyPhillips/New-RockyLinux-20240307.ps1

This time around I'm working on Windows 11 Pro Hyper-V Manager

Here you can see me working on Postgres DB...

Powerball Statistics

 I had just finished learning R but was annoyed by it. So, in  learning to do Python, and running into so many Python Matplotlab examples, I decided to do it like that. I put the code blob here in github:https://github.com/LesleyPhillips/Powerball.py/blob/main/Powerball-20231004.py

Some takeaways... apparently there are functions for standard deviations and averaging. : )

Here are the results...

This code... 

• Gets it numbers from 'https://data.ny.gov/api/views/d6yy-54nr/rows.xml?accessType=DOWNLOAD' 
• It then loops through the XML data into white balls and red balls.
• Plots the white ball data
• Plots the red ball data.

You're done!   : )

How to Compile .vb Code

Ever needed to compile some .vb code? Here's how I did it today.

Given a text file called Helloworld.vb with the following content...

Imports System

Module HelloWorld

    Sub Main(args As String())

        Console.WriteLine("Hello World!")

    End Sub

End Module

You can create this file on any path you see fit with any editor Notepad, Notepad++, Atom, VSCode, pick one.  : ) 

For this example I'm using Visual Studio 2022, which look like this...

Visual Studio defaults to creating content in your %userprofile%\source\repos dir. On creating the VB Console project HelloWorld, it saved the source as %userprofile%\source\repos\HelloWorld\HelloWorld\HelloWorld.vb. 

Make sure the latest vbc.exe is on the path.

On My Windows 11 PC it existed in...

C:\Windows\Microsoft.NET\Framework64\v4.0.30319.

With Visual Studio Community Edition 2022, I found it located in C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\Roslyn.

Make sure to search and use the on you want to work with.

Start cmd

Issue commands to go to the directory with your source .vb file

>c:

>cd %userprofile%\source\repos\HelloWorld\HelloWorld

>dir

You directory may look like the following...

Visual Studio will also create other legacy elements for managing the code within its environment. For our purposes they are not needed and can be ignored.

Issue the follow commands

>vbc HelloWorld.vb

** Vbc (the older .Net version) will tell you that there is a better method to use called Roslyn which comes automatically with Visual Studio.   

 

If you are using the Roslyn vbc it will look like this...

 

 

>dir

There's our .exe file!   :)

Run HelloWorld

From the cmd prompt issue the command "helloworld"

It works!   : )

You now have a .exe you can use anywhere.

 

Sharepoint Redirect to Notes:// - Part II

 Back in Thursday, November 19, 2015, I showed a bit if html that can be used as a redirector for custom URL handlers. Since then Microsoft has hardened their products a bit and an extra step is needed.

Create the content as shown in Sharepoint Redirect to Notes://.

Save the file, with the content shown in the post, with the extension as .http

Test it by double click the file. IT should open a browser tab and automatically launch the application as expected.

Copy the .http file, then change the extension to .aspx.

You typically cannot test this until it's used from a Sharepoint site.

Save the file on your Sharepoint site.

Test this by clicking on the file you just saved, it should behave as the redirector and open your application.

Get the URL to the .aspx file.

Test this by trying the URL in the address bar of a new browser tab or window, it should behave as the redirector and open your application.

On the Sharepoint page, modify the link to use the working URL acquired in the last step.

Test this by lots of happy clicks!    : )

You're done!