processing openssl-encrypted files through pgdump and pgrestore

From: Mark Steben <mark(dot)steben(at)drivedominion(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: processing openssl-encrypted files through pgdump and pgrestore
Date: 2016-04-18 20:34:06
Message-ID: CADyzmyy=YSG9C+z7A0RU3dMuGND-xkqx2rFM5svyS9_2uWCsbw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)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 <http://www.autorevenue.com/> | Autobase
<http://www.autobase.net/>
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 <http://www.autorevenue.com/>

<http://autobasedigital.net/marketing/DD12_sig.jpg>

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2016-04-18 20:42:54 Re: processing openssl-encrypted files through pgdump and pgrestore
Previous Message rajan 2016-04-18 04:54:22 Partition table data are not found in the pg_dump backup