Difference between revisions of "DB/Postgres PostgreSQL"
< DB
Jump to navigation
Jump to search
Line 37: | Line 37: | ||
-- Reset user password, no need to flush or log out | -- Reset user password, no need to flush or log out | ||
ALTER USER user_name WITH PASSWORD 'new_password'; | 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) | |||
</source> | </source> |
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)