Greetings,
* Hilbert, Karin (ioh1@psu.edu) wrote:
> The other year, we had to restore the prod database backup to dev & that changed the schema name. I was thinking
thatit would be better have the same names used for dev & prod so that restores from one environment to another would
beeasier. (That's a standard that our DBA team employs for our SQL Server databases.) Does it make sense to also
employthat 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