Connecting to PostgresSQL database


After logging into postgres user, from cmdline type psql.

bash-4.1$ psql
could not change directory to "/var/lib/mysql/TESTDB"
psql (8.4.18)
Type "help" for help.

Some basic cmds:

postgres=# show databases;   -- This list databases
postgres=# List databases      -- This list databases

-- Listing database in postgres

##     \l will give you basic information of databases in instance


postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges 
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
(3 rows)

\l+ will give you advanced information of databases in instance. Including Access privileges,size,tablespace.. etc


postgres=# \l+
                                                           List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   |  Size   | Tablespace | 
     Description       
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+---
------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 5510 kB | pg_default |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           | 5408 kB | pg_default |
                                                             : postgres=CTc/postgres                         
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           | 5408 kB | pg_default | de
fault template database
                                                             : postgres=CTc/postgres                         
(3 rows)

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges 
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
(3 rows)

Comments

Post a Comment

Popular posts from this blog

Oracle Alter session like in postgres

If you are looking for looking for alter session set curent_schema=ABCD;  kind of statement in postgres we have  Example : dvdrental=# create user test password 'test'; CREATE ROLE                         If you want to inherit permissions of another role you can do : dvdrental=# alter role test  CREATEROLE CREATEDB; ALTER ROLE dvdrental=# SELECT SESSION_USER, CURRENT_USER;  session_user | current_user --------------+--------------  postgres     | postgres (1 row) If you want to change user to another user without reconnecting (like sudo in Linux) : dvdrental=# SET SESSION AUTHORIZATION 'test'; SET dvdrental=> SELECT SESSION_USER, CURRENT_USER;  session_user | current_user --------------+--------------  test         | test (1 row) dvdrental=>
Read more

switching between users in postgres

SET SESSION AUTHORIZATION SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT RESET SESSION AUTHORIZATION This command sets the session user identifier and the current user identifier of the current SQL session to be  username . The user name may be written as either an identifier or a string literal. Using this command, it is possible, for example, to temporarily become an unprivileged user and later switch back to being a superuser. Example: C:\WINDOWS\system32> psql -U postgres psql (10.11) WARNING: Console code page (437) differs from Windows code page (1252)          8-bit characters might not work correctly. See psql reference          page "Notes for Windows users" for details. Type "help" for help. postgres=# select current_user,session_user;  current_user | session_user --------------+--------------  postgres     | postgres (1 row) postgres=# set session authorization 
Read more