Greetings, * Hilbert, Karin (ioh1@xxxxxxx) wrote: > The other year, we had to restore the prod database backup to dev & that changed the schema name. I was thinking that it would be better have the same names used for dev & prod so that restores from one environment to another would be easier. (That's a standard that our DBA team employs for our SQL Server databases.) Does it make sense to also employ that standard for PostgreSQL databases? Is there any reason to keep the names different between the environments? When possible, the approach that I tend to use here is to incorporate the testing of backups into the dev/staging restore cycle. That is, I'll do a backup of prod and then restore that into dev (and/or staging, as appropriate) on a regular basis. Using pgbackrest's delta restore option, that can be done rather quickly, even for quite large databases. If you want to change the schema/user names, that can be done in a post-restore script that's run (along with any data-cleaning processes and such, if appropriate). For further checking, you can also pg_dump the result and then pg_restore into a new/clean database, which will also verify your constraints and such too. As for if you should change schema/user... that really depends on the specifics of your environment. Having the usernames be different can be good as it adds an additional level of seperation between dev and prod (so that a dev system wouldn't be able to mistakenly log into a prod system or similar...), but it also means that dev and prod don't act exactly the same, and you likely have other measures in place to reduce the risk of dev talking to prod. Thanks! Stephen
Attachment:
signature.asc
Description: PGP signature
