CODEWITHSUNDEEP
postgresql
Migwell
Migwell

Reputation: 20127

Is the "postgres" database always available in PostgreSQL?

I'm writing a script that needs to get all the databases in a postgres cluster, and so it needs to connect to a database (it doesn't matter which) to do this.

Is it okay to assume that the "postgres" database will always be available to connect to, or if not, is there a programatic way to determine an arbitrary database in the cluster that I can connect to?

Upvotes: 9

Views: 3830

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

PostgreSQL has three "system" databases: postgres, template0, and template1. I'm pretty sure all of them can be dropped. (You might want to test this in a virtual machine.) I'm certain that postgres and template1 can be dropped by a privileged user. By default, template0 doesn't accept connections.

Having said that, the main purpose of "postgres" is to give you a database to connect to.

The postgres database is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of template1 and can be dropped and recreated if necessary.

So it's a reasonable assumption, but not a bullet-proof assumption. I can imagine some paranoid (careful) shops might drop "postgres" and use a different database as their internal default in case their server were compromised. That's kind of like using a non-default port for SSH.

Upvotes: 9

avenet
avenet

Reputation: 3043

In my personal experience, yes, it's almost guaranteed that it will exist. But, according to the PostgreSQL official documentation:

The database server itself does not require the postgres database to exist, 
but many external utility programs assume it exists.

So, you can't assume it will always be available, although in the practice, it will be available almost for sure.

Upvotes: 6

Related Questions