pgsql
Categories:
Connect to database
$ PGPASSWORD=<password> psql -h 192.168.1.29 -p 5432 -U postgres -d postgres
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1), server 15.1)
Type "help" for help.
postgres=#
List Users
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repmgr | Superuser, Replication | {}
postgres=#
List Databases
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+-------------+-------------+------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
repmgr | 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
(4 rows)
List Schemas
postgres=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
postgres=#
Save result to file
postgres=# \o out.txt
Disable wrapping
postgres=# \setenv PAGER 'less -S'
postgres=# \pset pager off
postgres=# \pset pager on
PAGER="less -S" psql -U postgres -d postgres
See also:
[^] https://stackoverflow.com/questions/6523019/postgresql-scripting-psql-execution-with-password