Thread: [GENERAL] Postgres backup solution

[GENERAL] Postgres backup solution

From
Lawrence Cohan
Date:
________________________________

Attention:
The information contained in this message and or attachments is intended only for the person or entity to which it is
addressedand may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. If you received this in error, please contact the sender and delete the material from any
systemand destroy any copies. 

Attachment

Re: [GENERAL] Postgres backup solution

From
Adrian Klaver
Date:
On 03/14/2017 01:58 PM, Lawrence Cohan wrote:

Cut-and-paste from winmail.dat:

"Let's try this one more time in plain text and please note that I'm
creating the email totally in plain text, and send as plain text.
Unfortunately I have no  control on the mail server if that's where this
message gets mesed up and flipped from plain text to something else.

What would be a recommended solution for backing up a very large
Postgres (~13TeraBytes) database in order to prevent from data
deletion/corruption. Current setup is only to backup/restore to a
standby read-only Postgres server via AWS S3 using wal-e however this
does not offer the comfort of keeping a full backup available in case we
need to restore some deleted or corrupted data.

Thanks,
Lawrence Cohan"


Still not coming through as plain text. You might want to talk to
whoever is in charge of the email server.

>
> ________________________________
>
> Attention:
> The information contained in this message and or attachments is intended only for the person or entity to which it is
addressedand may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. If you received this in error, please contact the sender and delete the material from any
systemand destroy any copies. 
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: [GENERAL] Postgres backup solution

From
Rich Shepard
Date:
On Tue, 14 Mar 2017, Adrian Klaver wrote:

> What would be a recommended solution for backing up a very large Postgres
> (~13TeraBytes) database in order to prevent from data deletion/corruption.
> Current setup is only to backup/restore to a standby read-only Postgres
> server via AWS S3 using wal-e however this does not offer the comfort of
> keeping a full backup available in case we need to restore some deleted or
> corrupted data.

> Still not coming through as plain text. You might want to talk to whoever is
> in charge of the email server.

Lawrence,

   A.) My MUA is alpine and your message displays just fine here.

   2.) I'm far from being a professional DBA but if I had to back up a 13T
database what I'd do (since I use only linux) is run pg_dump with the
archive (tar) format, then use dirvish to synchronize it with a remote copy.
Dirvish <http://www.dirvish.org/> uses rsync and records only changes since
the last run. I use it to back up my server/workstation daily. I've restored
files accidently deleted with no problems using either cp or rsync.

   That's my $0.25 worth (inflation, you know).

Regards,

Rich


Re: [GENERAL] Postgres backup solution

From
Francisco Olarte
Date:
Rich:

On Tue, Mar 14, 2017 at 11:42 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>   2.) I'm far from being a professional DBA but if I had to back up a 13T
> database what I'd do (since I use only linux) is run pg_dump with the
> archive (tar) format, then use dirvish to synchronize it with a remote copy.
> Dirvish <http://www.dirvish.org/> uses rsync and records only changes since
> the last run. I use it to back up my server/workstation daily. I've restored
> files accidently deleted with no problems using either cp or rsync.

Dirvish ( I use it ) uses rsync, and can send only changes from the
last run, but you'll better use directory format for such a huge db,
as tar is a single file backup and you will not be able to use the
hard links and other nice things rsync/dirvish can do to preserve
space ( this way you send diffs and link unchanging files, which, if
partitioning or some other tactic for unchanging tables is used, can
result in big space savings ).

If your db is small enough I would recommend the custom format, with
built in compression. I never use tar format, as I find dir or custom
are always better than it ( and normally everything you can do with
tar x and a tar backup is possible with pg_restore, and then more ).

Francisco Olarte.