Table of Contents
What is included in this blog post ?
In part1 of this series(Postgres for Oracle DBAs) we covered two topics 1) Executing sql scripts 2) Default tablespaces . In this post we are going to compare startup/shutdown modes between Oracle and Postgres
Oracle shutdown modes
Oracle has different shutdown modes as detailed below – Normal , Immediate , transactional and abort are the four different shutdown modes
Shutdown normal(Default mode if nothing is specified) : waits for the current users to disconnect from the database before shutting down the database. The database instance will not accept any further database connections
shutdown immediate: This is the ideal shutdown we use, all the connected sessions are disconnected immediately, all uncommitted transactions are rolled back, and the database completely shuts down.
shutdown transactional: waits for all uncommitted transactions to complete before shutting down the database instance. This saves the work for all users without requesting them to log off.
shutdown abort: is not recommended and only used on some occasions. The SHUTDOWN ABORT has a similar effect as you unplug the power of the server. The database will be in an inconsistent state. Therefore, you should never use this method.
Out of all these modes shutdown abort is the most dangerous one and is rarely used
Below is the comparison matrix for Oracle Shutdown modes
Postgres shutdown modes
Unlike Oracle Postgres only has 3 shutdown modes – Smart , Fast and Immediate
Smart Shutdown mode: After receiving SIGTERM, the server disallows new connections, but lets existing sessions end their work normally. It shuts down only after all of the sessions terminate.
Fast Shutdown mode(Default mode): The server disallows new connections and sends all existing server processes SIGTERM, which will cause them to abort their current transactions and exit promptly. It then waits for all server processes to exit and finally shuts down.
Immediate Shutdown mode: The server will send SIGQUIT to all child processes and wait for them to terminate. If any do not terminate within 5 seconds, they will be sent SIGKILL. The master server process exits as soon as all child processes have exited, without doing normal database shutdown processing. This will lead to recovery (by replaying the WAL log) upon next start-up. This is recommended only in emergencies
NOTE – Prior to PG 9.5 default mode was SMART and it was changed to FAST from 9.5
Postgres shutdown modes
Oracle Vs PG - Shutdown mode comparison
Below is the comparison study for shutdown modes:
- Oracle immediate mode is equivalent to Postgres fast mode
- Oracle abort is equivalent to Postgres immediate
- Oracle default mode is Normal whereas Postgres default mode is Fast
- Oracle DBAs have to be cautious about IMMEDIATE mode usage in Postgres . In Postgres it is totally different and can be confusing
- Oracle has 4 shutdown modes whereas PG has only 3 modes
Oracle startup modes
Below are different startup modes in Oracle – nomount , mount and open . In Oracle you can start the instance without mounting the database ;start instance ,mount database and open it in restricted mode etc.. Oracle offers lot of flexibility in this area but in Postgres we do not have different options – mount/nomount/force etc..
Postgres startup modes
A postgres startup is typically done using systemctl start or pg_ctl command typically . It is just one single command and unlike Oracle you can not issue different commands to stop at different stages of startup. Below diagram explains the flow of activities (Note – There are other internal activities that happen during startup which are not included in this diagram)
Oracle Vs PG - Startup mode comparison
Below is the comparison study for startup modes
- In Oracle you can use different options like mount , nomount , force etc.. when you start Oracle. In PG we do not have similar stages/options , typically we just use pg_ctl start or systemctl start commands
- People can use sqlplus to issue startup in Oracle and in postgres we dont use psql to start the instance . Instead we use systemctl or pg_ctl
Conclusion
In part 2 of the series “Postgres for Oracle DBAs” we compared startup/shutdown modes . Please also read part 1 which covers 1) default tablespaces 2) sql script execution methods . Also check our performance articles – Pgbouncer multiple instances(How we increased our TPS using 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