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

Activities

• Create your first database: CREATE DATABASE "yournewdbname";

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
• Database management: mysql, mariadb
• import asyncpg

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
• SQL, NewSQL, RDMS: GraphQL (2015), SQLite, select, insert, select count, Distributed SQL, TablePlus, Cache hit ratio, pg_stat_user_tables, EXPLAIN, Index (PostgreSQL), DDL, SQL/MED, Group by, List users, SQL Injection, SQLAlchemy, CREATE USER, CREATE ROLE, EXPLAIN, ALTER DATABASE, REVOKE, NOLOCK, NOWAIT, EXISTS, Unique constraint, .sql