u, -user TEXT Username to connect to the postgres database. U, -username TEXT Username to connect to the postgres database. p, -port INTEGER Port number at which the postgres instance is h, -host TEXT Host address of the postgres database. If you don't know how to install python packages, please check theĭetailed instructions. $ sudo apt-get install pgcli # Only on Debian based Linux (e.g. If you already know how to install python packages, then you can simply do: $ pip install -U pgcli This is a postgres client that does auto-completion and syntax highlighting. Please consider donating or volunteering. This place is right in the old city center. This is an image from my home town, Kharkiv. Hundreds were killed and injured, and thousands were displaced. A lot of civilians, women and children, are suffering. Check permissions in a table FROM information_schema.Ukrainian people are fighting for their country. JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) List all assigned roles for the users r.rolname, WHERE grantee = 'user_to_check' ORDER BY table_name List all grants for an specific user SELECT table_catalog, table_schema, table_name, privilege_type psql -c "\l+" -H -q postgres > out.html: Generate a html report of your databases (source: Daniel Westermann).nf: Annotations of all 269 nf settings for PostgreSQL 10. Quite interesting if you need to tune-up a postgres setup. A Performance Cheat Sheet for PostgreSQL: Great explanations of EXPLAIN, EXPLAIN ANALYZE, VACUUM, configuration parameters and more.PostgreSQL Exercises: An awesome resource to learn to learn SQL, teaching you with simple examples in a great visual way.100 psql Tips: Name says all, lots of useful tips!.Postgres Weekly newsletter: The best way IMHO to keep up to date with PG news.To obtain the CREATE TABLE query of a table, any visual GUI like pgAdmin allows to easily, but else you can use pg_dump, e.g.: pg_dump -t '.' -schema-only ( source).Collect statistics of a database (useful to improve speed after a Database Upgrade as previous query plans are deleted): ANALYZE VERBOSE.File to save PostgreSQL credentials and permissions (format: hostname:port:database:username:password): chmod 600 ~/.pgpass.Show IP of the DB Instance: SELECT inet_server_addr().$ echo "bind "^R " em-inc-search-prev " > $HOME/.editrc Get all indexes from all tables of a schema:.SELECT * FROM pg_indexes WHERE tablename='_table_name_' AND schemaname='_schema_name_' : Show table indexes.show statement_timeout : Show current user's statement timeout.SELECT pg_size_pretty(pg_database_size('_database_name_')) : Show DB space in use.SELECT pg_size_pretty(pg_total_relation_size('_table_name_')) : Show DB table space in use.SELECT * FROM pg_views WHERE viewname='_viewname_' : List view (including the definition).SELECT * FROM pg_proc WHERE proname='_procedurename_': List procedure/function.Parameters differ but can be checked at the official documentation. There are many CREATE choices, like CREATE DATABASE _database_name_, CREATE TABLE _table_name_. Show available extensions: SELECT * FROM pg_available_extension_versions How to add user who executed a PG statement to log (editing nf):Ĭheck Extensions enabled in postgres: SELECT * FROM pg_extension.Now you will get tons of details of every statement, error, and even background tasks like VACUUMs.Changing verbosity & querying Postgres log:ġ) First edit the config file, set a decent verbosity, save and restart postgres:.grant _test2_ to _test1_ : Allow _test1_ to set its role as _test2_.set role _test_ : Change role for current session to _test_.create role _test2_ noinherit login password _passsword_ : Create a role with username and password.create role _test1_: Create a role with an existing username.\du _username_: List a username if present.\! _bash_command_: execute _bash_command_ (e.g.\copy (SELECT * FROM _table_name_) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV.\x: Pretty-format query results instead of the not-so-useful ASCII tables.\df+ _function_ : Show function SQL code.\dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones).\d+ _table_: More detailed table definition including description and physical disk size.\d _table_: Show table definition (columns, etc.) including triggers.\?: Show help (list of available commands with an explanation).Most \d commands support additional param of _schema_.name_ and accept wildcards like *.* -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS).-E: will describe the underlaying queries of the \ commands (cool for learning!).Some interesting flags (to see all, use -h or -help depending on your psql version):
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |