> On 07/06/2023 23:19 CEST Jorge Silva <jorge.silva93@xxxxxxxxx> wrote: > > We have a database which recurrently we create a testing database making a > copy of it. Under the superuser “postgres” we use the command > “CREATE DATABASE totvs_test WITH TEMPLATE totvs_production;” which works > pretty well. How do you deal with existing connections to totvs_production? Connections to the template database are not possible during CREATE DATABASE and existing connections must be closed beforehand. That alone would be a dealbreaker for me given that it's a production database. But I don't know your requirements on availability. > But now I would like to allow an user, which is not the owner of the > totvs_production database, and neither is a superuser, to execute this same > command. Is it a requirement to create the database via SQL? The same can be achieved on the command line with pg_dump and pg_restore which are designed for copying databases without preventing other database connections. createdb totvs_test && pg_dump -Fc totvs_production | pg_restore -d totvs_test --single-transaction --verbose > In order to achieve this, it seems that I have to give him the access > “CREATEDB” and make our production database a template database. Yes. > Do you know if there any safety, performance or any other kind of issue in > making our production database as a template in postgres and keep using it > for production tasks? Autovacuum uses a different default freeze age for template databases [0]. So the global vacuum setting does not apply to template databases. But I don't know how that affects performance. Also I would not expect a live database to be flagged as a template database. > This user, who would make this copy, already has access to SELECT in all the > data of this database, but they have no rights in DELETE, UPDATE or INSERT. The docs[1] on templates databases don't say anything about access privileges besides the CREATEDB and SUPERUSER privileges that you already mentioned. You have to test that and see if the new database contains everything. The access privileges definitely affect what the user can dump with pg_dump. That's why pg_dump is usually run as superuser to make a full copy. I never tested it with a user that doesn't have full access. [0] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/postmaster/autovacuum.c;h=f929b62e8adaa4219694c2c74648c623d7e9f688;hb=378d73ef204d0dcbeab834d52478e8cb90578ab7#l2054 [1] https://www.postgresql.org/docs/current/manage-ag-templatedbs.html -- Erik
