PostgreSQL Connecting to a Database: Difference between revisions
Jump to navigation
Jump to search
| (10 intermediate revisions by the same user not shown) | |||
| Line 8: | Line 8: | ||
=Local Connection= | =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 local socket, and the identity used to connect is given by the O/S user account: | 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: | ||
<syntaxhighlight lang='bash'> | <syntaxhighlight lang='bash'> | ||
| Line 14: | Line 14: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
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. | 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: | ||
= | <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> | |||
=Options= | TO REFACTOR: | ||
==Options== | |||
==-h|--host= == | ===-h|--host= === | ||
==-U|--username= == | ===-U|--username === | ||
==-d|--dbname= == | ===-d|--dbname === | ||
Specifies the name of the [[PostgreSQL_Concepts#Database|database]] to connect to. | Specifies the name of the [[PostgreSQL_Concepts#Database|database]] to connect to. | ||
===-p=== | |||
==-p== | |||
Specifies the port. | Specifies the port. | ||
===-c, --command=== | |||
==-c, --command== | |||
Run only single command (SQL or internal) and exit. | Run only single command (SQL or internal) and exit. | ||
==Examples== | |||
=Examples= | ===Connect to a Remote Database=== | ||
= | |||
==Connect to a Remote Database== | |||
psql -h <''host-name''> -U <''username''> -d <''database-name''> | psql -h <''host-name''> -U <''username''> -d <''database-name''> | ||
| Line 58: | Line 56: | ||
If the database name is not specified, will connect to <font color=darkgray>...</font> | 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== | ===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 | 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