PostgreSQL Database Backup Tools – Pg_dumpall

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

–Backup the whole cluster of postgres database and  give superuser password 10 time

-bash-3.2$ ./pg_dumpall -W > /opt/PostgresPlus/9.1AS/data/all.sql
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:

-bash-3.2$ pwd
/opt/PostgresPlus/9.1AS/bin
-bash-3.2$ cd ../data

–List the backup file and read the the filie using tail -2

-bash-3.2$ ls -lrt|tail -2
-rw-r--r--  1 enterprisedb enterprisedb 102468 Nov  9 14:27 all.sql
drwx------  2 enterprisedb enterprisedb   4096 Nov  9 14:28 pg_stat_tmp
--
-- PostgreSQL database cluster dump
--
\connect edb
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-- Roles
--
CREATE ROLE enterprisedb;
ALTER ROLE enterprisedb WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md5517f3a577a9141b5d875bafafa5c686c';
CREATE ROLE group1;
ALTER ROLE group1 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION;
CREATE ROLE lk;
ALTER ROLE lk WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION PASSWORD 'md57786f458677c72747a6fa1a3c7effff1';
CREATE ROLE rock;
ALTER ROLE rock WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5f4ba7703a0af45acbde31b65c19ef897';
CREATE ROLE sss1;
ALTER ROLE sss1 WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md5b75db5a4df2ab1c8fce185e756578b62';
---- Role memberships
--
GRANT group1 TO enterprisedb GRANTED BY sss1
GRANT group1 TO sss1 GRANTED BY sss1;
--
-- Tablespaces
CREATE TABLESPACE tbs1 OWNER sss1 LOCATION '/u02/data';
--
-- Database creation
--
CREATE DATABASE era WITH TEMPLATE = template0 OWNER = lk;
REVOKE ALL ON DATABASE era FROM PUBLIC;
REVOKE ALL ON DATABASE era FROM lk;
GRANT ALL ON DATABASE era TO lk;
GRANT CONNECT,TEMPORARY ON DATABASE era TO PUBLIC;
GRANT ALL ON DATABASE era TO rock;
CREATE DATABASE etl WITH TEMPLATE = template0 OWNER = lk;
CREATE DATABASE ipl WITH TEMPLATE = template0 OWNER = enterprisedb;
CREATE DATABASE jk WITH TEMPLATE = template0 OWNER = rock;
CREATE DATABASE lol WITH TEMPLATE = template0 OWNER = rock;
CREATE DATABASE sssdb WITH TEMPLATE = template0 OWNER = sss1;
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM enterprisedb;
GRANT ALL ON DATABASE template1 TO enterprisedb;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
CREATE DATABASE wwe WITH TEMPLATE = template0 OWNER = rock;
--
-- 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;
--
-- Name: edb; Type: COMMENT; Schema: -; Owner: enterprisedb
--
COMMENT ON DATABASE edb IS 'default administrative connection database';
--
-- Name: _edb_scheduler; Type: SCHEMA; Schema: -; Owner: enterprisedb
--
CREATE SCHEMA _edb_scheduler;
ALTER SCHEMA _edb_scheduler OWNER TO enterprisedb;
CREATE TABLESPACE tbs1 OWNER sss1 LOCATION '/u02/data';
--
-- Database creation
--
CREATE DATABASE era WITH TEMPLATE = template0 OWNER = lk;
REVOKE ALL ON DATABASE era FROM PUBLIC;
REVOKE ALL ON DATABASE era FROM lk;
GRANT ALL ON DATABASE era TO lk;
GRANT CONNECT,TEMPORARY ON DATABASE era TO PUBLIC;
GRANT ALL ON DATABASE era TO rock;
CREATE DATABASE etl WITH TEMPLATE = template0 OWNER = lk;
CREATE DATABASE ipl WITH TEMPLATE = template0 OWNER = enterprisedb;
CREATE DATABASE jk WITH TEMPLATE = template0 OWNER = rock;
CREATE DATABASE lol WITH TEMPLATE = template0 OWNER = rock;
CREATE DATABASE sssdb WITH TEMPLATE = template0 OWNER = sss1;
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM enterprisedb;
GRANT ALL ON DATABASE template1 TO enterprisedb;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
CREATE DATABASE wwe WITH TEMPLATE = template0 OWNER = rock;

\connect edb
-
-- 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;
--
-- Name: edb; Type: COMMENT; Schema: -; Owner: enterprisedb
--
COMMENT ON DATABASE edb IS 'default administrative connection database';
--
-- Name: _edb_scheduler; Type: SCHEMA; Schema: -; Owner: enterprisedb
--
CREATE SCHEMA _edb_scheduler;
ALTER SCHEMA _edb_scheduler OWNER TO enterprisedb;
--
-- Name: dbms_job_procedure; Type: SCHEMA; Schema: -; Owner: enterprisedb
--
CREATE SCHEMA dbms_job_procedure;
ALTER SCHEMA dbms_job_procedure OWNER TO enterprisedb;
--
-- Name: SCHEMA dbms_job_procedure; Type: COMMENT; Schema: -; Owner: enterprisedb
--
COMMENT ON SCHEMA dbms_job_procedure IS 'dbms_job what procedures';
--
-- Name: enterprisedb; Type: SCHEMA; Schema: -; Owner: enterprisedb
--
CREATE SCHEMA enterprisedb;
ALTER SCHEMA enterprisedb OWNER TO enterprisedb;
--
-- Name: pgagent; Type: SCHEMA; Schema: -; Owner: enterprisedb
ALTER SCHEMA enterprisedb OWNER TO enterprisedb;
--
-- Name: pgagent; Type: SCHEMA; Schema: -; Owner: enterprisedb
--
CREATE SCHEMA pgagent;
ALTER SCHEMA pgagent OWNER TO enterprisedb;
--
-- Name: SCHEMA pgagent; Type: COMMENT; Schema: -; Owner: enterprisedb
--

COMMENT ON SCHEMA pgagent IS 'pgAgent system tables';


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
COMMENT ON EXTENSION edb_dblink_oci IS 'EnterpriseDB Foreign Data Wrapper for Oracle';
SET search_path = dbms_job_procedure, pg_catalog, sys;
--
-- Name: run_job(integer, boolean); Type: PROCEDURE; Schema: dbms_job_procedure; Owner: enterprisedb
--
CREATE OR REPLACE PROCEDURE run_job(job integer, manual boolean DEFAULT false) AUTHID CURRENT_USER IS
     $__sys__$
DECLARE
        next_date timestamp;
        err text;
        errstate text;
        jobowner text;
        jobownerId oid;
        whatProName text;
        jobStartDate TIMESTAMP;
BEGIN
     errstate := NULL;
        jobStartDate := clock_timestamp::timestamp;

    -- Fetch the job owner name and OID.
    SELECT jobloguser, pg_catalog.pg_authid.oid INTO jobowner, jobownerId
          FROM pgagent.pga_job, pg_catalog.pg_authid
          WHERE jobid = job
          AND jobjclid = (SELECT jclid FROM pgagent.pga_jobclass WHERE jclname = 'DBMS_JOB')
          AND rolname = jobloguser
          FOR SHARE;
        BEGIN
                -- 1) Validate that the 'what' procedure is sane and safe to invoke:

                -- Check that the procedure is a
                --   security definer procedure
                --   owned by the job owner
                --   has no arguments (they could have malicious DEFAULT expressions)
                -- The pg_proc entry is locked (FOR SHARE) sto avoid the race condition
                -- where the user could swap the original procedure with malicious one
                -- just after we've run the check but before it's executed.
                PERFORM * FROM pg_catalog.pg_proc
                        WHERE proname = 'job_' || job || '_what'
                        AND pronamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'dbms_job_procedure')
                      AND prosecdef
                        AND proowner = jobOwnerId
                        AND pronargs = 0 AND NOT proisagg AND NOT proretset
                        AND prorettype = 'pg_catalog.void'::regtype
                        AND protype = 1::char
                        FOR SHARE;
                IF NOT SQL%FOUND THEN
                        raise 'Invalid what procedure' USING ERRCODE = 'syntax_error';
                END IF;

                -- 2) Set session authorization. This is just to set the search_path
                --    to that of the user, it's not an effective security measure
                --    because you can easily call RESET SESSION AUTHORIZATION to revert
                --    it. We do this in LOCAL mode, so that it's automatically reverted
                --    when we're done. Note that we're in a BEGIN ... EXCEPTION block,
                --    IOW in a subtransaction, so the authorization will be revertd
                --    as soon as we exit the block.
                --        XXX: We can't do this if we're invoked from dbms_job.run(),
                --    because you can't call SET SESSION AUTHORIZATION within a
                --    security definer function, and dbms_job is a security definer
                --    package.
                IF NOT manual THEN
                        EXECUTE IMMEDIATE 'SET LOCAL SESSION AUTHORIZATION ' || quote_ident(jobowner);
                END IF;
             -- 3) execute the 'what' procedure
                EXECUTE IMMEDIATE 'BEGIN dbms_job_procedure.job_' || job || '_what; END;';

        EXCEPTION WHEN OTHERS THEN
                -- At any exception, update job next run, COMMIT , raise error again
                err := SQLERRM;
                errstate := SQLSTATE;
        END;
        -- End of begin-exception-end block: this reverts the change in SESSION
        -- AUTHORIZATION
        BEGIN
                -- Perform similar check to the interval-function that we did for
                -- 'what'. Note that we have to do this *after* executing 'what',
                -- because it could have replaced the 'interval' function with a
                -- malicious one.
                PERFORM * FROM pg_catalog.pg_proc
                        WHERE proname = 'job_' || job || '_interval'
                        AND pronamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'dbms_job_procedure')
                        AND prosecdef
                        AND proowner = jobOwnerId
                        AND pronargs = 0 AND NOT proisagg AND NOT proretset
                        AND prorettype = 'pg_catalog.timestamp'::regtype
                        AND protype = 0::char
                        FOR SHARE;
               IF NOT SQL%FOUND THEN
                        raise 'Invalid interval function' USING ERRCODE = 'syntax_error';
                END IF;

                -- 4) Run the 'interval' function to get next_date
                IF NOT manual THEN
                EXECUTE IMMEDIATE 'SET LOCAL SESSION AUTHORIZATION ' || quote_ident(jobowner);
                END IF;
                EXECUTE IMMEDIATE 'SELECT dbms_job_procedure.job_' || job || '_interval' INTO next_date;

          -- Just rethrow any error. The purpose of this EXCEPTION BLOCK is really
          -- to just end the subtransaction and undo the change of SESSION
          -- AUTHORIZATION
        EXCEPTION WHEN OTHERS THEN
                raise;
        END;
        -- 6) Update jobnextrun
        IF next_date IS NULL THEN
                UPDATE pgagent.pga_job SET jobnextrun = NULL, jobenabled = false WHERE jobid = job;
                UPDATE pgagent.pga_job SET jobenabled = true WHERE jobid = job;
        ELSE
                -- Next_date should not be past date
                IF (transaction_timestamp() - next_date > '1 SECS') THEN
                        -- Rollback what
                       err := 'Interval must evaluate to a time in the future';
                ELSE
                        UPDATE pgagent.pga_job SET jobnextrun = next_date where jobid = job;
                END IF;
        END IF;
        -- 7) insert log entry if run manually, pgagent takes care of this in scheduled runs
        IF manual THEN
                IF err IS NOT NULL THEN
                        INSERT INTO pgagent.pga_joblog (jlgjobid, jlgstatus, jlgstart, jlgduration)
                        VALUES (job, 'f', jobStartDate, (clock_timestamp - jobStartDate));
                ELSE
                        INSERT INTO pgagent.pga_joblog (jlgjobid, jlgstatus, jlgstart, jlgduration)
                        VALUES (job, 's', jobStartDate, (clock_timestamp - jobStartDate));
                END IF;
        END IF;
        -- If we got an error while running 'what', COMMIT the change to
        -- jobnextrun and rethrow the error.
        IF err IS NOT NULL THEN
                COMMIT;
                IF errstate IS NULL THEN
                        raise '%', err USING ERRCODE = 'syntax_error';
                ELSE
                        raise '%', err USING ERRCODE = errstate;
                END IF;
        END IF;
END$__sys__$;
ALTER PROCEDURE dbms_job_procedure.run_job OWNER TO enterprisedb;
SET search_path = pgagent, pg_catalog, sys;

CREATE FUNCTION pga_schedule_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        -- update pga_job from remaining schedules
        -- the actual calculation of jobnextrun will be performed in the trigger
        UPDATE pgagent.pga_job
           SET jobnextrun = NULL
         WHERE jobenabled AND jobid=OLD.jscjobid;
        RETURN OLD;
    ELSE
        UPDATE pgagent.pga_job
           SET jobnextrun = NULL
         WHERE jobenabled AND jobid=NEW.jscjobid;
        RETURN NEW;
    END IF;
END;
$$;

ALTER FUNCTION pgagent.pga_schedule_trigger() OWNER TO enterprisedb;
--
-- Name: FUNCTION pga_schedule_trigger(); Type: COMMENT; Schema: pgagent; Owner: enterprisedb
--
COMMENT ON FUNCTION pga_schedule_trigger() IS 'Update the job''s next run time whenever a schedule changes';
--
-- Name: pgagent_schema_version(); Type: FUNCTION; Schema: pgagent; Owner: enterprisedb
--
CREATE FUNCTION pgagent_schema_version() RETURNS smallint
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- RETURNS PGAGENT MAJOR VERSION
    -- WE WILL CHANGE THE MAJOR VERSION, ONLY IF THERE IS A SCHEMA CHANGE
    RETURN 3;
END;
$$;

–Now Restore the cluster  using pg_restore

bash-3.2$ ./pg_restore -d sssdb </opt/PostgresPlus/9.1AS/data/all.sql
pg_restore: [archiver] input file does not appear to be a valid archive
-bash-3.2$

-bash-3.2$ ./psql -p 5445 -f /opt/PostgresPlus/9.1AS/data/all.sql sssdb
password:
ENT
CREATE TRIGGER
COMMENT
CREATE TRIGGER
COMMENT
SET
CREATE TRIGGER
CREATE TRIGGER
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "era" as user "enterprisedb".
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "etl" as user "enterprisedb".
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "ipl" as user "enterprisedb".
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "jk" as user "enterprisedb".
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "lol" as user "enterprisedb".
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "sssdb" as user "enterprisedb".
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
ERROR:  invalid value for parameter "default_tablespace": "tbs1"
DETAIL:  Tablespace "tbs1" does not exist.
STATEMENT:  SET default_tablespace = tbs1;
psql:/opt/PostgresPlus/9.1AS/data/all.sql:3297: ERROR:  invalid value for parameter "default_tablespace": "tbs1"
DETAIL:  Tablespace "tbs1" does not exist.
SET
CREATE TABLE
ALTER TABLE
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SET
SET
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "template1" as user "enterprisedb".
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "wwe" as user "enterprisedb".
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
-bash-3.2$

-then check the cluster whether data is valid or not using \l,\dt,\dn,script….etc.