PostgreSQL vs. SQL Server Notes

This blog will be where I post my PostgreSQL administration learning and it’s comparison to SQL Server from a SQL Server DBA’s viewpoint. When working as a SQL DBA for almost 2 decades in various roles learning a new database engine can be daunting. How do I remember all the commands and internals of the new system such that I do not mix them up with SQL Server? This post will serve to show the equivalent concepts, commands and features between the two engines. The references in this post are for version PostgreSQL 9.6 and SQL Server 2012. References to features for newer version will be explicitly stated.
  

Running Tasks

Show the running, runnable and sleeping tasks in a database engine, we run the following:
SQL Server

PostgreSQL

  

KILL PID or SPID

How to kill a PID (PostgreSQL) or SPID (SQL):
SQL Server

PostgreSQL

  

Instance Configuration

Show the current server settings:
SQL Server

PostgreSQL

  

Blocking\Deadlocking

Show requests that are blocked or deadlocked and show the lead blocker
SQL Server

PostgreSQL

  

Database Size

show the current size for each database (formatted):
SQL Server

PostgreSQL

  

Check for Database Corruption

Check the database for consistency in it’s data and indexes etc.:
REF: Corruption Detection and Containment
REF: Checking for corruption
REF: Using pg_visibility
SQL Server

PostgreSQL

  

Updating Statistics

Show how to update statistics for slow queries
SQL Server

PostgreSQL

  

Backup Database

Show how to perform a database backup to disk:
REF: Continuous Archiving and Point-in-Time Recovery (PITR)
SQL Server

PostgreSQL

  

Restore Database

Show how to perform a database restore from disk:
SQL Server

PostgreSQL

  

— References
Execution Plans Explained

Postgres Log Monitoring 101: Deadlocks, Checkpoint Tuning & Blocked Queries

Guideline Settings for a New PostgreSQL Server

Analyze memory usage of PostgreSQL – why is it growing constantly?

How to detect query which holds the lock in Postgres?

Why is PostgreSQL Running Slow? Tips & Tricks to Get to the Source

PostgreSQL Monitoring Cheatsheet

PostgreSQL Performance Tuning Tutorial – DEVELOPER TIPS, TRICKS & RESOURCES

Key Things to Monitor in PostgreSQL – Analyzing Your Workload

Views – 44