PostgreSQL Connecting to a Database: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(26 intermediate revisions by the same user not shown)
Line 3: Line 3:
* [[PostgreSQL Operations]]
* [[PostgreSQL Operations]]


=Connect=
=Overview=


Connect to a [[PostgreSQL_Concepts#Database|database]].
Assuming that a [[PostgreSQL_Concepts#Instance|PostgreSQL instance]] is online and available, a user must connect to a particular [[PostgreSQL_Concepts#Database|database]] hosted by the instance before performing data operations. The user establishes a [[PostgreSQL_Concepts#Session|session]], over a lower-level network or local socket [[PostgreSQL_Concepts#Connection|connection]], but in practice, the term sessions and connections are used interchangeably.


=Options=
=Local Connection=
If the <code>psql</code> client and the PostgreSQL instance run on the same machine, the connection is established by default over a [[PostgreSQL_Concepts#Local_Socket_Connection|local socket]], and the identity used to connect is given by the O/S user account:


==-h|--host= ==
<syntaxhighlight lang='bash'>
==-U|--username= ==
psql
==-d|--dbname= ==
</syntaxhighlight>


=Examples=
Assuming that the username of the user executing the command is "bob", the command will attempt to connect to a database named "bob", under the user's O/S account identity. If no such database exists, the command fails with:


==Connect to a Remote Database==
<font size=-2>
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "bob" does not exist
</font>
 
==<span id='Connect_Locally_to_the_Default_Database'></span>Local Connection to a Specific Database==
 
<syntaxhighlight lang='bash'>
psql <database-name>
</syntaxhighlight>
Example:
<syntaxhighlight lang='bash'>
psql postgres
 
psql (14.9 (Homebrew))
Type "help" for help.
 
postgres=#
</syntaxhighlight>
 
=Remote Connection=
<font color=darkkhaki>
TO REFACTOR:
==Options==
===-h|--host= ===
===-U|--username ===
===-d|--dbname ===
Specifies the name of the [[PostgreSQL_Concepts#Database|database]] to connect to.
===-p===
Specifies the port.
===-c, --command===
Run only single command (SQL or internal) and exit.
==Examples==
===Connect to a Remote Database===


  psql -h <''host-name''> -U <''username''> -d <''database-name''>
  psql -h <''host-name''> -U <''username''> -d <''database-name''>


  psql -h dev01.example..us-west-2.rds.amazonaws.com -U root -d dev01
  psql -h dev01.example.us-west-2.rds.amazonaws.com -U root -d dev01
 
If the database name is not specified, will connect to <font color=darkgray>...</font>


==Connect to the Default Database==
===Connect to the Default Database===


  psql -h localhost -U admin
  psql -h localhost -U admin


==Connect as a Specific User to a Database==
===Connect as a Specific User to a Database===


  psql -U <''username''> <''dbname''>
  psql -U <''username''> <''dbname''>
===Connect to a PostgreSQL Instance Running in a Kubernetes Pod that Has Been Exposed as a NodePort Service===
psql -h localhost -p 5432 -U postgres

Latest revision as of 22:34, 6 October 2023

Internal

Overview

Assuming that a PostgreSQL instance is online and available, a user must connect to a particular database hosted by the instance before performing data operations. The user establishes a session, over a lower-level network or local socket connection, but in practice, the term sessions and connections are used interchangeably.

Local Connection

If the psql client and the PostgreSQL instance run on the same machine, the connection is established by default over a local socket, and the identity used to connect is given by the O/S user account:

psql

Assuming that the username of the user executing the command is "bob", the command will attempt to connect to a database named "bob", under the user's O/S account identity. If no such database exists, the command fails with:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "bob" does not exist

Local Connection to a Specific Database

psql <database-name>

Example:

psql postgres

psql (14.9 (Homebrew))
Type "help" for help.

postgres=#

Remote Connection

TO REFACTOR:

Options

-h|--host=

-U|--username

-d|--dbname

Specifies the name of the database to connect to.

-p

Specifies the port.

-c, --command

Run only single command (SQL or internal) and exit.

Examples

Connect to a Remote Database

psql -h <host-name> -U <username> -d <database-name>
psql -h dev01.example.us-west-2.rds.amazonaws.com -U root -d dev01

If the database name is not specified, will connect to ...

Connect to the Default Database

psql -h localhost -U admin

Connect as a Specific User to a Database

psql -U <username> <dbname>

Connect to a PostgreSQL Instance Running in a Kubernetes Pod that Has Been Exposed as a NodePort Service

psql -h localhost -p 5432 -U postgres