Table of Contents
What is included in this blog post ?
We are starting “Postgres for Oracle DBAs” series with this post . In this post we compare two aspects – 1) Executing sql scripts 2) Default tablespaces
Multiple script execution methods are compared with Postgres in this blog post . We also discuss about default tablespaces in Oracle and compare them with Postgres default tablespaces
OracleVsPostgres - Executing scripts using “@” and “\i”
In Oracle you can execute a sql script using ‘@’ operator e.g – @basictest.sql; In Postgres you can perform the same using ‘\i’ operator e.g – \i basictest.sql
Below diagram compares script execution method using ‘@’ in Oracle and ‘/i’ in Postgres . Once you run the script you can see the output as illustrated below
OracleVsPostgres - Executing sql statements using “sqlplus” and “psql”
In this section we are going to illustrate another scenario – Running scripts via command line (without logging into psql/sqlplus)
In postgres you can specify the database using -d option . There are multiple other command line options that can be used with psql
Oracle command : sqlplus hr/hr@pdb @scriptname
Postgres command : psql -d postgres -U username < scriptname
OracleVsPostgres - Executing sql statements in shell script
In this section we are going to see how sql scripts can be executed via shell scripts
In below diagram you can see the comparison between postgres and Oracle . You can see the contents of shell script and how it is executed (for both Oracle and Postgres)
Oracle default tablespaces
In this section we compare default tablespaces .Below are default tablespaces in Oracle – SYSTEM,SYSAUX,USERS,UNDO and TEMPORARY . Purpose of each tablespace is illustrated in the diagram below
In Postgres we only have two default tablespaces as opposed to 5 in Oracle . Pg_default and Pg_global are the tablespace names . Purpose of each tablespace is illustrated in below diagram
OracleVsPostgres - Default tablespaces comparison study
Below is the comparison between Oracle and Postgres
- System tablespace in Oracle is equal to Postgres pg_blobal tablespace
- Undo tablespace in Oracle does not have an equivalent in Postgres(Postgres MVCC is different)
- Users tablespaces in Oracle is equal to Postgres Pg_default tablespace
- Sysaux tablespace in Oracle is used to store performance metrics , AWR related info etc.. Postgres does not have an equivalent
Conclusion
This is part 1 of the series(Postgres for Oracle DBAs) and please stay tuned for other parts . Please also check our other performance articles – Pgbouncer multiple instances , Postgres and temporary files , Pgpool and performance tuning, How we improved Lambda performance by 130x etc..
Looking for Postgres support ? Please reach us at support@klouddb.io