Skip site navigation (1)
Skip section navigation (2)
Episode 1: Restoring a Corrupted Template1 using Template0
Copyright © May 2002 Josh Berkus.
May be redistributed in unaltered form including this copyright notice. All other rights reserved.
All of us have had the experience where our typing fingers get ahead of our brains. Usually, for me, this happens on e-mail, but occasionally I really goof things up on my database server by pressing "Enter" at the wrong time. This was one of those times.
I'd dumped the database I was working on, and edited some referential integrity links and constraints (this is probably the best way to do this in PostgreSQL). Now, I was ready to re-load my edited database. On my console:
Anybody spot my mistake? I did, after about 30 seconds ... which was 30 seconds too late. I'd loaded the entire database schema into Template1, the template database, and not the StaffOS database! This would mess up every database I created on the server thereafter.
(For those who don't know, Template1 is the PostgreSQL "database template". You should modify it when you want something -- for example, a user or the PL/pgSQL language -- added to all databases you create on that server. However, you do not want to use it as a regular database ... ever!)
What could I do? I could dump the entire server, INITDB, and reload all the databases one at a time (eleven databases - ouch!). I could try and clean up the Template1 database by hand, but with 28 tables, 40 views, and 85 functions in the StaffOS database, that solution would be nigh impossible.
So, instead, I got on the mailing lists. A helpful user on PGSQL-SQL pointed me to this documentation page.
Eureka! I cried. I was saved. In PostgreSQL 7.2, there was a second template database, Template0, which exists as an emergency backup to Template1 -- just in case anyone blundered as badly as me.
Speaking of blunders, I immediately backed up the whole server. Disk space is cheap, and one can't be too careful. (Note: If you proceed on any of the instructions below, be aware that messing with template1 and template0 can crash your database server. We are not responsible for any problems.)
However, to protect its status as an emergency backup and prevent mucking it up, too, Template0 is protected. Therefore:
The secret is a "system table" called pg_databases. This table exists in all databases, and defines some basic properties for the databases. We're particularly interested in two of those properties, datistemplate and datallowconn. "datistemplate" tells us which are our template databases, and "datallowconn" tells us to which databases registered users may connect. If we look at the table, we see:
In order to connect to template0, we need to change that flag:
Now we can connect, and drop the Template1 database in order to replace it with a copy of Template0.
Ooops! We need to set another flag:
Now, we can (this is the risky part, backup first!):
Whew! Quickly, now, lets put stuff back the way we found it:
Unfortunately, I have lost a few things in this process. I had several functions set up in Template1 that I wanted to keep there, such as the results of "createlang plpgsql" and a few text-parsing functions. These I had to re-create by hand. I reccomend that you create a file of these Template1 modifications somewhere to save yourself the effort.
Finally, acting on the advice of the docs, I did one more thing to ensure maximum efficiency from my database server:
Whew! There we are, back in business!
Peripheral Links
Header And Logo
|
Site Navigation
| This page is user contributed documentation. See the bottom of the page for information about the author. | |
Restoring a Corrupted Template1 using Template0
Technical documentation > Community Generated Articles, Guides, and Documentation > Restoring a Corrupted Template1 using Template0
Adventures in PostgreSQL
Episode 1: Restoring a Corrupted Template1 using Template0
Copyright © May 2002 Josh Berkus.
May be redistributed in unaltered form including this copyright notice. All other rights reserved.
All of us have had the experience where our typing fingers get ahead of our brains. Usually, for me, this happens on e-mail, but occasionally I really goof things up on my database server by pressing "Enter" at the wrong time. This was one of those times.
I'd dumped the database I was working on, and edited some referential integrity links and constraints (this is probably the best way to do this in PostgreSQL). Now, I was ready to re-load my edited database. On my console:
chocolate-mousse:> psql -U postgres template1
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=# DROP DATABASE staffos;
DROP
template1=# CREATE DATABASE staffos;
CREATE
template1=# \i staffos_revised.pgdump
CREATE
CREATE
CREATE
CREATE
etc ...
Anybody spot my mistake? I did, after about 30 seconds ... which was 30 seconds too late. I'd loaded the entire database schema into Template1, the template database, and not the StaffOS database! This would mess up every database I created on the server thereafter.
(For those who don't know, Template1 is the PostgreSQL "database template". You should modify it when you want something -- for example, a user or the PL/pgSQL language -- added to all databases you create on that server. However, you do not want to use it as a regular database ... ever!)
What could I do? I could dump the entire server, INITDB, and reload all the databases one at a time (eleven databases - ouch!). I could try and clean up the Template1 database by hand, but with 28 tables, 40 views, and 85 functions in the StaffOS database, that solution would be nigh impossible.
So, instead, I got on the mailing lists. A helpful user on PGSQL-SQL pointed me to this documentation page.
Eureka! I cried. I was saved. In PostgreSQL 7.2, there was a second template database, Template0, which exists as an emergency backup to Template1 -- just in case anyone blundered as badly as me.
Speaking of blunders, I immediately backed up the whole server. Disk space is cheap, and one can't be too careful. (Note: If you proceed on any of the instructions below, be aware that messing with template1 and template0 can crash your database server. We are not responsible for any problems.)
However, to protect its status as an emergency backup and prevent mucking it up, too, Template0 is protected. Therefore:
template1=# \c template0
FATAL 1: Database "template0" is not currently accepting connections
Previous connection kept
The secret is a "system table" called pg_databases. This table exists in all databases, and defines some basic properties for the databases. We're particularly interested in two of those properties, datistemplate and datallowconn. "datistemplate" tells us which are our template databases, and "datallowconn" tells us to which databases registered users may connect. If we look at the table, we see:
template1=# select * from pg_database;
datname | datdba | encoding | datistemplate | datallowconn |
-------------+--------+----------+---------------+--------------+
backup_test | 27 | 0 | f | t |
britlist | 27 | 0 | f | t |
dcl | 1 | 0 | f | t |
template0 | 1 | 0 | t | f |
kevinprob | 28 | 0 | f | t |
kitchen | 1 | 0 | f | t |
oooconlist | 103 | 0 | f | t |
regression | 1 | 0 | f | t |
staffos | 27 | 0 | f | t |
template1 | 1 | 0 | t | t |
etc ...
In order to connect to template0, we need to change that flag:
template1=# UPDATE pg_database SET datallowconn = TRUE
template1-# WHERE datname = 'template0';
UPDATE 1
Now we can connect, and drop the Template1 database in order to replace it with a copy of Template0.
template1=# \c template0
CONNECT
template0=# drop database template1;
ERROR: DROP DATABASE: database is marked as a template
Ooops! We need to set another flag:
template0=# UPDATE pg_database SET datistemplate = FALSE
template0-# WHERE datname = 'template1';
UPDATE 1
Now, we can (this is the risky part, backup first!):
template0=# drop database template1;
DROP
template0=# create database template1 with template = template0;
CREATE
Whew! Quickly, now, lets put stuff back the way we found it:
template0=# UPDATE pg_database SET datistemplate = TRUE
template0-# WHERE datname = 'template1';
UPDATE 1
template0=# \c template1
CONNECT
template1=# UPDATE pg_database SET datallowconn = FALSE
template1-# WHERE datname = 'template0';
Unfortunately, I have lost a few things in this process. I had several functions set up in Template1 that I wanted to keep there, such as the results of "createlang plpgsql" and a few text-parsing functions. These I had to re-create by hand. I reccomend that you create a file of these Template1 modifications somewhere to save yourself the effort.
Finally, acting on the advice of the docs, I did one more thing to ensure maximum efficiency from my database server:
template1-# VACUUM FULL FREEZE;
VACUUM
Whew! There we are, back in business!