Thread: rename
Guys, Is it possible to rename databases, and what's the syntax I should use? thanks k. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs SkyScan service. For more information on a proactive anti-virus service working around the clock, around the globe, visit http://www.messagelabs.com ________________________________________________________________________
On Wed, 2002-11-27 at 09:29, Ken Guest wrote: > Guys, > Is it possible to rename databases, and what's the syntax I should > use? Well, it's *possible* -- you can manually update the system catalogs, like so: UPDATE pg_database SET datname = 'newname' WHERE datname = 'oldname'; VACUUM FULL pg_database; (The VACUUM is required as pg_database is not MVCC-aware). However, this is not recommended operating procedure, if it destroys all your data I won't take any responsibility :-) A safer route would be to pg_dump the database and restore it with a different name. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway wrote: >On Wed, 2002-11-27 at 09:29, Ken Guest wrote: > > >>Guys, >>Is it possible to rename databases, and what's the syntax I should >>use? >> >> > >Well, it's *possible* -- you can manually update the system catalogs, >like so: > >UPDATE pg_database SET datname = 'newname' WHERE datname = 'oldname'; >VACUUM FULL pg_database; > >(The VACUUM is required as pg_database is not MVCC-aware). > >However, this is not recommended operating procedure, if it destroys all >your data I won't take any responsibility :-) A safer route would be to >pg_dump the database and restore it with a different name. > > so would pg_dump olddb | pgsql newdb work or this is too simplistic an approach? > > ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs SkyScan service. For more information on a proactive anti-virus service working around the clock, around the globe, visit http://www.messagelabs.com ________________________________________________________________________
Alvaro Herrera wrote: >On Wed, Nov 27, 2002 at 04:56:39PM +0000, Ken Guest wrote: > > >>Neil Conway wrote: >> >> >> >>>On Wed, 2002-11-27 at 09:29, Ken Guest wrote: >>> >>> >>> >>>>Is it possible to rename databases, and what's the syntax I should >>>>use? >>>> >>>> >>>However, this is not recommended operating procedure, if it destroys all >>>your data I won't take any responsibility :-) A safer route would be to >>>pg_dump the database and restore it with a different name. >>> >>> >>so would pg_dump olddb | pgsql newdb work or this is too simplistic an >>approach? >> >> > >Yeah, it should work... What do you mean by too simplistic? It is too >simple so it must be wrong? :-D > > > Something like that! Anyways I got it working - much thanks to Neil Conway. Now I have the strange occurance that creating a gist index: CREATE INDEX key2img_fr_rdtree_idx on image_fr using gist ( key2img gist__intbig_ops) with ( islossy ); works on one database (the old, test, one) but not on the new one, I get this error ERROR: DefineIndex: opclass "gist__intbig_ops" not found No errors occurred during the restore process. What do I need to do to create that index? k. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs SkyScan service. For more information on a proactive anti-virus service working around the clock, around the globe, visit http://www.messagelabs.com ________________________________________________________________________