SRI SRI's Postgresql Knowledge

#Command to find the size of all Databases :  postgres=# SELECT datname,pg_size_pretty(pg_database_size(datname)) FROM pg_database;      datname     | pg_size_pretty -----------------+----------------  template1            | 6393 kB  template0            | 6385 kB  postgres               | 6501 kB  test1                     | 6517 kB  testdb1                  | 6393 kB  mydb                    | 6501 kB  foo                        | 6501 kB  example_backups | 6517 kB # Command to find the size of all tablespaces :  postgres=# SELECT spcname, pg_size_pretty(pg_tablespace_size(spcn...

I.Oracle Tools equivalence to postgresql :- 1.SQLPLUS : PSQL but much more 2.TOAD / Oracle SQL Developper : TORA or pgAdmin 3.EXPLAIN PLAN : EXPLAIN ANALYZE 4.ANALYZE TABLE : ANALYZE 5.Cold backup : both are file system backup 6.Hot backup : REDOLOGS = ARCHIVELOGS 7.Logical Export : exp = pg_dump 8.Logical Import : imp = pg_restore or psql 9.SQL Loader : pgLoader 10.RMAN : Barman or Pitrery 11.AUDIT TRAIL : pgAudit 12.Pooling / Dispatcher : – PgBouncer – PgPool 13.Active Data Guard : – PostgreSQL master / slave replication – Slony 14.Replication master / master : – PostgreSQL-XC – Bucardo 15.Logical replication : – PostgreSQL 9.5 / 10 – Slony 16.RAC Horizontal scaling : PostgreSQL-XC – PostgreSQL-XL - plProxy, pg_shard 17.Oracle => Postgres Plus Advanced Server – Same as PostgreSQL but with proprietary code and database feature compatibility for Oracle. – Compatible with applications written for Oracle. – No need to rewrite PL/SQL into PLPGSQL – Applica...

When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The other processes that the postgres process starts are responsible for the ongoing work of the database.  PostgreSQL`s architecture is based on a client/server communication. In the server side there is a server process that manages the files, database operations, connection and so on.. On the client side there is a tool/program which can be a graphical tool (gui)  like pgAdmin or a command line tool(cli) like psql  that is repnsible for connection the database and all...