Install PostgreSQL 18 and DataGrip for SQL Server Users
On this page
What you'll set up
A PostgreSQL 18 server running on your machine, a training_db database, and DataGrip connected to it. DataGrip plays the part SSMS plays for you today: object explorer, query editor and execution plans in one tool.
Step 1: Install PostgreSQL 18
Windows. Download the installer linked from postgresql.org/download/windows. It asks for an install folder, a data folder, a password for the postgres superuser and a port (keep 5432). PostgreSQL then runs as a Windows service, named something like postgresql-x64-18, that you can manage from services.msc. You can skip Stack Builder at the end.
macOS. Use Homebrew:
brew install postgresql@18
brew services start postgresql@18Linux. Use the PostgreSQL project's own package repository (PGDG), because distribution packages often lag behind. On Ubuntu, after adding the repository:
sudo apt install postgresql-18On RHEL-family systems, initialize the data directory yourself after installing:
sudo dnf install postgresql18-server
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable --now postgresql-18That initdb step is the PostgreSQL equivalent of creating a new SQL Server instance. It builds the data directory, the cluster from Day 1, with three starter databases: postgres (a default to connect to) and template0 and template1 (templates for new databases). Most installers run it for you.
Step 2: postgresql.conf and pg_hba.conf
SQL Server spreads server settings across Configuration Manager, sp_configure and SSMS dialogs. PostgreSQL keeps them in two text files. Ask the server where they are, because the location differs by platform:
SHOW config_file;
SHOW hba_file;postgresql.conf holds server settings. A few you'll meet again:
| Setting | What it does | Closest SQL Server setting |
|---|---|---|
listen_addresses | Network interfaces to accept connections on | TCP/IP in Configuration Manager |
port | Listening port, 5432 by default | Port 1433 |
max_connections | Connection limit | User connections |
shared_buffers | The shared page cache | Max server memory, roughly |
work_mem | Memory per sort or hash operation | Query memory grants, roughly |
Most settings take effect after SELECT pg_reload_conf();. A few, like shared_buffers, need a restart.
pg_hba.conf has no SQL Server equivalent, and it's behind most "can't connect" problems. It decides who may connect, from which addresses, to which database, and how they must authenticate. Each line is a rule, and the first match wins:
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host training_db app_user 192.168.1.0/24 scram-sha-256Use scram-sha-256 for passwords. trust means no password at all, which is never acceptable on a reachable server. After editing the file, reload the configuration.
Step 3: Create the course database
Connect with psql, PostgreSQL's command-line client (on Linux, sudo -u postgres psql), and create the database:
CREATE DATABASE training_db;Step 4: Install DataGrip
DataGrip is JetBrains' database IDE. It works with PostgreSQL and SQL Server alike, which helps during a migration. Licensing: it's free for non-commercial use such as learning, and commercial work needs a paid license. Download it from the JetBrains site or install it with the JetBrains Toolbox app, then sign in with a JetBrains account on first launch.
Step 5: Connect DataGrip to PostgreSQL
In DataGrip, a saved connection is called a data source, like a registered server in SSMS.
- In the Database Explorer, click +, then Data Source, then PostgreSQL.
- If DataGrip shows "Download missing driver files", click it. It fetches the PostgreSQL JDBC driver, so you don't install anything else.
- Fill in Host
localhost, Port5432, Userpostgres, your password, and Databasetraining_db. - Click Test Connection and wait for the green check mark.
- Click OK. The data source appears in the explorer, like a server in SSMS Object Explorer.
If a schema or database doesn't show up in the tree, that's a filter, not a missing object. DataGrip only introspects what you select under the data source's schema settings, which keeps big servers fast.
For servers you can't reach directly, the SSH/SSL tab sets up an SSH tunnel through a jump host, or TLS encryption.
Open a query console and run:
SELECT version();You should see PostgreSQL 18.x. You're ready for the rest of the course.
Common connection errors and fixes
no pg_hba.conf entry for host ... user ... database ...No rule in pg_hba.conf matches your connection. Add a line for your address and user, then runSELECT pg_reload_conf();.Connection refusedThe server isn't running, or isn't listening on that address. Check the service, and checklisten_addresseswhen connecting from another machine. A wrong password gives a different error.password authentication failed for user "postgres"The password is wrong, or the role has no password set. On Linux, connect locally withsudo -u postgres psqland set one with\password postgres.- Remote connections time out A firewall is blocking port 5432, or
listen_addressesis stilllocalhost.
Check yourself
Add a pg_hba.conf line allowing app_user from that address to training_db, with scram-sha-256, then reload the configuration. The server is running fine. It just has no rule for that connection.
In postgresql.conf, or with ALTER SYSTEM SET shared_buffers = '4GB', which writes to postgresql.auto.conf. Unlike SQL Server's max server memory, shared_buffers is only PostgreSQL's own cache, and the operating system's file cache does much of the rest. Memory settings like it need a restart; most others need only SELECT pg_reload_conf();.
MD5-based password authentication is weak and is being phased out, with deprecation warnings in PostgreSQL 18. scram-sha-256 never sends the password or a reusable hash over the wire and resists replay. Make sure clients and drivers support it, which every current driver, including Npgsql, does.
DataGrip only introspects the schemas you've selected for that data source, so new schemas can stay hidden. Open the data source properties, or the schema list in the Database Explorer, and tick the schemas you need, then refresh.
No. It hides permission problems until production and gives the app far more power than it needs. Create a role that owns the course database for migrations, and a separate application role with only the privileges it needs, and use those from the start.