Re: pg data backup from vps

On Sat, Dec 2, 2017 at 8:02 AM John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 12/1/2017 12:44 PM, basti wrote:
> > Replication is no backup.
> > Its more like a RAID.
> >
> > That mean tubles that are delete on master by a mistake there are also
> > delete on slave.
> >
> > correct me if i'am wrong.
>

You have not mentioned the version. If you are using 9.4 or above, you can
apply a delay between master and slave by specifying
"recovery_min_apply_delay" on slave's reccovery.conf. This might be the
fastest way to recover a dropped/truncated/deleted table specially for
large databases.

https://www.postgresql.org/docs/9.4/static/standby-settings.html

The challenge with this approach would be to choose the duration of delay.
Usually something upto 6hours should be fine. If you can not identify a
dropped table in 6hours, that means very likely you can go another 6 hours
without it (time good enough to restore a backup and perform PITR as
suggested in other answers).

>
> a wal archive plus occasional basebackups lets you restore to any point
> in time (PITR) covered since the oldest basebackup.
>
> think of a base backup as a 'full' backup, and the wal logs in the
> archive as incrementals. one such approach might be a weekly
> basebackup, where you keep the last 4 weeks, and keep all wal files
> since the start of oldest basebackup. yes, this will take quite a bit
> of space
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
> --

--

Best Regards,

*Sameer Kumar | Senior Solution Architect*

*ASHNIK PTE. LTD.*

36 Robinson Road, #14-04 City House, Singapore 068877

T: +65 6438 3504 | www.ashnik.com

Skype: sameer.ashnik | M: +65 8110 0350