template0 and template1 database dropped accidently

On my CentOS 7 box, I was not so lucky as to still have a database to connect to. However:

su postgres -c "initdb /var/lib/pgsql/data"

(as root) created a template0 and template 1 to work with.


For postgres 12.3 the following worked for me.

I deleted template1 not knowing what it was. I was able to create it from template0. You could also create it from postgres.

createdb -T template0 template1
createdb -T postgres template1

This is suggested in the docs -

template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects.

https://www.postgresql.org/docs/current/manage-ag-templatedbs.html


Luckily I had postgres database preserved because it was required for the postgres user to log into psql. Thus, created a template0 and template1 database :

create database template0 TEMPLATE postgres;

and same for template1. Then executed :

update pg_database set datistemplate=true  where datname='template0';

for both databases to stop myself from accidentally deleting these templates again.

Everything works fine now :)

Tags:

Postgresql