PostgreSQL for Oracle DBAs

 As an Oracle DBA in my past life, I have thought about giving some understanding and relatable definitions of PostgreSQL compared to Oracle Databases. I hope this helps to understand how these two relational databases are comparable for Oracle DBAs to quickly adapt to the new DB server technology. I will not be giving detailed explanations in this article. For more details on each topic, please refer to the postgreSQL documentation.


Database architecture compared:

Oracle : SGA: Fixed physical memory size allocated to each server out of available RAM
Postgres: Total memory is allocated to postgres server running multiple databases within.

Oracle : Buffer Pool
Postgres: Share Buffers

Oracle: Redo log buffers / Redo log files
Postgres: WAL buffers / Wal files

Oracle: Archivelog Files
Postgres: Archivelog Files

Oracle: UNDO files are used to hold original copy of the dirty pages for transaction isolation
Postgres: WAL handles this.

Oracle : SMON process is responsible for instance recovery and startup
Postgres: POSTMASTER process is responsible for the same.

Background process
Oracle   : SMON, PMON, DB Writer , REDO,  Archiver ,   checkpointer   
Postgres: POSTMASTER, Writer , WAL Writer, Archiver  ,  checkpointer  , logger , stats collector

Oracle: PGA_AGGREGATE_TARGET is used for sorting and indexing
Postgres: WORK_MEM_SPACE is used for sorting, indexes, joins and bitmap operations



Comments

Post a Comment

Popular posts from this blog

PostgreSQL Tools

Postgres has few tools that help to query or do maintenance tasks: 1. psql   2. pg_ctl 3. pg_lsclusters 4. pg_ctlcluster Psql is an terminal based front-end interface for PostgreSQL for querying anything about Postgres server and its databases. Few examples are here. psql   commands \? : show all psql commands. \h sql-command : show syntax on SQL command. \c dbname [username] : Connect to database, with an optional username (or  \connect ). Display Commands: You can append  +  to show more details. \l : List all database (or  \list ). \d : Display all tables, indexes, views, and sequences. \dt : Display all tables. \di : Display all indexes. \dv : Display all views. \ds : Display all sequences. \dT : Display all types. \dS : Display all system tables. \du : Display all users. \x auto|on|off : Toggle|On|Off expanded output mode. For automations this below may be helpful as this is executed from Linux command line and the output can be redirected to...
Read more

Move postgreSQL server data to new Folder Location

 Move the entire postgres server files from default location to a custom location on the server A word of Caution: This procedure involves a database shutdown and restart needed.  Please make sure you have  change request raised if you are working on production database and get it approved before scheduling this change.  You must notify the application team about the change as well and set the down time expectations. It is better to maintain the standards in the naming convention for the common scripts to monitor and support any activities. Pre-Steps: Create the required folder structure and modify the ownership and permissions on the folder structure similar to the below steps           $ sudo su - root           $ mkdir -p /myorg/postgres/           $ chown -R postgres:postgres /myorg/postgres           $ chown -R 755 /myorg/postgres Note:  D...
Read more