Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL 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.

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL