Category: PostgreSQL
-
About Us
At CLOTA Technology, we provide innovative solutions in database technologies, web development, cloud maintenance, and software support to help businesses achieve their goals. Our services include expert SQL and NoSQL database design, implementation, and maintenance with certified DBAs boasting 15+ years of experience. We specialize in custom website and app creation, cloud services for migrations,…
//
-
Deadlock
Deadlock is a situation where two or more transactions wait for completion of each-other for resources but none of them completes. or Deadlock occur between the transactions when 2 or more translations permanently block each-other in such a ways that each transaction having a lock on the resource where the other tasks are trying to…
//
-
Delete vs Truncate
Its a common interview question which people like to ask to know your understanding on RDMS concepts. I too few times encountered the question in interviews. Pleases don’t ask me interview result. Haha ha… DELETE: 1. DELETE is a DML Command. 2. DELETE statement is executed using a row lock, each row in the table…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL PostgreSQL SQL Server
Pages in Database
Boot Page Every DB has only one boot page. Boot page stores DB configuration information. It can find in page # 9 of the first DB data file. Bulk Changed Map (BCM) Page BCM page keep track of the extends which has modified by bulked-logged operations. Log backup thread reads this page and get the extends…
//
-
Phases of Recovery in Database
At the time of SQL instance reboot or start, database goes in the three phases of recovery… 1. Analysis Phase of recovery 2. Redo Phase of recovery 3. Undo Phase of recovery 1. Analysis Phase of recovery This phase does two things… First, construct a Dirty Page Table (DPT) Second, construct an Active Transaction Table. Dirty Page Table consist…
//
-
DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA Monitoring My SQL Performance Tuning PostgreSQL SQL Server
SQL Server Memory Architecture
There are four major components of SQL memory architecture… 1. Memory Broker2. Memory Object3. Memory Clerk4. Memory Node In short, BOCN. (For remember it “Boy Of Cartoon-Network”) 1. Memory Broker Purpose of Memory Broker is to provide a centralized mechanism to distribute/control memory the allocation made by each component in SQL Server. Note: Memory brokers do…
//
-
DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL PostgreSQL SQL Server
Mirroring Witness getting disappear from Mirroring Setup…
Recently we have faced one issue where DB mirroring witness getting disappear from mirroring setup. This is happening once Principal get down or SQL services restarts. In the case, we found DB fail-over is also is not working. Once the Principal server come online the DB start serving role as Principal DB and get in…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL Performance Tuning PostgreSQL SQL Server
Ghost Records
When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records. SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically…
//
-
PostgreSQL Backup
pg_basebackup is used to take base backups of a running PostgreSQL database cluster. We can take hot-backup and the backup can be used for… 1. point-in-time recovery as the starting point for a log shipping or 2. streaming replication standby servers It makes a binary copy of the database cluster files, while making sure the system is…
//
-
Streaming Replication Step by step notes
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD ‘******’; echo “host replication replicator 192.168.0.126/32 md5” >> pg_hba.conf \ /u01/app/postgres/bin/psql -c “select pg_reload_conf()” /u01/app/postgres/pgsql/bin/pg_basebackup -h 192.168.0.117 -U replicator -p 5432 -D /u05/pgsql/database -Fp -Xs -P -R NOTE:- In my case, I have installed PostgreSQL (PostgreSQL 12) in /u01/app/postgres/pgsql/bin/ directory and data directory is /u05/pgsql/database
//
-
CAP Theorem
In theoretical computer science, the CAP theorem, also named Brewer’s theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees: Consistency Every read receives the most recent write or an error. Availability Every request receives a (non-error)…
//