Difference between revisions of "Psql"
Jump to navigation
Jump to search
| (7 intermediate revisions by the same user not shown) | |||
| Line 93: | Line 93: | ||
<code>[[\du]], [[\dg]], \ddp, \dp, \list, \dn, \dt, \c, \l</code> | <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 105: | Line 108: | ||
* <code>[[heroku psql]]</code> | * <code>[[heroku psql]]</code> | ||
* [[psql: SCRAM authentication requires libpq version 10 or above]] | * [[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}} | ||
| − | * {{ | + | * {{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).
- Ubuntu installation:
apt install postgresql-client-12 - Amazon Linux 2:
yum install postgresql.x86_64(PostgreSQL client programs)sudo amazon-linux-extras install postgresql10
Contents
Examples[edit]
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://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]
- Connect to a database
- Show databases
- Create database
- Delete database
- Create user
- List users
- List databases
- List tables:
show tables - GRANT
Activities[edit]
- Create your first database:
CREATE DATABASE "yournewdbname";
Related terms[edit]
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-instancesheroku psql- psql: SCRAM authentication requires libpq version 10 or above
- Database management:
mysql, mariadb import asyncpg
See also[edit]
- 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
Advertising:
