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