postgresql - template0 and template1 database dropped accidently

ID : 274515

viewed : 44

Tags : postgresqlpostgresql





Top 5 Answer for postgresql - template0 and template1 database dropped accidently

vote vote

100

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 :)

vote vote

87

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.

vote vote

80

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

vote vote

65

on my debian machine, i upgraded some clusters to version 11. the template[01] databases on the target cluster have been dropped, rendering me with the ones from the older versions.

as root, i created a new cluster with pg_createcluster.

as user postgres:

dump the databases of the new cluster with pg_dumpall (with the --clean flag).

drop the new cluster.

import the resulted source in your cluster with psql.

i would remove the drop role postgres and creation of the same.

best regards, alex

vote vote

52

Top 3 video Explaining postgresql - template0 and template1 database dropped accidently







Related QUESTION?