Pearl DB Service

Setp:1. Bart tool use pg_basebackup utility tool so we can all basebackup configuration (postgresql.conf, pg_hba.conf) files.(you will try basebackup). Setp:2. Normal ssh for postgresql server and backup server Step:3. Yum install edb-bart Step:4. bart default path : /usr/edb/bart/bin            ----- à bart tool path /usr/edb/bart/etc            ----- à configuration Path Step:5. Go to bart configcuration path and change bart.cfg.sample file rename to bart.cfg Step:6. edit bart.cfg file [BART] bart_host= enterprisedb@192.168.1.202 backup_path = /opt/backups pg_basebackup_path = /opt/edb/as10/bin/pg_basebackup logfile = /usr/edb/bart/bart.log scanner_logfile = /usr/edb/bart/bart_scanner.log thread_count = 5 [siva] host = 192.168.1.123 port = 5444 user = enterprisedb xlog_method = stream #wal_compression = enabled archive_command = 'cp %p %a/%f' cluster_owner = <cluster_owner> description = "PPAS 96

Benefits of Using Stored Procedures: Transaction control allowing us to COMMIT and ROLLBACK inside procedures. Very helpful for Oracle to PostgreSQL migration, the new procedure functionality can be a significant time saver. As you can see there are a couple of similarities between CREATE FUNCTION and CREATE PROCEDURE so things should be really easy for most end users. In this syntax: First, specify the name of the stored procedure after the CREATE PROCEDURE clause. Next, define a parameter list which is similar to the parameter list of user-defined functions. Then, specify the programming language for the stored procedure such as PLpgSQL and SQL. After that, place the code in the body of the stored procedure after that AS keyword. Finally, use double dollar ($$) to end the stored procedure. CREATE TABLE accounts (     id INT GENERATED BY DEFAULT AS IDENTITY,     name VARCHAR(100) NOT NULL,     balance DEC(15,2) NOT NULL,     PRIMARY KEY(id) ); INSERT INTO accounts(nam

                A PostgreSQL database cursor is a read-only pointer that allows a program, regardless of the language used, to access the result set of a query. This conserves the free memory of the server or machine running the SQL commands when a result set contains a large number of rows. Using cursors to iterate over rows of data also offers more control over the SQL records returned and makes the whole process more efficient. 1.    First, declare a cursor. 2.    Next, open the cursor. 3.    Then, fetch rows from the result set into a target. 4.    After that, check if there is more row left to fetch. If yes, go to step 3, otherwise, go to step 5. 5.    Finally, close the cursor. he following is valid for the  cursor: •    NEXT •    LAST •    PRIOR •    FIRST •    ABSOLUTE count •    RELATIVE count •    FORWARD •    BACKWARD Using DECLARE CURSOR and FETCH test=# BEGIN; BEGIN test=# DECLARE mycur CURSOR FOR     SELECT * FROM t_large WHERE id > 0; DECLARE CURSOR test=# FETCH

Creating a Trigger in PostgreSQL PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs. The following are important points about PostgreSQL triggers − PostgreSQL trigger can be specified to fire Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted) After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed) A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies. If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. The BEFORE, AFTER or INSTEAD OF keyword determines when the trigger actions will be executed relative to the insertion, m

repmgr single and multi server configuration: step 1:   normal physical streaming replication for two servers and add postgresql.conf "wal_log_hints = on"         master data path =/var/lib/pgsql/10/data    port=5432         slave data path = /var/lib/pgsql/10/tada    port=5433         step 2: install repmgr (yum install repmgr10-conrib)         repmgr bin dir=/usr/pgsql-10/bin/ -->   repmgr and repmgrd         repmgr configuration dir=/etc/repmgr/10/repmgr.conf             both postgresql.conf changes parameter for shared_preload_library='repmgr'  //restart both server         step 3: copy repmgr.conf file         cp -rf repmgr.conf repmgr_data.conf        // repmgr_data.conf is a current master repmgr configuration file         cp -rf repmgr.conf repmgr_tada.conf        // repmgr_tada.conf is a current slave repmgr configuration file         step 4: edit master repmgr configuration file         vi repmgr_data.conf              node-id=1              node_

                step 1:   Download 2ndquadrant repo file:          goto http://docs.pgbarman.org/release/2.10/#installation-on-redhatcentos-using-rpm-packages         click 2ndQuadrant Public RPM repository on https://dl.2ndquadrant.com/default/release/site/         and choose version copy repo link and paste for linux os on root user step 2:   barman server: /et/yum.repo.d/         must pgdg repo file, epel repo file and 2ndQuadrant repo file         open pgdg repo file and add the line for end of version link ---> exclude=barman*         example:        [pgdg11]                         name=PostgreSQL 11 for RHEL/CentOS $releasever - $basearch                         baseurl=http://download.postgresql.org/pub/repos/yum/11/redhat/rhel-$releasever-$basearch                         enabled=1                         gpgcheck=1                         gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG                         exclude=barman* step 3:  barman server :