On Oct 8, 2014, at 9:30 AM, Emanuel Calvo <emanuel.calvo@xxxxxxxxxxxxxxx> wrote: > -----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. No, that wouldn't work well, because I need full access to my development server, and I need to be able to NOT have it in sync while I am working on it. > > Usually there is no need to have *all* the data from prod to > development. Both environments should be isolated for security reasons. Agreed. and no, I don't need all the data. But pg_dump doesn't give me an option to, say, only grab the last week of data. > > 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. A QA server is great, and nightly snapshots are probably fine for that, however it doesn't help with my development server, I don't think. > > 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? At the moment, only about 538MB. Which I realize isn't all that large in the grand scheme of databases. > > > - -- > - -- > 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----- > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
