processing openssl-encrypted files through pgdump and pgrestore

[Mark Steben <mark.steben@xxxxxxxxxxxxxxxxx>]

Good afternoon,

We run postgres 9.2.12

We've been given a mandate to encrypt all our database backups.

We also use the -f Fc subcommand in pgdump to create pg_restore suitable input/output files

The first step to create the encrypted dump works fine:

  pg_dump -U postgres auth_production_test -Fc | openssl enc -aes-256-cbc -kfile /home/postgres/.openssl.postgres.pass -e > /storage/backups/dbdumps/authproductiontest.custom.gz.enc &

However when I run pg_restore with the -l option to create the table of contents, pg_restore doesn't recognize the encrypted backup as a suitable archive:

 pg_restore -l -U postgres authproductiontest.custom.gz.enc > authproductiontest.list | openssl enc -aes-256-cbc -kfile /home/postgres/.openssl.postgres.pass -e > /storage/backups/dbdumps/authproductiontest.custom.list.enc &

[2] 1070

[postgres@diablo dbdumps]$ pg_restore: [archiver] input file does not appear to be a valid archive

Do I need to create an unencrypted dump first for pg_restore to recognize and act upon?  I don't see anything in the pg_restore documentation that allows for reading encrypted files. 

Any suggestions welcome.  Thanks,

--

Mark Steben
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com