pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIncremental Refresh - PostgreSQL 9.2
12+ messages / 6 participants
[nested] [flat]
* Incremental Refresh - PostgreSQL 9.2
@ 2016-02-23 21:59 drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-23 21:59 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I've got a Master server and a Test Server.
Master server has a 1.7 TB
Test server has a full copy from master - 1.7 TB.
The copy was made using pg_dump
I.E: /usr/pgsql-9.2/bin/pg_dump --exclude-table-data='junk.*'
--format=custom geoop_live > bigdump.sql
Question:
Now that the test server has a full copy from master, how could I just do a
incremental refreshing once a month?
Instead copying all the DB (1.7GB), just copy what has been changed?
Is that possible?
Cheers
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-23 22:14 David G. Johnston <david.g.johnston@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
1 sibling, 3 replies; 12+ messages in thread
From: David G. Johnston @ 2016-02-23 22:14 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: pgsql-admin
On Tue, Feb 23, 2016 at 2:59 PM, drum.lucas@gmail.com <drum.lucas@gmail.com>
wrote:
> Hi all,
>
> I've got a Master server and a Test Server.
>
> Master server has a 1.7 TB
> Test server has a full copy from master - 1.7 TB.
>
> The copy was made using pg_dump
>
> I.E: /usr/pgsql-9.2/bin/pg_dump --exclude-table-data='junk.*'
> --format=custom geoop_live > bigdump.sql
>
> Question:
> Now that the test server has a full copy from master, how could I just do
> a incremental refreshing once a month?
>
> Instead copying all the DB (1.7GB), just copy what has been changed?
> Is that possible?
>
>
You cannot accomplish incremental updates if your choice of tool is
pg_dump. The only way to do a true incremental would be to rely upon
something like rsync on the data directory while the master server is
offline.
So, assuming that is not an option, you should probably look at the various
true replication solutions that are available and see which of those might
serve as an alternative.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-23 22:25 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-23 22:25 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
>
>
> You cannot accomplish incremental updates if your choice of tool is
> pg_dump. The only way to do a true incremental would be to rely upon
> something like rsync on the data directory while the master server is
> offline.
>
> So, assuming that is not an option, you should probably look at the
> various true replication solutions that are available and see which of
> those might serve as an alternative.
>
> David J.
>
>
The point is: I don't want a slave server - I want a full copy from my
MASTER server.
So replication wouldn't be a nice solution...
Can I do a rsync from /data folder from my SLAVE into my TEST server?
To do that, both postgres process should be stopped, right?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-23 22:42 Scott Whitney <scott@journyx.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Scott Whitney @ 2016-02-23 22:42 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: pgsql-admin
Are you using it for just select queries, or is this a full 100% test (insert/update/delete) test environment?
I have both. In my case they are geographically separate. Production server at data center. Replicated "just in case," also at data center. At HQ, I replicate that for DR purposes. INTERNALLY (gigabit backend), I have another internal test server that I refresh FROM THE REPLICATED SERVER AT HQ on a regular basis. I use the replicated server to query stuff (select) and dump databases to the interaction test server regularly for full testing.
-------- Original message --------
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: 02/23/2016 4:16 PM (GMT-06:00)
To: drum.lucas@gmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Incremental Refresh - PostgreSQL 9.2
On Tue, Feb 23, 2016 at 2:59 PM, drum.lucas@gmail.com<mailto:drum.lucas@gmail.com> <drum.lucas@gmail.com<mailto:drum.lucas@gmail.com>> wrote:
Hi all,
I've got a Master server and a Test Server.
Master server has a 1.7 TB
Test server has a full copy from master - 1.7 TB.
The copy was made using pg_dump
I.E: /usr/pgsql-9.2/bin/pg_dump --exclude-table-data='junk.*' --format=custom geoop_live > bigdump.sql
Question:
Now that the test server has a full copy from master, how could I just do a incremental refreshing once a month?
Instead copying all the DB (1.7GB), just copy what has been changed?
Is that possible?
?You cannot accomplish incremental updates if your choice of tool is pg_dump. The only way to do a true incremental would be to rely upon something like rsync on the data directory while the master server is offline.
So, assuming that is not an option, you should probably look at the various true replication solutions that are available and see which of those might serve as an alternative.
?David J.
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-24 00:45 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 12+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-24 00:45 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
On 24 February 2016 at 11:42, Scott Whitney <scott@journyx.com> wrote:
> Are you using it for just select queries, or is this a full 100% test
> (insert/update/delete) test environment?
>
> I have both. In my case they are geographically separate. Production
> server at data center. Replicated "just in case," also at data center. At
> HQ, I replicate that for DR purposes. INTERNALLY (gigabit backend), I have
> another internal test server that I refresh FROM THE REPLICATED SERVER AT
> HQ on a regular basis. I use the replicated server to query stuff (select)
> and dump databases to the interaction test server regularly for full
> testing.
>
>
>
That's what I'm looking for:
https://coderwall.com/p/jxebnw/incremental-backups-for-postgresql
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-24 01:23 Scott Whitney <scott@journyx.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Whitney @ 2016-02-24 01:23 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
Please do not refer me (or the list) to a URL when you are asking for help. Be clear and concise. If you have a URI that helps you, please be specific in the places you do not understand.
-------- Original message --------
From: drum.lucas@gmail.com
Date: 02/23/2016 6:46 PM (GMT-06:00)
To: Scott Whitney <scott@journyx.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Incremental Refresh - PostgreSQL 9.2
On 24 February 2016 at 11:42, Scott Whitney <scott@journyx.com<mailto:scott@journyx.com>> wrote:
Are you using it for just select queries, or is this a full 100% test (insert/update/delete) test environment?
I have both. In my case they are geographically separate. Production server at data center. Replicated "just in case," also at data center. At HQ, I replicate that for DR purposes. INTERNALLY (gigabit backend), I have another internal test server that I refresh FROM THE REPLICATED SERVER AT HQ on a regular basis. I use the replicated server to query stuff (select) and dump databases to the interaction test server regularly for full testing.
That's what I'm looking for:
https://coderwall.com/p/jxebnw/incremental-backups-for-postgresql
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-24 01:34 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 12+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-24 01:34 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
On 24 February 2016 at 14:23, Scott Whitney <scott@journyx.com> wrote:
> Please do not refer me (or the list) to a URL when you are asking for
> help. Be clear and concise. If you have a URI that helps you, please be
> specific in the places you do not understand.
>
Scott,
I've asked for some tips about how could I do incremental backups in
Postgres WITHOUT stopping it.
As I haven't had an expected answer, I decided to post here what I've found.
So in the future others with the same question may be helped.
And at any time I referred you or the list to a URL
Lucas
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-24 01:36 drum.lucas@gmail.com <drum.lucas@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: drum.lucas@gmail.com @ 2016-02-24 01:36 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
On 24 February 2016 at 14:34, drum.lucas@gmail.com <drum.lucas@gmail.com>
wrote:
>
>
> On 24 February 2016 at 14:23, Scott Whitney <scott@journyx.com> wrote:
>
>> Please do not refer me (or the list) to a URL when you are asking for
>> help. Be clear and concise. If you have a URI that helps you, please be
>> specific in the places you do not understand.
>>
>
> Scott,
>
> I've asked for some tips about how could I do incremental backups in
> Postgres WITHOUT stopping it.
>
> As I haven't had an expected answer, I decided to post here what I've
> found.
> So in the future others with the same question may be helped.
>
> I haven't referred you or the list to a URL********
>
> Lucas
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-24 03:12 Keith <keith@keithf4.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Keith @ 2016-02-24 03:12 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: Scott Whitney <scott@journyx.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
On Tue, Feb 23, 2016 at 8:36 PM, drum.lucas@gmail.com <drum.lucas@gmail.com>
wrote:
>
>
> On 24 February 2016 at 14:34, drum.lucas@gmail.com <drum.lucas@gmail.com>
> wrote:
>
>>
>>
>> On 24 February 2016 at 14:23, Scott Whitney <scott@journyx.com> wrote:
>>
>>> Please do not refer me (or the list) to a URL when you are asking for
>>> help. Be clear and concise. If you have a URI that helps you, please be
>>> specific in the places you do not understand.
>>>
>>
>> Scott,
>>
>> I've asked for some tips about how could I do incremental backups in
>> Postgres WITHOUT stopping it.
>>
>> As I haven't had an expected answer, I decided to post here what I've
>> found.
>> So in the future others with the same question may be helped.
>>
>> I haven't referred you or the list to a URL********
>>
>> Lucas
>>
>
>
What you originally asked for and what you're referring to in that link are
two very different things. An incremental backup using the methods in that
link is possible without any third-party tools (look into pg_basebackup &
pg_receivexlogs which both come with 9.2+), it's just that the third party
tools can possibly make it slightly easier and provide some additional
options
However, once you restore that backup and bring it up as a standalone
database instance, you can no longer use the WAL stream from the original
instance to "replay" those changes. This is also the case with any sort of
rsync'ing of the data files from one instance to another.
You may be better off looking into a logical replication solution to copy
your data to a testing system if it needs to be a standalone system. That
is not currently built into Postgres, but it's on track for possible
inclusion with 9.6. In the mean time look into tools such as Mimeo or
Bucardo.
https://github.com/omniti-labs/mimeo
https://bucardo.org/wiki/Bucardo
Keith
https://www.keithf4.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-24 10:56 Matheus de Oliveira <matioli.matheus@gmail.com>
parent: drum.lucas@gmail.com <drum.lucas@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Matheus de Oliveira @ 2016-02-24 10:56 UTC (permalink / raw)
To: drum.lucas@gmail.com <drum.lucas@gmail.com>; +Cc: pgsql-admin
On Tue, Feb 23, 2016 at 6:59 PM, drum.lucas@gmail.com <drum.lucas@gmail.com>
wrote:
> I.E: /usr/pgsql-9.2/bin/pg_dump --exclude-table-data='junk.*'
> --format=custom geoop_live > bigdump.sql
>
How are restoring it? Have tried using pg_restore with "-j" or "--jobs"
option? This tend to make the restore process way faster. And as you should
be doing logical backups routinely, you can simple use those.
In fact, if you are on 9.3+, you can also use directory format on pg_dump
to have -j option there also.
Now that the test server has a full copy from master, how could I just do a
> incremental refreshing once a month?
Besides the already mentioned options, I see two more, but both are based
on physical backup of the primary dabase, and not a logical backup (by
using pg_dump):
Option 1: you can use rsync to copy from primary to test database, while on
the first run it will take awhile, the next runs might be way faster *if*
good part of the files haven't changed. Problem with rsync is that you
either need to have the primary database completely shtdown or you need to
use --checksum option of rsync (it is not safe otherwise and it makes a
read the entire data set, but transfer only the changed files). Another
option is to use rsync from an standby, streaming replica, database. If you
choose to use rsync with --checksum, you need to do somthing in the lines
of:
a) shutdown test database
b) on primary: psql -c "SELECT pg_start_backup('rsync-test');"
c) on any node: rsync -azvP --checksum <primary>:/path/to/pgdata
<test>:/path/to/pgata
d) on primary: psql -c "SELECT pg_stop_backup();"
e) copy archivelogs to test database and setup recovery.conf with
restore_command at least
f) start test database
You need to check how much rsync --checksum really buy you, sometimes it is
just faster to copy everything (but then pg_dump/pg_restore is probably
fast enough either), but, if the nodes are geographic apart and most of the
files haven't changed, then --checksum is probably faster.
Option 2: use pg_rewind [1], if you can afford to save every WAL segment
generated since each "refresh".
[1] http://www.postgresql.org/docs/current/static/app-pgrewind.html
Best regards,
--
Matheus de Oliveira
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-24 16:31 Stephen Frost <sfrost@snowman.net>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Stephen Frost @ 2016-02-24 16:31 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: drum.lucas@gmail.com <drum.lucas@gmail.com>; pgsql-admin
David,
* David G. Johnston (david.g.johnston@gmail.com) wrote:
> On Tue, Feb 23, 2016 at 2:59 PM, drum.lucas@gmail.com <drum.lucas@gmail.com>
> wrote:
> > Question:
> > Now that the test server has a full copy from master, how could I just do
> > a incremental refreshing once a month?
> >
> > Instead copying all the DB (1.7GB), just copy what has been changed?
> > Is that possible?
> >
> >
> You cannot accomplish incremental updates if your choice of tool is
> pg_dump.
Agreed.
> The only way to do a true incremental would be to rely upon
> something like rsync on the data directory while the master server is
> offline.
pgBackRest actually provides incremental backups and restores and I've
used it for exactly this kind of setup previously.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Incremental Refresh - PostgreSQL 9.2
@ 2016-02-24 18:10 Stephen Frost <sfrost@snowman.net>
parent: Keith <keith@keithf4.com>
0 siblings, 0 replies; 12+ messages in thread
From: Stephen Frost @ 2016-02-24 18:10 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: drum.lucas@gmail.com <drum.lucas@gmail.com>; Scott Whitney <scott@journyx.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
Keith,
* Keith (keith@keithf4.com) wrote:
> However, once you restore that backup and bring it up as a standalone
> database instance, you can no longer use the WAL stream from the original
> instance to "replay" those changes. This is also the case with any sort of
> rsync'ing of the data files from one instance to another.
Right, but if you don't mind throwing away the changes which were made
to the restored server (the test server) then you can use rsync (if the
source database is shut down) to re-sync the restored server without
having to re-copy everything (I'd strongly recommend using the CRC
option to rsync). The same is possible with pgBackRest, which allows
you to keep the source database online (pgBackRest will check that the
hashes match between the backup and the restored system when doing
incremental restores, there's no need to explicitly ask for that as
there is with rsync).
> You may be better off looking into a logical replication solution to copy
> your data to a testing system if it needs to be a standalone system. That
> is not currently built into Postgres, but it's on track for possible
> inclusion with 9.6. In the mean time look into tools such as Mimeo or
> Bucardo.
>
> https://github.com/omniti-labs/mimeo
> https://bucardo.org/wiki/Bucardo
Right, and there's also Slony. These are useful if the requirement is
that changes to the test/restored server be persisted.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2016-02-24 18:10 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-02-23 21:59 Incremental Refresh - PostgreSQL 9.2 drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-23 22:14 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-23 22:25 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-23 22:42 ` Scott Whitney <scott@journyx.com>
2016-02-24 00:45 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-24 01:23 ` Scott Whitney <scott@journyx.com>
2016-02-24 01:34 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-24 01:36 ` drum.lucas@gmail.com <drum.lucas@gmail.com>
2016-02-24 03:12 ` Keith <keith@keithf4.com>
2016-02-24 18:10 ` Stephen Frost <sfrost@snowman.net>
2016-02-24 16:31 ` Stephen Frost <sfrost@snowman.net>
2016-02-24 10:56 ` Matheus de Oliveira <matioli.matheus@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox