Modifications in pg_dump
PostgreSQL 12 expands the capabilities of the pg_dump utility through additional command-line switches designed for flexible data recovery.
Revised Parameters
| Parameter | Functionality |
|---|---|
--on-conflict-do-nothing |
Attaches an ON CONFLICT DO NOTHING directive to INSERT statements. Requires enabling --inserts or --column-inserts beforehand. |
--extra-float-digits |
Executes a session setting to configure extra_float_digits prior to export. Alowed integer values fall between -15 and 3. Non-numeric inputs resolve to 0. The dump file excludes the SET command itself. |
--rows-per-insert |
Cotnrols batch size when using --inserts. Supports integer values up to 2,147,483,647 to aggregate multiple rows into a single statement. |
Illustrative Commands
Conflict Resolution
[admin@server ~]$ pg_dump -t employees --inserts --on-conflict-do-nothing
INSERT INTO public.employees VALUES (1001, 'Sara Connor') ON CONFLICT DO NOTHING;
INSERT INTO public.employees VALUES (1002, 'Kyle Reese') ON CONFLICT DO NOTHING;
Row Aggregation
[admin@server ~]$ pg_dump -t transactions --inserts --rows-per-insert=2
INSERT INTO public.transactions VALUES (1, 'paid'), (2, 'refunded');
INSERT INTO public.transactions VALUES (3, 'pending'), (4, 'paid');
Modifications in pg_dumpall
Cluster-wide exports in pg_dumpall now support specific filtering and formatting updates.
Changed Switches
| Parameter | Notes |
|---|---|
--extra-float-digits |
Identical behavior to pg_dump concerning precision settings. |
--exclude-database |
Specifies data base names to omit. Accepts glob patterns consistent with psql syntax. Multiple instances of the flag are permissible. |
--oids |
No longer supported in this release. |
| Metadata Tags | Added explanatory comments regarding user settings and database properties. |
Operational Workflows
Filtering Targets To omit a specific instance:
[admin@server ~]$ pg_dumpall --exclude-database='archive_01' -f cluster_dump.sql
Pattern Matching To exclude a group of namespaces:
[admin@server ~]$ pg_dumpall --exclude-database='stage_[1-5]' -f cluster_dump.sql
Compound Criteria
[admin@server ~]$ pg_dumpall --exclude-database='db_x' --exclude-database='db_y' -f cluster_dump.sql
Result Format Output files will include identification strings:
-- User Configurations
-- User Config root
-- Databases
-- Database master_pg dump