One of the useful data types of PostgreSQL is the inet data type. inet is used to define IPv4 and IPv6 addresses. The ip address can also be stored with the subnet if desired. It supports a total of 17 operators, including basic operators “<,>, =” operators.
In addition to this, with the help of functions such as network (inet) and netmask (inet), it makes network calculations easier for us.
pg_stat_activity view
The pg_stat_activity view lists the current connections to inform us about their status.
This view lists all connections on PostgreSQL and their state, such as query start or transaction start time, client ip, user, and database info.
When we query this view in a database that has a lot of connections, it may be a little difficult. So we have to query this view by filtering.
As a filter example, let’s list the connections for a particular user;
|
1 |
SELECT * FROM pg_stat_activity WHERE usename = 'kullanıcı_adi'; |
Let’s list the connections from a specific client IP;
|
1 |
SELECT * FROM pg_stat_activity WHERE client_addr = inet '10.0.2.2'; |
Now, combine the above 2 information to list all connections under a specific IP mask. For example, let’s list the connections from all ips that start with “10.0….”
|
1 |
SELECT * FROM pg_stat_activity WHERE client_addr << inet '10.0.0.0/16' |
Here, let’s look at the << (is contained by) operator. Thanks to this operator, we have been able to easily extract the connections from an ip block.
