How to recover from template1 modification?

If you have modified template1 and want to revert it to its original, clean state, you can do so by using template0 as the source.

Since template1 is usually in use or exists as a default, you can't simply "overwrite" it while the system is running normally. The safest way to "reset" it is to drop it and recreate it from template0.

Follow these steps inside your psql shell:

1. Connect to the postgres database

You cannot drop template1 if you are currently connected to it. Connect to the administrative postgres database instead:

\c postgres

2. Drop the modified template1

Run the following command to remove the current version of template1:

DROP DATABASE template1;

3. Recreate template1 from template0

Now, create a brand new template1 using template0 as the template. This copies the "factory settings" back over:

CREATE DATABASE template1 STRATEGY = 'FILECOPY' TEMPLATE template0;

(Note: In most versions, simply CREATE DATABASE template1 TEMPLATE template0; is sufficient).

4. Mark it as a template again

Finally, you need to tell PostgreSQL that this new database is officially a template database:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

Important Note:

If you cannot drop the database because other sessions are connected to it, you may need to kick other users off first:

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'template1';

Why this works:
Because you followed the rule of not modifying template0, it remained a perfect copy of the system's original state, allowing you to "reset" your environment easily!

Now that you've learned about recovery, would you like to continue with the lab and see how to use \dt to list tables?