Skip to main content

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 :
– PgBouncer
– PgPool
13.Active Data Guard :
– PostgreSQL master / slave replication
– Slony
14.Replication master / master :
– PostgreSQL-XC
– Bucardo
15.Logical replication :
– PostgreSQL 9.5 / 10
– Slony
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

Comments

Post a Comment

Popular posts from this blog

PostgreSQL Vacuum and Vacuum full are not two different processes

  PostgreSQL’s   VACUUM   and   VACUUM FULL   are not separate processes but rather different operational modes of the same maintenance command. Here’s why: Core Implementation Both commands share the same underlying codebase and are executed through the  vacuum_rel()  function in PostgreSQL’s source code ( src/backend/commands/vacuum.c ). The key distinction lies in the  FULL  option, which triggers additional steps: Standard  VACUUM : Removes dead tuples (obsolete rows) and marks space reusable  within PostgreSQL Updates the visibility map to optimize future queries Runs concurrently with read/write operations VACUUM FULL : Rewrites the entire table into a new disk file, compressing it and reclaiming space for the  operating system Rebuilds all indexes and requires an  ACCESS EXCLUSIVE  lock, blocking other operations Key Differences in Behavior Aspect Standard VACUUM VACUUM FULL Space Reclamation Internal reuse onl...
1 comment
Read more

PostgreSQL Health Check - Performance Audit and Recommendations

   PostgreSQL Server Capacity Planning and Optimization: Conduct a thorough assessment of current and projected workload demands on the database server's critical resources. This evaluation should cover key factors such as CPU utilization, RAM consumption, storage requirements, I/O performance, and network bandwidth usage. Consider both short-term fluctuations and long-term usage patterns to accurately forecast future needs and potential bottlenecks. Collect and analyze performance metrics over extended periods, including peak and off-peak hours, to gain a comprehensive understanding of server behavior. Use appropriate monitoring tools to gather detailed data on query execution times, resource utilization patterns, and system responsiveness under varying loads. Identify recurring performance issues, resource contention points, or capacity limitations that may affect the overall efficiency of the PostgreSQL environment. Based on this analysis, develop actionable recommendations...
Post a Comment
Read more

PostgreSQL - Architecture

When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The other processes that the postgres process starts are responsible for the ongoing work of the database.  PostgreSQL`s architecture is based on a client/server communication. In the server side there is a server process that manages the files, database operations, connection and so on.. On the client side there is a tool/program which can be a graphical tool (gui)  like pgAdmin or a command line tool(cli) like psql  that is repnsible for connection the database and all...
7 comments
Read more