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@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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
