Восстановление template1 базы в PostgreSQL (eng) (postgresql crash recover dump)
Ключевые слова: postgresql, crash, recover, dump, (найти похожие документы)
From: Josh Berkus <josh at agliodbs.com>
Date: Mon, 17 Sep 2004 18:21:07 +0000 (UTC)
Subject: Восстановление template1 базы в PostgreSQL (eng)
Оригинал: http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php
Восстановление потерянного template1 из резервной версии template0 в PostgreSQL.
Episode 1: Restoring a Corrupted Template1 using Template0
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.
http://www2.ca.postgresql.org/users-lounge/docs/7.2/postgres/managing-databases.html#MANAGE-AG-TEMPLATEDBS
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!
Copyright © 2001-2004 PostgreSQL Global Development Group <webmaster@postgresql.org>.
