0

I have a dump of pg_dump before and after an update.

Unfortunately the COPY statements are not sorted.

I want to get the dump into a format for easy diffing with the tool "meld".

I could do this with some editing (cut, copy, sort) with my favourite text editor, but an automated tool would be nice.

SET statement_timeout = 0;
...


CREATE TABLE table2 (
    id integer NOT NULL,
    cls_id integer NOT NULL,
    activity character varying(4) NOT NULL,
    name character varying(1024) NOT NULL
);


ALTER TABLE public.table2 OWNER TO postgres;

COPY table2 (id, cls_id, activity, name) FROM stdin;
9130    1309    0005    sun
9131    1309    0006    shining
\.

CREATE TABLE table2 (
    id integer NOT NULL,
    name character varying(1024) NOT NULL
);


ALTER TABLE public.table2 OWNER TO postgres;

COPY table2 (id, name) FROM stdin;
1    foo2
2    bar2
\.

-- ...

Requirements:

  • open source
  • command line tool
1

There is a tool called pgtricks which does this.

From the docs:

pg_dump_splitsort is a handy script for pre-processing PostgreSQL's pg_dump output to make it more suitable for diffing and storing in version control.

Usage:

pg_dump_splitsort <filename>.sql

The script splits the dump into the following files:
0000_prologue.sql: everything up to the first COPY
0001_<schema>.<table>.sql
:
:
NNNN_<schema>.<table>.sql: COPY data for each table sorted by the first field
9999_epilogue.sql: everything after the last COPY

If you have pip installed, you can installed it using

pip install pgtricks
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.