Re: Sync production DB with development?

On Oct 8, 2014, at 9:30 AM, Emanuel Calvo <emanuel.calvo@2ndquadrant.com> 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
insync 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general