Trashed Postgres template1

Question

I was screwing around in psql and renamed the template0 and template1 before realizing their usage. Now, I am getting a "permission denied to copy database 'template1'" from inside psql and form command-line when I try to recreate template1.

To save time, is there anything else I need to know about template1 vis a vis OS read/write permissions in /data/base or granting on template1 etc.

TIA

Answer 1

You have to tell PostGreSQL that template1 is a template. If you don't, you won't be allowed to copy it if you aren't the owner:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

I had to read the source code of /src/backend/commands/dbcommands.c (found by Google) to understand that. It says:

/*
 * Permission check: to copy a DB that's not marked datistemplate, you
 * must be superuser or the owner thereof.
 */
if (!src_istemplate)
{
        if (!pg_database_ownercheck(src_dboid, GetUserId()))
                ereport(ERROR,
                                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                                 errmsg("permission denied to copy database \"%s\"",
                                                dbtemplate)));
}

And I found how to do it on a blog

my similar problem

Answer 2

How have you tried "to recreate template1"?

The easiest would be to drop the "template1" schema and recreate it from the "template0" schema. That is - if "template0" is still there.

More - here.