simple howto about restoring damaged template1

this is nothing new, but i blog it to have a place to point people to.

let's assume you accidentally loaded dump file in template1 database.

this is definitely not something one could have wanted (i mean i can see some uses for this but it's not really likely).

so, now you want to cleanup your template1.

how? that's easy.

connect with your superuser account (postgres) to some database other than template1.

and then issue these queries:

  1. # update pg_database set datistemplate = false where datname = ‘template1';
  2. # drop database template1;
  3. # create database template1 with template template0;
  4. # update pg_database set datistemplate = true where datname = ‘template1';

be sure that there is no connection to template1 at the moment you're dealing with it.

and that's all. nothing really complicated.

5 thoughts on “simple howto about restoring damaged template1”

  1. Posty po angielsku a kategorie po polsku 😉 Ułatw zagraniczniakom przeglądanie zasobów ;p

  2. @marek:
    kategorie tyczą się zasadniczo starych postów.
    nowe i tak są wszystkie w kategorii postgresql.
    a stare są po polsku, więc tłumaczenie ich na angielski byłoby trochę bez sensu.
    wiem, nie jest idealnie, ale tak to już jest jak się ma bloga w dwóch językach.

  3. Thank You for idea! 🙂

    My way is not so good:
    1) pg_dumpall
    2) stop DB cluster
    3) backup PG *.conf
    4) remove DB data directory
    5) initialize DB cluster (initdb)
    6) restore *.conf
    7) start DB cluster
    8) restore data from 1)

  4. Uwaga językowa: plural “this” -> “these” (te) lub “those” (tamte)

    s/this queries/these queries/

Comments are closed.