PostgreSQL: Dump Schema Only or Triggers Only
I needed to extract triggers only, sequence/index only etc when we migrated database.
I received advice from postgresql General List and thought I should post exact step/script of how to accomplish this.
Open Terminal:
1. dump schema only
pg_dump -h localhost -p 5432 –username UserName –schema-only –format c –file path_to_backup.backup –schema ‘public’ db_name
2. create list of item that you want to extract
2.1. TRIGGERS only
pg_restore –list “path_to_backup.backup” | awk ‘/[0-9]*; [0-9]* [0-9]* (TRIGGER)/ { print }’ > “path_to_triggers.list”
2.2. SEQUENCE, INDEX
pg_restore –list “path_to_backup.backup” | awk ‘/[0-9]*; [0-9]* [0-9]* (SEQUENCE|INDEX)/ { print }’ > “path_to_seq.list”
3. Create SQL script from the list
3.1. TRIGGERS only
pg_restore -L path_to_triggers.list -f dest_path_triggers.sql path_to_backup.backup
3.2. SEQUENCE, INDEX
pg_restore -L path_to_seq.list -f dest_path_seq.sql path_to_backup.backup