yum update
The PostgreSQL Yum Repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.
CentOS 8:
sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
CentOS 7:
sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
CentOS 8:
- Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql - Then Install both client and server packages:
sudo dnf -y install postgresql12 postgresql12-server
CentOS 7:
- Install PostgreSQL client and server packages:
sudo yum -y install epel-release yum-utils
sudo yum-config-manager --enable pgdg12
sudo yum install postgresql12-server postgresql12
- After installation, database initialization is required before service can be started:
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb - The database main configuration ifile is written to:
/var/lib/pgsql/12/data/postgresql.conf - Start and enable the database server service:
sudo systemctl enable --now postgresql-12 - If you have a running Firewall service and remote clients should connect to your database server, allow PostgreSQL service:
sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload
$ sudo su - postgres
~]$ psql -c "alter user postgres with password 'StrongPassword'"
ALTER ROLE
- Edit the file
/var/lib/pgsql/12/data/postgresql.confand set Listen address to your server IP address or “*” for all interfaces:listen_addresses = '192.168.10.10' - Also set PostgreSQL to accept remote connections:
$ sudo vim /var/lib/pgsql/12/data/pg_hba.conf
# Accept from anywhere
host all all 0.0.0.0/0 md5
# Accept from trusted subnet
host all all 192.168.18.0/24 md5
- Restart database service after committing the change:
sudo systemctl restart postgresql-12 - Connecting to remote database:
$ psql -U <dbuser> -h <serverip> -p 5432 <dbname>
adduser automation
sudo su
passwd automation
Login as postgres and connect to database. Create user role (with same password as shell) and new database
su postgres
pgsql
>CREATE DATABASE masterdb;
>CREATE ROLE automation WITH PASSWORD 'UserPassword';
>GRANT ALL PRIVILEGES ON DATABASE masterdb TO automation;
>ALTER ROLE automation WITH LOGIN;
- A default PostgresSQL installation always includes the postgres superuser. Initially, you must connect to PostgreSQL as the postgres user until you create other users (which are also referred to as roles).
- At the command line, type the following command as the server's root user:
su - postgres - You can now run commands as the PostgreSQL superuser. To create a user, type the following command:
createuser --interactive --pwprompt
su - postgres- To create a database, type the following command. Replace user with the name of the user that you want to own the database, and replace dbname with the name of the database that you want to create:
createdb -O user dbname - PostgreSQL users that have permission to create databases can do so from their own accounts by typing the following command, where dbname is the name of the database to create:
createdb dbname
To grant an existing user privileges to a database
- Run the psql program as the database's owner, or as the postgres superuser
- Type the following command. Replace permissions with the permissions you want to grant, dbname with the name of the database, and username with the user:
GRANT permissions ON DATABASE dbname TO username;
dropdb dbname
dropuser username- If the user owns any databases or other objects, you cannot drop the user. Instead, you receive an error message similar to the following:
dropuser: removal of role "username" failed: ERROR: role "username" cannot be dropped because some objects depend on it DETAIL: owner of database dbnameYou should change the database's owner (or drop the database entirely), and then you can drop the user.