CREATE is adding tables I don't want

Question

I'm creating and populating a new database like so:

# Create...
CREATE DATABASE music_library;

# Populate...
myname=# \i /path/to/music_library.sql

This seemed to work, but I'm now in the bizarre position such that all subsequent CREATE statements result in a new database that already has the tables and data specified in music_library.sql.

CREATE DATABASE sports_teams;
\c sports_teams
\dt


            List of relations
 Schema |  Name   | Type  |     Owner
--------+---------+-------+---------------
 public | albums  | table | me
 public | artists | table | me

I've since dropped the music library, but the problem remains.

DROP DATABASE music_library;
ERROR:  database "music" does not exist

I seem to have ended up in a situation whereby this script is run automatically following every CREATE operation. I've no idea how this happened, or how to fix it, because I can't find any mention of custom CREATE scripts for PostgreSQL.

What's going on?

Update Following Zegarek's comment I've just run the following:

\c template1
\dt
>>>
            List of relations
 Schema |  Name   | Type  |     Owner
--------+---------+-------+---------------
 public | albums  | table | paulpatterson
 public | artists | table | paulpatterson

Does this suggest that I have unwittingly altered the template? If it does, how do I revert it to its intended state?

Answer 1

One way this could happen is if you:

1. Connected to template1 database.
2. Issued CREATE DATABASE music_library; without running \c music_library afterwards, so you stayed on template1.
3. You ran \i /path/to/music_library.sql but the commands in it did not attempt to switch to the other db either so it populated template1.

Quoting the doc linked above:

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1.

Since these steps would lead to the template db now holding everything music_library.sql was meant to set up, all future databases will mirror that, unless you use the template clause:

CREATE DATABASE music_library TEMPLATE some_other_db_than_template1;

You can also reset the template1:

ALTER DATABASE template1 IS_TEMPLATE=false;
DROP DATABASE template1;
CREATE DATABASE template1 TEMPLATE template0 IS_TEMPLATE=true;