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

Performing backups in Linux and PostgreSQL is a bit different than SQL Server on Windows but some aspects are certainly the same. One example i if you are able to stop the postgresql service on a data directory, you can just copy the whole directory as you can also restore the entire instance this way. That is not possible in Windows and SQL Server.  
Show how to perform a database backup to disk:
REF: SQL Dump
SQL Server

PostgreSQL

or to backup all databases, run the following:

NOTE: pg_dumpall has a few aspects that the DBA needs to keep in mind. It scripts out all the databases but does not guarantee that all the data is atomic. If the databases are large, it could take time to complete the backup and it simply scripts out each databases one by one and each table one by one without checking for referential consistency. It’s best ot run this when you can stop all transactions from happening. You also cannot restore selective databases this way as the restore runs the entire script.
  

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 – 214