split_pg_dump

Split / parse pg_dump output to individual files per object

positional arguments:
sourcefile            SQL input file. Typically from pg_dump
optional arguments:
-h, --help            show this help message and exit
-of OUTPUTDIR, -outputdir OUTPUTDIR
Optional alternative destination / output directory
for SQL output files
-ns, -nosequence      Omit the sequence number prefix for resulting
filenames. Handy for comparing schemas between
databases
-nt, -notype          Ommit the type prefix in resulting filenames
-nc, -noclean         Skip the step of cleaning the target directory of
*.sql
-xn EXCLUDENAMES [EXCLUDENAMES ...], -exludenames EXCLUDENAMES [EXCLUDENAMES ...]
Exclude objects these strings in their names
-xt EXLUDETYPES [EXLUDETYPES ...], -exludetypes EXLUDETYPES [EXLUDETYPES ...]
Exclude objects these types. Options are MATERIALIZED
VIEW,SEQUENCE,INDEX,TABLE,TYPE,VIEW,FUNCTION,SCHEMA,CO
NSTRAINT,TRIGGER,FK CONSTRAINT
Samples: To exclude schema and foreign key constraints: -xt 'SCHEMA' 'FK CONTSTAINT'

Tested on PostrgeSQL 9.6 and Ubuntu 14.04.2 LTS

The python script split_pg_dump.py will take the output of pg_dump above as parameter and generate multiple .sql files in the current directory. Each filename is prefixed with a sequence number so that they can be run in the correct order (for depandancies)

To make the resulting files portable (to allow you applying the changes to another database or schema) perform pg_dump with the following parameters: -s -x -O

Output Filenames

Defaults with no options

00001_ty_some_type.sql
00002_fn_some_function.sql
00003_vw_some_view.sql
00004_mv_some_materialised_view.sql
00005_sq_some_sequence.sql
00006_ix_some_index.sql

-ns , -nosequence

ty_some_type.sql
fn_some_function.sql
vw_some_view.sql
mv_some_materialised_view.sql
sq_some_sequence.sql
ix_some_index.sql

-nt , -notype

00001_some_type.sql
00002_some_function.sql
00003_some_view.sql
00004_some_materialised_view.sql
00005_some_sequence.sql
00006_some_index.sql

bulkpsql.sh will allow you to apply the whole folder of files to the server by only specifying your password and schema once.