Wednesday, November 1, 2017

Not able to connect to Postgres PDO without database name

By Hường Hana 10:30 AM , , Leave a Comment

I am using the following connection to connect to Postgres SQL without Database as I need to fetch all the database names later for configuration

try{     $this->connection = new \PDO($this->database.":host=".$this->host,$this->user,$this->password);     $this->connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);     return $this->connection; }catch(\PDOException $e){     echo $e->getMessage(); }

But I am getting the following error 

SQLSTATE[08006] [7] FATAL: database "admin" does not exist

The following are the values that are set in the properties:

$this->database = pgsql $this->host = localhost $this->user = admin $this->password = admin

Can anyone please help me out how to connect to Postgres SQL without any database selection with PHP PDO

6 Answers

Answers 1

As you always have a default database called postgres on PostgreSQL you can By default make a connection to it like this :

$dbh = new PDO($this->database.":host=".$this->host.";dbname=postgres",$this->user,$this->password);

Then you will make another one after to the good one.

Answers 2

There are tree options:

  • postgres
  • template0
  • template1

Please, be aware that connection to the database requires permission. Here is explanation what that templates are. You can look for postgresql default database. Which is quite similar topic.

Answers 3

This answer explains it well:

https://stackoverflow.com/questions/2370525#2411860

You'll need to explicitly point to the one database that is always guaranteed to exist as described above, namely "postgres": $this->connection = new PDO($this->database.":host=".$this->host.";dbname=postgres",$this->user,$this->password);

...or if preferred, set $this->dbname = postgres and then $this->connection = new PDO($this->database.":host=".$this->host.";dbname=".$this->dbname,$this->user,$this->password);

Answers 4

For what I've understood from th error message, it seems your DSN is wrong (admin, which is your username and/or password is interpreted as the database name)...

Answers 5

In addition to the current excellent answers, also, Consider creating a db for each unix user (or at least for the "admin" user), so the "psql" command works without parameters and you can conveniently use it for interactive use.

Answers 6

Postgresql does not have a possibility to connect to the database server without specifying to an existing database.

The reason you are getting this error:

SQLSTATE[08006] [7] FATAL: database "admin" does not exist

is that if you do not specify a database name, it will default to trying to connect to a database with the same name as the user you have specified (see description of connection parameter dbname).

When postgresql is first initialized, three default databases are created:

  • postgres - intended as a general purpose database for the default administrator user postgres to access
  • template0 - Master template - should not be changed after initialization
  • template1 - Default template - used to create all new databases from

See Template Databases for a full description of these.

If you want to get a list of the available databases, you will need to connect to one of the above databases.

I would normally use template1 for this, because in theory the postgres database might have been dropped, and it is dangerous to connect to the template0 database in case you accidentally change it. Since template1 is used as the default template when creating databases, it would most likely exist.

Also note that whatever user you are connecting with needs to have permission to connect to the given database. All three of these databases are owned by the postgres user, which means if you do not connect using this user, you will have to make sure the user you are using has permission to access the database.

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment