Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 1 · Lesson 2 · 5 min read

Install PostgreSQL 18 and DataGrip for SQL Server Users

On this page
  1. What you'll set up
  2. Step 1: Install PostgreSQL 18
  3. Step 2: postgresql.conf and pg_hba.conf
  4. Step 3: Create the course database
  5. Step 4: Install DataGrip
  6. Step 5: Connect DataGrip to PostgreSQL
  7. Common connection errors and fixes
  8. Check yourself

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:

Shell
brew install postgresql@18
brew services start postgresql@18

Linux. Use the PostgreSQL project's own package repository (PGDG), because distribution packages often lag behind. On Ubuntu, after adding the repository:

Shell
sudo apt install postgresql-18

On RHEL-family systems, initialize the data directory yourself after installing:

Shell
sudo dnf install postgresql18-server
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable --now postgresql-18

That 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:

Code
SHOW config_file;
SHOW hba_file;

postgresql.conf holds server settings. A few you'll meet again:

SettingWhat it doesClosest SQL Server setting
listen_addressesNetwork interfaces to accept connections onTCP/IP in Configuration Manager
portListening port, 5432 by defaultPort 1433
max_connectionsConnection limitUser connections
shared_buffersThe shared page cacheMax server memory, roughly
work_memMemory per sort or hash operationQuery 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:

Code
# 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-256

Use 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:

SQL
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.

  1. In the Database Explorer, click +, then Data Source, then PostgreSQL.
  2. If DataGrip shows "Download missing driver files", click it. It fetches the PostgreSQL JDBC driver, so you don't install anything else.
  3. Fill in Host localhost, Port 5432, User postgres, your password, and Database training_db.
  4. Click Test Connection and wait for the green check mark.
  5. 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:

Code
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 run SELECT pg_reload_conf();.
  • Connection refused The server isn't running, or isn't listening on that address. Check the service, and check listen_addresses when 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 with sudo -u postgres psql and set one with \password postgres.
  • Remote connections time out A firewall is blocking port 5432, or listen_addresses is still localhost.

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.