• 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_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.

        Prev PostgreSQL Database Backup Tools – Pg_dump
        Next PostgreSQL Database Backup Tools – Pg_basebackup

        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