• 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_dump

        • As everything that contains valuable data, PostgreSQL DBs should be backed up regularly.
        • DB backups allow DBs to be restored if a disk drive fails, a table is accidentally dropped, or a DB file is accidentally deleted.
        • The idea behind the SQL-dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the DB in the same state as it was at the time of the dump.

        Dump Individual DBs with pg_dump 
        PostgreSQL provides the utility program pg_dump for dumping individual DBs:

        pg_dump dbname > outfile
        • pg_dump writes its results to the standard output.
        • pg_dump is a regular PostgreSQL client application.
        • This means that you can do this backup procedure from any remote host that has access to the DB.
        • pg_dump does not operate with special permissions. You must have read access to all tables that you want to back up.
        • Large objects (blobs) are not dumped by default,

        Restoring the dump 
        The text files created by pg_dump are intended to be read in by the psql program:

        psql dbname < infile
        •  The DB dbname will not be created by this command, you must create it yourself before.
        • psql and pg_dump support options for controlling the DB server location and the user names.

        PRACTICAL 1:
        Dump the workshop DB in the file “workshop.dump”:

        $> pg_dump workshop >workshop.dump

        –Look at the file “workshop.dump”:

        $> more workshop.dump

        –Create a new DB for restoring:

        $> createdb dump_test

        –Restore the workshop DB into the dump_test DB:

        $> psql dump_test < workshop.dump

        –Connect to the dump_test DB:

        $> psql dump_test

        –The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a DB directly from one server to another, for example:

        pg_dump -h host1 dbname | psql -h host2 dbname

        PRACTICAL 2:
        Tricks for large DBs–>Compressed dumps:

        pg_dump dbname | gzip > filename.gz

        Reload with

        ./createdb dbname
        gunzip -c filename.gz | psql dbname
        
        

        PRACTICAL 3:
        split allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:

        pg_dump dbname | split -b 1m - filename

        Reload with

        createdb dbname
        cat filename* | psql dbname
        
        

        2.Dumping a Complete Cluster with pg_dumpall 
        pg_dumpall is a utility for dumping all PostgreSQL DBs of a cluster into one file. It also dumps global objects that are common to all DBs:

        pg_dumpall > outfile

        The resulting dumps can be restored with psql.
        Example “dump and reload all DBs”:

        $ pg_dumpall >cluster.dump

        Reload the DBs (into an empty cluster space as the server will not accept duplicate DB name) with:

        $ psql -f cluster.dump template1

        File system level backup :
        An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database cluster with whatever method you prefer for doing file system backups, for example:

        tar -cf backup.tar /home/fred/databases/postgresql/data
        • The database server must be shut down before.
        • It will not work to restore only certain individual tables or databases from their respective files or directories, because the information contained in these files must be combined with the commit log files pg_clog/*, which contain the commit status of all transactions.
        • The file system backup will likely be larger than an SQL dump, because a pg_dump does not need to dump the contents of indexes for example, just the commands to recreate them.
        pg_dump -t MyTable mydb > db.sql
        -bash-3.2$ ./pg_dump -U sss1 -c -f /u02/spl_bkp/sample_bakcup sssdb
        Password:
        
        [root@asmhost u02]# cd spl_bkp/
        [root@asmhost spl_bkp]# ls
        sample_bakcup
        [root@asmhost spl_bkp]# cat sample_bakcup 
        --
        -- EnterpriseDB database dump
        --
        SET statement_timeout = 0;
        SET client_encoding = 'UTF8';
        SET standard_conforming_strings = on;
        SET check_function_bodies = false;
        SET client_min_messages = warning;
        
        DROP EXTENSION edb_dblink_oci;
        DROP EXTENSION edb_dblink_libpq;
        DROP EXTENSION plpgsql;
        DROP SCHEMA public;
        --
        -- Name: public; Type: SCHEMA; Schema: -; Owner: enterprisedb
        --
        CREATE SCHEMA public;
        ALTER SCHEMA public OWNER TO enterprisedb;
        --
        -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: enterprisedb
        --
        
        COMMENT ON SCHEMA public IS 'Standard public schema';
        --
        -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
        --
        CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
        --
        -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
        --
        COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
        --
        -- Name: edb_dblink_libpq; Type: EXTENSION; Schema: -; Owner: 
        --
        CREATE EXTENSION IF NOT EXISTS edb_dblink_libpq WITH SCHEMA pg_catalog;
        
        --
        -- Name: EXTENSION edb_dblink_libpq; Type: COMMENT; Schema: -; Owner: 
        --
        COMMENT ON EXTENSION edb_dblink_libpq IS 'EnterpriseDB Foreign Data Wrapper for PostgreSQL';
        --
        -- Name: edb_dblink_oci; Type: EXTENSION; Schema: -; Owner: 
        --
        CREATE EXTENSION IF NOT EXISTS edb_dblink_oci WITH SCHEMA pg_catalog;
        --
        -- Name: EXTENSION edb_dblink_oci; Type: COMMENT; Schema: -; Owner: 
        --
        COMMENT ON EXTENSION edb_dblink_oci IS 'EnterpriseDB Foreign Data Wrapper for Oracle';
        --
        -- Name: public; Type: ACL; Schema: -; Owner: enterprisedb
        --
        REVOKE ALL ON SCHEMA public FROM PUBLIC;
        REVOKE ALL ON SCHEMA public FROM enterprisedb;
        GRANT ALL ON SCHEMA public TO enterprisedb;
        GRANT ALL ON SCHEMA public TO PUBLIC;
        --
        -- EnterpriseDB database dump complete
        --
        sssdb=# create database sssnew;
        CREATE DATABASE
        
        
        -bash-3.2$ ./psql -d sssnew -U sss1 -f /u02/spl_bkp/sample_bakcup 
        Password for user sss1: 
        SET
        SET
        SET
        SET
        SET
        DROP EXTENSION
        DROP EXTENSION
        psql:/u02/spl_bkp/sample_bakcup:13: ERROR:  cannot drop extension plpgsql because other objects depend on it
        DETAIL:  function edbreport(bigint,bigint) depends on language plpgsql
        function statio_tables_rpt(integer,integer,integer,text) depends on language plpgsql
        function statio_indexes_rpt(integer,integer,integer,text) depends on language plpgsql
        function stat_tables_rpt(integer,integer,integer,text) depends on language plpgsql
        function stat_indexes_rpt(integer,integer,integer,text) depends on language plpgsql
        function stat_db_rpt(integer,integer) depends on language plpgsql
        function sesshist_rpt(integer,integer) depends on language plpgsql
        function sessid_rpt(integer,integer,integer) depends on language plpgsql
        function sess_rpt(integer,integer,integer) depends on language plpgsql
        function sys_rpt(integer,integer,integer) depends on language plpgsql
        function truncsnap() depends on language plpgsql
        function purgesnap(integer,integer) depends on language plpgsql
        function get_snaps() depends on language plpgsql
        function edbsnap() depends on language plpgsql
        function edb_get_sys_info() depends on language plpgsql
        function connectby_cyclecheck(anyarray,anyelement) depends on language plpgsql
        function convertargdir("char"[],smallint) depends on language plpgsql
        view pg_function depends on function convertargdir("char"[],smallint)
        function new_time(timestamp without time zone,character varying,character varying) depends on language plpgsql
        function showseq(oid) depends on language plpgsql
        view user_sequences depends on function showseq(oid)
        view all_sequences depends on function showseq(oid)
        view dba_sequences depends on function showseq(oid)
        HINT:  Use DROP ... CASCADE to drop the dependent objects too.
        DROP SCHEMA
        CREATE SCHEMA
        ALTER SCHEMA
        COMMENT
        CREATE EXTENSION
        COMMENT
        CREATE EXTENSION
        COMMENT
        CREATE EXTENSION
        COMMENT
        REVOKE
        REVOKE
        GRANT
        GRANT

        check the database

        sssdb=# \l
                                                List of databases
           Name    |    Owner     | Encoding |   Collate   |    Ctype    |       Access privileges       
        -----------+--------------+----------+-------------+-------------+-------------------------------
         edb       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
         sssdb     | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
         sssnew    | sss1         | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
        -bash-3.2$ ./pg_dump -U sss1 -Fc -f /u02/spl_bkp/level2_bkp sssdb
        Password: 
        -bash-3.2$
        -bash-3.2$ ./pg_restore -l /u02/spl_bkp/level2_bkp 
        ;
        ; Archive created at Mon Nov  5 14:32:53 2012
        ;     dbname: sssdb
        ;     TOC Entries: 12
        ;     Compression: -1
        ;     Dump Version: 1.12-0
        ;     Format: CUSTOM
        ;     Integer: 4 bytes
        ;     Offset: 8 bytes
        ;     Dumped from database version: 9.1.2.2
        ;     Dumped by pg_dump version: 9.1.2.2
        ;
        ;
        ; Selected TOC Entries:
        ;
        3476; 1262 40964 DATABASE - sssdb enterprisedb
        5; 2615 2200 SCHEMA - public enterprisedb
        3477; 0 0 COMMENT - SCHEMA public enterprisedb
        3478; 0 0 ACL - public enterprisedb
        303; 3079 12456 EXTENSION - plpgsql 
        3479; 0 0 COMMENT - EXTENSION plpgsql 
        302; 3079 13457 EXTENSION - edb_dblink_libpq 
        3480; 0 0 COMMENT - EXTENSION edb_dblink_libpq 
        301; 3079 13460 EXTENSION - edb_dblink_oci 
        3481; 0 0 COMMENT - EXTENSION edb_dblink_oci 
        -bash-3.2$
        -bash-3.2$ ./pg_dumpall --help
        pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.
        
        Usage:
          pg_dumpall [OPTION]...
        
        General options:
          -f, --file=FILENAME         output file name
          --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock
          --help                      show this help, then exit
          --version                   output version information, then exit
        
        Options controlling the output content:
          -a, --data-only             dump only the data, not the schema
          -c, --clean                 clean (drop) databases before recreating
          -g, --globals-only          dump only global objects, no databases
          -o, --oids                  include OIDs in dump
          -O, --no-owner              skip restoration of object ownership
          -r, --roles-only            dump only roles, no databases or tablespaces
          -s, --schema-only           dump only the schema, no data
          -S, --superuser=NAME        superuser user name to use in the dump
          -t, --tablespaces-only      dump only tablespaces, no databases or roles
          -x, --no-privileges         do not dump privileges (grant/revoke)
          --binary-upgrade            for use by upgrade utilities only
          --column-inserts            dump data as INSERT commands with column names
          --disable-dollar-quoting    disable dollar quoting, use SQL standard quoting
          --disable-triggers          disable triggers during data-only restore
          --inserts                   dump data as INSERT commands, rather than COPY
          --no-security-labels        do not dump security label assignments
          --no-tablespaces            do not dump tablespace assignments
          --no-unlogged-table-data    do not dump unlogged table data
          --quote-all-identifiers     quote all identifiers, even if not key words
          --use-set-session-authorization
                                      use SET SESSION AUTHORIZATION commands instead of
                                      ALTER OWNER commands to set ownership
        
        Connection options:
          -h, --host=HOSTNAME      database server host or socket directory
          -l, --database=DBNAME    alternative default database
          -p, --port=PORT          database server port number
          -U, --username=NAME      connect as specified database user
          -w, --no-password        never prompt for password
          -W, --password           force password prompt (should happen automatically)
          --role=ROLENAME          do SET ROLE before dump
        
        If -f/--file is not used, then the SQL script will be written to the standard
        output.
        
        bash-3.1$ ./pg_dump -p 5544 -f '/u01/postgre/t1.sql' -t t1 gopaldb
        -bash-3.1$ vi /u01/postgre/t1.sql 
        -bash-3.1$
        More Ref :
        • PostgreSQL Multile Schema Backup & Restore,Backup Script,Restoring Script,Backup & Restore Prerequest & PostRequest
        • Taking PostgreSQL Basebackup script
        • How can I take only PostgreSQL functions & procedure backup
        • PostgreSQL Daily backup automated shell script
        Prev PostgreSQL Backup & Recovery Overview
        Next PostgreSQL Database Backup Tools – Pg_dumpall

        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

        • thought behind whiteboard activity
        • 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

        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