Обсуждение: processing openssl-encrypted files through pgdump and pgrestore
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
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
Good afternoon,We run postgres 9.2.12We'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 filesThe 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 archiveDo 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,
You seem to have answered your own question.
The general flow in this kind of situation is:
pg_dump | do-stuff > file-at-rest
undo-stuff < file-at-rest | pg_restore
Whatever you do after getting output from pg_dump needs to be undone before sending said data base into pg_restore.
You can encrypt the data at-rest but any active processing has to be done on unencrypted data.
David J.