Difference between revisions of "DB/Postgres PostgreSQL"
< DB
Jump to navigation
Jump to search
Line 41: | Line 41: | ||
SELECT * FROM pg_stat_activity; | SELECT * FROM pg_stat_activity; | ||
SELECT usename,application_name,client_addr,backend_start,state FROM pg_stat_activity; | SELECT usename,application_name,client_addr,backend_start,state FROM pg_stat_activity; | ||
usename | application_name | client_addr | backend_start | state | usename | application_name | client_addr | backend_start | state | ||
----------+------------------+--------------+-------------------------------+-------- | ----------+------------------+--------------+-------------------------------+-------- |
Revision as of 15:33, 18 November 2021
- Managing PostgreSQL users and roles 04 MAR 2019, AWS Blog
Cli client
# Install apt-get install -yq postgresql-client # Connect export POSTGRES_DB=postgres export POSTGRES_USER=postgres export POSTGRES_PASSWORD=mypassword psql -h localhost -p 5432 -U postgres postgres psql (12.3 (Debian 12.3-1.pgdg100+1)) Type "help" for help. postgres=# \l -- list all databases \dt -- list all the tables in the current database \c mydatabase -- switch database \d <table_name> -- describe a table \g -- execute previous command \? -- help \h DROP TABLE -- help about particular comamnd \timing -- execution times of queries # Show version postgres=# SELECT version(); version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit (1 row)
Operational queries
-- Reset user password, no need to flush or log out ALTER USER user_name WITH PASSWORD 'new_password'; -- Show connections to database SELECT * FROM pg_stat_activity; SELECT usename,application_name,client_addr,backend_start,state FROM pg_stat_activity; usename | application_name | client_addr | backend_start | state ----------+------------------+--------------+-------------------------------+-------- postgres | | | 2021-11-18 13:21:45.376108+00 | postgres | psql | 10.10.11.111 | 2021-11-18 13:28:56.659556+00 | active (2 rows)