Re: Sync production DB with development? - Mailing list 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-----