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! : )
