pg data backup from vps

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

pg data backup from vps

support-tiger
To diversify risk, we would like to have a daily or weekly data backup
stored in another location besides the VPS service we are using -
pg_dump is great for the backup but transferring a growing db across the
internet to a local machine disk seems slow - how are others handling
this with postgresql ?  Thks.

--
Support Dept
Tiger Nassau, Inc.
www.tigernassau.com
406-624-9310



lup
Reply | Threaded
Open this post in threaded view
|

Re: pg data backup from vps

lup


On 12/01/2017 12:56 PM, support-tiger wrote:
> To diversify risk, we would like to have a daily or weekly data backup
> stored in another location besides the VPS service we are using -
> pg_dump is great for the backup but transferring a growing db across
> the internet to a local machine disk seems slow - how are others
> handling this with postgresql ? Thks.
>
So long as you're looking for more kinds of risk, why not stream the WAL
files to another instance?


Reply | Threaded
Open this post in threaded view
|

Re: pg data backup from vps

Melvin Davidson-5


On Fri, Dec 1, 2017 at 3:07 PM, Rob Sargent <[hidden email]> wrote:


On 12/01/2017 12:56 PM, support-tiger wrote:
To diversify risk, we would like to have a daily or weekly data backup stored in another location besides the VPS service we are using - pg_dump is great for the backup but transferring a growing db across the internet to a local machine disk seems slow - how are others handling this with postgresql ? Thks.

So long as you're looking for more kinds of risk, why not stream the WAL files to another instance?



>how are others handling this with postgresql ?

One technique is to replicate to a slave on another server and do the pg_dump on the slave.

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Reply | Threaded
Open this post in threaded view
|

Re: pg data backup from vps

Joshua D. Drake
In reply to this post by support-tiger
On 12/01/2017 11:56 AM, support-tiger wrote:
> To diversify risk, we would like to have a daily or weekly data backup
> stored in another location besides the VPS service we are using -
> pg_dump is great for the backup but transferring a growing db across
> the internet to a local machine disk seems slow - how are others
> handling this with postgresql ?  Thks.

I would consider something like an archiving slave (PITR).

JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.org
*****     Unless otherwise stated, opinions are my own.   *****


Reply | Threaded
Open this post in threaded view
|

Re: pg data backup from vps

basti-2
In reply to this post by Melvin Davidson-5
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.

Best regards,
basti

On 01.12.2017 21:10, Melvin Davidson wrote:

>
>
> On Fri, Dec 1, 2017 at 3:07 PM, Rob Sargent <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>
>
>     On 12/01/2017 12:56 PM, support-tiger wrote:
>
>         To diversify risk, we would like to have a daily or weekly data
>         backup stored in another location besides the VPS service we are
>         using - pg_dump is great for the backup but transferring a
>         growing db across the internet to a local machine disk seems
>         slow - how are others handling this with postgresql ? Thks.
>
>     So long as you're looking for more kinds of risk, why not stream the
>     WAL files to another instance?
>
>
>
>>how are others handling this with postgresql ?
>
> One technique is to replicate to a slave on another server and do the
> pg_dump on the slave.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.

Reply | Threaded
Open this post in threaded view
|

Re: pg data backup from vps

Rory Campbell-Lange
In reply to this post by support-tiger
On 01/12/17, support-tiger ([hidden email]) wrote:
> To diversify risk, we would like to have a daily or weekly data backup
> stored in another location besides the VPS service we are using - pg_dump is
> great for the backup but transferring a growing db across the internet to a
> local machine disk seems slow - how are others handling this with postgresql
> ?  Thks.

Speed is related to size. If you have a problem with the speed of data
transfer, you really might want to look at different ways of backing up.
Here is one pretty straightforward strategy based on both pg_dump and
streaming replication:

1. pg_dump
   use the -Fc flags for compression
   use the -j option to parallelize the dumps
   consider adding an audit schema and inserting a row just before the
   dump to make it easy to check the dump worked with pg_restore (you
   can grep pg_restore output)
   consider dumping twice a day and then rsync those files to nearby
   machines and offsite.
   'nearby machines' is in case we have to restore quickly and it can
   take a lot of time to get big files back into production.
   'offsite' because the place you have your database server might
   evaporate

2. streaming replication
   keep streaming changes to other servers
   https://www.postgresql.org/docs/current/static/warm-standby.html
   again you might want local and remote servers to the existing main
   database
   'local' so if you have other servers using the database they can
   switch over to another server in the same space
   'remote' so you have pretty up-to-date information on a remote server
   that you can use if the place where your main database server
   evaporates
   a cool thing is you can play with the WAL file replay mechanisms so
   that you can keep a slave roughly an hour behind the main database
   server, for example, which can be nice if someone just did something
   really bad in production.

These two approaches serve different purposes. Generally having a live,
up-to-date version of your database elsewhere based on streaming
replication is the most valuable thing to have if your main database
server goes down, and it can be brilliant having those other servers for
read-only tests and - if you are careful about offlining them and making
them masters - upgrade testing.  But if a data bug or some other issue
started affecting data over time, or you need to retrieve the state of
something a month ago, you really might need dumps too.

I'm sure others will have much more sage advice, but that is a starter
for 10.

Rory


Reply | Threaded
Open this post in threaded view
|

Re: pg data backup from vps

John R Pierce
In reply to this post by basti-2
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.

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


Reply | Threaded
Open this post in threaded view
|

Re: pg data backup from vps

Sameer Kumar


On Sat, Dec 2, 2017 at 8:02 AM John R Pierce <[hidden email]> 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.


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