Difference between revisions of "Psql"

From wikieduonline
Jump to navigation Jump to search
 
(49 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
{{lowercase}}
 
{{lowercase}}
 
[[wikipedia:psql]] command line tool to connect and execute queries against a [[PostgreSQL]] (port 5432) or [[Amazon Redshift]] database (port [[5439]]).
 
[[wikipedia:psql]] command line tool to connect and execute queries against a [[PostgreSQL]] (port 5432) or [[Amazon Redshift]] database (port [[5439]]).
 +
* https://www.postgresql.org/docs/current/app-psql.html
  
 
* Ubuntu installation: <code>[[apt install]] postgresql-client-12</code>
 
* Ubuntu installation: <code>[[apt install]] postgresql-client-12</code>
 +
* [[Amazon Linux 2]]:
 +
** <code>[[yum install postgresql.x86_64]]</code> (PostgreSQL client programs)
 +
** <code>[[sudo amazon-linux-extras install postgresql10]]</code>
 +
 +
* macOS: <code>[[brew install libpq]]</code>
 
* <code>[[$HOME/.pg_service.conf]]</code>
 
* <code>[[$HOME/.pg_service.conf]]</code>
 +
* <code>[[psql --help]]</code>
  
 
== Examples ==
 
== Examples ==
 
* <code>[[sudo -u postgres psql]]</code>
 
* <code>[[sudo -u postgres psql]]</code>
  
−
Connect to remote database:
+
[[Connect to remote database]]:
−
* <code>psql --host=mypostgresql.c6c8mwvfdgv0.us-west-2.rds.amazonaws.com --port=[[5432]] --username=awsuser --password --dbname=mypgdb</code>  
+
* <code>[[psql --host]]=mypostgresql.c6c8mwvfdgv0.us-west-2.rds.amazonaws.com --port=[[5432]] --username=awsuser --password [[--dbname]]=mypgdb</code>  
  
 
* <code>psql -h localhost -U YOUR_USERNAME DB_NAME</code>
 
* <code>psql -h localhost -U YOUR_USERNAME DB_NAME</code>
Line 15: Line 22:
 
* <code>psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME></code>
 
* <code>psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME></code>
  
−
* <code>psql service=MyConfiguredDB</code> (Database needs to be defined in <code>[[.pg_service.conf]]</code>)
+
* <code>[[psql service=MyConfiguredDB]]</code> (Database needs to be defined in <code>[[$HOME/.pg_service.conf]]</code>)
 +
* <code>[[psql service=MyConfiguredDB]] [[-c]] "[[\list]]"</code>
 +
 
 +
* <code>[[psql postgres]]</code>
 +
 
 +
* <code>[[psql]] "postgres://[[tsdbadmin]]@xjsgtxszr.u2gst5tl.vpc.tsdb.forge.timescale.com:5432/tsdb?[[sslmode]]=require"</code>
 +
 
  
 
=== Other examples ===
 
=== Other examples ===
  
 
  psql
 
  psql
−
  psql: FATAL:  [[role]] "root" does not exist
+
  psql: [[FATAL:]] [[role]] "root" does not exist
  
 
  [[su - postgres]] && psql
 
  [[su - postgres]] && psql
Line 26: Line 39:
 
  Type "help" for help.
 
  Type "help" for help.
 
  postgres=#
 
  postgres=#
 +
 +
psql (12.7, server 10.21)
 +
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256 .../..., bits: 256, compression: off)
 +
Type "help" for help.
 +
yourdbname=>
  
 
  sudo -u postgres psql
 
  sudo -u postgres psql
Line 37: Line 55:
 
  Error: You must install at least one postgresql-client-<version> package
 
  Error: You must install at least one postgresql-client-<version> package
  
 +
psql -h 10.10.10.10
 
  FATAL: no [[pg_hba.conf]] entry for host "1x.1xx.2xx.1xx", user "your_username", database "your_db_name", SSL off
 
  FATAL: no [[pg_hba.conf]] entry for host "1x.1xx.2xx.1xx", user "your_username", database "your_db_name", SSL off
 +
 +
Solution: error provided by the server. Review <code>[[pg_hba.conf]]</code> configuration file in the server.
  
 
  psql
 
  psql
Line 44: Line 65:
 
         connections on Unix domain socket "[[/var/run/postgresql/]].s.PGSQL.5432"?
 
         connections on Unix domain socket "[[/var/run/postgresql/]].s.PGSQL.5432"?
  
−
macOS
+
=== [[macOS]] ===
 
  psql
 
  psql
 
  psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
 
  psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
−
Is the server running locally and accepting connections on that socket?
+
Is the server running locally and accepting connections on that socket?
 +
Solution: Check if your local database is runnning or provide [[remote server to connect]] using [[--host]] or [[service]] options defined in your <code>[[$HOME/.pg_service.conf]]</code> file.
  
 
  psql service=pro
 
  psql service=pro
−
  psql: error: could not connect to server: Connection refused
+
  psql: error: could not connect to server: [[Connection refused]] Is the server running on host "your_rds_instance.eu-central-1.[[rds.amazonaws.com]]" (7.123.109.22) and accepting TCP/IP connections on port [[5432]] ?
−
        Is the server running on host "your_rds_instance.eu-central-1.[[rds.amazonaws.com]]" (7.123.109.22) and accepting
+
 
−
        TCP/IP connections on port [[5433]] ?
 
 
  psql
 
  psql
 
  Password for user YOUR_USERNAME:
 
  Password for user YOUR_USERNAME:
 
  psql: error: FATAL:  password authentication failed for user "YOUR_USERNAME"
 
  psql: error: FATAL:  password authentication failed for user "YOUR_USERNAME"
 
  FATAL:  no [[pg_hba.conf]] entry for host "xx.xx.xx.xx", user "YOUR_USERNAME", database "YOUR_DATABASE", [[SSL]] off
 
  FATAL:  no [[pg_hba.conf]] entry for host "xx.xx.xx.xx", user "YOUR_USERNAME", database "YOUR_DATABASE", [[SSL]] off
 +
 +
psql
 +
psql: error: [[connection to server on socket]] "/tmp/.s.PGSQL.5432" failed: FATAL:  database "your-db" [[does not exist]]
 +
 +
Solution: [[createdb (command)|createdb]] your-db && psql your-db
 +
 +
psql
 +
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "user" does not exist
  
 
== Options ==
 
== Options ==
−
* Port (<code>-p</code>): <code>psql -p 5432</code>
+
* Port (<code>-p</code>): <code>psql -p [[5432]]</code>
  
−
== Meta-commands ==
+
== [[Meta-commands]] ==
−
[[\du]], [[\dg]], \ddp, \dp, \list, \dn, \dt, \c, \l,
+
<code>[[\du]], [[\dg]], \ddp, \dp, \list, \dn, \dt, \c, \l</code>
  
−
== Operations ==
+
== [[Operations]] ==
 
* {{TOC operation databases}}
 
* {{TOC operation databases}}
 +
 +
== Activities ==
 +
* Create your first database: <code>[[CREATE DATABASE]] "yournewdbname";</code>
  
 
== Related terms ==
 
== Related terms ==
Line 71: Line 103:
 
* <code>[[.psqlrc]]</code>
 
* <code>[[.psqlrc]]</code>
 
* [[Postgres installation]]: <code>[[apt install postgresql]]</code>
 
* [[Postgres installation]]: <code>[[apt install postgresql]]</code>
−
* <code>[[aws rds describe-db-instances]]</code>
 
 
* <code>[[nping -tcp]] -p 5432 your_postgres_host -q</code>
 
* <code>[[nping -tcp]] -p 5432 your_postgres_host -q</code>
 
* [[Amazon Redshift query editor]]
 
* [[Amazon Redshift query editor]]
 +
* <code>[[aws rds describe-db-instances]]</code>
 
* <code>[[heroku psql]]</code>
 
* <code>[[heroku psql]]</code>
 +
* [[psql: SCRAM authentication requires libpq version 10 or above]]
 +
* [[Database management]]: <code>[[mysql (command)|mysql]], [[mariadb (command)|mariadb]]</code>
 +
* <code>[[import asyncpg]]</code>
  
 
== See also ==
 
== See also ==
 
* {{psql}}
 
* {{psql}}
−
* {{PostgreSQL}}
+
* {{SQL}}
  
  
 
[[Category:PostgreSQL]]
 
[[Category:PostgreSQL]]

Latest revision as of 10:29, 23 September 2024

wikipedia:psql command line tool to connect and execute queries against a PostgreSQL (port 5432) or Amazon Redshift database (port 5439).

Examples[edit]

Connect to remote database:

  • psql --host=mypostgresql.c6c8mwvfdgv0.us-west-2.rds.amazonaws.com --port=5432 --username=awsuser --password --dbname=mypgdb
  • psql -h localhost -U YOUR_USERNAME DB_NAME
  • psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME>
  • psql "postgres://tsdbadmin@xjsgtxszr.u2gst5tl.vpc.tsdb.forge.timescale.com:5432/tsdb?sslmode=require"


Other examples[edit]

psql
psql: FATAL:  role "root" does not exist

su - postgres && psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.
postgres=#

psql (12.7, server 10.21)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256 .../..., bits: 256, compression: off)
Type "help" for help.
yourdbname=>

sudo -u postgres psql
could not change directory to "/root": Permission denied
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

Execution with errors[edit]

psql
Warning: No existing cluster is suitable as a default target. Please see man pg_wrapper(1) how to specify one.
Error: You must install at least one postgresql-client-<version> package

psql -h 10.10.10.10 
FATAL: no pg_hba.conf entry for host "1x.1xx.2xx.1xx", user "your_username", database "your_db_name", SSL off

Solution: error provided by the server. Review pg_hba.conf configuration file in the server.

psql
psql: error: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

macOS[edit]

psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
Solution: Check if your local database is runnning or provide remote server to connect using --host or service options defined in your $HOME/.pg_service.conf file.

psql service=pro
psql: error: could not connect to server: Connection refused Is the server running on host "your_rds_instance.eu-central-1.rds.amazonaws.com" (7.123.109.22) and accepting TCP/IP connections on port 5432 ?

psql
Password for user YOUR_USERNAME:
psql: error: FATAL:  password authentication failed for user "YOUR_USERNAME"
FATAL:  no pg_hba.conf entry for host "xx.xx.xx.xx", user "YOUR_USERNAME", database "YOUR_DATABASE", SSL off

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

Solution: createdb your-db && psql your-db

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

Options[edit]

  • Port (-p): psql -p 5432

Meta-commands[edit]

\du, \dg, \ddp, \dp, \list, \dn, \dt, \c, \l

Operations[edit]

Activities[edit]

Related terms[edit]

See also[edit]

Retrieved from "https://www.wikieduonline.com/index.php?title=Psql&oldid=345310"

Advertising: