Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 4 · Lesson 26 · 4 min read

Best PostgreSQL Extensions for SQL Server Users

On this page
  1. SQL Server features as PostgreSQL extensions
  2. Installing and managing extensions
  3. pgcrypto: hashing and encryption
  4. PostGIS: spatial done properly
  5. pg_cron: replacing SQL Agent jobs
  6. UUIDs no longer need an extension
  7. Check yourself

SQL Server features as PostgreSQL extensions

SQL Server ships one large engine where most features are built in, from spatial types to SQL Agent. PostgreSQL ships a smaller core and an extension mechanism: packages of types, functions, operators, index methods and even background workers that plug into the engine. Many SQL Server features you'll miss after migrating are one CREATE EXTENSION away.

You used in SQL ServerPostgreSQL extensionNotes
Query Storepg_stat_statementsNeeds shared_preload_libraries and a restart (Day 20)
geometry, geography typespostgisFar broader than SQL Server's spatial support
ENCRYPTBYKEY, HASHBYTESpgcryptoHashing and symmetric or PGP encryption
SQL Server Agent jobspg_cronSchedule SQL inside the database
Partition maintenance scriptspg_partmanCreates and drops partitions on a schedule
Linked serverspostgres_fdwQuery tables on another PostgreSQL server
Case-insensitive collationcitextCase-insensitive text type (Day 17 covers alternatives)
Index rebuild onlinepg_repackRemoves bloat without long locks (Day 18)

Installing and managing extensions

An extension is installed per database. Check what your server offers, install it, and see what's installed:

SQL
SELECT name, default_version FROM pg_available_extensions ORDER BY name;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT extname, extversion FROM pg_extension;

After upgrading PostgreSQL or an extension package, run ALTER EXTENSION pgcrypto UPDATE; to move the database's copy to the new version.

Some extensions hook deeper into the server and also need shared_preload_libraries in postgresql.conf plus a restart. pg_stat_statements, pg_cron and TimescaleDB are examples.

Managed services limit the list. Amazon RDS, Aurora, Azure Database for PostgreSQL and Cloud SQL each support a specific set of extensions, sometimes only after you allow-list them in a parameter. Check your target platform's list during migration planning, not after cutover. It can decide whether a feature you depend on is available at all.

pgcrypto: hashing and encryption

SQL
CREATE TABLE app_users (user_id int PRIMARY KEY, password_hash text);
INSERT INTO app_users VALUES (42, crypt('MyPassword1', gen_salt('bf')));   -- bcrypt hash

SELECT password_hash = crypt('MyPassword1', password_hash) AS password_ok
FROM app_users WHERE user_id = 42;                                         -- true

SELECT pgp_sym_decrypt(pgp_sym_encrypt('123-45-6789', 'demo-key'), 'demo-key');

crypt() with a bf salt gives bcrypt password hashes. Hash passwords in the application if you can, so plain-text passwords never travel to the database. pgp_sym_encrypt encrypts a value with a key. A key written into SQL, as above, ends up in logs and in pg_stat_statements, so in production pass it as a parameter from a secret store. For Always Encrypted-style protection, where the database never sees the key, encrypt in the application instead.

PostGIS: spatial done properly

If the source database uses geometry or geography columns, PostGIS is the destination. It adds geometry and geography types, hundreds of ST_ functions, many of which share names with SQL Server's methods, and GiST indexes for spatial queries:

SQL
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE stores (store_id int PRIMARY KEY, name text, location geography(Point, 4326));
CREATE INDEX ON stores USING gist (location);

SELECT name, round(ST_Distance(location, ST_MakePoint(-122.33, 47.60)::geography)) AS meters
FROM stores
ORDER BY location <-> ST_MakePoint(-122.33, 47.60)::geography
LIMIT 5;

The syntax differs: SQL Server calls methods on the value, as in location.STDistance(@p), while PostGIS uses functions, as in ST_Distance(location, p). Plan time to convert spatial queries.

pg_cron: replacing SQL Agent jobs

Jobs that ran T-SQL on a schedule can often move into pg_cron:

SQL
CREATE EXTENSION pg_cron;   -- also needs shared_preload_libraries = 'pg_cron'
SELECT cron.schedule('nightly-refresh', '0 3 * * *',
                     'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');
SELECT jobid, schedule, command FROM cron.job;

It uses ordinary cron syntax and runs the SQL in the database. Agent jobs that also run operating system commands, SSIS packages or multi-step workflows with branching belong in an external scheduler instead.

UUIDs no longer need an extension

Older guides install uuid-ossp for UUIDs. Today gen_random_uuid() (random, version 4) is built in, and PostgreSQL 18 adds uuidv7() (time-ordered). Keep uuid-ossp only for the name-based v3 and v5 algorithms.

In DataGrip, after installing an extension, refresh the data source so autocomplete learns the new functions and types.

Check yourself

That the service supports the PostGIS extension, and which version. Then plan to convert SQL Server's method syntax, like .STDistance(), to PostGIS functions such as ST_Distance().

pg_cron can schedule SQL inside the database, such as refreshes, cleanup and partition maintenance. Jobs that run OS commands or complex multi-step workflows need an external scheduler.

The extension's view exists, but the module that collects statistics must be loaded into shared memory at server start. Add pg_stat_statements to shared_preload_libraries in postgresql.conf, or in your managed service's parameter group, then restart the server. After that the view returns data.

Use crypt(password, gen_salt('bf')) to store a bcrypt hash, and check with crypt(input, stored_hash) = stored_hash. Even better, hash passwords in the application with a modern algorithm and never send plaintext passwords to the database at all.

Managed services only allow extensions from their approved list, and versions vary. If the application depends on PostGIS, pg_cron, pgvector or a specific pgcrypto feature, confirm the service supports it, at the version you need, before migrating.