1. Query to get list of databases.
\l or select datname from pg_database;
2. How to start, stop and restart PostgreSQL server
systemctl stop postgresql systemctl start postgresql systemctl restart postgresql
3. Check the database size in pgsql
4. How to reset postgresql user password.
5. Create User
6.What is the difference between pg_dump vs pg_dumpall
7. Prevent User connections(lock)
8. How to get data directory path in postgresql
show data_directory;
9. How to get hba path in postgresql
show hba_file;
10. How to get the postgresql start time?
select pg_postmaster_start_time;
11. How to find out the PostgreSQL Server uptime?
select date_trunc('second',current_timestamp-pg_postmaster_start_time()) as "PostgreSQL Uptime";
12. How to check number of tables in PostgreSQL Database?
SELECT count(*) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog');
13. What is the command used to get of tables from PostgreSQL Database?
First, connect to your database
\c database_name
Then, this shows all tables in the current schema:
\dt
Programmatically (or from the psql interface too, of course):
SELECT * FROM pg_catalog.pg_tables;
The system tables live in the pg_catalog database.
14. How to find the table size in PostgreSQL Server?
select pg_total_relation_size('emp'); \dt+ emp
15. How to prevent a user from connecting to PostgreSQL Server.
alter user george nologin;
16. How to limit number of concurrent connections by a user in PostgreSQL.
With below user ‘george’ is allowed for 10 concurrent connections.
alter user george connection limit 10;