PostgreSQL – Oracle Vs PostgreSQL
I. Oracle Tools equivalence to postgresql :-
1.SQLPLUS : PSQL but much more
2.TOAD / Oracle SQL Developper : TORA or pgAdmin
3.EXPLAIN PLAN : EXPLAIN ANALYZE
4.ANALYZE TABLE : ANALYZE
5.Cold backup : both are file system backup
6.Hot backup : REDOLOGS = ARCHIVELOGS
7.Logical Export : exp = pg_dump
8.Logical Import : imp = pg_restore or psql
9.SQL Loader : pgLoader
10.RMAN : Barman or Pitrery
11.AUDIT TRAIL : pgAudit
12.Pooling / Dispatcher :
13.Active Data Guard :
– PostgreSQL master / slave replication
14.Replication master / master :
15.Logical replication :
– PostgreSQL 9.5 / 10
16.RAC Horizontal scaling : PostgreSQL-XC – PostgreSQL-XL – plProxy, pg_shard
17.Oracle => Postgres Plus Advanced Server
– Same as PostgreSQL but with proprietary code and database feature compatibility for Oracle.
– Compatible with applications written for Oracle.
– No need to rewrite PL/SQL into PLPGSQL
– Applications written for Oracle run on Postgres Plus Advanced Server without modification.
II. Postgres Monitoring / Audit tools:-
1.PgBadger: A fast PostgreSQL log analyzer
2.PgCluu: PostgreSQL and system performances monitoring and auditing tool
3.Powa: PostgreSQL Workload Analyzer. Gathers performance stats and provides real-time charts and graphs to help monitor and tune your PostgreSQL servers. Similar to Oracle AWR.
4.PgObserver: monitor performance metrics of different PostgreSQL clusters.
5.OPM: Open PostgreSQL Monitoring. Gather stats, display dashboards and send warnings when something goes wrong. Tend to be similar to Oracle Grid Control.
6.check_postgres: script for monitoring various attributes of your database. It is designed to work with Nagios, MRTG, or in standalone scripts.
7.Pgwatch: monitor PostgreSQL databases and provides a fast and efficient overview of what is really going on.
8.pgAgent:pgAgent is a job scheduler for PostgreSQL which may be managed using pgAdmin. Prior to pgAdmin v1.9, pgAgent shipped as part of pgAdmin. From pgAdmin v1.9 onwards, pgAgent is shipped as a separate application.
9.pgbouncer:PgBouncer is a lightweight connection pooler for PostgreSQL.It contains the connection pooler and it is used to establish connection between application and database
III. Oracle vs postgres data types comparison
1.VARCHAR2 : VARCHAR or TEXT
2.CLOB, LONG : VARCHAR or TEXT
3.NCHAR, NVARCHAR2, NCLOB : VARCHAR or TEXT
4.NUMBER : NUMERIC or BIGINT or INT or SMALLINT or DOUBLE PRECISION or REAL (bug potential)
5.BINRAY FLOAT/BINARY DOUBLE : REAL/DOUBLE PRECISION
6.BLOB, RAW, LONG RAW : BYTEA (additional porting required)
7.DATE : DATE or TIMESTAMP