Re: Enhancement request for pg_dump - Mailing list pgsql-general

On 04/22/2016 11:16 AM, Pierre Chevalier Géologue wrote:
> Le 22/04/2016 19:11, Adrian Klaver a écrit :
>>> 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
>
> Yes, thanks for the advice.  Now I remember that I had used it also: I
> just found these notes in my numeric attic:
>
>    # pierre@autan: ~        < 2013_08_17__17_00_23 >
> pg_restore -l database_2013_08_14_20h34.pg_dump -n pierre >
> tt_schema_pierre

Yea, you can also use the filtering switches to create a filtered TOC, so:

pg_restore -l -s test.out > test_s.toc

Now the TOC has only a summary line of what is being done, but it is
easy enough to feed it back to pg_restore and have it restore to a plain
text file instead of a database:

pg_restore -L test_s.toc  -f test_s.sql


>
>    # pierre@autan: ~        < 2013_08_17__17_00_23 >
> vi tt_schema_pierre
>
>    # pierre@autan: ~        < 2013_08_17__17_00_23 >
> cat tt_schema_pierre
> DROP VIEW IF EXISTS pierre.dh_collars;
> DROP VIEW IF EXISTS pierre.dh_litho;
> DROP VIEW IF EXISTS pierre.dh_sampling_grades;
> DROP VIEW IF EXISTS pierre.dh_sampling;
> DROP VIEW IF EXISTS pierre.topo_points;
> DROP VIEW IF EXISTS pierre.baselines;
> DROP VIEW IF EXISTS pierre.dh_devia;
> ...
>
>
> I just cannot remember why it did not fulfill my needs, so that I rather
> opted to pg_dump's.
>
>
> ...
>> 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
> ...
>
> Thanks, I'll dig a bit more in pg_restore, for my current issues.
>
> Pierre


--
Adrian Klaver
adrian.klaver@aklaver.com