PostgreSQL is an open-source database system and It is powerful like other relational database systems. In this tutorial, you will learn how to install PostgreSQL 12 ON CentOS 7 or RHEL 7.
Prerequisite to Install PostgreSQL 12 on CentOS 7 or RHEL 7:
- Pre-Installed CentOS 7 or RHEL 7 on your Server/VM
- Root or sudo user-level access to install packages
- Internet access to your system
This comprehensive guide will walk you through the step-by-step process of installing PostgreSQL 12 on CentOS 7. PostgreSQL is a powerful, open-source relational database management system known for its robustness and scalability. Following this guide, you can quickly set up PostgreSQL 12 on your CentOS 7 server and harness its full potential for your database needs.
Steps to Install PostgreSQL 12 ON CENTOS 7 or RHEL 7
Step 1: Integrate PostgreSQL Yum Repository with your CentOS 7 or RHEL 7 system
yum install -y https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Currently, PostgreSQL Yum Repository support for below mentioned Linux system:
- CentOS
- Scientific Linux
- Oracle Linux
- Fedora*
Download Packages and Installers
Step 2: Install PostgreSQL 12 on CentOS 7 or RHEL 7
Now that the repository is added, it’s time to install PostgreSQL 12 along with its associated packages. Execute the following command:
# yum install postgresql12 postgresql12-server postgresql12-contrib postgresql12-libs -y
Demo Output:
[root@CentOS-7 ~]# yum install postgresql12 postgresql12-server postgresql12-contrib postgresql12-libs -y Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.piconets.webwerks.in * epel: ftp.riken.jp * extras: mirrors.piconets.webwerks.in * updates: mirrors.piconets.webwerks.in Resolving Dependencies --> Running transaction check ---> Package postgresql12.x86_64 0:12.4-1PGDG.rhel7 will be installed ---> Package postgresql12-contrib.x86_64 0:12.4-1PGDG.rhel7 will be installed ---> Package postgresql12-libs.x86_64 0:12.4-1PGDG.rhel7 will be installed ---> Package postgresql12-server.x86_64 0:12.4-1PGDG.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================ Package Arch Version Repository Size ============================================================================================================ Installing: postgresql12 x86_64 12.4-1PGDG.rhel7 pgdg12 1.6 M postgresql12-contrib x86_64 12.4-1PGDG.rhel7 pgdg12 610 k postgresql12-libs x86_64 12.4-1PGDG.rhel7 pgdg12 370 k postgresql12-server x86_64 12.4-1PGDG.rhel7 pgdg12 5.1 M Transaction Summary =========================================================================================================== Install 4 Packages Total size: 7.6 M Total download size: 5.1 M Installed size: 32 M Downloading packages: No Presto metadata available for pgdg12 warning: /var/cache/yum/x86_64/7/pgdg12/packages/postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Public key for postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm is not installed postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm | 5.1 MB 00:00:09 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-11.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : postgresql12-libs-12.4-1PGDG.rhel7.x86_64 1/4 Installing : postgresql12-12.4-1PGDG.rhel7.x86_64 2/4 Installing : postgresql12-server-12.4-1PGDG.rhel7.x86_64 3/4 Installing : postgresql12-contrib-12.4-1PGDG.rhel7.x86_64 4/4 Verifying : postgresql12-libs-12.4-1PGDG.rhel7.x86_64 1/4 Verifying : postgresql12-server-12.4-1PGDG.rhel7.x86_64 2/4 Verifying : postgresql12-12.4-1PGDG.rhel7.x86_64 3/4 Verifying : postgresql12-contrib-12.4-1PGDG.rhel7.x86_64 4/4 Installed: postgresql12.x86_64 0:12.4-1PGDG.rhel7 postgresql12-contrib.x86_64 0:12.4-1PGDG.rhel7 postgresql12-libs.x86_64 0:12.4-1PGDG.rhel7 postgresql12-server.x86_64 0:12.4-1PGDG.rhel7 Complete! [root@CentOS-7 ~]#
Step 3: Start PostgreSQL on CentOS
Initialize PostgreSQL:
# /usr/pgsql-12/bin/postgresql-12-setup initdb
Demo Output:
[root@CentOS-7 ~]# /usr/pgsql-12/bin/postgresql-12-setup initdb Initializing database ... OK [root@CentOS-7 ~]#
Start/Enable PostgreSQL:
To start the PostgreSQL service and enable it to start automatically on boot, run the following commands:
# systemctl enable postgresql-12.service # systemctl start postgresql-12.service
[root@CentOS-7 ~]# systemctl enable postgresql-12.service Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service. [root@CentOS-7 ~]# systemctl start postgresql-12.service [root@CentOS-7 ~]#
Step 4: Accessing the PostgreSQL Database
first, you switch the user to Postgres user following the below command
# sudo su - postgres
Now connect to the PostgreSQL terminal :
# psql
Demo Output:
[root@CentOS-7 ~]# sudo su - postgres Last login: Fri Aug 21 07:22:58 EDT 2020 on pts/1 -bash-4.2$ -bash-4.2$ psql psql (12.4) Type "help" for help. postgres=#
Step 5: Setup PostgreSQL admin user’s password
$ sudo su - postgres $ psql -c "alter user postgres with password 'YourStrongPassword'"
Demo Output:
-bash-4.2$ psql -c "alter user postgres with password 'YourStrongPassword'" ALTER ROLE -bash-4.2$
Allow remote clients to connect to the PostgreSQL server:
Edit the file /var/lib/pgsql/12/data/postgresql.conf and set Listen address to your server IP address or “*” for all interfaces.
listen_addresses = '*'
Demo Output:
[root@CentOS-7 ~]# grep -ir listen_addresses /var/lib/pgsql/12/data/postgresql.conf listen_addresses = '*' # what IP address(es) to listen on; [root@CentOS-7 ~]#
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.5.0/24 md5
Update this as per your requirement.
Note: METHOD can be “trust”, “reject”, “md5”, “password”, “scram-sha-256”, “gss”, “sspi”, “ident”, “peer”, “pam”, “ldap”, “radius” or “cert”.
Restart the database service after committing the change.
# sudo systemctl restart postgresql-12
Demo Output:
[root@CentOS-7 ~]# systemctl status postgresql-12 postgresql-12.service - PostgreSQL 12 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled) Active: active (running) since Fri 2020-08-21 07:45:16 EDT; 11s ago Docs: https://www.postgresql.org/docs/12/static/ Process: 10308 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 10313 (postmaster) Tasks: 8 CGroup: /system.slice/postgresql-12.service ├─10313 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/ ├─10315 postgres: logger ├─10317 postgres: checkpointer ├─10318 postgres: background writer ├─10319 postgres: walwriter ├─10320 postgres: autovacuum launcher ├─10321 postgres: stats collector └─10322 postgres: logical replication launcher Aug 21 07:45:16 CentOS-7 systemd[1]: Stopped PostgreSQL 12 database server. Aug 21 07:45:16 CentOS-7 systemd[1]: Starting PostgreSQL 12 database server... Aug 21 07:45:16 CentOS-7 postmaster[10313]: 2020-08-21 07:45:16.117 EDT [10313] LOG: starting PostgreSQL 12.4 on x86_6...64-bit Aug 21 07:45:16 CentOS-7 postmaster[10313]: 2020-08-21 07:45:16.117 EDT [10313] LOG: listening on IPv4 address "0.0.0....t 5432 Aug 21 07:45:16 CentOS-7 postmaster[10313]: 2020-08-21 07:45:16.117 EDT [10313] LOG: listening on IPv6 address "::", port 5432 Aug 21 07:45:16 CentOS-7 postmaster[10313]: 2020-08-21 07:45:16.128 EDT [10313] LOG: listening on Unix socket "/var/ru....5432" Aug 21 07:45:16 CentOS-7 postmaster[10313]: 2020-08-21 07:45:16.140 EDT [10313] LOG: listening on Unix socket "/tmp/.s....5432" Aug 21 07:45:16 CentOS-7 postmaster[10313]: 2020-08-21 07:45:16.188 EDT [10313] LOG: redirecting log output to logging...rocess Aug 21 07:45:16 CentOS-7 postmaster[10313]: 2020-08-21 07:45:16.188 EDT [10313] HINT: Future log output will appear in..."log". Aug 21 07:45:16 CentOS-7 systemd[1]: Started PostgreSQL 12 database server. Hint: Some lines were ellipsized, use -l to show in full. [root@CentOS-7 ~]#
Congratulations! You have successfully installed PostgreSQL 12 on CentOS 7.
Pros and Cons of Installing PostgreSQL 12 on CentOS 7
Pros:
- Robust and Feature-Rich: PostgreSQL is a powerful relational database management system with a wide range of features, including support for advanced data types, indexing, and complex queries.
- Open-Source and Free: PostgreSQL is an open-source software, which means you can use it without any licensing costs. It is maintained by a large community of developers, ensuring continuous improvements and security updates.
- Cross-Platform: PostgreSQL is designed to work on various operating systems, including CentOS 7, making it versatile and suitable for different environments.
- Excellent Data Integrity: PostgreSQL emphasizes data integrity and provides support for multi-version concurrency control (MVCC), ensuring that data remains consistent even in high-concurrency environments.
- Extensibility: PostgreSQL allows you to create custom functions, operators, and data types, making it highly extensible and adaptable to specific business requirements.
- Scalability: PostgreSQL can handle large volumes of data and is suitable for both small-scale projects and enterprise-level applications.
- Active Community: PostgreSQL has a thriving community of users and developers, providing extensive documentation, support, and a wealth of third-party tools and extensions.
Cons:
- Resource Intensive: PostgreSQL can be resource-intensive, especially when dealing with large databases and complex queries. It may require substantial hardware resources for optimal performance.
- Steep Learning Curve: While PostgreSQL is feature-rich, its advanced capabilities can make it challenging for newcomers to learn and fully utilize all its functionalities.
- Limited GUI Tools: Compared to some other database systems, PostgreSQL has relatively fewer graphical user interface (GUI) tools available, which might be preferred by some users for database management.
- Lack of Some SQL Features: PostgreSQL, while highly compliant with SQL standards, might lack support for certain SQL features that are available in other database systems.
- Initial Configuration Complexity: Setting up and configuring PostgreSQL initially can be complex, especially for users with limited experience in database administration.
- Upgrade Process: Upgrading PostgreSQL to newer major versions may require careful planning and testing to ensure compatibility with existing applications and data.
In summary, PostgreSQL 12 on CentOS 7 is a robust and feature-rich database management system with a large community and strong support for data integrity. However, it may require adequate hardware resources and database administration expertise for optimal performance and configuration. Users should consider their specific requirements and available resources before choosing PostgreSQL as their database solution.
Do’s, Don’ts, and Common Mistakes: Installing PostgreSQL 12 on CentOS 7
Here’s a guide on how to install PostgreSQL 12 on CentOS 7, along with some do’s and don’ts and common mistakes to avoid:
Do’s:
- Update Your System: Before installing PostgreSQL, it’s always a good idea to update your CentOS system to ensure you have the latest packages and security updates. Use the following command to update your system:
sudo yum update
- Install PostgreSQL from Official Repositories: Use the official PostgreSQL repositories for CentOS to get the latest stable version. This ensures you get the official and supported version of PostgreSQL.
- Use the Package Manager: On CentOS, it’s recommended to use the package manager (yum) to install software. Avoid manually installing from source unless you have specific reasons for doing so.
Don’ts:
- Don’t Install Multiple Versions: Installing multiple versions of PostgreSQL on the same system can lead to conflicts and confusion. Stick to a single version unless you have a specific need for multiple versions.
- Don’t Run PostgreSQL as Root: Running PostgreSQL as the root user is a security risk. During the installation, PostgreSQL creates a system user called ‘postgres’ automatically. This user will run the PostgreSQL processes, and you should use it for management.
- Don’t Disable SELinux: Disabling SELinux (Security-Enhanced Linux) might seem like a quick fix for permission issues, but it can compromise your system’s security. Instead, set the appropriate SELinux policies to allow PostgreSQL to function correctly.
Common Mistakes:
- Incorrect Repository Setup: One common mistake is setting up the wrong repository for PostgreSQL, which might lead to the installation of an older or unsupported version. Double-check the repository configuration to avoid this.
- Missing Dependencies: PostgreSQL may have dependencies on CentOS that need to be installed before the actual PostgreSQL installation. Always check the installation output for any missing dependencies and install them.
- Firewall Blocking Connections: If you’re unable to connect to PostgreSQL after installation, it might be due to the firewall blocking the required ports. Ensure that ports 5432 (default PostgreSQL port) and 22 (SSH) are open in your firewall.
Presented in a table format:
| Do’s | Don’ts | Common Mistakes |
|---|---|---|
| 1. Update Your System: | 1. Don’t Install Multiple Versions: | 1. Incorrect Repository Setup: |
| Before installing PostgreSQL, update your CentOS | Installing multiple versions of PostgreSQL on the | Setting up the wrong repository can lead to installing |
| system to ensure you have the latest packages and | same system can lead to conflicts and confusion. | an older or unsupported version of PostgreSQL. |
| security updates. | Stick to a single version unless necessary. | |
| 2. Install from Official Repositories: | 2. Don’t Run PostgreSQL as Root: | 2. Missing Dependencies: |
| Use official PostgreSQL repositories for CentOS | Running PostgreSQL as the root user is a security | PostgreSQL might have dependencies on CentOS that need |
| to get the latest stable version. | risk. Create a dedicated ‘postgres’ user and run | to be installed before the actual PostgreSQL |
| PostgreSQL under that account. | installation. Always check for any missing dependencies. | |
| 3. Use the Package Manager: | 3. Don’t Disable SELinux: | 3. Firewall Blocking Connections: |
| On CentOS, prefer using the package manager (yum) | Disabling SELinux can compromise system security. | If unable to connect to PostgreSQL, the firewall might |
| to install PostgreSQL. | Instead, set appropriate SELinux policies. | be blocking the required ports. Check firewall settings. |
Now let’s see how to troubleshoot PostgreSQL installation.
PostgreSQL Troubleshooting:
If you are not able to connect from the remote client system to the Postgresql database server then check your firewall and allow it to connect.
follow the below command to add Postgresql service in your remote client system.
sudo firewall-cmd --add-service=postgresql --permanent sudo firewall-cmd --reload
PostgreSQL commands with examples
Command to connect to remote Postgresql database:
$ psql -U <dbuser> -h <serverip> -p 5432 <dbname>
Create a Database in PostgreSQL
postgres=# CREATE DATABASE student WITH OWNER = postgres ENCODING = 'UTF8'; CREATE DATABASE postgres=#
List all the databases in PostgreSQL:
# \list
Connect to any PostgreSQL database:
# \c database_name
postgres=# \c student You are now connected to database "student" as user "postgres". student=#
Create a table in PostgreSQL
# create table students (name varchar(25), surname varchar(25));
student=# create table students (name varchar(25), surname varchar(25)); CREATE TABLE student=#
Insert records
# INSERT INTO students VALUES (‘pavan’,’patni’);
student=# INSERT INTO students VALUES ('pavan','patni'); INSERT 0 1 student=#
List all the tables in PostgreSQL
# \d
student=# \d List of relations Schema | Name | Type | Owner --------+----------+-------+---------- public | students | table | postgres (1 row) student=#
Exit from PostgreSQL prompt:
# \q
student=# \q -bash-4.2$
Drop PostgreSQL database:
#drop database database-name;
postgres=# drop database openerpdemo; DROP DATABASE postgres=#
End of tutorials, how to install PostgreSQL 12 ON CentOS 7 or RHEL 7. For any queries comment us below.
Thanks for reading this article, you’ll also like to read Database blogs.
FAQs
1. How can I verify if PostgreSQL is installed on my CentOS 7 or RHEL 7 server?
To check if PostgreSQL is installed, open the terminal and run the command: psql --version
2. Can I install PostgreSQL 12 on CentOS 8 or RHEL 8?
Yes, PostgreSQL 12 can be installed on CentOS 8 or RHEL 8 following similar steps.
3. How can I uninstall PostgreSQL 12 if needed?
To uninstall PostgreSQL 12, use the following command: sudo yum remove postgresql12-server postgresql12
4. Can I use PostgreSQL with my preferred programming language?
Yes, PostgreSQL has extensive support for various programming languages, including Python, Java, and C/C++, among others.
5. Is it necessary to have a powerful server to run PostgreSQL 12?
While a powerful server can enhance performance, PostgreSQL is known for its efficiency even on modest hardware configurations. It can be run on a wide range of systems.
