How to add Database to default server in Postgres, if all databases are deleted?

Question

I ran and new node application that used a Postgres SQL server. It created a default database called postgres and I wanted to remove it.

I used the below command and close the connection and was able to remove that database.

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

Now the pgAdmin looks like the below image.

I am not sure how to add a database again here. It always gives me errors while adding a new server that Database "postgres" does not exist. Any ideas or search query to google about this issue?

Laurenz Albe · Accepted Answer · 2022-09-14 15:04:56Z

2

I am tempted to say that if you are randomly deleting system databases without first researching if they are needed, you deserve what you get. But while it is customary to leave the postgres database as default database for tools to connect to, it is certainly not forbidden to drop it.

The solution in your case is probably to reconfigure your pgAdmin connection to use template1 instead of postgres as "maintenance database". The only problem with that (and the main reason why the postgres database was introduced) is that it will prevent you from creating new databases with template1 as the (default) template if others are connected to that database as well.

edited Sep 14, 2022 at 15:04

answered Sep 14, 2022 at 6:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

Richard Huxton Over a year ago

Ah, I couldn't remember if your own connection would prevent you using it as a template too.

Laurenz Albe Over a year ago

@RichardHuxton No, your own connection is no problem.

NoughT Over a year ago

Thank you very much for your answer and for your time. Tbh it was easier to uninstall the server to fix the issue. I have tried so many things and was fed up. Was glad, I did this in the begging.

Richard Huxton · Accepted Answer · 2022-09-14 06:12:51Z

0

You are connecting as user "postgres" and the default database name it will try is also "postgres". You should be able to specify a different database in the pgadmib settings though. Try "template0" and connect to that to create a new db.

answered Sep 14, 2022 at 6:12

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

1 Comment

Laurenz Albe Over a year ago

You cannot connect to template0, and it is illegal to change that.

Collectives™ on Stack Overflow

How to add Database to default server in Postgres, if all databases are deleted?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related