1

Basically, here's the problem.

I work remotely for a company, and I need to have a local copy of the production database to play around with it and test my work on it before pushing the code. The prod DB is around 6GB, and so downloading it and then restoring it is time-intensive, to say the least. I want to commit the prod database to a repo, and create a cron job that automatically commits the daily updates to the repo. Since the data in a database is stored as files on the computer, I am assuming only the diffs will be committed and uploaded to Github. And then I want to check out locally and then pull the latest changes. That way, I will only be downloading the changes, not the whole prod replica, but will also keep updated.

Please note that I am not doing it to create a backup, i.e. I don't want or need the history. I just need a method to effectively only download the changes to my database locally instead of downloading the entire replica.

10
  • 13
    You should take this question to the company, not to stackoverflow. And for all companies I worked with, this action would get you fired,
    – mtj
    Commented Jun 30, 2021 at 7:07
  • 2
    That's not how relational databases or GitHub work.
    – Tulains Córdova
    Commented Jun 30, 2021 at 7:11
  • 5
    Be aware that the data in that database may be security relevant. Therefore creating a copy (even in an theoraticly closed environment) may be a breach of the security guidelines of your company.
    – JanRecker
    Commented Jun 30, 2021 at 7:31
  • 1
    One approach of doing this is to set up a local test server using postgres replication option, and using a VPN tunnel to mirror the production database (assuming you got an official permission from the company to keep a full copy of the production data). An even better option would be if you can manage it to work in a way you don't need the whole production DB.
    – Doc Brown
    Commented Jun 30, 2021 at 7:43
  • 4
    And community, downvotes are for bad questions, not for bad ideas.
    – Doc Brown
    Commented Jun 30, 2021 at 7:48

4 Answers 4

Reset to default
6

This isn't a proper use for Github, which is mainly a source code repository and not a general file store. Your assumptions about saving time by only storing the daily updates in git do not hold, database files are binary, and the changes cannot be expressed as textual diffs.

Forget about using a git repository and look at other ways of doing differential backups. This question and this other might be starting points.

Note that if you play around with your copy of the database you're most likely not able to merge later changes from the production database. You will have to restore the playground database to a copy of the production again. However, with WAL archiving that should be much faster (you're basically storing a full backup of the production database with the ability to restore to any point in time, and incremental update of this backup from the production server).

Note2: I have no practical experience doing this, just gathered that info using a search engine, so YMMV, but I'm very positive about not using GitHub or any other source code repository tool for this. And of course, what @mtj said: Production database content does not belong on a publicly accessible service. Ever. And what @JanRecker said, as well: it probably doesn't belong on your computer either.

8
  • 2
    "Production database content does not belong on a publicly accessible service." <- it's perfectly possible to have private repos on Github, just like lots of companies have their private source code on Github.
    – Philip Kendall
    Commented Jun 30, 2021 at 7:17
  • 1
    Also be aware that as much as I value software, the data in a database is often much more valuable and must be protected much more stringently. Think of password hashes and the legal requirements around protecting personal data.
    – Hans-Martin Mosner
    Commented Jun 30, 2021 at 7:30
  • 1
    @PhilipKendall even in the companies that do, source code likely sits in an entirely different classification to the prod database because the latter almost certainly contains PII and the former shouldn't. If the OP's assuming "we put other things on GitHub so I can put this there", that can be a problem.
    – jonrsharpe
    Commented Jun 30, 2021 at 7:31
  • 2
    "Your assumptions about saving time by only storing the daily updates in git do not hold, database files are binary, and the changes cannot be expressed as textual diffs." It's worse than that. Git doesn't store textual diffs, even for source code files - it always stores the entire contents of every version of every file.
    – Ross Patterson
    Commented Jun 30, 2021 at 16:00
  • 1
    @RossPatterson: it is a little bit more complicated.
    – Doc Brown
    Commented Jul 1, 2021 at 12:19
2

Others have discussed why uploading production data to the cloud is most likely a very bad idea.

What I did in a similar situation was to create a docker image with the database in question with the production data restored into it, so that instantiating the docker image instantly give access to the full data set (for a lenient interpretation of "instant")

I then put it on our internal Gitlab instance which includes a docker repository, so that it didn't leave our network at all. Our dataset was around 1.5 Gb so this is most likely also feasible with your 6 Gb.

This worked very well for us.

1
1

I want to commit the prod database to a repo, and create a cron job that automatically commits the daily updates to the repo.
... the data in a database is stored as files on the computer, I am assuming only the diffs will be committed ...

Database != File(s)

The only time that the above is not true is after the database has been cleanly shut down.

The files used by just about every DBMS are in a proprietary, binary format, so working out what "changes" have been made "in there" is likely to be very difficult for any version control system. You may well find that it just takes another copy of the whole thing, anyway!

Stop thinking about databases as files.
Perform database operations using database tools.

You don't say which DBMS you're using; you may find that a "dump" of the database could be considerably smaller than the database itself.

1
  • 2
    Well he did state "postgres" in the title...
    – Hans-Martin Mosner
    Commented Jun 30, 2021 at 15:28
0

As all the others said, it's not how the Database to be used for test. Below are the points to be considered for production data replication.

  1. You must have a test database.
  2. You can have a daily job to replicate the data. Or you can do it manually for the required tables.
  3. You must mask sensitive data and replace with placeholders.

Also, if you only need data to be read, you can create a user with only read privilege and you can use it for development.

Not the answer you're looking for? Browse other questions tagged or ask your own question.