PostgreSQL Barman configuration and commands

-
               

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 : 

   yum install barman (root user)
 
   /usr/bin/barman  (bin path)
   /etc/barman.conf (barman configuration file)
   /et/barman.d/  (server configuration path)

step 4: barman server:(make ssh for both machine)

  ssh-keygen
        cd .ssh
        ssh-copy-id postgres@192.168.1.21
  postgres server:
       su - postgres
       ssh-keygen
       cd .ssh
       cat id_rsa.pub >> autorized_keys
       scp authorized_keys barman@192.168.1.21:/var/lib/barman/.ssh/  

step 5:  PostgreSQL server:  (make base backup settings)

            su - postgres
                cd /usr/pgsql-11/bin/
  
                ./createuser -s -P barman
                ./createuser -P --replication streaming_barman
  
            vi pg_hba.conf
                ipv4:
                    host    postgres    barman  192.168.1.22/32        trust
                replication:
                    host    replication        streaming_barman    192.168.1.22/32        trust
   
   vi postgresql.conf
   listern_address=*
   archive_mode=on
   archive_command='cp %p /arch/%f'
   wal_level=replica
   full_page_writes=on
   max_wal_sender=6
   wal_keep_segement=500
   max_replication_slot=2
   log_statement='all'
 
        restart the server

step 6:  barman server:(create .pgpass file)

        su - barman(var/lib/barman/)
        touch .pgpass
        chmod 600 .pgpass
        vi .pgpass
             192.168.1.21:*:*:barman:password
             192.168.1.21:*:*:streaming_barman:password

step 7:   edit barman configuration file:

        vi /etc/barman.conf        ---> remove the line(;)
            barman_user = barman
            barman_home = /var/lib/barman/backup
            minimum_redundancy = 1
            retention_policy = REDUNDANCY 2
            :wq

step 8:  barman server:(server configuration file create pg1.conf)

            vi /etc/barman.d/pg1.conf
                [pg1]
                description = "db server 1"
                ssh_command = ssh postgres@192.168.1.21
                conninfo = host=192.168.1.162 user=barman dbname=postgres port=5432
                streaming_conninfo = host=192.168.1.21 user=streaming_barman port=5432
                streaming_archiver = on
                slot_name = barman
                archiver = on
                backup_method = rsync
                backup_options = concurrent_backup
                parallel_jobs = 4
                reuse_backup = link
                streaming_archiver_name = barman_receive_wal
                path_prefix = "/usr/pgsql-11/bin"
            chown -R barman:barman pg1.conf

step 9: Run barman tool      

 su - barman

barman list-server
barman check pg1
            ----------------->barman receive-wal --create-slot pg1

 vi postgresql.conf
 set archive_command = 'cp %p /arch11/%f && rsync -a %p barman@192.168.1.162:/var/lib/barman/backup/pg1/incoming/%f'
            how to find barman xlogfilepath--> barman show-server pg1 | grep incoming_wals_directory
            
check backup server:               
           barman check pg1
backup server:
           barman backup pg1
backup list:
        barman list-backup pg1
backup Details:
barman show-backup pg1 backup_id
Restore command:
barman recover --remote-ssh-command "ssh postgres@192.168.1.162" pg1 20200206T151256 /var/lib/pgsql/11/tada
Point-in-time-Recovery command:
barman recover --remote-ssh-command "ssh postgres@192.168.1.162" pg1 20200206T151256 --target-time "2020-02-06 15:34:24.842" /var/lib/pgsql/11/tada
Incremental Backup:(only last full backup)
barman backup --reuse-backup=link pg1    
           

Comments

Post a Comment

Popular posts from this blog

PostgreSQL pg_pool-II Installation and Configuration

-
Image
Pgpool-II Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.  It provides the following features: 1.Connection Pooling:     Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.   2.Load Balancing:     If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server.  It does that by distributing SELECT queries among available servers, improving the system's overall throughput. In an ideal scenario, read performance could improve proportion...
Read more

PostgreSQL Pages and Tuples

-
Image
INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index,...
Read more

PostgreSQL Reporting Tools(Pgbadger) Installation & Configuration

-
PgBadger:                 The PostgreSQL log analyzer “pgBadger” is an open source “fast PostgreSQL log analysis report” program written in Perl that takes the log output from a running PostgreSQL instance and processes it into an HTML file.                  The report it generates shows all information found in a nice and easy to read report format.  These reports can help shed light on errors happening in the system, checkpoint behavior, vacuum behavior, trends, and other basic but crucial information for a PostgreSQL system. FEATURES:  pgBadger reports everything about your SQL queries:         Overall statistics.         The most frequent waiting queries.         Queries that waited the most.         Queries generating the most temporary files.         Queries generating the largest temporar...
Read more