[IMP] db: create new databases based on template0

As a follow-up to commit 276ea817f4675a73576905edbc2a4567570ac206,
we change the default template database used for CREATE DATABASE
operations, and we avoid setting the LC_COLLATE option unless
this default template is used (to avoid locale compatibility errors).

Rationale for the default template choice:

PostgreSQL uses `template1` by default, and recommends that users alter
`template1` if they need a custom template, while keeping template0
virgin. This is likely to be the reason why we had `template1` as the
default previously (from a1e5c645d97e4e8f48cec546e858643029b7424a).

However, for new Odoo databases, it's actually better to use `template0`
as template, for multiple reasons:
- We do not customize the template database, and the system will work
  fine when starting from a completely virgin database
- Since template0 does not allow connections by default, we have a
  stronger guarantee that no user will be connected to the template
  database (which would otherwise block database creations!)
- We use the same logic for creating new databases and for creating
  placholder databases for restoring dumps. In the latter case,
  PostgreSQL recommends using a virgin template, to obtain a perfect
  copy of the database being restored (unaffected by any template
  customization)
- Using `template0` as a template lets us choose any encoding and locale
  settings (LC_COLLATE/LC_CTYPE) upon creation. Any other database
  template might cause errors unless we select compatible encodings
  and locale settings.
  As the locale setting is system-dependent, we can't just hope
  that there will be no compatibility problem, we must explicitly
  default to template0. Therefore the change in
  276ea817f4675a73576905edbc2a4567570ac206 was prone to causing
  locale compatibility errors.

Finally, if users have a reason to prefer a different template, they
should select it explicitly with the --db_template config.

Note: existing environments will likely continue to use `template1`
as a default due to presence of older config files.

Complements #25196

2 files changed, 8 insertions, 2 deletions

diff --git a/odoo/service/db.py b/odoo/service/db.py
index 063d8eaee7d..6bac41282de 100644
--- a/odoo/service/db.py
+++ b/odoo/service/db.py
@@ -97,7 +97,13 @@ def _create_empty_database(name):
             raise DatabaseExists("database %r already exists!" % (name,))
         else:
             cr.autocommit(True)     # avoid transaction block
-            cr.execute("""CREATE DATABASE "%s" ENCODING 'unicode' LC_COLLATE 'C' TEMPLATE "%s" """ % (name, chosen_template))
+
+            # 'C' collate is only safe with template0, but provides more useful indexes
+            collate = "LC_COLLATE 'C'" if chosen_template == 'template0' else ""
+            cr.execute(
+                """CREATE DATABASE "%s" ENCODING 'unicode' %s TEMPLATE "%s" """ %
+                (name, collate, chosen_template)
+            )
 
 @check_db_management_enabled
 def exp_create_database(db_name, demo, lang, user_password='admin', login='admin', country_code=None):
diff --git a/odoo/tools/config.py b/odoo/tools/config.py
index 7fa33f400fd..1a026486bbb 100644
--- a/odoo/tools/config.py
+++ b/odoo/tools/config.py
@@ -221,7 +221,7 @@ class configmanager(object):
                          help="specify the database ssl connection mode (see PostgreSQL documentation)")
         group.add_option("--db_maxconn", dest="db_maxconn", type='int', my_default=64,
                          help="specify the the maximum number of physical connections to PostgreSQL")
-        group.add_option("--db-template", dest="db_template", my_default="template1",
+        group.add_option("--db-template", dest="db_template", my_default="template0",
                          help="specify a custom database template to create a new database")
         parser.add_option_group(group)