Re: Creating new database - SOLVED

Malcolm Warren wrote:
> template1 was full of data, presumably somehow from a badly-run dump
> and restore.
> So when I created the new database, that too was full of data from the
> template.
>
> I ran a clean pg_dump on template1 from my production database and
> restored it on my test machine, and finally everything is normal.

you can clean up template 1 2 ways...

A) drop all the objects in it as shown by \d in psql
or
B) drop template1 and recreate it using template0 which is sacrosanct.

to do this latter, while logged on as user postgres, do something like...
$ psql postgres
postgres=# drop database template1;
postgres=# create database template1 with template=template0;

to copy your database without data, I'd use something like......

$ createdb --owner=someuser newdb
$ pg_dump --schema-only olddb | psql newdb