Hacker News Reader

Comment by penagwin | Hacker News Reader

Question: Can this clone a database, but also apply certain operations to it?

I work at a small company, and currently we clone our production database to our dev machines for testing. However certain information in the database is sensitive and we don't want to include it on our dev machines. (This specific sensitive data is also stored in an encrypted format and the key is not included, but we'd still prefer it not included).

Basically I'd like to be able to clone the database, and run some SQL to replace the data in some tables with some mock data. But I can't think of an easy way to do this without cloning the database, starting a temporary one, run the sql, then clone it again - and distribute that final build to the devs.

View on HN

Hi! Postgres.ai founder here.

This is requested quite often – for example, if we copy the database from production, sometimes it's needed to remove all personal data not to break regulations.

It is possible in Database Lab, but it's not a very user-friendly feature yet. Briefly, the process is as follows.

The "sync" Postgres instance is configured to be a production replica (better using WAL shipping from the WAL archive). Then, periodically, a new snapshot is created, currently it's done using this Bash script: https://gitlab.com/postgres-ai/database-lab/-/blob/master/sc.... (We are going to make it a part of the database-lab server in the upcoming releases).

Here https://gitlab.com/postgres-ai/database-lab/-/blob/master/sc... you can place any data transformations, so the final snapshot that will be used for thin cloning has adjusted data sets. For example, all personal data is removed or obfuscated.

Of course, if you do this, you need to keep in mind that physically, you'll have a different database. It may affect some kinds of testing (for example, troubleshooting bloat issues or some cases of index performance degradation). There are various choices to be made here. If interested, we'll be happy to help, please join our community Slack which is mentioned in the docs and README.

It doesn't appear to be able to at this point, no. Just clones the existing DB.

There are masking tools out there that can mask data from production in-flight before dropping it into a dev environment for testing (so you can get the same data with the sensitive parts changed), but this doesn't appear to be one of them. I know people who work in the field, it's a tough nut to crack to keep the database good enough to test with while making sure you don't have devs running around with customer data on their laptop/server.

> There are masking tools out there that can mask data from production

Thank you so much! "Masking" was the phrase I needed to find some potential solutions! I wasn't able to find much before, that points me in the correct direction. Thanks!

Other common terms for this are sanitization or redaction

There are various approaches here, and there are some FOSS tools that you can use.

Some links:

- https://blog.taadeem.net/english/2019/01/03/8_anonymization_... – description of methods, and a tool for Postgres, postgresql_anonymizer

- https://habr.com/en/company/yandex/blog/485096/ – not for Postgres, it's for ClickHouse (open-source DBMS for analytics) but covers the topic very well.

If you're willing to pay for it Delphix would do what you want. I've not used those features but their tech guys were describing that very scenario in a presentation I saw.

Our company is pretty small (We only have 5 developers) and judging by the lack of pricing info (you can only request a demo) - I'm guessing it's too expensive for us haha.

But thanks for the suggestion!

Reply on news.ycombinator.com