Re: Sync production DB with development? - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512



El 08/10/14 a las 17:24, Israel Brewster escibió:
> On Oct 8, 2014, at 9:30 AM, Emanuel Calvo
> <emanuel.calvo@2ndquadrant.com> wrote:
>
>
>
> 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.
>
>

That's the issue: streaming will left your development server as
read-only and I guess you don't want to do that.

> 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.
>

Yeah, at this point you will need to build your own sample data using
production values.

>
> 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.
>

Well, the nightly snapshots will allow you to sync every night your
development server and write over it during the day (you can sync
many times as you want).

>
> 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.
>

I think that at this point you shouldn't be worried at all.

Usually, you can build sample data DB and using it as a template.
So, any time you want to drp and recreate your database, you just do
a *create database* with the template option.


- --
- --
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

iQIcBAEBCgAGBQJUNaRnAAoJEIBeI/HMagHm5sEQALsfKrf3UHoVAr3N9EQfvlza
Ds7ZXue48Mt63FHLlsscC/lsJlfublJXwnV/7H9aFgviVDgWRqpjfrtFWk/5WzXn
g0c6zbjB13uc5K50OQqeFjo4Sb1UMZqSInGLDa3wi2BCT8XQepUQk60Hy9YJo449
2VkibVrJPulEJN2pviL7nlHNQoW3A2KHne9uEc3sdVDtAPgXrbB45BW184Qgpc34
r2ReAqM8S533lTe/ZfXpn6ZUru8uJHXnwkRirt8HOelXeIYNxvZyjmzaFpXmt5ZG
grJfx0WZB+qTmT4lLw2OdwZ/3U/M1y4cMLnePmBWpdxph43gSH7AEDO2XMyhpsnQ
5To1zgfZexvZXZ1AIMAAShgGxPMLgCVy7lTdzfZVS92CiU2ou4gsh8s3wZpwjc68
VuplS28HIY2GNZQm4OackfAXwm9yR80YdW7rE6Il7eUx1pAv8OZJyQPDmoav4J7V
Ir7X9kIMK8JUtb+G79lpsQcBwJ6f+BGW+OMMqfYHfuSfPErLprYuoN9A7cZVJNtv
D9fWyHtcvyFMfyxfZmGdY1pK5M/9DWrI7e9ILp29oywZies0Zk9b9AuV3Hki8LZm
kQuJOswXKM5/g1U4QS9a5Pf0DF5qx4vAZq9OgtLnN8kyUykgZaHuJJzVSdE+wsOW
Q14aZJWWdJpBSu45NtrG
=Zb7i
-----END PGP SIGNATURE-----