From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Mark Steben <mark(dot)steben(at)drivedominion(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: processing openssl-encrypted files through pgdump and pgrestore |
Date: | 2016-04-18 20:42:54 |
Message-ID: | CAKFQuwaUzAB+qumNpy+pKq_F1FGyxptGqmnNcA8sSZEyhotpZg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Mon, Apr 18, 2016 at 1:34 PM, Mark Steben <mark(dot)steben(at)drivedominion(dot)com>
wrote:
> 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,
>
>
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.
From | Date | Subject | |
---|---|---|---|
Next Message | drum.lucas@gmail.com | 2016-04-18 22:46:25 | DB Size - How much is growing - PostgreSQL 9.2 |
Previous Message | Mark Steben | 2016-04-18 20:34:06 | processing openssl-encrypted files through pgdump and pgrestore |