Oracle 23c New Features for DBA'sA quick features, commands, tools comparison of Postgres Database for Oracle DBA.
| Oracle | |
| Architecture | https://www.linkedin.com/pulse/postgresql-architecture-sumeet-shukla-1/ |
| SGA | Postgres memory is divided into shared memory and per backend memory which acts as SGA |
| BufferPool | shared buffers |
| Shared Pool | Other buffer |
| log buffer | wal buffers |
| pga | work mem, temp buffer, maintenance work buffer, catalog cache, optimize executor |
| Dictionary | pg_catalog, pg_information_schema, |
| spfile/pfile | in pgdata folder postgresql.conf |
| sqlnet.ora | in pgdata folder pg_hba.conf |
| Database Server | ps -eaf | grep postgres |
| Database Client | psql |
| Database Listener | Unix level socket , created in pgdata folder or defined by pid option in conf file , daemon process |
| redolog | wal files resided in pgdata folder under pg_wal directory |
| archives | archive files resided in pgdata folder under archives directory when archive=on in conf file |
| database | can have multiple databases under one postgresql instance |
| schema is mapped to user 1:1 | logically segregated under a database , a database can have multiple schemas and a user can access multiple scehamas |
| sys schema or user | postgres database by default created and a public schema is associated with it |
| datafile | physically stored in base directory under pgdata, each folder will have a number represented by dbid |
| alertlog | postgresql-day.log under pgdata/pg_log directory |
| shutdown | pg_ctl -stop -d <datadirectory> |
| startup | pg_ctl -start -d <datadirectory> |
| expdp | pg_dump |
| impdp | psql < sqldump file |
| expdp full | |
| expdp schema | |
| expdp table | |
| impdp full database | |
| impdp only metadata | |
| V$DATABASE | PG_DATABASE |
| V$SESSION | PG_STAT_ACTIVITY |
| DBA_USERS | PG_USER |
| DBA_TABLES | PG_TABLES |
| DBA_ROLES | PG_ROLES |
| DBA_TAB_COLS | PG_ATTRIBUTE |
| V$LOCKED_OBJECT | PG_LOCKS |
| V$PARAMETER | PG_SETTINGS |
| V$SYSSTAT | PG_STAT_DATABASE |
| DBA_TAB_PRIVS | TABLE_PRIVILEGES |
| V$SEGSTAT | PG_STATIO_ALL_TABLES |
| v$ views | Statistic collection views- Subsystem that collects runtime dynamic information about certain server activities such as statistical performance information. Some of these tables could be thought as comparable to Oracle V$ views. - pg_stat* |
| dba_objects, dba_tables etc | System catalog tables - Static metadata regarding the PostgreSQL database and static information about schema objects. Some of these tables could be thought as comparable to Oracle DBA_* Data Dictionary tables. - pg_catalog |
| user_objects, user_tables | Information schema tables - Set of views that contain information about the objects defined in the current database. The information schema is specified by the SQL standard and as such, supported by PostgreSQL. Some of these tables could be thought as comparable to Oracle USER_* Data Dictionary tables. information_schema |
| identify locks (12>) | SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, substring(blocked_activity.query,1,50) AS blocked_statement, substring(blocking_activity.query,1,50) AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted; |
| identify locks 9.6 | SELECT a.datname, l.relation::regclass, l.transactionid, l.mode, l.GRANTED, a.usename, substring(a.query,1,80), a.query_start, age(now(), a.query_start) AS "age", a.pid FROM pg_stat_activity a JOIN pg_locks l ON l.pid = a.pid and mode='RowExclusiveLock' ORDER BY a.query_start; |
| Kill one blocking session | SELECT pg_cancel_backend(<pid of the process>) SELECT pg_terminate_backend(<pid of the process>); |
| Kill all blocking session for particular db | SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE — don’t kill my own connection! pid pg_backend_pid() — don’t kill the connections to other databases AND datname = 'databasename' |
| connections by user,database,state | select state,usename,datname,count(*) from pg_stat_activity group by state,usename,datname; |
| long running sessions ex: more than 5 mins | SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'; |
| oldest transaction in db | SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active'); |
| Table & Index frag check | SELECT relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) As "Total Size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "Index Size", pg_size_pretty(pg_relation_size(relid)) as "Actual Size" FROM pg_catalog.pg_statio_user_tables RDER BY pg_total_relation_size(relid) DESC; |
| manually collect stats on postgres | analyze table or analyze index |
| Create User | create user U with password '' superuser/nosuperuser; |
| Connect, resource privileges | grant connect on database to user; grant usage,select on schema s1 to user; |
| switch database | \c dbname |
| swtich shema, user alter session set current_schema=user; | set search_path schemaname; |
| stats collection | analyze |
| explain plan | explain select statement |
| shared_buffers parameter default 128, but if you increase you must increase bg_writer parameters | |
| control shared memory | work_memory |
| control backend memory | |
| Check point parameters | |
| readonly slaves | |
| Standby | REPMGR |
| DGMGRL | Repmgr conf file |
| DGMGRL set properties | force_parallel_mode |
| replica sync check | |
| enable parallelism | |
| called as gather node | |
| parallelism - coordinate | called as worker ndoe |
| parallelsim - px slave | max_worker_processes – Maximum number of background processes that can be launched to support parallel queries. |
| parallel_processes | max_parallel_workers – Maximum number of workers that support parallel operations. |
| max_parallel_workers_per_gather (default 0) – Number of additional workers that can be used on a query. | |
| degree on a object | parallel_tuple_cost (default 0.1) – Estimates the cost of transferring one tuple from a parallel worker process to another process |
| parallel_setup_cost (default 1000.0) – Estimates the cost of launching parallel worker processes | |
| min_parallel_relation_size (default 8 MB) – A relation larger than this parameter is considered for parallel scans | |
| consider the table size for default parallelims | force_parallel_mode (default off) – This is useful when testing parallel query scans even when there is no performance benefit |
| SSL | #ssl = off #ssl_ca_file = '' #ssl_cert_file = 'server.crt' #ssl_crl_file = '' #ssl_key_file = 'server.key' Enable hostssl at pg_hba.conf |
| Changing bulk tables owner and sequence to new user | do $$ declare row record; begin for row in select tablename as name from pg_tables where tableowner = 'memorystudiodbprd_admin' loop raise notice 'row: %', row; execute format('alter table "%s" owner to memorystudioecdc_admin', row.name); end loop; end; $$; do $$ declare row record; begin for row in select sequencename as name from pg_sequences where sequenceowner = 'memorystudiodbprd_admin' loop raise notice 'row: %', row; execute format('alter sequence "%s" owner to memorystudioecdc_admin', row.name); end loop; end; $$; |
| changin single table owner and seuqence to user | alter sequence sequencename owner to newowner; alter tablename tablename owner to newowner; |
| User that’s has privileges on a table | SELECT * FROM ( SELECT schemaname ,objectname ,usename ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS sel ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS ins ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS upd ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS del ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'alter') AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS alt ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS ref FROM ( SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables UNION SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views ) AS objs ,(SELECT * FROM pg_user) AS usrs ORDER BY fullobj ) WHERE (sel = true or ins = true or upd = true or del = true or ref = true) and objectname = 'd_app_map_group' and usename='fpradmin'; |
| Check connections uses ssl or not | select pg_ssl.pid, pg_ssl.ssl, pg_ssl.version, pg_sa.backend_type, pg_sa.usename, pg_sa.client_addr from pg_stat_ssl pg_ssl join pg_stat_activity pg_sa on pg_ssl.pid = pg_sa.pid; |
Follow Me!!!