- #Postgresql commands how to
- #Postgresql commands upgrade
- #Postgresql commands password
corrupted indexes, which means query planner can't generate efficient query execution plans for them and as a result DB performance degrades over time.table and index bloat, which means they occupy way more disk space and memory than actually required.Check permissions in a table FROM information_schema.Large and heavily updated database tables in PostgreSQL often suffer from two issues: 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). #Postgresql commands upgrade
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
#Postgresql commands how to
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_. #Postgresql commands password
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 kommentar(er)
