Postgres for Oracle DBAs – Script execution and default tablespaces

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

Share this Post :