From: | Emanuel Calvo <emanuel(dot)calvo(at)2ndquadrant(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sync production DB with development? |
Date: | 2014-10-08 17:30:08 |
Message-ID: | 543574A0.7020402@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512
El 08/10/14 a las 14:01, Israel Brewster escibió:
> I am currently doing periodic syncs of one of my production
> databases to my development database using the command pg_dump -ch
> <production host> <database name> | psql <database name>, run on my
> development server. This works well enough, but as the size of the
> production database grows, this command is, for obvious reasons,
> getting progressively slower (a full sync like this currently takes
> about 35 seconds). Is there a better way? Something that will only
> transfer records that are different on the production server, like
> rsync does for files perhaps?
You can setup a streaming server, however I wont' recommend to sync
from a production server.
Usually there is no need to have *all* the data from prod to
development. Both environments should be isolated for security reasons.
Other thing is to implement a QA server, streaming from the master or
taking a nightly snapshot with pg_basebackup. I think it could be more
than enough.
Actually, doing pg_dump | psql could take more time than pg_basebackup.
>
> My main concern here is the time it takes to sync, given that the
> database size will only continue growing as time passes (unless I
> start implementing an archive at some point). The current database
> has two years worth of records. I would assume that the time the
> sync takes would grow roughly linearly with the number of records,
> so I could easily be over a minute of sync time in another two
> years. I would really rather not have to wait several minutes every
> time I want to update my development data.
Which is the entire size of your production cluster?
- --
- --
Emanuel Calvo http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Bs. As., Argentina (GMT-3)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.18 (Darwin)
Comment: GPGTools - http://gpgtools.org
iQIcBAEBCgAGBQJUNXSfAAoJEIBeI/HMagHmUHwQALpvwm44MJnDPOIGiPInZNRB
7DghWZD/JY4HbO1sKFMneXTJuNAbnNgE3MVyPnuqBnji1X0GyyDb0/NHW7yee7kd
3g+/FcryUAdY0WLuiuezcvc92eMrQTqE0eVEkM8WhlwFdmog3vbln1BeOYdMN/MF
mrOjCBZRPYbIHwk6+hGN+C/OvE4e6n91+kN+J00Ga3+oD+LZxUZTzjY2ywh5d7yd
HYsu2V2S508IQVISfoOR/skWWIWGr43ouzyPauJ/lSu/H0Jgor9c0ZjBeQUu6hsh
N6i48yY80VRDC1F5EoEg5NVzzaPcye7r3IgEp2Yx3dsvVw+sF71P/1dqJNg9K1I/
GplwEVn0Uq7X5ld8crMMqmzLdGCQXXl1CJlLa6g593SzhjWsqeC+Fj1bFJnspNB6
xrEolLC1r+ZG7DTIRr4fmomk702/X8NDJPd4FcJ8G5lOm3keJz3yygmrP1X6GVYt
UPj4p+DnwlikQqDIbLAzSAKoj/pGyya2GtadUwqQkzLYGELTNU2UR5yVcwIr/Wi5
oMA5iNqxHwrZoPzMaFQzvK+e2IHQSe/IH1EVqrqtOy+FqQLvNgABtrBrEjcy3JLq
2jL0tA9yNuixncv6JBe8kFXJ7+gwpdrGG69YGSq74B1/IzoMsWo9L+eIv1x4YnBw
xDGMjU/lhV7A9MagRZa6
=g73R
-----END PGP SIGNATURE-----
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2014-10-08 19:34:25 | Re: Processor usage/tuning question |
Previous Message | Israel Brewster | 2014-10-08 17:01:49 | Sync production DB with development? |