Обсуждение: restoring template1
I inadvertantly wrote over the template1 file when I restored my data from a pgdump. So how can I get the original template1 back from template0 now that they name the files with numbers. It's gotta be something simple but I can't find it in the manual. -- Dr. Roy F. Cabaniss Associate Professor of Business University of Arkansas Monticello http://cabanisspc.uamont.edu/~rcaban
Roy Cabaniss <rcaban@cabanisspc.uamont.edu> writes: > So how can I get the original template1 back from template0 DROP DATABASE; CREATE DATABASE. Note the comments in http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/managing-databases.html#MANAGE-AG-TEMPLATEDBS regards, tom lane
On Wednesday 13 February 2002 16:03, Tom Lane wrote: > Roy Cabaniss <rcaban@cabanisspc.uamont.edu> writes: > > So how can I get the original template1 back from template0 Just to help others who find themself in a similar situation to me. To recap. I accidently overwrote my template1 when doing a restore and now it is full of things I do NOT want in a template. Non-unique sequences. Indexes, and tables. Tom Lane gave the following instructions.: > DROP DATABASE; CREATE DATABASE. Note the comments in Ok.. I went psql template1 (because you have to be in a database to use the postgres commands and got the following error. template1=# DROP DATABASE template1; ERROR: DROP DATABASE: cannot be executed on the currently open database Silly me... that makes sense... I can't drop a database I am in the middle of. Quit and go into another database called southern. southern=# DROP DATABASE template1; ERROR: DROP DATABASE: database is marked as a template ok, things are a little more serious. I still have the corrupted template1. http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/managing-databases.html#MANAGE-AG-TEMPLATEDBS does not seem to address the problems I am having. It assumes that you can drop the database in the first place. Let's try another tack. open phpPgAdmin. using both 2.4-beta.1 and 2.3.1 In the much older versions it used to show the template database. It no longer does. New and improved. Darn. Open Webmin. Aha. It does show template one. Drop the database. Nope it does not allow that. Ok. I Can see the database however. Drop every table that I accidently put into template1. That it allows. Better. But webmin does not show sequences, indexes or that other things I filled template1 with by accident. So I cannot delete them. Grrrr. back into psql template1 delete every sequence manually delete everything else I added manually. at last a pristine template. That is what I ended up having to do. I am sure that the drop database / create database sequence will work. But the manual does not say how to unmark something as a template so it can be dropped. Not that I found. -- Dr. Roy F. Cabaniss Associate Professor of Business University of Arkansas Monticello http://cabanisspc.uamont.edu/~rcaban
Hi. How about this way: pg_dump -csu <database> >clean_schema.sql (replace <database> with the name of the database you accidently imported into template1). Now open the created file with your favourite text editor and delete everything after the last "DROP ..." line and execute it in psql via \i clean_schema.sql This will drop all tables (including all triggers for that table), indizes, sequences and functions which don't belong in template1. Worked for me a few times. Cheers, Florian > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Roy Cabaniss > Sent: Thursday, February 14, 2002 3:44 PM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] restoring template1 > > > On Wednesday 13 February 2002 16:03, Tom Lane wrote: > > Roy Cabaniss <rcaban@cabanisspc.uamont.edu> writes: > > > So how can I get the original template1 back from template0 > > Just to help others who find themself in a similar situation to me. > > To recap. I accidently overwrote my template1 when doing a > restore and now > it is full of things I do NOT want in a template. Non-unique sequences. > Indexes, and tables. > > Tom Lane gave the following instructions.: > > > DROP DATABASE; CREATE DATABASE. Note the comments in > > Ok.. I went psql template1 (because you have to be in a database > to use the > postgres commands and got the following error. > > template1=# DROP DATABASE template1; > ERROR: DROP DATABASE: cannot be executed on the currently open database > > Silly me... that makes sense... I can't drop a database I am in > the middle > of. Quit and go into another database called southern. > > southern=# DROP DATABASE template1; > ERROR: DROP DATABASE: database is marked as a template > > ok, things are a little more serious. I still have the corrupted > template1. > > http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/managi > ng-databases.html#MANAGE-AG-TEMPLATEDBS > does not seem to address the problems I am having. It assumes > that you can > drop the database in the first place. > > Let's try another tack. open phpPgAdmin. using both 2.4-beta.1 > and 2.3.1 In > the much older versions it used to show the template database. > It no longer > does. New and improved. Darn. > > Open Webmin. Aha. It does show template one. Drop the > database. Nope it > does not allow that. Ok. I Can see the database however. Drop > every table > that I accidently put into template1. That it allows. Better. > But webmin > does not show sequences, indexes or that other things I filled > template1 with > by accident. So I cannot delete them. Grrrr. > > back into psql template1 > > delete every sequence manually > delete everything else I added manually. > > at last a pristine template. > > That is what I ended up having to do. I am sure that the drop database / > create database sequence will work. But the manual does not say how to > unmark something as a template so it can be dropped. Not that I found. > > > -- > Dr. Roy F. Cabaniss > Associate Professor of Business > University of Arkansas Monticello > http://cabanisspc.uamont.edu/~rcaban > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Roy Cabaniss <rcaban@cabanisspc.uamont.edu> writes:
> southern=# DROP DATABASE template1;
> ERROR: DROP DATABASE: database is marked as a template
Sorry about that; but perhaps you should have read the whole section I
pointed you to. The procedure that would actually work would be
something like (as superuser, from a different database):
regression=# update pg_database set datistemplate = false where datname
regression-# = 'template1';
UPDATE 1
regression=# drop database template1;
DROP DATABASE
regression=# create database template1 with template = template0;
CREATE DATABASE
regression=# update pg_database set datistemplate = true where datname
regression-# = 'template1';
UPDATE 1
regression=#
regards, tom lane