Archive for the ‘PostgreSQL’ Category

How to get long running queries in PostgreSQL

To get a list of running queries in PostgreSQL which are ordered by how long they have been executing. We can get long running queries by calculating time difference between query star time and current time stamp. select current_timestamp – query_start as runtime, datname,pid, usename, query from pg_stat_activity where query ‘idle’ order by 1 desc; […]

How to get running queries in PostgreSQL

In this article, we can get the list of users running on PostgreSQL and their queries. Also, we can see particular user running queries and idle users in PostgreSQL. To get list of users current running. select usename from pg_stat_activity; To get list of users current running. But, not idle sessions.

Find List of active user connections to PostgreSQL

If we want to get list of users connected to the PostgreSQL server, use system view pg_stat_activity. It contains information like database name,process id, username, application name, client ip address, query start time, which query user is running. If we want to all information about all users connected to the PostgreSQL, run below query. select […]

Drop user in PostgreSQL

Drop user means is easy process in PostgreSQL. But, if the user owns database or its objects(table, views, stored procedure, …) it troughs error. To drop user, we must have superuser privilege. or createrole privilege. Syntax: DROP USER user_name; Example: postgres=# drop user federer; DROP ROLE But, when user owns database or database objects(tables, views, […]

Grant Access to all objects in schema PostgreSQL

Sometimes we have to grant access to all objects(tables, views, stored procedures,….) of schema. This can be done with the following command. GRANT SELECT ON ALL TABLES IN SCHEMA accounts to john; To grant this access, user must have GRANT privileges on the SCHEMA. But, not for superusers. To Create user, we must be superuser […]

Create schema in PostgreSQL

Schema is nothing to separate groups of tables into their own names. Schema is database specific. Single schema can be present in multiple database in PostgreSQL. Syntax: CREATE SCHEMA schema_name; Examples: CREATE SCHEMA finance; CREATE SCHEMA HR; CREATE SCHEMA sales; CREATE SCHEMA transport; CREATE SCHEMA accounts;

Kill PostgreSQL session

Sometimes we have to kill User sessions on PostgreSQL database server for various reasons. We can kill session with PostgreSQL function pg_terminate_backend(). To kill the session, we have to find out user sessions by running following query. If we know the user process id(pid), then execute following query. SELECT pg_terminate_backend(41864) FROM pg_stat_activity WHERE pid pg_backend_pid() […]

FATAL: too many connections for role PostgreSQL

If we receive error “too many connections for role” when, we try to connect to PostgreSQL database. Then it may be allowed connections list is set or connection limit set to zero. Why this error throughs? 1. Check the number of active connection for the user with which are trying to login.

How to get the table size in PostgreSQL

We can determine table size by running following query will give the size of table accounts of the datbase ‘bank’ bank=# select pg_relation_size(‘accounts’); pg_relation_size —————— 8192 Or run the command \dt+ accounts To get table sizes by order use following query

How to get PostgreSQL uptime

There is not direct command to get the server uptime. We can get it by the difference of PostgreSQL server start time and current timestamp of PostgreSQL database. Run the following query in psql or pgadmin tool. select date_trunc(‘second’,current_timestamp-pg_postmaster_start_time()) as uptime; Output uptime ———- 19:02:47 (1 row)

Powered by k2schools