Re: Enhancement request for pg_dump

On 04/22/2016 09:44 AM, Pierre Chevalier Géologue wrote:
> Hi,
> Le 18/04/2016 02:26, Sergei Agalakov a écrit :
>
>> If you never encountered a situation when in the dozens of
>> environments the databases has diverged because somebody has
>> done something manually - good for you, you are lucky guy then.
>
> I'm definitely not a lucky guy at all! :-)
> And this is happening to me *right now*... My case is a little bit more
> complicated, but I'll come back to this later on, probably in another
> thread, in order to avoid confusion.
>
> Last time I had to do this kind of exercise, a few years ago, I was in a
> remote place without Internet access, so I could not get any information
> or ask any help. I was kind of surprised/frustrated by the (apparent)
> lack of order of the pg_dump output. So I manually wrote scripts to
> export the tables and views' definitions separately, one by one, (using
> pg_dump, of course) and stack them in the order I wished into a large
> file. That was quite suboptimal, but it worked as expected, and I was
> able to diff and patch correctly.

You realize there is pg_restore -l and pg_restore -L :

http://www.postgresql.org/docs/9.5/interactive/app-pgrestore.html

-l
--list

List the contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed.

-L list-file
--use-list=list-file

Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored.

list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples.

It does not solve all problems but it does do some ordering and is amenable to be
imported as space separated file for further ordering :

aklaver(at)panda:~> pg_restore -l test.out
;
; Archive created at Fri Apr 22 10:07:50 2016
; dbname: test
; TOC Entries: 67
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.4.6
; Dumped by pg_dump version: 9.4.6
;
;
; Selected TOC Entries:
;
2702; 1262 983301 DATABASE - test postgres
9; 2615 1298825 SCHEMA - MASTER_USER postgres
8; 2615 2200 SCHEMA - public postgres
2703; 0 0 COMMENT - SCHEMA public postgres
2704; 0 0 ACL - public postgres
2; 3079 12456 EXTENSION - plpgsql
2705; 0 0 COMMENT - EXTENSION plpgsql
1; 3079 1730602 EXTENSION - plpythonu
2706; 0 0 COMMENT - EXTENSION plpythonu
191; 1255 1057054 FUNCTION public fn_plpgsqltestmulti(character varying) aklaver
210; 1255 1730594 FUNCTION public measurement_insert_trigger() postgres
205; 1255 1065246 FUNCTION public myfunc(refcursor, refcursor) aklaver
206; 1255 1065247 FUNCTION public myfunc(refcursor, refcursor, integer) aklaver
209; 1255 1065248 FUNCTION public myfunc(refcursor, refcursor, character varying) aklaver
208; 1255 1730587 FUNCTION public pg_stat_allusers() postgres
211; 1255 1730607 FUNCTION public test() postgres
207; 1255 1730585 FUNCTION public trigger_test() aklaver
198; 1255 1299304 FUNCTION public user_update() postgres
180; 1259 1298826 TABLE MASTER_USER test_tbl postgres
175; 1259 1016073 TABLE public a aklaver
178; 1259 1057055 TABLE public cash_journal aklaver
2707; 0 0 COMMENT public COLUMN cash_journal.click aklaver
2708; 0 0 COMMENT public COLUMN cash_journal.cash_journal_id aklaver
2709; 0 0 COMMENT public COLUMN cash_journal.fairian_id aklaver
2710; 0 0 COMMENT public COLUMN cash_journal.debit aklaver
2711; 0 0 COMMENT public COLUMN cash_journal.credit aklaver
2712; 0 0 COMMENT public COLUMN cash_journal.balance aklaver
2713; 0 0 COMMENT public COLUMN cash_journal.description aklaver
182; 1259 1299634 TABLE public company postgres
2714; 0 0 ACL public company postgres
183; 1259 1727447 TABLE public final hplc_admin
189; 1259 1730617 TABLE public gold1604_test aklaver
190; 1259 1730623 TABLE public goldmast_test aklaver
188; 1259 1730609 TABLE public jsonb_test aklaver
179; 1259 1081630 TABLE public max_test aklaver
186; 1259 1730588 TABLE public measurement postgres
187; 1259 1730591 TABLE public measurement_y2016m03 postgres
176; 1259 1016078 TABLE public tbl_a aklaver
177; 1259 1016082 TABLE public tbl_b aklaver
185; 1259 1730581 TABLE public trigger_test aklaver
184; 1259 1727466 TABLE public user_tbl hplc_admin
181; 1259 1299615 TABLE public xid_test aklaver
2687; 0 1298826 TABLE DATA MASTER_USER test_tbl postgres
2682; 0 1016073 TABLE DATA public a aklaver
2685; 0 1057055 TABLE DATA public cash_journal aklaver
2689; 0 1299634 TABLE DATA public company postgres
2690; 0 1727447 TABLE DATA public final hplc_admin
2696; 0 1730617 TABLE DATA public gold1604_test aklaver
2697; 0 1730623 TABLE DATA public goldmast_test aklaver
2695; 0 1730609 TABLE DATA public jsonb_test aklaver
2686; 0 1081630 TABLE DATA public max_test aklaver
2693; 0 1730588 TABLE DATA public measurement postgres
2694; 0 1730591 TABLE DATA public measurement_y2016m03 postgres
2683; 0 1016078 TABLE DATA public tbl_a aklaver
2684; 0 1016082 TABLE DATA public tbl_b aklaver
2692; 0 1730581 TABLE DATA public trigger_test aklaver
2691; 0 1727466 TABLE DATA public user_tbl hplc_admin
2688; 0 1299615 TABLE DATA public xid_test aklaver
2563; 2606 1016077 CONSTRAINT public a_pkey aklaver
2565; 2606 1299641 CONSTRAINT public company_pkey postgres
2567; 2606 1727454 CONSTRAINT public final_pkey hplc_admin
2569; 2606 1727473 CONSTRAINT public user_tbl_pkey hplc_admin
2572; 2620 1730595 TRIGGER public insert_measurement_trigger postgres
2571; 2620 1730601 TRIGGER public test_trigger aklaver
2570; 2606 1727474 FK CONSTRAINT public g_s_fk hplc_admin

>
> And today, I thought: "time has passed, I'm sure that pg_dump must
> magically have an option to get the output in some kind of order, by
> now"... 'man pg_dump' didn't help. And as I can read this discussion (I
> haven't finished yet, obviously), this is not the case.
>
> À+
> Pierre

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com