Re: [GENERAL] Postgres backup solution

[Francisco Olarte]

Rich:

On Tue, Mar 14, 2017 at 11:42 PM, Rich Shepard  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  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres backup solution

[Stuart Bishop]

On 15 March 2017 at 03:04, John McKown  wrote:

> Your message is not diplaying. At least not for me. I guess that my reader
> does not understand the "smime.p7m" file, which shows as an attachment. For
> others, his question is:
>
> === original question from Lawrence Cohan ===
>
> Yes, this is what I intended to ask:
>
> 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.

'wal-e backup-push' will store a complete backup in S3, which can be
restored using 'wal-e backup-fetch'. And since you are already using
wal-e for log shipping, you get full PITR available.

pg_dump for a logical backup is also a possibility, although with 13TB
you probably don't want to hold a transaction open that long and are
better off with wal-e, barman or other binary backup tool.

-- 
Stuart Bishop 
http://www.stuartbishop.net/


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres backup solution

[Lawrence Cohan]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres backup solution

[Lawrence Cohan]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres backup solution

[Rich Shepard]

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  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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres backup solution

[Adrian Klaver]

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 addressed and may contain confidential 
and/or privileged material. Any review, retransmission, dissemination or other 
use of, or taking of any action in reliance upon, this information by persons 
or entities other than the intended recipient is prohibited. If you received 
this in error, please contact the sender and delete the material from any 
system and destroy any copies.







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres backup solution

[Stephen Frost]

Lawrence,

First off, I strongly recommend that you figure out how to send regular
plain-text emails, at least to this mailing list, as the whole
"winmail.dat" thing is going to throw people off and you're unlikely to
get many responses because of it.

Regarding your question..

* Lawrence Cohan (lco...@web.com) 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.

If the goal is to be able to do partial restores (such as just one
table) then your best bet is probably to use pg_dump.  Given the size of
your database, you'll probably want to pg_dump in directory format and
then send each of those files to S3 (assuming you wish to continue using
S3 for backups).  Note that pg_dump doesn't directly support S3
currently.  Also, the pg_dump will hold open a transaction for a long
time, which may be an issue depending on your environment.

If you're looking for file-based backups of the entire cluster and don't
mind using regular non-S3 storage then you might consider pgBackrest or
barman.  With file-based backups, you have to restore at least an
entire database to be able to pull out data from it.

We are working to add S3 support to pgBackrest, but it's not there
today.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Postgres backup solution

[John McKown]

Your message is not diplaying. At least not for me. I guess that my reader
does not understand the "smime.p7m" file, which shows as an attachment. For
others, his question is:

=== original question from Lawrence Cohan ===

Yes, this is what I intended to ask:

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

===

On Tue, Mar 14, 2017 at 2:57 PM, Lawrence Cohan <lco...@web.com> wrote:

>
> 
>
> Attention:
> The information contained in this message and or attachments is intended
> only for the person or entity to which it is addressed and may contain
> confidential and/or privileged material. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon,
> this information by persons or entities other than the intended recipient
> is prohibited. If you received this in error, please contact the sender and
> delete the material from any system and destroy any copies.
>
>
> -- Forwarded message --
> From: Lawrence Cohan <lco...@web.com>
> To: John R Pierce <pie...@hogranch.com>, "pgsql-general@postgresql.org" <
> pgsql-general@postgresql.org>
> Cc:
> Bcc:
> Date: Tue, 14 Mar 2017 15:57:39 -0400
> Subject: RE: [GENERAL] Postgres backup solution
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>


-- 
"Irrigation of the land with seawater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

Re: [GENERAL] Postgres backup solution

[Lawrence Cohan]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres backup solution

[John R Pierce]

On 3/14/2017 12:31 PM, Lawrence Cohan wrote:

Subject:
Postgres backup solution
From:
Lawrence Cohan 
Date:
3/14/2017 12:31 PM

To:
"pgsql-general@postgresql.org" 



was there supposed to be a question or statement or something here ?



--
john r pierce, recycling bits in santa cruz

[GENERAL] Postgres backup solution

[Lawrence Cohan]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general