PostgreSQL, as a popular open source relational database management system (RDBMS), has existed for more than thirty years. It provides SQL language support for managing databases and performing CRUD operations (create, read, update, delete).
Due to its excellent robustness, flexibility and excellent performance, PostgreSQL has won a wide reputation and has become the preferred data storage solution for many web websites and data analysis application software. Many well-known companies around the world, such as Spotify, Instagram, Trivago, Uber, and Netflix, are widely adopting PostgreSQL.
The officially maintained modular repository on CentOS 8/RHEL 8 has PostgreSQL versions 13, 12, 10, and 9.6, and we will be adding the PostgreSQL Yum Repository, which contains all the latest versions.
In fact, the PostgreSQL project provides package repositories for all supported versions of the most common distributions, supporting all Red Hat series, such as CentOS, Fedora, Scientific Linux, Oracle Linux and Red Hat Enterprise Linux.
Currently, the mainstream stable version of PostgreSQL is version 15. In this article, we will detail the process of installing PostgreSQL version 15 on CentOS 8 systems.
To install PostgreSQL 15.4 on CentOS 8 system, we can follow the following steps:
-
Open the terminal: You can open the terminal by clicking the “Applications” menu and searching for “Terminal”, or use Xshell to remotely connect to the CentOS 8 server.
-
PostgreSQL has different versions in the Appstream repository. We can make the selection by enabling the preferred package using the dnf package manager.
To list the available modules for PostgreSQL, run the following command:
# dnf module list postgresql
[root@pgserver ~]# dnf module list postgresql The last metadata expiration check: 0:43:44 ago, executed on Monday, October 9, 2023, 13:22:39. CentOS-8.5.2111 - AppStream - mirrors.aliyun.com Name Stream Profiles Summary postgresql 9.6 client, server [d] PostgreSQL server and client module postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 12 client, server [d] PostgreSQL server and client module postgresql 13 client, server [d] PostgreSQL server and client module Tips: [d] Default, [e] Enabled, [x] Disabled, [i] Installed [root@pgserver ~]# </code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack. png" alt="" title="">
From the output of the module command that lists Postgresql, the output indicates that four versions are available for download from the AppStream repository: versions 9.6, 10, 12, and 13. We can also see that the default version is Postgresql 10[d] as indicated by the tag.
If you want to install the default Postgresql 10, you can install it directly by running the following command:
# dnf install postgresql-server
But if you want to install PostgreSQL 15, the steps are different.
Step 1: Add PostgreSQL Yum repository
The Yum repository is specifically designed to provide support for the CentOS and RHEL series of Linux distributions.
Once successfully added, this repository will provide our server systems with automatically updated PostgreSQL packages throughout the lifecycle of a supported PostgreSQL version.
To complete this step, we need to perform the following detailed actions to add the repository of PostgreSQL packages to our CentOS/RHEL 8 server:
# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@pgserver ~]# sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm The last metadata expiration check: 1:04:22 ago, executed at 13:22:39 on Monday, October 9, 2023. pgdg-redhat-repo-latest.noarch.rpm 8.3 kB/s | 13 kB 00:01 Dependencies are resolved. ================================================== ================================================== ================================================== Package Architecture Version Repository Size ================================================== ================================================== ================================================== Install: pgdg-redhat-repo noarch 42.0-35PGDG @commandline 13k transaction summary ================================================== ================================================== ================================================== Install 1 package Total: 13k Installation size: 15k sure? [y/N]：y Download the package: Run transaction check Transaction check successful. Run transaction tests Transaction test successful. Run transaction In preparation: 1/1 Installation: pgdg-redhat-repo-42.0-35PGDG.noarch 1/1 Verification: pgdg-redhat-repo-42.0-35PGDG.noarch 1/1 Installed: pgdg-redhat-repo-42.0-35PGDG.noarch complete! [root@pgserver ~]# </code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack. png" alt="" title="">
Step 2: Install PostgreSQL on CentOS 8/RHEL 8
We need to disable the PostgreSQL AppStream repository on CentOS 8, CentOS 8 Linux contains other versions of PostgreSQL.
# dnf -qy module disable postgresql
[root@pgserver ~]# dnf -qy module disable postgresql Import GPG public key 0x442DF0F8: Userid: "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Import GPG public key 0x442DF0F8: Userid: "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Import GPG public key 0x442DF0F8: Userid: "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Import GPG public key 0x442DF0F8: Userid: "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Import GPG public key 0x442DF0F8: Userid: "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Import GPG public key 0x442DF0F8: Userid: "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Import GPG public key 0x442DF0F8: Userid: "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG </code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack. png" alt="" title="">
Use the following command to check whether PostgreSQL 15 is included in the list of enabled repositories.
# dnf repolist
[root@pgserver ~]# dnf repolist Warehouse id warehouse name AppStream CentOS-8.5.2111 - AppStream - mirrors.aliyun.com base CentOS-8.5.2111 - Base - mirrors.aliyun.com extras CentOS-8.5.2111 - Extras - mirrors.aliyun.com pgdg-common PostgreSQL common RPMs for RHEL/Rocky 8 - x86_64 pgdg11 PostgreSQL 11 for RHEL/Rocky 8 - x86_64 pgdg12 PostgreSQL 12 for RHEL/Rocky 8 - x86_64 pgdg13 PostgreSQL 13 for RHEL/Rocky 8 - x86_64 pgdg14 PostgreSQL 14 for RHEL/Rocky 8 - x86_64 pgdg15 PostgreSQL 15 for RHEL/Rocky 8 - x86_64 pgdg16 PostgreSQL 16 for RHEL/Rocky 8 - x86_64 </code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack. png" alt="" title="">
Use the following command to check if the PostgreSQL 15 package is available in the repository.
# yum search postgresql15
[root@pgserver ~]# yum search postgresql15 The last metadata expiration check: 0:02:13 ago, executed at 15:56:22 on Monday, October 9, 2023. ================================================== ========== Exact name match: postgresql15 ===================================== ========================== postgresql15.x86_64 : PostgreSQL client programs and libraries ================================================== ============ Name matching: postgresql15 ================================== ============================= postgresql15-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL postgresql15-devel.x86_64 : PostgreSQL development header files and libraries postgresql15-docs.x86_64 : Extra documentation for PostgreSQL postgresql15-libs.x86_64 : The shared libraries required for any PostgreSQL clients postgresql15-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL postgresql15-odbc.x86_64 : PostgreSQL ODBC driver postgresql15-plperl.x86_64 : The Perl procedural language for PostgreSQL postgresql15-plpython3.x86_64 : The Python3 procedural language for PostgreSQL postgresql15-pltcl.x86_64 : The Tcl procedural language for PostgreSQL postgresql15-server.x86_64 : The programs needed to create and run a PostgreSQL server postgresql15-tcl.x86_64 : A Tcl client library for PostgreSQL postgresql15-test.x86_64 : The test suite distributed with PostgreSQL [root@pgserver ~]# </code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack. png" alt="" title="">
Now install PostgreSQL 15 packages on CentOS 8 – server and client
# dnf install postgresql15 postgresql15-server
This will install the PostgreSQL database server and some additional functional modules.
[root@pgserver ~]# dnf install postgresql15 postgresql15-server The last metadata expiration check: 0:09:13 ago, executed at 15:56:22 on Monday, October 9, 2023. Dependencies are resolved. ================================================== ================================================== ================================================== Package Architecture Version Repository Size ================================================== ================================================== ================================================== Install: postgresql15 x86_64 15.4-1PGDG.rhel8 pgdg15 1.6 M postgresql15-server x86_64 15.4-1PGDG.rhel8 pgdg15 5.9 M Install dependencies: postgresql15-libs x86_64 15.4-1PGDG.rhel8 pgdg15 293 k transaction summary ================================================== ================================================== ================================================== Install 3 packages Total downloads: 7.8 M Installation size: 33M sure? [y/N]：y Download the package: (1/3): postgresql15-libs-15.4-1PGDG.rhel8.x86_64.rpm 23 kB/s | 293 kB 00:12 (2/3): postgresql15-15.4-1PGDG.rhel8.x86_64.rpm 34 kB/s | 1.6 MB 00:48 (3/3): postgresql15-server-15.4-1PGDG.rhel8.x86_64.rpm 28 kB/s | 5.9 MB 03:34 -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- Total 37 kB/s | 7.8 MB 03:34 </code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack. png" alt="" title="">
A GPG key repository is also required during installation.
PostgreSQL 15 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00 Import GPG public key 0x442DF0F8: Userid: "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG sure? [y/N]：y Import public key successfully Run transaction check Transaction check successful. Run transaction tests Transaction test successful. Run transaction In preparation: 1/1 Installation: postgresql15-libs-15.4-1PGDG.rhel8.x86_64 1/3 Run script: postgresql15-libs-15.4-1PGDG.rhel8.x86_64 1/3 Installation: postgresql15-15.4-1PGDG.rhel8.x86_64 2/3 Run script: postgresql15-15.4-1PGDG.rhel8.x86_64 2/3 Run the script: postgresql15-server-15.4-1PGDG.rhel8.x86_64 3/3 Installation: postgresql15-server-15.4-1PGDG.rhel8.x86_64 3/3 Run the script: postgresql15-server-15.4-1PGDG.rhel8.x86_64 3/3 [/usr/lib/tmpfiles.d/pesign.conf:1] Line references path below legacy directory /var/run/, updating /var/run/pesign → /run/pesign; please update the tmpfiles.d/ drop- in file accordingly. Verification: postgresql15-15.4-1PGDG.rhel8.x86_64 1/3 Verification: postgresql15-libs-15.4-1PGDG.rhel8.x86_64 2/3 Verification: postgresql15-server-15.4-1PGDG.rhel8.x86_64 3/3 Installed: postgresql15-15.4-1PGDG.rhel8.x86_64 postgresql15-libs-15.4-1PGDG.rhel8.x86_64 postgresql15-server-15.4-1PGDG.rhel8.x86_64 complete! [root@pgserver ~]# </code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack. png" alt="" title="">
After installing PostgreSQL and its dependencies, use the following psql command to verify its version.
[root@pgserver ~]# psql -V psql (PostgreSQL) 15.4 [root@pgserver ~]#
The output of the above command shows that PostgreSQL 15.4 is already installed on the system.
Step 3: Initialize and start the database service
PostgreSQL database initialization must occur before the database server can be used.
Run the following command to initialize the PostgreSQL database
/usr/pgsql-15/bin/postgresql-15-setup initdb
[root@pgserver ~]# /usr/pgsql-15/bin/postgresql-15-setup initdb Initializing database...OK [root@pgserver ~]#
The main configuration file and storage path of the database are in /var/lib/pgsql/15/data/postgresql.conf
[root@pgserver ~]# ll /var/lib/pgsql/15/data/postgresql.conf -rw-------. 1 postgres postgres 29459 October 9 16:17 /var/lib/pgsql/15/data/postgresql.conf
Start the PostgreSQL database server and set it to start at boot.
[root@pgserver ~]# systemctl enable --now postgresql-15 Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-15.service → /usr/lib/systemd/system/postgresql-15.service. [root@pgserver ~]#
Usually use the following 2 commands to start and configure:
systemctl start postgresql-15 systemctl enable postgresql-15
You can also use the systemctl enable --now postgresql-15 command to replace the following two commands:
systemctl start postgresql-15is used to start the PostgreSQL 15 service.systemctl enable postgresql-15is used to set the PostgreSQL 15 service to start automatically at boot.
The systemctl enable --now postgresql-15 command combines these two operations. It will start the PostgreSQL 15 service and set it to start automatically at boot. This is a more convenient way to perform both tasks at the same time.
Check the service status to confirm it is running.
systemctl status postgresql-15
[root@pgserver ~]# systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2023-10-09 16:25:49 CST; 55s ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 17669 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 17675 (postmaster)
Tasks: 7 (limit: 49189)
Memory: 17.5M
CGroup: /system.slice/postgresql-15.service
├─17675 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
├─17676 postgres: logger
├─17678 postgres: checkpointer
├─17679 postgres: background writer
├─17681 postgres: walwriter
├─17682 postgres: autovacuum launcher
└─17683 postgres: logical replication launcher
October 09 16:25:49 pgserver systemd[1]: Starting PostgreSQL 15 database server...
Oct 09 16:25:49 pgserver postmaster[17675]: 2023-10-09 16:25:49.955 CST [17675] Log: Log output redirected to log collection process
October 09 16:25:49 pgserver postmaster[17675]: 2023-10-09 16:25:49.955 CST [17675] Tip: Subsequent log output will appear in the directory "log".
October 09 16:25:49 pgserver systemd[1]: Started PostgreSQL 15 database server.
[root@pgserver ~]#
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack. png" alt="" title="">
You can see that the PostgreSQL service has been activated and running.
Step 4: Set the password of the PostgreSQL administrator user
PostgreSQL databases are insecure by default, you can easily switch to the postgres user without any password and start using psql as shown below
Therefore, after the deployment is completed, we need to strengthen the security of the PostgreSQL database, update and modify the PostgreSQL system user password and database administrator password.
By default, PostgreSQL does not set a password, you need to set an administrator password.
Run the following command, replacing “your_password” with your desired password:
sudo -u postgres psql
After entering the PostgreSQL command line, execute the following command to set the password:
ALTER USER postgres PASSWORD 'your_password';
Exit the PostgreSQL command line:
\q
Or modify it as follows.
1. Use the passwd command to set a password for the postgres user;
2. Use the ALTER USER database command to set the database administrator password;
$ passwd postgres $ su – postgres $ psql -c "ALTER USER postgres WITH PASSWORD 'xxxxxxxxxxx';"
Step 5: Enable remote database connection
By default, PostgreSQL usually restricts only local connections after the installation is completed. This is to increase the security of the database.
This means that you can only connect to the database on the local machine running the PostgreSQL server and cannot be accessed remotely from other computers.
Enabling remote database connections can increase the flexibility of the database, allowing other computers to connect to the database server through the network, so that the database can be accessed and used on different computers. However, this also increases some security risks, so before enabling remote connections, some security measures need to be taken, such as:
1. Access control: Restrict which IP addresses or hosts can connect to the database by modifying PostgreSQL’s pg_hba.conf file. You can configure a whitelist to only allow specific IP addresses or hosts to access the database.
2. Firewall rules: Make sure the firewall rules on the server allow external computers to connect to the PostgreSQL port (default is 5432).
3. Security best practices: Follow database security best practices, including regularly backing up the database, using strong passwords, regularly updating PostgreSQL and the operating system, etc.
Before enabling remote connections, it is important to consider security and take appropriate steps to ensure that your database is protected from unauthorized access or potential threats. This increases flexibility while protecting the security of the database.
To enable remote database connection we need to perform the following steps:
-
Edit the
pg_hba.conffile:PostgreSQL uses the
pg_hba.conffile to configure the client’s access control rules. We need to edit this file to allow remote connections. Open the file using a text editor such asnanoorvi:vim /var/lib/pgsql/15/data/pg_hba.conf
Find the appropriate place to add a line of rules to allow remote access. For example, to allow remote access from all IP addresses, you can add the following line:
host all all 0.0.0.0/0 md5
This means that all hosts (0.0.0.0/0) are allowed to connect to all users (all) of all databases (all) and require password authentication (md5).
Please note that this will allow connections from any IP address, so use with caution, if in a production environment it is recommended to more strictly limit the IP addresses that can access the database.
-
Configuration
postgresql.conffile:PostgreSQL’s
postgresql.conffile contains information about the server configuration. Make sure that thelisten_addressesin this file is set to allow remote connections. Open the file with a text editor:vim /var/lib/pgsql/15/data/postgresql.conf
Make sure the following lines are not commented out (remove the previously commented
#):
Then set the “Listening Address” to the server IP address or ” * ” for all interfaces.listen_addresses = '*'
This will allow the server to listen on all available network interfaces.
-
Restart PostgreSQL:
For the above changes to take effect, restart the PostgreSQL service:
sudo systemctl restart postgresql-15
-
Firewall rules:
Make sure our server firewall allows remote connections to the PostgreSQL port (default port is 5432). Specific rules may vary depending on your firewall management tool. You can use
firewall-cmdoriptablesto configure rules. -
Test remote connection:
Using the PostgreSQL client tool on the remote machine, connect to your PostgreSQL server to test the remote connection. Make sure to use the correct IP address, username and password.
$ psql -U <dbuser> -h <serverip> -p 5432 <dbname>
Please note that enabling remote connections will increase potential security risks, so appropriate security measures should be taken, such as limiting the IP addresses allowed to connect, using strong passwords, regularly updating PostgreSQL and the operating system, etc., to ensure the security of the database .
Now, we have successfully installed and configured PostgreSQL 15.4 on CentOS 8 system. We can use the psql command line tool or other PostgreSQL client to connect to the database and start using it. You can also install other PostgreSQL extensions and tools if needed.
Finally, it is recommended to install pgAdmin 4 to manage PostgreSQL 15 databases. pgAdmin is the leading open source feature-rich PostgreSQL management and development platform that runs on Linux, Unix, Mac OS X and Windows.
The specific installation method will be introduced in the next article.