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

EXEC sp_who2;
-- or
EXEC sp_WhoIsActive;

EXEC sp_who2;

-- or

EXEC sp_WhoIsActive;

PostgreSQL

SELECT pid, usesysid, usename, client_addr, client_port, datname, state, query_start, xact_start, wait_event_type, query, application_name
  FROM pg_stat_activity
  ORDER BY state;

SELECT pid, usesysid, usename, client_addr, client_port, datname, state, query_start, xact_start, wait_event_type, query, application_name

FROM pg_stat_activity

ORDER BY state;

KILL PID or SPID

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

KILL SPID; -- replace "SPID" with actual SPID ID

1	KILL SPID; -- replace "SPID" with actual SPID ID

PostgreSQL

SELECT pg_cancel_backend(PID); --The graceful, but possibly slow way
SELECT pg_terminate_backend(PID); --The FAST way; Same as KILL [SPID] in SQL Server

1 2	SELECT pg_cancel_backend(PID); --The graceful, but possibly slow way SELECT pg_terminate_backend(PID); --The FAST way; Same as KILL [SPID] in SQL Server

Instance Configuration

Show the current server settings:
SQL Server

EXEC sp_configure('Show advanced options', 1);
RECONFIGURE;
GO

EXEC sp_configure;

EXEC sp_configure('Show advanced options', 1);

RECONFIGURE;

EXEC sp_configure;

PostgreSQL

SHOW ALL;

SHOW ALL;

Enabling Configuration Changes

Both SQL Server and PostgreSQL handle configuration changes by either implementing the change immediately, without a service restart, or the change requires the service be restarted before the change can be in effect, thus making the instance temporarily unavailable. For SQL Server the change either becomes immediately effective as a result of simply saving the change, either through a GUI, or by executing the following:
SQL Server

--  An example: enable the SQL Agent XPs so Agent can run
EXEC sp_configure('Agent XPs', 1);
RECONFIGURE;
GO

-- An example: enable the SQL Agent XPs so Agent can run

EXEC sp_configure('Agent XPs', 1);

RECONFIGURE;

PostgreSQL
For PostgreSQL, the keyword to use with pg_ctl is ‘reload’ versus ‘restart’. Reload allows for a configuration change without breaking connections or restarting the service. The keyword ‘restart’ is a full restart of the PostgreSQL engine service and will break connections and take the service temporarily offline while it restarts. An example of how to execute both at the Linux command-line are shown below:

Blocking\Deadlocking

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

SELECT spid,sp.STATUS, RTRIM(loginame) AS 'loginname', RTRIM(hostname) AS 'hostname', blk = CONVERT(CHAR(3), blocked),
				open_tran, DB_NAME(sp.dbid) AS 'dbname', cmd, waittype, waittime, last_batch,
				SQLStatement = SUBSTRING(qt.text, er.statement_start_offset / 2, 
					(
						CASE
							WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
							ELSE er.statement_end_offset
						END - er.statement_start_offset) / 2
					)
	FROM master.dbo.sysprocesses sp
	LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid
	OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
	WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
		AND blocked = 0

SELECT spid,sp.STATUS, RTRIM(loginame) AS 'loginname', RTRIM(hostname) AS 'hostname', blk = CONVERT(CHAR(3), blocked),

open_tran, DB_NAME(sp.dbid) AS 'dbname', cmd, waittype, waittime, last_batch,

SQLStatement = SUBSTRING(qt.text, er.statement_start_offset / 2,

(

CASE

WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE er.statement_end_offset

END - er.statement_start_offset) / 2

)

FROM master.dbo.sysprocesses sp

LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND blocked = 0

PostgreSQL

SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query AS blocked_query
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query AS blocked_query

FROM pg_stat_activity

WHERE cardinality(pg_blocking_pids(pid)) > 0;

Database Size

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

SELECT	sdb.name AS 'Database Name',
				(SELECT REPLACE(CONVERT(VARCHAR(20), CAST((smf.size * 8) / 1024 AS MONEY), 1), '.00', '')
						FROM sys.master_files smf
						WHERE d.database_id = smf.database_id
							AND smf.type_desc = 'ROWS'
							AND smf.file_id = 1) AS 'File Size in MB',
				(SELECT REPLACE(CONVERT(VARCHAR(20), CAST((smf.size * 8) / 1024 AS MONEY), 1), '.00', '')
						FROM sys.master_files smf
						WHERE d.database_id = smf.database_id
							AND smf.type_desc = 'LOG'
							AND smf.file_id = 2) AS 'Log Size in MB'
	FROM master.dbo.sysdatabases sdb
	INNER JOIN sys.databases d ON d.database_id = sdb.dbid
	INNER JOIN sys.master_files smf ON smf.database_id = d.database_id
	LEFT OUTER JOIN msdb.dbo.backupset bs ON sdb.name = bs.database_name AND bs.type = 'D'
	LEFT OUTER JOIN msdb.dbo.log_shipping_primary_databases lspd ON lspd.primary_database = sdb.name
	LEFT OUTER JOIN msdb.dbo.log_shipping_secondary_databases lssd ON lssd.secondary_database = sdb.name
	WHERE smf.file_id = 1
	GROUP BY	sdb.name, lspd.primary_database, d.compatibility_level, lspd.last_backup_date, 
						lssd.last_restored_date, lssd.secondary_database, smf.size, d.database_id
	ORDER BY 1

SELECT sdb.name AS 'Database Name',

(SELECT REPLACE(CONVERT(VARCHAR(20), CAST((smf.size * 8) / 1024 AS MONEY), 1), '.00', '')

FROM sys.master_files smf

WHERE d.database_id = smf.database_id

AND smf.type_desc = 'ROWS'

AND smf.file_id = 1) AS 'File Size in MB',

(SELECT REPLACE(CONVERT(VARCHAR(20), CAST((smf.size * 8) / 1024 AS MONEY), 1), '.00', '')

FROM sys.master_files smf

WHERE d.database_id = smf.database_id

AND smf.type_desc = 'LOG'

AND smf.file_id = 2) AS 'Log Size in MB'

FROM master.dbo.sysdatabases sdb

INNER JOIN sys.databases d ON d.database_id = sdb.dbid

INNER JOIN sys.master_files smf ON smf.database_id = d.database_id

LEFT OUTER JOIN msdb.dbo.backupset bs ON sdb.name = bs.database_name AND bs.type = 'D'

LEFT OUTER JOIN msdb.dbo.log_shipping_primary_databases lspd ON lspd.primary_database = sdb.name

LEFT OUTER JOIN msdb.dbo.log_shipping_secondary_databases lssd ON lssd.secondary_database = sdb.name

WHERE smf.file_id = 1

GROUP BY sdb.name, lspd.primary_database, d.compatibility_level, lspd.last_backup_date,

lssd.last_restored_date, lssd.secondary_database, smf.size, d.database_id

ORDER BY 1

PostgreSQL

SELECT datname AS "Database", pg_size_pretty(pg_database_size(datname)) AS "Size"
  FROM pg_database
  ORDER BY datname;

SELECT datname AS "Database", pg_size_pretty(pg_database_size(datname)) AS "Size"

FROM pg_database

ORDER BY datname;

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

DBCC CHECKDB [MyDatabase];

1	DBCC CHECKDB [MyDatabase];

PostgreSQL

VACUUM?;

VACUUM?;

Updating Statistics

Show how to update statistics for slow queries
SQL Server

USE AdventureWorks; 
GO 
UPDATE STATISTICS AdventureWorks.<table_name>; 
GO

USE AdventureWorks;

UPDATE STATISTICS AdventureWorks.<table_name>;

PostgreSQL

ANALYZE;

ANALYZE;

Backup Database

Performing backups on Linux and PostgreSQL is a bit different than performing the same task in SQL Server on Windows. One example is 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. However you cannot restore a single database this way. There is also a method for backing up a single database in cases where that is needed. I will show both methods in this section.
How to perform a database backup to disk:
REF: SQL Dump
SQL Server

BACKUP DATABASE MyDatabase TO DISK 'E:\SQL\Backups\MyDatabase.bak' WITH COMPRESSION;

1	BACKUP DATABASE MyDatabase TO DISK 'E:\SQL\Backups\MyDatabase.bak' WITH COMPRESSION;

PostgreSQL

# You need to be executing the commands as the 'postgres' user to perform this task. Run as 'SUDO SU postgres" first. Then run the following commands as shown. PostgreSQL backs up a single database as a full SQL script that can be replayed back restoring the state of the database including the data exactly at that point. Also not that pg_dump does not back up roles (users and groups) and tablespaces. pg_dumpall does back those up. You can however, run 'pg_dumpall --roles-only > myroles.sql' or --tablespaces-only, or --globals-only which dumps both roles and tablespaces to a single file.
postgres> pg_dump -F c mydatabase -f outfile.sql

# You need to be executing the commands as the 'postgres' user to perform this task. Run as 'SUDO SU postgres" first. Then run the following commands as shown. PostgreSQL backs up a single database as a full SQL script that can be replayed back restoring the state of the database including the data exactly at that point. Also not that pg_dump does not back up roles (users and groups) and tablespaces. pg_dumpall does back those up. You can however, run 'pg_dumpall --roles-only > myroles.sql' or --tablespaces-only, or --globals-only which dumps both roles and tablespaces to a single file.

postgres> pg_dump -F c mydatabase -f outfile.sql

or to backup all databases, run the following:

postgres> pg_dumpall -F outfile.sql

1	postgres> pg_dumpall -F outfile.sql

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

RESTORE DATABASE MyDatabase FROM FILE 'E:\SQL\Backups\MyDatabase.bak' WITH RECOVERY;

1	RESTORE DATABASE MyDatabase FROM FILE 'E:\SQL\Backups\MyDatabase.bak' WITH RECOVERY;

PostgreSQL

pg_restore ...

1	pg_restore ...

— References
Streaming Replication

9.26. System Administration Functions

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

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, .Net etc…

PostgreSQL vs. SQL Server Notes

Running Tasks

KILL PID or SPID

Instance Configuration

Enabling Configuration Changes

Blocking\Deadlocking

Database Size

Check for Database Corruption

Updating Statistics

Backup Database

Restore Database