{"id":1499,"date":"2016-06-15T16:03:30","date_gmt":"2016-06-15T13:03:30","guid":{"rendered":"http:\/\/www.fatihacar.com\/blog\/?p=1499"},"modified":"2016-06-22T11:59:42","modified_gmt":"2016-06-22T08:59:42","slug":"postgresql-frequently-using-commands","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/postgresql-frequently-using-commands\/","title":{"rendered":"PostgreSQL Frequently Using Commands"},"content":{"rendered":"

List,\u00a0Add, Drop Database<\/strong><\/p>\n

List databases<\/strong><\/p>\n

postgres=# \\d<\/p>\n

Add database<\/strong><\/p>\n

postgres=# createdb testdb<\/p>\n

or<\/p>\n

postgres=# create database testdb with owner facar;<\/p>\n


\nDrop database<\/strong><\/p>\n

postgres=# dropdb testdb<\/p><\/blockquote>\n

\"Postgres_logo\"<\/p>\n

List, Add, Drop, Grant User and Schema<\/strong><\/p>\n

List Users<\/strong><\/p>\n

postgres=# select * from pg_user;<\/p>\n

Add User<\/strong><\/p>\n

postgres=# create user facar with encrypted password ‘password’;<\/p>\n

or<\/p>\n

postgres=# createuser (interactive user create)<\/p>\n

Change User Password<\/strong><\/p>\n

postgres=# \\password facar
\npostgres=# alter user facar with encrypted password ‘newpassword’;<\/p>\n

Create Schema<\/strong><\/p>\n

postgres=# create schema schfacar;
\npostgres=# grant usage on schema schfacar to facar;
\npostgres=# grant all on schema schfacar to facar;<\/p>\n

Add Search Path<\/strong><\/p>\n

postgres=# alter user facar set search_path=schfacar;<\/p>\n

Grant Permissions to User<\/strong><\/p>\n

postgres=# alter user facar superuser;
\npostgres=# alter user facar createdb;
\npostgres=# alter user facar replication;
\npostgres=# alter user facar bypassrls;
\npostgres=# grant all privileges on database testdb to facar;<\/p>\n

Revoke Permissions from User<\/strong><\/p>\n

postgres=# alter user facar nosuperuser;
\npostgres=# alter user facar nocreatedb;
\npostgres=# alter user facar noreplication;
\npostgres=# alter user facar nobypassrls;
\npostgres=# revoke all privileges on database testdb from facar;\n<\/p><\/blockquote>\n

Connect Database<\/strong><\/p>\n

\n[root@testdb ~]\u00a0su – postgres
\n-bash-4.2$ psql
\n-bash-4.2$ psql testdb (connect testdb)
\n-bash-4.2$ psql -U facar testdb (connect testdb with facar user)
\n-bash-4.2$ psql -U facar testdb -h “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 IP and 5432 port)<\/p>\n

[root@testdb ~]\u00a0 psql
\n[root@testdb ~]\u00a0 psql testdb (connect testdb)
\n[root@testdb ~]\u00a0 psql -U facar testdb (connect testdb with facar user)
\n[root@testdb ~]\u00a0 psql -U facar testdb -h “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 IP and 5432 port)<\/p>\n

Note :<\/strong> If you want to connect database with different user from postgres, you have to add line pg_hba.conf like that<\/p>\n

host \u00a0 \u00a0 \u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 192.168.20.12\/32 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0md5<\/p><\/blockquote>\n

Information Queries<\/strong><\/p>\n

postgres=# \\conninfo (current connection information)
\npostgres=# \\dt (tables of current user schema)
\npostgres=# \\d (list of object of current user schema)
\npostgres=# \\d tablename (columns of tablename)
\npostgres=# \\df (list of functions)
\npostgres=# \\df+ (list of functions with details)
\npostgres=# select version(); (detailed version of database)
\npostgres=# \\? (psql commands list for help)
\npostgres=# \\h (list of help options)
\npostgres=# \\h CREATE USER (help of CREATE USER command)
\npostgres=# \\timing (open timing to show query execution time)
\npostgres=# \\c testdb (connect testdb)
\npostgres=# \\c testdb facar (connect testdb with facar user)
\npostgres=# select pg_database_size(current_database()); (size of current database as byte)
\npostgres=# select pg_database_size(‘testdb’); (size of testdb database as byte)
\npostgres=# select pg_relation_size(‘tablename’); (size of table as byte)
\npostgres=# select usename, datname from pg_stat_activity; (who are connected to which database now)
\npostgres=# select pg_reload_conf(); (to reload configurations like service postgresql reload)<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

List,\u00a0Add, Drop Database List databases postgres=# \\d Add database postgres=# createdb testdb or postgres=# create database testdb with owner facar; Drop database postgres=# dropdb testdb List, Add, Drop, Grant User and Schema List Users postgres=# select * from pg_user; Add User postgres=# create user facar with encrypted password ‘password’; or postgres=# createuser (interactive user create)…<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[10,7],"tags":[14,93,88],"class_list":["post-1499","post","type-post","status-publish","format-standard","hentry","category-administration-postgresql","category-sql-postgresql","tag-database-administration","tag-postgresql","tag-postgresql-administration"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-ob","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1499","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=1499"}],"version-history":[{"count":17,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1499\/revisions"}],"predecessor-version":[{"id":1518,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1499\/revisions\/1518"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=1499"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=1499"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=1499"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}