How to delete postgresql database on linux

Question

I am trying to learn postgresql on linux using the command line interface.

I have added some databases a while back, following some tutorials (which I have since forgot everything I have learned).

Now I want to delete these databases.

I made the assumption that I should be doing this by using psql, the command-line interface to postgresql.

You can see what I have tried in the following command line output, and that none of it has succeeded.

psql (9.5.6)
Type "help" for help.

postgres=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
 template0 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
(4 rows)

postgres=# dropdb template1
postgres-# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
 template0 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
(4 rows)

postgres-# DROP DATABASE template1
postgres-# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
 template0 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
(4 rows)

Do not drop template databases, read about them.

klin
– klin

2017-03-25 17:19:42 +00:00
Commented Mar 25, 2017 at 17:19 — klin
– klin, Commented Mar 25, 2017 at 17:19

Lemayzeur · Accepted Answer · 2018-05-31 02:42:36Z

15

Yes,

DROP DATABASE template1;

And dont forget to backup your database:

to backup: pg_dump name_of_database > name_of_backup_file.bak

to restore: psql empty_database < backup_file.bak

edited May 31, 2018 at 2:42

Lemayzeur

8,5153 gold badges27 silver badges55 bronze badges

answered Mar 25, 2017 at 16:38

Carlos UCR

3593 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J2112O · Accepted Answer · 2017-03-25 16:31:33Z

5

Make sure and end your SQL commands with a semicolon (;) Try issuing the command

DROP DATABASE template1;

with the semicolon on the end.

answered Mar 25, 2017 at 16:31

J2112O

6353 gold badges11 silver badges25 bronze badges

1 Comment

perlyking Over a year ago

and upper case DROP DATABASE too (my mistake)

Vu Ruby · Accepted Answer · 2020-03-28 19:09:40Z

0

If your database contains 'template1' and 'template0' db name. You can use my script below:

UPDATE pg_database SET datistemplate='false' WHERE datname='template1';
DROP DATABASE template1;

answered Mar 28, 2020 at 19:09

Vu Ruby

563 bronze badges

Collectives™ on Stack Overflow

How to delete postgresql database on linux

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related