Installation Instructions Database version for this installation: PostgreSQL 14 Operating system: RHEL 8.6 Server Architecture: x86_64
Turn off the firewall sudo systemctl stop firewalld sudo systemctl disable firewalld
Disable SeLinux sudo setenforce 0 sudo sed -i ‘s/^SELINUX=enforcing/SELINUX=disabled/’ /etc/selinux/config
Installation and Startup of PostgreSQL 14 Install the PostgreSQL Yum repository: sudo dnf install -y
Install the PostgreSQL 14 package: sudo dnf install -y postgresql14-server
Initialize the PostgreSQL 14 database: sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
Set the PostgreSQL 14 service to start automatically at boot: sudo systemctl enable postgresql-14
Start the PostgreSQL 14 service: sudo systemctl start postgresql-14
Verify that PostgreSQL has been successfully installed and is running: sudo systemctl status postgresql-14
PostgreSQL 14 Configuration Instructions: After installation, the default files are: The database software package installation files are located under the /usr/pgsql-14 directory The data directory is located under /var/lib/pgsql/14/data The configuration file is located in /var/lib/pgsql/14/data/postgresql.conf. This file contains many PostgreSQL configuration parameters, such as max_connections, shared_buffers, etc. The authentication configuration file is located in /var/lib/pgsql/14/data/pg_hba.conf. This file contains the rules for PostgreSQL client authentication. The service file is located in /usr/lib/systemd/system/postgresql-14.service. This file contains the startup parameters, environment variables, and other information for the PostgreSQL 14 service. The log files are located under /var/lib/pgsql/14/data/log.
Set the superuser password When PostgreSQL is installed, a superuser named postgres is automatically created. This user does not have a password set by default, so you can log in to the PostgreSQL service as the operating system user postgres. Log in to the operating system as the postgres user. su - postgres Connect to the PostgreSQL service with the following command: psql In the PostgreSQL command line, change the password of the postgres user with the following command: \password postgres Exit the PostgreSQL command line: \q
Configure Remote Access Modify the pg_hba.conf file to configure the authentication rules for PostgreSQL. By default, PostgreSQL does not allow connections from external networks, so you need to modify the rules in pg_hba.conf to allow connections from specified IPs or IP ranges. Allow all IP addresses to access sed -i ‘$ a\host all all 0.0.0.0/0 md5’ /var/lib/pgsql/14/data/pg_hba.conf
After installing PostgreSQL, by default, it only accepts connection requests from localhost. Edit the configuration file postgresql.conf, remove the preceding #, and change to listen_addresses =‘’ sed -i "s/#listen_addresses = ‘localhost’/listen_addresses = '’/g" /var/lib/pgsql/14/data/postgresql.conf
Set the maximum number of connections for PostgreSQL The default is 100, you can set the maximum number of connections for PostgreSQL to 1000 sed -i ‘s/^#\smax_connections\s*=.*$/max_connections = 1000/’ /var/lib/pgsql/14/data/postgresql.conf Restart the PostgreSQL 14 database to make the configuration take effect: systemctl restart postgresql-14 Verify through SQL SHOW max_connections;
Basic Usage View all databases: You can log in to the PostgreSQL database server and list existing databases with the following command: Connect to PostgreSQL using the psql command: $ psql -U postgres Here the -U parameter specifies the username to connect with, which is the default administrator user postgres for PostgreSQL. List all databases: postgres=# \l Here the meta-command \l is used in psql to list all existing databases.
View which users exist psql -c ‘\du’ \du is a built-in command to display a list of created PostgreSQL users and their role attributes. This command outputs a table containing the user’s name, login permissions, superuser status, and whether it is a group role.
Create users, databases, and grant permissions Create a user named myuser and a database named mydb CREATE USER myuser WITH PASSWORD ‘111111’; CREATE DATABASE mydb; GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser; This will create a user named myuser with a password of 111111, and create a database named mydb, then grant all permissions to access the mydb database to the myuser user.
Login using this user psql -d mydb -h 127.0.0.1 -p 5432 -U myuser Here the newly created user myuser is used to log in, the database to connect to is mydb, the host to connect to is 127.0.0.1, and the port is 5432.