Pearl DB Service

OPERATORS: An operator performs on separate data items and returns a result. The data items are called operands or arguments. Operators are mentioned by special characters or by keywords. Arithmetic operators Comparison operators Logical operators Bitwise operators Arithmetic operators: + addition - subtraction * multiplication / division % modulo (remainder) examples: pearl=# select 10+5;  ?column? ----------        15 (1 row) pearl=# select 10-5;  ?column? ----------         5 (1 row) pearl=# select 10*5;  ?column? ----------        50 (1 row) pearl=# select 10/5;  ?column? ----------         2 (1 row) pearl=# select 10%5;  ?column? ----------         0 (1 row) Comparison operators: < less than > greater than <= less than or equal to >= greater than or equal to = equal <> or != not equal examples: pearl=# select * from emp ;  id |  name   | salary ----+---------+--------   1 | muthu   |   500

CONSTRAINTS: constraints are used to specify rules for the data in a table. Check Constraints Not-Null and Null Constraints Unique Constraints Primary Keys Foreign Keys Exclusion Constraints Check Constraints: The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and is not entered into the table. example 1: CREATE TABLE muthu (     id integer,     name text,     age numeric CHECK (age > 18) ); example 2: CREATE TABLE muthu (     id integer,     name text,     age numeric CONSTRAINT constraint_name CHECK (age > 0) ); Not-Null and Null Constraints: A not-null constraint simply specifies that a column must not assume the null value. example : CREATE TABLE muthu (     id integer NOT NULL,     name text NULL,     age numeric CHECK (age > 18) ); Unique Constraints: Unique constraints ensure that the data contained in a column, or a

Streaming Replication: a synchronous replication synchronous replication 1.asynchronous replication Primary Node(192.168.1.201) //install bin and cluster(data) path Standby Node (192.168.1.202)    // only install bin path step 1: ssh for both servers primary node: su - postgres ssh-keygen standby node: su - postgres ssh-keygen primary node: cd .ssh ssh-copy-id postgres@192.168.1.202 standby node: cd .ssh cat id_rsa.pub >> autheraized_keys scp autheraized_keys postgres@192.168.1.201:/var/lib/pgsql/.ssh step 2:primary server configuration: cd /var/lib/pgsql/11/data vi pg_hba.conf # TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix domain socket connections only local   all             all                                     peer # IPv4 local connections: host    all             all             192.168.1.201/32        trust host    all             all             

Postgresql Index: HEAP: Storage area for storing the whole row of the table. This is divided into multiple pages (as shown in the above picture) and each page size is by default 8KB. Within each page, each item pointer (e.g. 0,1, 2, ….) points to data within the page. Index Storage: This storage stores only key values i.e. columns value contained by index. This is also divided into multiple pages and each page size is by default 8KB. Tuple Identifier (TID): TID is 6 bytes number which consists of two parts. The first part is 4-byte page number and remaining 2 bytes tuple index inside the page. The combination of these two numbers uniquely points to the storage location for a particular tuple. ======================================================================== postgresql index types: ======================= B-tree  Hash  GiST  sp-Gist  GIN  BRIN ============================================= B-Tree Index: B-Tree is the default and the most commonly u

How to Setup Base Backup go to data directory step1: pg_hba.conf ipv4 and repliaction entery  vi pg_hba.conf # TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix domain socket connections only local   all             all                                     peer # IPv4 local connections: host    all             all             192.168.1.221/32        trust host    all             all             127.0.0.1/32            ident # IPv6 local connections: host    all             all             ::1/128                 ident # Allow replication connections from localhost, by a user with the # replication privilege. local   replication     all                                     peer host    replication     all             192.168.1.222/32        trust step2: vi postgresql.conf listern_address=* archive_mode=on archive_command='cp %p /arch/%f' wal_level=replica full_page_writes=on

2.Logical Backup dump backup   dumpall backup  //full backup 1.Dump Backup use pg_dump Utility: su - postgres cd /usr/pgsql-11/bin pg_dump Options: -h    hostname -p    port number -d    database name -t     table name -F    format         p   plain format  (filename=filename.sql)         c   custom format (filename=filename.dump or filename.dmp)         t    tar format  (filename=filename.tar)         d   directory format (filename=filename) ./pg_dump -h localhost -p 5432 -d postgres -t emp -Fp -f /backup/emp.sql Dump Restore: Plain format Restore: ./psql -h localhost -p 5432 -d db2 -f /backup/emp.sql     //plain Custom, tar, directory format Restore: ./pg_restore -h localhost -p 5432 -d db2 -f /backup/emp.dump  //c,t,d 2.Dump All Backup use pg_dumpall Utility: su - postgres cd /usr/pgsql-11/bin Backup Command: ./pg_dumpall -h 192.168.1.21 -p 5432 -f /backup/emp.sql Restore Command: ./psql -h localhost -p 543

1.Full Backup physical backup logical backup base backup 2.Incremetal Backup 1.differential incremental backup  (bart) - edb 2.cumulative incremental backup    (barman)-opensource  1.Physical Backup   //full backup only 1.soft or hold backup    server down time cp -rf /var/lib/pgsql/11/data /backup/bk_1             soft or hold backup restore:             su - postgres             /usr/pgsql-11/bin/pg_ctl -D /backup/bk_1 start 2.hard or hot backup server running postgres=#select pg_start_backup('bk_1'); show lsn cp -rf /var/lib/pgsql/11/data /backup/bk_1 postgre=#select pg_stop_backup(); show lsn            hard or hot backup restore:             su - postgres             /usr/pgsql-11/bin/pg_ctl -D /backup/bk_1 start