• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL
        6
        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION
        9
        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE
        6
        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE
        16
        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
        3
        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC
        3
        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT
        9
        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS
        6
        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE
        5
        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS
        1
        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE
        1
        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE
        4
        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY
        13
        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING
        5
        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
        11
        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        PostgreSQL Database Backup Tools – Pg_basebackup

        • pg_basebackup is used to take base backups of a running PostgreSQL database cluster. These are taken without affecting other clients to the database.
        • These can be used For Point-in-time recovery,Streaming replication standby servers
        • Backups are always taken of the entire database cluster
        • It is not possible to back up individual databases or database objects.
        • Individual database backups, a tool such as pg_dump must be used.
        • pg_basebackup can make a base backup from not only the master but also the standby. To take a backup from the standby

        some limitations in an online backup from the standby:-

        • backup history file is not created
        • If the standby is promoted to the master during online backup, the backup fails

        Some Important Options in Pg_basebackup:-

        -D (OR)–pgdata —> pg_basebackup will create the directory and any parent directories if necessary Or Destinatioon Location
        -F (OR)–format —>Selects the format for the output.
        Format can be one of the following:

              p —> plain text(default)
          t —> tar format

        –xlogdir  ––> Specifies the location for the transaction log directory.
        -x  (or) –xlog       —> Using this option is equivalent of using -X with method fetch.
        X  (or) –xlog-method —>Includes the required transaction log files (WAL files) in the backup.

        The following methods for collecting the transaction logs are supported:
           f  fetch —>The transaction log files are collected at the end of the backup. Therefore, it is necessary for the wal_keep_segments parameter to be set high enough that the log is not removed before the end of the backup. If the log has been rotated when it’s time to transfer it, the backup will fail and be unusable.
          s stream —>Stream the transaction log while the backup is created. This will open a second connection to the server and start streaming the transaction log in parallel while running the backup. Therefore, it will use up two connections configured by the max_wal_senders parameter. As long as the client can keep up with transaction log received, using this mode requires no extra transaction logs to be saved on the master.

        -z (or) –gzip —> Enables gzip compression of tar file output, with the default compression level. Compression is only available when using the tar format.
        -Z (or) –compress   —> Enables gzip compression of tar file output, and specifies the compression level (0 through 9, 0 being no compression and 9 being best compression). Compression is only available when using the tar format.
         -l (or) –label  —> Sets the label for the backup. If none is specified, a default value of “pg_basebackup base backup” will be used.
        -P(caps letter) (or) –progress —> Estimat target size without WAL
        -v (or) –verbose —> show the exact file name that is currently being processed if progress reporting is also enabled.
        -d (or) –dbname  —>Database name
        -p (or) –port —> Database port number
        -U (or) –username  —> User name to connect as.
        -h (or) –host    —-> Specifies the host name
        -? (or) –help —> Show help about pg_basebackup command line arguments, and exit.

        Step By Step Backup And Restore Postgres Database Using Pg_basebackup Utility Perform Remote And Local System

        Prerequest:-
        Step 1.Check The Server what Version And what type of Linux We Are Using ,here ‘nijam’ is the hostname

        nijam:~$ lsb_release -a
        No LSB modules are available.
        Distributor ID: Debian
        Description:    Debian GNU/Linux 8.2 (jessie)
        Release:        8.2
        Codename:       jessie
        
        postgres@nijam:~$ psql -A -t -c "select version()"
        PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

        Step 2. Configure database server &Increase upper limit of the number of simultaneously running Write Ahead Log sender processes.
        Set wal_level to at least archive degree

        nijam:~$ sudo sed -i -e "s/^#wal_level = minimal/wal_level = archive/" /etc/postgresql/9.4/main/postgresql.conf
        nijam:~$ sudo sed -i -e "s/^#max_wal_senders = 0/max_wal_senders = 4/" /etc/postgresql/9.4/main/postgresql.conf

        Step 3.Create database role which role is allowed to carry out replication backup mode.

        postgres@nijam:~$ psql -c "CREATE ROLE rep REPLICATION LOGIN ENCRYPTED PASSWORD 'password';"

        Step 4.Grant the permission to “postgres” user to perform “local” backup.

        nijam:~$ cat << EOF | sudo tee -a /etc/postgresql/9.4/main/pg_hba.conf
        local   replication     postgres                                peer
        EOF

        Step 5.Grant the permission to “rep” role to perform remote “database” backup.

        nijam:~$ cat << EOF | sudo tee -a /etc/postgresql/9.4/main/pg_hba.conf
        host    replication     rep              0.0.0.0/0              md5
        EOF

        Step 6.Restart PostgreSQL service to apply changes.

        postgres@nijam:~$ pg_ctlcluster 9.4 main restart

        Postgres pgbasebackup:-
        Step 1.Create ready to restore local backup as “postgres” user and store it inside “pgbackup” directory.

        postgres@nijam:~$ pg_basebackup -x -D pgbackup

        Step 2.List the Backup Summary of “pgbackup” directory

        postgres@nijam:~$ ls pgbackup/
        $ ls -l pgbackup/
        total 76
        -rw------- 1 postgres postgres  208 Jan 22 03:02 backup_label
        drwx------ 5 postgres postgres 4096 Jan 22 03:02 base
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 global
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_clog
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_dynshmem
        drwx------ 4 postgres postgres 4096 Jan 22 03:02 pg_logical
        drwx------ 4 postgres postgres 4096 Jan 22 03:02 pg_multixact
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_notify
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_replslot
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_serial
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_snapshots
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_stat
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_stat_tmp
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_subtrans
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_tblspc
        drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_twophase
        -rwx------ 1 postgres postgres    4 Jan 22 03:02 PG_VERSION
        drwx------ 3 postgres postgres 4096 Jan 22 03:02 pg_xlog
        -rwx------ 1 postgres postgres   88 Jan 22 03:02 postgresql.auto.conf

        Step 3.Just check the information of backup_label in above backup directory

        postgres@nijam:~$ cat pgbackup/pgbackup/backup_label
        START WAL LOCATION: 0/2D000028 (file 00000001000000000000002D)
        CHECKPOINT LOCATION: 0/2D000028
        BACKUP METHOD: streamed
        BACKUP FROM: master
        START TIME: 2016-01-22 03:02:47 CET
        LABEL: pg_basebackup base backup

        Step 3.Create ready to restore local backup as postgres user, store it inside “pglabelbackup” directory and define custom label.

        postgres@nijam:~$ pg_basebackup -l "Backup created at $(hostname) on $(date)" -x -D pglabelbackup
        
        postgres@nijam:~$ cat pglabelbackup/base_backup
        START WAL LOCATION: 0/2E000028 (file 00000001000000000000002E)
        CHECKPOINT LOCATION: 0/2E000028
        BACKUP METHOD: streamed
        BACKUP FROM: master
        START TIME: 2016-01-22 03:04:14 CET
        LABEL: Backup created at nijam on Fri Jan 22 03:04:14 CET 2016

        Step 4.Create ready to restore backup from remote server using rep role with defined replication attribute, store it inside “pgremotebackup” directory.

        postgres@nijam:~$ pg_basebackup -x -h 100.0.2.150 -U rep -D pgremotebackup

        Step 5.Create ready to restore backup using two connections in parallel (to stream the transaction log while the backup is created) from remote server using rep role with defined replication attribute, store it inside pgremsbackup directory.

        postgres@nijam:~$ pg_basebackup -X stream -h 100.0.2.150 -U rep -D pgremsbackup

        Step 6.Create ready to restore gzipped tar backup from remote server using rep role with defined replication attribute, store it inside “pgremotetarbackup” directory.

        postgres@nijam:~$ pg_basebackup -x -Ft -z -h 100.0.2.150 -U rep -D pgremotetarbackup

        Step 6.List the backup summary of “pgremotetarbackup” directory

        postgres@nijam:~$ ls -l pgremotetarbackup/
        total 2396
        -rw-r--r-- 1 postgres postgres 2453147 Jan 22 03:11 base.tar.gz

        Postgres pgbasebackup Recovery :-
        Step 1.Stop database server.

        postgres@nijam:~$ pg_ctlcluster 9.4 main stop

        Step 2.Backup current database files.

        postgres@nijam:~$ mv main Lastmain.backup

        Step 3.Copy archived database files to local location.

        postgres@nijam:~$ cp -r /pgbackup main

        Step 4.Start the database server to perform recovery.

        postgres@nijam:~$ pg_ctlcluster 9.4 main start

        Postrequest:-
        Step 1.Monitor recovery process using log file.

        postgres@nijam:~$ tail -f /var/log/postgresql/postgresql-9.4-main.log

        Step 2.Check our postgres database whether recovery mode or not

        select * from pg_is_in_recovery();

        Prev PostgreSQL Database Backup Tools – Pg_dumpall
        Next PostgreSQL COPY data From Text File

        Leave A Reply Cancel reply

        You must be logged in to post a comment.

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft


        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres
        • postgres script for finding queries more than 1 hours ?

        2ndquadrant.in

        (+91) 8838953252

        ITsupport@rayafeel.com

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Postgres Migration Blogs
        • Forum

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now

        Modal title

        Message modal