Home > mailing lists

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering) - Mailing list pgsql-general

From	Ken Tanzer
Subject	Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)
Date	June 6, 2017 08:25:44
Msg-id	CAD3a31VuTCM2x2=guZahDcz=zvWyiaORR+BoiWE7tUcAKXURtg@mail.gmail.com Whole thread Raw
In response to	Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering) (Tom Lane <tgl@sss.pgh.pa.us>)
Responses	Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering) (Tom Lane <tgl@sss.pgh.pa.us>)
List	pgsql-general

Tree view

> So I can switch to Custom format for future backups. But regarding the
> existing backups I have in Tar format, is there any way to successfully
> restore them?

FWIW, the business with making and editing a list file should work just
fine with a tar-format dump, not only with a custom-format dump. The
metadata is all there in either case.

I had tried that originally, but got an error:

bash-4.1$ pg_restore -L spc_restore_list.tmp -d spc_test_1 agency_backup.spc.2017.06.05_10.30.01.tar

pg_restore: [tar archiver] restoring data out of order is not supported in this archive format: "10608.dat" is required, but comes before "10760.dat" in the archive file.

The pg_dump doc page kinda suggests but doesn't quite say that you can't re-order tar files; between that and the error message I gave up on that possibility. Are you suggesting it should work?

https://www.postgresql.org/docs/9.3/static/app-pgdump.html

The alternative archive file formats must be used with pg_restore to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

When used with one of the archive file formats and combined with pg_restore, pg_dump provides a flexible archival and transfer mechanism. pg_dump can be used to backup an entire database, then pg_restore can be used to examine the archive and/or select which parts of the database are to be restored. The most flexible output file formats are the "custom" format (-Fc) and the "directory" format(-Fd). They allow for selection and reordering of all archived items, support parallel restoration, and are compressed by default. The "directory" format is the only format that supports parallel dumps.

Cheers,

Ken