Search Postgresql Archives

Re: Enhancement request for pg_dump

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux