help

PGExtractor - pg_extractor.pl

DESCRIPTION
A script for doing advanced dump filtering and managing schema for PostgreSQL databases

SYNOPSIS

    /path/to/pg_extractor.pl [options]

  NOTES

     - Requires using a trusted user or a .pgpass file. No option to send password.
     - For all options that use an external file list, separate each item in the file by a newline.
        pg_extractor.pl will accept a list of objects output from a psql generated file using "\t \o filename"
     - If no schema name is given in a filter for tables, it will assume public schema (same as pg_dump). For other objects, not designating
        a schema will match across all schemas included in given filters. So, recommended to give full schema.object name for all objects.
     - If a special character is used in an object name, it will be replaced with a comma followed by its hexcode
        Ex: table|name becomes table,7cname.sql
     - VCS options (svn/git) assume a local repository has already been created. Recommend running pg_extractor once without any VCS options,
        committing manually, then adding any VCS options.
     - Comments/Descriptions on any object should be included in the export file. If you see any missing, please contact the author

OPTIONS

    --options_file
        Use a configuration file to list all the options you'd like to use. Each option is
        listed on its own line exactly as it would appear on the command line. This can be
        used in combination with command line options, but preference will be given to
        whichever is listed LAST on the command line. Also note that unlike other options
        here, there is NO equal sign between the option and the path to the options file.

  database connection

    --host (-h)
        database server host or socket directory (Default: Result of running
        Sys::Hostname::hostname)

    --port (-p)
        database server port

    --username (-U)
        database user name

    --pgpass
        full path to location of .pgpass file

    --dbname (-d)
        database name to connect to. Also used as directory name under --hostname

    --encoding
        create the dump files in the specified character set encoding. By default, the
        dump is created in the database encoding.

  directories

    --basedir (ddlbase)
        base directory for ddl export. ddlbase is from old version that was schema only.
        kept for compatibility. (Default: directory pg_extractor is run from '.' )

    --hostname
        hostname of the database server; used as directory name under --basedir

    --dbdir
        database name (replaces --dbname as the directory name); used as directory under
        --hostname (If you are extracting multiple databases this is unsafe)

    --rolesdir
        name of the directory under database name to place the export file with role data.
        no impact without the --getroles or --getall option.

    --schemasubdir
        breakout each schema's content into subdirectories under the database directory
        (hostname/databasedir/schema)

    --pgbin
        location of the required postgresql binaries (pg_dump, pg_restore, pg_dumpall). If
        these are all in the same location, you can use this single option instead of the
        individual --pgdump, --pgrestore and --pgdumpall options.

    --pgdump
        location of pg_dump executable (Default: searches $PATH )

    --pgrestore
        location of pg_restore executable (Default: searches $PATH )

    --pgdumpall
        location of pg_dumpall executable. only required if --getroles or --getall options
        are used (Default: searches $PATH )

  filters

    --getschemata
        export schema ddl

    --gettables
        export table ddl. Each file includes table's indexes, constraints, sequences,
        comments, rules and triggers

    --getviews
        export view ddl

    --getfuncs
        export function and/or aggregate ddl. Overloaded functions will all be in the same
        base filename. Custom aggregates are put in a separate folder than regular
        functions.

    --gettypes
        export custom types.

    --getroles
        include an export file containing all roles in the cluster.

    --getall
        gets all tables, views, functions, types and roles. Shortcut to having to set all
        --get* options. Does NOT include data

    --getdata
        include data in the output files. Note that format will be plaintext (-Fp) unless
        -Fc option is explicitly given.

    --Fc
        output in pg_dump custom format (useful with --getdata). Otherwise, default is
        always -Fp

    --N csv list of schemas to EXCLUDE

    --N_file
        path to a file listing schemas to EXCLUDE.

    --n csv list of schemas to INCLUDE

    --n_file
        path to a file listing schemas to INCLUDE.

    --T csv list of tables to EXCLUDE. Schema name may be required (same for all table
        options)

    --T_file
        path to file listing tables to EXCLUDE.

    --t csv list of tables to INCLUDE. Only these tables will be exported

    --t_file
        path to file listing tables to INCLUDE.

    --V csv list of views to EXCLUDE.

    --V_file
        path to file listing views to EXCLUDE.

    --v csv list of views to INCLUDE. Only these views will be exported

    --v_file
        path to file listing views to INCLUDE.

    --P_file
        path to file listing functions or aggregates to EXCLUDE.

    --p_file
        path to file listing functions or aggregates to INCLUDE.

    --O csv list of object owners to EXCLUDE. Objects owned by these owners will NOT be
        exported

    --O_file
        path to file listing object owners to EXCLUDE. Objects owned by these owners will
        NOT be exported

    --o csv list of object owners to INCLUDE. Only objects owned by these owners will be
        exported

    --o_file
        path to file listing object owners to INCLUDE. Only objects owned by these owners
        will be exported

    --regex_incl_file
        path to a file containing a regex pattern of objects to INCLUDE. Note this will
        match against all objects (tables, views, functions, etc)

    --regex_excl_file
        path to a file containing a regex pattern of objects to EXCLUDE. Note this will
        match against all objects (tables, views, functions, etc)

    --no-owner
        do not add commands to dump files to set ownership of objects to match the
        original database

    --no-acl OR --no-privileges
        prevent dumping of access privileges (grant/revoke commands)

    --inserts
        dump data as INSERT commands (rather than COPY). Only useful with --getdata option

    --column-inserts OR --attribute-inserts
        dump data as INSERT commands with explicit column names (INSERT INTO table
        (column, ...) VALUES ...). Only useful with --getdata option

  Version Control

    --git
        perform git commit of basedir/hostname folder. This is a local commit only. See
        --gitpush for pushing to remote repositories

    --gitpush
        perform a local git commit of basedir/hostname folder as well as push to an
        already configured remote repository

    --gitcmd
        full path location of git command (Default: searches $PATH )

    --gitdel
        delete any files from the git repository that are no longer part of the desired
        export. WARNING: This WILL delete ALL .sql files in the destination folder(s)
        which don't match your desired output. --delete option is not required when this
        is set, since it will also delete files from disk if they were part of a previous
        export.

    --svn
        perform svn commit of basedir/hostname/dbname folder.

    --svn_userfile
        file containing the svn username and password if needed. Make sure the user
        running pg_extractor can read this file. File should contain a single line in the
        format: --username svnuser --password svnpassword

    --svncmd
        full path location of svn command (Default: searches $PATH )

    --svndel
        delete any files from the svn repository that are no longer part of the desired
        export. WARNING: This WILL delete ALL .sql files in the destination folder(s)
        which don't match your desired output. --delete option is not required when this
        is set, since it will also delete files from disk if they were part of a previous
        export.

    --commitmsg
        Commit message to send to git or svn

    --commitmsgfn
        File containing the commit message to send to git or svn

  other

    --delete
        Use when running again on the same destination directory as previous runs so that
        objects deleted from the database or items that don't match your filters also have
        their old files deleted. WARNING: This WILL delete ALL .sql files in the
        destination folder(s) which don't match your desired output. Not required when
        using the --svndel option.

    --clean
        Adds DROP commands to the SQL output of all objects. Allows the same behavior as
        OR REPLACE since ACLs are included with all objects. WARNING: For overloaded
        function/aggregates, this adds drop commands for all versions to the single output
        file.

    --orreplace
        Modifies the function and view ddl files to replace CREATE with CREATE OR REPLACE.

    --sqldump
        Also generate a pg_dump file. Will only contain schemas and tables designated by
        original options. Note that other filtered items will NOT be filtered out of the
        dump file.

    --sqldumpdir
        name of the directory under the database name directory to place the pg_dump file.
        has no impact without the --sqldump option

    --quiet
        Suppress all program output

    --help (-?)
        show this help page

EXAMPLES

    Basic minimum usage. This will extract all tables, functions/aggregates, views, types
    & roles. It uses the directory that pg_extractor is run from as the base directory
    (objects will be found in ./hostname/mydb/) and will also produce a permanent copy of
    the pg_dump file that the objects were extracted from. It expects the locations of the
    postgres binaries to be in the $PATH.
        perl pg_extractor.pl -U postgres --dbname=mydb --getall --sqldump

    Extract only functions from the "keith" schema
        perl pg_extractor.pl -U postgres --dbname=mydb --getfuncs --n=keith

    Extract only specifically named functions in the given filename (newline separated
    list). Ensure the full function signature is given with only the variable types for
    arguments. When using include files, it's best to explicitely name the schemas they're
    in as well (it makes the temporary dump file that's created smaller).
        perl pg_extractor.pl -U postgres --dbname=mydb --getfuncs
        -p_file=/home/postgres/func_incl --n=dblink

         func_incl file contains:
         dblink_exec(text, text)
         dblink_exec(text, text, boolean)
         dblink_exec(text)
         dblink_exec(text, boolean)

    Extract only the tables listed in the given filename (newline separated list) along
    with the data in the pg_dump custom format.
        perl pg_extractor.pl -U postgres --dbname=mydb --gettables -Fc
        --t_file=/home/postgres/tbl_incl --getdata

    Example of excluding partitions that have the pattern tablename_pYYYY_MM or fairly
    similar. Binaries are also not in the $PATH and EXCLUDES several schemas. part_exclude
    file contains: _p_?(20|19)\d\d(_?\d+)*$ .
        perl pg_extractor.pl -U postgres --dbname=mydb --pgdump=/opt/pgsql/bin/pg_dump
        --pgrestore=/opt/pgsql/bin/pg_restore --pgdumpall=/opt/pgsql/bin/pg_dumpall
        --getall --regex_excl_file=/home/postgres/part_exclude
        --N=schema1,schema2,schema3,schema4

    Using svn (svn username & password must be set in script source). Also cleans up
    objects from svn that have been removed from the database.
        perl pg_extractor.pl -U postgres --dbname=mydb --svn --svncmd=/opt/svn/bin/svn
        --commitmsg="Weekly svn commit of postgres schema" --svndel

    Using an options file
        perl pg_extractor.pl --options_file /path/to/options_file

AUTHOR

        Keith Fiske
        OmniTI, Inc - http://www.omniti.com
        Download source from https://github.com/omniti-labs/pg_extractor

LICENSE AND COPYRIGHT

    PGExtractor is released under the PostgreSQL License, a liberal Open Source license,
    similar to the BSD or MIT licenses.

    Copyright (c) 2012 OmniTI, Inc.

    Permission to use, copy, modify, and distribute this software and its documentation
    for any purpose, without fee, and without a written agreement is hereby granted,
    provided that the above copyright notice and this paragraph and the following two
    paragraphs appear in all copies.

    IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL,
    INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE
    OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE
    POSSIBILITY OF SUCH DAMAGE.

    THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
    IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE
    SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS
    TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.