-
BASIC POSTGRESQL
6-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
Lecture2.5
-
Lecture2.6
-
Lecture2.7
-
Lecture2.8
-
Lecture2.9
-
-
POSTGRESQL DATABASE
6-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
Lecture3.4
-
Lecture3.5
-
Lecture3.6
-
-
POSTGRESQL TABLE
16-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Lecture4.4
-
Lecture4.5
-
Lecture4.6
-
Lecture4.7
-
Lecture4.8
-
Lecture4.9
-
Lecture4.10
-
Lecture4.11
-
Lecture4.12
-
Lecture4.13
-
Lecture4.14
-
Lecture4.15
-
Lecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
Lecture5.1
-
Lecture5.2
-
Lecture5.3
-
-
TRANSACTIONS - MVCC
3-
Lecture6.1
-
Lecture6.2
-
Lecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
Lecture7.1
-
Lecture7.2
-
Lecture7.3
-
Lecture7.4
-
Lecture7.5
-
Lecture7.6
-
Lecture7.7
-
Lecture7.8
-
Lecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
Lecture8.1
-
Lecture8.2
-
Lecture8.3
-
Lecture8.4
-
Lecture8.5
-
Lecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
Lecture9.1
-
Lecture9.2
-
Lecture9.3
-
Lecture9.4
-
Lecture9.5
-
-
POSTGRESQL VIEWS
1-
Lecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
Lecture12.1
-
Lecture12.2
-
Lecture12.3
-
Lecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
Lecture13.1
-
Lecture13.2
-
Lecture13.3
-
Lecture13.4
-
Lecture13.5
-
Lecture13.6
-
Lecture13.7
-
Lecture13.8
-
Lecture13.9
-
Lecture13.10
-
Lecture13.11
-
Lecture13.12
-
Lecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
Lecture14.1
-
Lecture14.2
-
Lecture14.3
-
Lecture14.4
-
Lecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
Lecture15.1
-
Lecture15.2
-
Lecture15.3
-
Lecture15.4
-
Lecture15.5
-
Lecture15.6
-
Lecture15.7
-
Lecture15.8
-
Lecture15.9
-
Lecture15.10
-
Lecture15.11
-
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.