In this article we are going to set PostgreSQL concurrent connection limit for user. As a administrator, we have to revoke permissions from the user, set the connection limits to user and set unlimited concurrent connection limits.
Set PostgreSQL concurrent connection limit:
In this video, we have the concurrent user connection limit to none,5, and unlimited.
1. Set concurrent connection limit none.
alter user anil connection limit 0;
In this tutorial, we find certain user connected to PostgreSQL server or not by querying pg_stat_activity system view.
If below query returns any results means, then user is connected to PostgreSQL Server database.
Check whether PostgreSQL User is connected or not:
select usename,datname from pg_stat_activity where usename='anil';
Continue reading Check whether PostgreSQL User is connected or not
In this tutorial, we are going to find data directory and configuration files paths in PostgreSQL Server.
If environment variables configured, then we can get the data directory path by running below command on Linux Shell i.e.
echo $PGDATA. If it is not set then we have to execute SQL statements. 1. Find the data directory path in PostgreSQL Server.
select setting from pg_settings where name = 'data_directory';
Continue reading Find PostgreSQL data directory and configuration file location
In this article, we are going Lock and Unlock PostgreSQL User. Privileges required to Lock and Unlock PostgreSQL User is ALTER USER.
Steps to Lock and Unlock PostgreSQL User:
1. Lock the user
ALTER USER anil NOLOGIN;
In this article, we will see how to install PostgreSQL 11 on Redhat Linux Operating System.
Steps to Install PostgreSQL 11 on Redhat:
Step1: Configure PostgreSQL yum repository:
rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Step2: Install PostgreSQL 11 Server.
Continue reading Install PostgreSQL 11 on Redhat Linux Operating System
In this article, we will see how to change owner of table in PostgreSQL. To change owner of the table, you must be owner of the table or must have alter table or superuser permissions.
It is database administrator routine task to change table owner in PostgreSQL. This can be done with ALTER TABLE statement.
Syntax to Change table owner in PostgreSQL:
ALTER TABLE public.table_name OWNER TO another_username;
Verify the owner of the table by running below command:
\dt table_name;
We can create user in PostgreSQL by using createuser program or by using create user statement. To create user in PostgreSQL server, user must have create user(createrole) privileges.
Syntax to Create User in PostgreSQL:
CREATE USER name [ [ WITH ] option [ ... ] ] where option can be: SYSID uid | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime'
It is daily activity to monitor the database size on any database server. We have to check the database size either for planning or space monitoring.
1. Query to check the database size in PostgreSQL:
select datname database_name,pg_database_size(datname) from pg_database where datname='name of the database';
PostgreSQL 11 installation on Mac OS is simple and less time taking.
PostgreSQL 11 installation on Mac OS Steps:
1. Download the PostgreSQL software.
2. Install postgreSQL server.
3. Connect to PostgreSQL server.
These are explained in this video.
After installation of PostgreSQL on MAC OS, when tried to connect to PostgreSQL using psql tool then received error:
bash: psql: command not found
Steps to fix this issue:
Continue reading bash psql command not found