(o_0 ): May 2025

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