psql

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: apt install postgresql-client-12
• Amazon Linux 2:
  • yum install postgresql.x86_64 (PostgreSQL client programs)
  • sudo amazon-linux-extras install postgresql10

• macOS: brew install libpq
• $HOME/.pg_service.conf
• psql --help

Examples

• sudo -u postgres psql

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 service=MyConfiguredDB (Database needs to be defined in $HOME/.pg_service.conf)
• psql service=MyConfiguredDB -c "\list"

• psql postgres

• psql "postgres://tsdbadmin@xjsgtxszr.u2gst5tl.vpc.tsdb.forge.timescale.com:5432/tsdb?sslmode=require"

Other examples

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

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

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

• Port (-p): psql -p 5432

Meta-commands

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

Operations

• Connect to a database
• Show databases
• Create database
• Delete database
• Create user
• List users
• List databases
• List tables: show tables
• GRANT

Related terms

• pg_hba.conf (PostgreSQL Client Authentication Configuration File)
• .psqlrc
• Postgres installation: apt install postgresql
• nping -tcp -p 5432 your_postgres_host -q
• Amazon Redshift query editor
• aws rds describe-db-instances
• heroku psql
• psql: SCRAM authentication requires libpq version 10 or above

See also

• PostgreSQL: psql, .psqlrc, .pgpass, $HOME/.pg service.conf, -U, -W, -d, meta-commands: \du, \dg, \ddp, \dp, \list, \dn, \dt, \c, \l, \di, \q, show users, show, su - postgres, psql -c, \?, \connect, \conninfo, --version, --help, PL/pgSQL
• PostgreSQL: pg_dump, pg_restore, pgAdmin, pg_config, psql, pg_ctl, pg_isready, initdb, Role, Schema, DBeaver, Navicat, DataGrip, OmniDB, Adminer, docker-compose.xml PostgreSQL, PostgreSQL version, PostgreSQL logs, postgresql.conf, pg_hba.conf, $HOME/.pg_service.conf, Create database (PostgreSQL), Create user, createdb, GRANT, pg_stat, PostgreSQL VACUUM, EXPLAIN, pg stat activity, Autovacuum, ALTER DATABASE, PostgreSQL statistics collector, Shared buffers, EXPLAIN (PostgreSQL), EXPLAIN ANALYZE, Bitmap scan, EXPLAIN VERBOSE, EXPLAIN VERBOSE (PostgresSQL), WAL, ALTER USER, CREATE ROLE, CREATE USER, \du, show users, \l, The Statistics Collector, pganalyze, cron.schedule, Master, pg_tables, PostgreSQL replication, CREATE, SET, TOAST, PgBouncer, Restore DB, Index, meta-commands, Table, foreign table, Schema, Sequence, Views, materialized view, Table Partitioning, monitoring, PostgreSQL System Administration Functions, PostgreSQL extension, privileges, logging, PGTune, PostgreSQL parameter tunning, PostgreSQL modules, pgbench, PostgreSQL users, catalogs