PostgreSQL Production Labs · DBA Transition Guide
You Already Know This.
You Already Know This.
You Just Need to Know It in PostgreSQL.
Managing Oracle, SQL Server, or MySQL in production? You already have the instincts. Use this checklist to see where your skills map directly — and where PostgreSQL does things differently.
🔴 Oracle DBA
🔵 SQL Server DBA
🟡 MySQL DBA
→
🐘 PostgreSQL
Similarity
🟢 Same concept, different syntax
🟡 Similar — small differences
⚪ Different approach — needs learning
— Not applicable
1
Installation & Initial Setup
13 activities
| PostgreSQL Activity | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Install prerequisites (make, gcc, readline, zlib) | 🟢 SameOS prereqs for OUI installer | 🟢 SamePrerequisites installer check | 🟢 SameStandard Linux prep |
| Install PostgreSQL using RPMs on CentOS | 🟢 SameOracle RPM installer | 🟡 SimilarSQL Server on Linux RPM | 🟢 SameMySQL RPM install |
| Install PostgreSQL using YUM on CentOS | 🟢 SameOracle yum repo install | 🟡 SimilarSQL Server yum install | 🟢 SameMySQL yum install |
| Install PostgreSQL using Source code | ⚪ DifferentOracle closed-source — no equivalent | ⚪ DifferentSQL Server closed-source — no equivalent | 🟡 SimilarMySQL source build (cmake) |
| Initialize PostgreSQL cluster (initdb) | 🟡 Similardbca / manual create database | 🟡 Similarsetup.exe / sqlservr --create | 🟡 Similarmysqld --initialize |
| Initialize cluster — non-default data location | 🟡 SimilarORACLE_BASE / dbca custom paths | 🟢 SameSQL Server data directory config | 🟢 Samedatadir in my.cnf |
| Install contrib modules, create pg_buffercache extension | 🟡 SimilarOracle optional components | 🟡 SimilarSQL Server optional features | 🟡 SimilarMySQL INSTALL PLUGIN |
| Uninstall PostgreSQL (YUM / RPM / Source) Optional | 🟢 SameOracle deinstall utility | 🟢 SameAdd/Remove Programs | 🟢 Sameyum remove mysql-server |
| Install postgresql-devel module | ⚪ DifferentNo direct equivalent | ⚪ DifferentNo direct equivalent | 🟡 Similarmysql-devel package |
| Start / stop cluster using pg_ctl | 🟢 Samesqlplus startup / shutdown | 🟢 Samenet start/stop MSSQLSERVER | 🟢 Samemysqld_safe / mysqladmin |
| Shutdown modes (smart / fast / immediate) | 🟢 Sameshutdown normal / transactional / abort | 🟡 Similarnet stop / SHUTDOWN WITH NOWAIT | 🟡 Similarmysqladmin shutdown modes |
| Set up bash profile (PGDATA, PGPORT, PATH) | 🟢 SameORACLE_HOME, ORACLE_SID in .bashrc | 🟡 SimilarEnv vars in systemd / .profile | 🟢 SamePATH to mysql bin in .bashrc |
| Set postgres password / pgpass file / psqlrc | 🟢 SameOracle wallet / orapass | 🟡 SimilarCredential Manager / .sqlcmd file | 🟢 Same.my.cnf password file |
2
Configuration & Client Tools
5 activities
| PostgreSQL Activity | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Running psql client and shortcuts | 🟢 Samesqlplus CLI client | 🟡 Similarsqlcmd / Azure Data Studio | 🟢 Samemysql CLI client |
| Locate the configuration file (postgresql.conf) | 🟢 Sameinit.ora / spfile | 🟢 Samesp_configure / SQL Server config | 🟢 Samemy.cnf / my.ini |
| Tune parameters (pgtune) in postgresql.conf | 🟢 SameSGA, PGA, db_block_size tuning | 🟢 Samemax server memory, MAXDOP | 🟢 Sameinnodb_buffer_pool_size etc. |
| Apply parameters (reload vs restart) | 🟢 SameALTER SYSTEM / scope=memory/spfile | 🟢 SameRECONFIGURE / service restart | 🟢 SameSET GLOBAL / FLUSH / restart |
| Benchmarking with pgbench | 🟡 SimilarSwingbench / SLOB | 🟡 SimilarHammerDB / ostress | 🟡 Similarsysbench |
3
Security, Access Control & Users
8 activities
| PostgreSQL Activity | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Secure connections via pg_hba.conf (md5 auth) | 🟡 Similarsqlnet.ora / listener.ora | 🟡 SimilarSQL Server Login / Windows Auth | 🟢 Sameuser@host grants in mysql.user |
| Enable SSL for connections | 🟢 SameOracle Advanced Security / TCPS | 🟢 SameForce Encryption / TLS | 🟢 Samerequire_secure_transport |
| Create a user in PostgreSQL | 🟢 SameCREATE USER | 🟢 SameCREATE LOGIN + CREATE USER | 🟢 SameCREATE USER |
| Create readonly user | 🟢 SameGRANT SELECT to user | 🟢 Samedb_datareader role | 🟢 SameGRANT SELECT on *.* to user |
| Create group role (DBA / dev role segregation) | 🟢 SameOracle roles (DBA, CONNECT, RESOURCE) | 🟢 SameServer roles / DB fixed roles | 🟡 SimilarRole-based GRANT in MySQL 8+ |
| Drop a user in PostgreSQL | 🟢 SameDROP USER | 🟢 SameDROP LOGIN / DROP USER | 🟢 SameDROP USER |
| Audit database access via logging | 🟢 SameOracle Unified Auditing | 🟢 SameSQL Server Audit | 🟡 SimilarGeneral log / audit log plugin |
| Audit PostgreSQL using pgaudit extension | 🟡 SimilarOracle Unified Auditing (more granular) | 🟡 SimilarSQL Server Audit feature | 🟡 SimilarMySQL Enterprise Audit plugin |
4
Storage, Schemas & Database Objects
8 activities
| PostgreSQL Activity | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Create and drop databases | 🟡 SimilarOracle = one DB per instance; PDB in 12c+ | 🟢 SameCREATE / DROP DATABASE | 🟢 SameCREATE / DROP DATABASE |
| Locate a database and table on the filesystem | 🟡 Similardatafiles in dba_data_files | 🟡 Similar.mdf / .ndf files on disk | 🟢 Samedatadir / table .ibd files |
| Create a schema in PostgreSQL | 🟢 SameCREATE SCHEMA (user = schema in Oracle) | 🟢 SameCREATE SCHEMA | ⚪ DifferentMySQL schema = database; no sub-schemas |
| Create tablespaces Optional | 🟢 SameCREATE TABLESPACE | 🟡 SimilarFilegroups | 🟡 SimilarInnoDB tablespaces (limited) |
| Move tables to a different tablespace Optional | 🟢 SameALTER TABLE MOVE TABLESPACE | 🟡 SimilarCREATE INDEX … ON filegroup | — |
| Move database objects between schemas | 🟡 SimilarExport/import between schemas | 🟢 SameALTER SCHEMA … TRANSFER | — |
| Add or remove tablespaces | 🟢 SameALTER / DROP TABLESPACE | 🟡 SimilarAdd / remove filegroups | ⚪ DifferentVery limited tablespace control |
| Create and refresh materialized views | 🟢 SameMaterialized Views (Oracle native) | 🟡 SimilarIndexed Views (limited auto-refresh) | ⚪ DifferentNo native MVs — manual sync tables |
5
Backup & Recovery
7 activities
| PostgreSQL Activity | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Backup & restore a database (pg_dump / pg_restore) | 🟢 Sameexpdp / impdp (Data Pump) | 🟢 SameBACKUP DATABASE / RESTORE DATABASE | 🟢 Samemysqldump / mysqlpump |
| Backup one or more tables using pg_dump | 🟢 Sameexpdp TABLES= parameter | 🟡 SimilarBCP / SELECT INTO from source | 🟢 Samemysqldump --tables |
| Backup all databases using pg_dumpall | 🟡 Similarexpdp FULL=Y | 🟡 SimilarBack up all system + user databases | 🟢 Samemysqldump --all-databases |
| Parallel backup & restore with pg_dump | 🟢 Sameexpdp PARALLEL= option | 🟡 SimilarStriped backups to multiple files | ⚪ DifferentNo native parallel dump in mysqldump |
| Backup cluster using pg_basebackup (physical) | 🟢 SameRMAN cold / hot backup | 🟢 SameFull database backup to disk | 🟢 Samextrabackup / mysqlbackup |
| Set up WAL archiving (archive_mode) | 🟢 SameARCHIVELOG mode | 🟢 SameFull recovery model + log backups | 🟢 SameBinary logging (binlog) |
| Restore pg_basebackup with PITR | 🟢 SameRMAN restore + recover to SCN/time | 🟢 SameRESTORE DATABASE WITH STOPAT | 🟡 SimilarRestore + replay binlog to point-in-time |
6
Replication & High Availability
7 activities
| PostgreSQL Activity | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Set up streaming replication | 🟢 SameData Guard physical standby | 🟢 SameAlways On Availability Groups | 🟢 SameAsync / semi-sync replication |
| Monitor standby WAL receive / apply lag | 🟢 Samev$archive_gap / DG lag view | 🟢 SameAG dashboard / log send queue | 🟢 SameSeconds_Behind_Master |
| Perform failover (promote standby) | 🟢 SameData Guard failover | 🟢 SameAG manual failover | 🟢 SameCHANGE MASTER TO / MHA failover |
| Perform switchover (planned role swap) | 🟢 SameData Guard switchover | 🟢 SameAG planned failover | 🟡 SimilarOrchestrator / MHA switchover |
| Set up replication slot based streaming Optional | ⚪ DifferentDG manages apply — no slots concept | ⚪ DifferentNo equivalent in AG | 🟡 SimilarGTID-based tracking concept |
| Set up Patroni (automatic HA + etcd) | 🟢 SameData Guard Broker / Observer | 🟢 SameAlways On AG automatic failover | 🟢 SameMHA / Group Replication / Orchestrator |
| Set up PgBouncer connection pooler | 🟡 SimilarOracle CMAN / Connection Manager | 🟡 SimilarDriver-level pooling (no standalone tool) | 🟡 SimilarProxySQL |
7
Performance, Monitoring & Logging
12 activities
| PostgreSQL Activity | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Set up Grafana for PostgreSQL monitoring | 🟡 SimilarOEM / Enterprise Manager Cloud Control | 🟡 SimilarSQL Server Dashboard / Azure Monitor | 🟡 SimilarMySQL Enterprise Monitor / PMM |
| Set up slow query logging | 🟢 Samesql_trace / v$sql by elapsed time | 🟢 SameQuery Store / Extended Events | 🟢 Sameslow_query_log / long_query_time |
| Log runtime execution plans (auto_explain) | 🟢 Samesql_trace + tkprof / DBMS_XPLAN | 🟢 SameActual execution plan / Extended Events | 🟡 SimilarEXPLAIN FORMAT=JSON in slow log |
| Log locks, waits, and temp usage | 🟢 Samev$lock / v$session_wait | 🟢 Samesys.dm_exec_requests / wait stats | 🟢 Sameperformance_schema lock tables |
| Log autovacuum activity | ⚪ DifferentNo equivalent — undo managed separately | ⚪ DifferentGhost cleanup is fully internal / silent | 🟡 SimilarInnoDB purge thread monitoring |
| Tune logging parameters for pgBadger | 🟡 Similarsql_trace / audit setup | 🟡 SimilarQuery Store / Extended Events setup | 🟢 Sameslow_query_log format settings |
| Generate a pgBadger report (log analysis) | 🟡 SimilarAWR / ADDM report | 🟡 SimilarQuery Store reports / SQL Server Reports | 🟡 Similarpt-query-digest (Percona Toolkit) |
| Configure pg_stat_statements extension | 🟢 Samev$sql / v$sqlstats | 🟢 Samesys.dm_exec_query_stats / Query Store | 🟢 Sameperformance_schema statement summary |
| Query analysis using pg_stat_statements | 🟢 SameTop SQL by elapsed / CPU in AWR | 🟢 SameQuery Store top resource consumers | 🟢 Sameperformance_schema top queries |
| Understanding PostgreSQL locking (pg_locks) | 🟢 Samev$lock / v$session | 🟢 Samesys.dm_os_waiting_tasks | 🟢 Sameinnodb_trx / innodb_lock_waits |
| Monitor VACUUM progress (pg_stat_progress_vacuum) | ⚪ DifferentNo vacuum — undo reclaimed automatically | ⚪ DifferentNo vacuum — ghost cleanup is silent | 🟡 Similarinnodb_purge_threads monitoring |
| Check slow queries / locks / kill a session | 🟢 Samev$session + ALTER SYSTEM KILL SESSION | 🟢 Samesp_who2 / KILL session_id | 🟢 SameSHOW PROCESSLIST / KILL query_id |
8
Maintenance, Upgrades & Advanced
10 activities
| PostgreSQL Activity | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Fix a bloated table (VACUUM FULL) | 🟡 SimilarALTER TABLE MOVE to reclaim space | 🟡 SimilarDBCC SHRINKFILE / SHRINKDATABASE | 🟡 SimilarOPTIMIZE TABLE |
| Reindex / find and remove unused indexes | 🟢 SameALTER INDEX REBUILD / monitoring_usage | 🟢 SameALTER INDEX REBUILD / dm_db_index_usage_stats | 🟢 SameOPTIMIZE TABLE / performance_schema |
| EXPLAIN ANALYZE — joins, subqueries, predicates | 🟢 SameEXPLAIN PLAN / DBMS_XPLAN | 🟢 SameSET SHOWPLAN_ALL / Actual Execution Plan | 🟢 SameEXPLAIN / EXPLAIN ANALYZE (MySQL 8+) |
| Force a query to use an index | 🟡 SimilarINDEX hint | 🟡 SimilarWITH (INDEX(…)) hint | 🟡 SimilarFORCE INDEX hint |
| Upgrade PostgreSQL (pg_upgrade) | 🟢 SameDBUA / manual upgrade | 🟢 SameIn-place / side-by-side upgrade | 🟢 SameIn-place upgrade / mysql_upgrade |
| Move pg_wal / postgresql.conf to another location Optional | 🟢 SameMove redo logs to different diskgroup | 🟢 SameMove .ldf / detach-attach | 🟢 SameMove binlog to different disk |
| Cascaded streaming replica Optional | 🟡 SimilarCascaded Data Guard standby | 🟡 SimilarDistributed AG / cascaded AG | 🟢 SameRelay-based chained replication |
| Synchronous streaming replication Optional | 🟢 SameData Guard SYNC / AFFIRM mode | 🟢 SameAG synchronous commit mode | 🟡 SimilarSemi-sync replication |
| pg_rewind to re-sync a demoted primary Optional | 🟢 SameFlashback / re-join old primary in DG | 🟡 SimilarAG seeding to re-join failed replica | 🟡 SimilarCHANGE MASTER TO + clone old primary |
| Set up logical replication in PostgreSQL | 🟢 SameOracle Streams / GoldenGate | 🟢 SameSQL Server Transactional Replication | 🟢 SameBinlog-based logical replication |
9
Cloud Managed Services
8 activities
| PostgreSQL Activity (AWS RDS / Aurora) | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| Launch a PostgreSQL RDS instance | 🟢 SameAWS RDS for Oracle | 🟢 SameAWS RDS for SQL Server | 🟢 SameAWS RDS for MySQL |
| Configure RDS parameter groups (= postgresql.conf) | 🟢 SameRDS Oracle parameter groups | 🟢 SameRDS SQL Server parameter groups | 🟢 SameRDS MySQL parameter groups |
| RDS automated backups, snapshots & PITR | 🟢 SameRDS Oracle automated backups | 🟢 SameRDS SQL Server automated backups | 🟢 SameRDS MySQL automated backups |
| Set up RDS Multi-AZ (HA in cloud) | 🟢 SameRDS Oracle Multi-AZ | 🟢 SameRDS SQL Server Multi-AZ | 🟢 SameRDS MySQL Multi-AZ |
| Create Aurora PostgreSQL cluster | ⚪ DifferentNo Oracle Aurora equivalent | ⚪ DifferentNo SQL Server Aurora equivalent | 🟢 SameAurora MySQL — same cluster concept |
| Use RDS Performance Insights | 🟡 SimilarCloudWatch for Oracle RDS | 🟡 SimilarRDS Performance Insights for SQL Server | 🟢 SameRDS Performance Insights for MySQL |
| Migrate to RDS using AWS DMS | 🟢 SameAWS DMS from Oracle source | 🟢 SameAWS DMS from SQL Server source | 🟢 SameAWS DMS from MySQL source |
| Provision infrastructure with Terraform (VPC, RDS, security groups) | 🟢 SameTerraform for Oracle RDS | 🟢 SameTerraform for SQL Server RDS | 🟢 SameTerraform for MySQL RDS |
10
Third-Party Tools & GUI Clients
8 activities
| PostgreSQL Tool / Task | Oracle Equivalent | SQL Server Equivalent | MySQL Equivalent |
|---|---|---|---|
| pgAdmin — GUI administration and query tool | 🟢 SameSQL Developer / TOAD | 🟢 SameSSMS (SQL Server Management Studio) | 🟢 SameMySQL Workbench |
| DBeaver — universal database GUI (free) | 🟢 SameDBeaver connects to Oracle too | 🟢 SameDBeaver connects to SQL Server too | 🟢 SameDBeaver connects to MySQL too |
| DataGrip — JetBrains IDE for SQL | 🟢 SameDataGrip supports Oracle | 🟢 SameDataGrip supports SQL Server | 🟢 SameDataGrip supports MySQL |
| ora2pg — schema & data migration from Oracle | 🟢 SamePurpose-built for Oracle → PostgreSQL | —Use AWS SCT or SSMA instead | —Use pgloader or DMS instead |
| AWS SCT (Schema Conversion Tool) | 🟢 SameConverts Oracle DDL + PL/SQL | 🟢 SameConverts SQL Server DDL + T-SQL | 🟢 SameConverts MySQL DDL |
| Prometheus + postgres_exporter (metrics collection) | 🟡 SimilarOEM metrics / custom scripts | 🟡 SimilarSQL Server Exporter for Prometheus | 🟢 Samemysqld_exporter / PMM |
| pgBadger (log analysis & reporting) | 🟡 SimilarAWR / ADDM / Statspack | 🟡 SimilarSQL Server Reports / Query Store | 🟡 Similarpt-query-digest (Percona Toolkit) |
| Install and work with pgadmin (web / desktop) | 🟢 SameSQL Developer (free Oracle tool) | 🟢 SameSSMS — equivalent experience | 🟢 SameMySQL Workbench — equivalent experience |