How to Install PostgreSQL on CentOS 7
PostgreSQL, commonly referred to as Postgres, is a powerful open-source relational database management system (RDBMS). It's known for its reliability, robustness, and extensibility, making it a popular choice for many developers and organizations. If you're looking to install PostgreSQL on CentOS 7, this step-by-step guide will walk you through the process.
Step 1: Update System Packages
Before installing any new software, it's essential to ensure that your system packages are up to date. You can do this by running the following commands :
sudo yum update
Step 2: Enable PostgreSQL Repository
PostgreSQL isn't included in the default CentOS repositories, so you need to enable the PostgreSQL repository to install the latest version. Create a new repository file using the following command:
sudo vi /etc/yum.repos.d/pgdg.repo
Now, paste the following configuration into the file :
[pgdg13] name=PostgreSQL 13 for RHEL/CentOS $releasever - $basearch baseurl=https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-$releasever-$basearch enabled=1 gpgcheck=0
Step 3: Install PostgreSQL
Once the repository is enabled, you can install PostgreSQL by running the following command :
sudo yum install postgresql13-server postgresql13-contrib
This command will install both the PostgreSQL server and additional contrib packages, which provide additional features and extensions.
Step 4: Initialize PostgreSQL Database
After installing PostgreSQL, you need to initialize the database. Run the following command :
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
This command initializes the PostgreSQL database cluster.
Once the database cluster has been initialized, there will be a file called postgresql.conf in the data folder, which is the main configuration file for PostgreSQL. We will change two parameters in this file. Using vi or your favorite text editor, open the file for editing.
sudo vi /var/lib/pgsql/13/data/postgresql.conf
And change the following lines:
Change #listen_addresses = 'localhost' to listen_addresses = '*' Change #port = 5432 to port = 5432
The first parameter specifies which IP address the database server will listen to. As a security measure, out-of-box Postgres installations only allow local host connections. Changing this to '*' means Postgres will listen for traffic from any source. The second parameter has been enabled by taking off the comment marker (#); it specifies the default port for Postgres.
Save and exit the file.
Next, we will edit pg_hba.conf, which is PostgreSQL's Host Based Access (HBA) configuration file. It specifies which hosts and IP ranges can connect to the database server. Each entry specifies whether the connection can be made locally or remotely (host), which database it can connect to, which user it can connect as, which IP block the request can come from, and what authentication mode should be used. Any connection requests not matching with any of these entries would be denied.
Open pg_hba.conf for editing.
sudo vi /var/lib/pgsql/13/data/pg_hba.conf
Scroll to the bottom of the file, and add this line:
host all all your_web_server_ip/32 md5
This line tells PostgreSQL to accept database connections coming only from IP address your_web_server_ip using a standard md5 checksum for password authentication. The connection can be made against any database as any user.
Save and exit the file.
Step 5: Start and Enable PostgreSQL Service
Now, start the PostgreSQL service and enable it to start automatically on boot :
sudo systemctl start postgresql-13 sudo systemctl enable postgresql-13Verify PostgreSQL Service Status:
sudo systemctl status postgresql-13
The output should indicate that the service is "active (running)". If it's not running, start it with :
sudo systemctl start postgresql-13
If you after making changes to its configuration files of Postgresql, you have to reload Postgresql
sudo systemctl reload postgresql-13
Configure PostgreSQL to run automatically at startup on CentOS 7:
sudo systemctl is-enabled postgresql-13.service
If the output is not enabled, activate the service with :
sudo systemctl enable postgresql-13.service
Step 6: Access PostgreSQL Prompt
Once PostgreSQL is installed and running, you can access the PostgreSQL prompt by switching to the postgres user and running the psql command :
sudo su - postgres psql
Step 7: Setting a Password for the PostgreSQL User
By default, PostgreSQL doesn't set a password for the postgres user. To set a password, you can use the following command from within the PostgreSQL prompt :
\password postgres
Step 8: Configuring PostgreSQL
PostgreSQL configuration files are located in the /var/lib/pgsql/13/data directory. You may need to adjust the configuration according to your requirements. Common configuration changes include adjusting memory settings, connection settings, and authentication methods.
Step 9: Updating the Database Server Firewall
We also need to allow Postgres database traffic to pass though the firewall. CentOS 7 implements a dynamic firewall through the firewalld daemon; the service doesn't need to restart for changes to take effect. The firewalld service should start automatically at system boot time, but it's always good to check.
sudo firewall-cmd --state
The default state should be running, but if it is not running start it with :
sudo systemctl start firewalld
Next, add the rules for port 5432. This is the port for PostgreSQL database traffic.
sudo firewall-cmd --permanent --zone=public --add-port=5432/tcp
Then reload the firewall.
sudo firewall-cmd --reload
Command to view the ports registered in your firewall on CentOS 7
sudo firewall-cmd --list-all
Step 10: Testing PostgreSQL Installation
Please connect to Postgres with this command :
psql -U postgres -h xx.xx.xx.xx
The script connects to the PostgreSQL database server with the username 'postgres' and the host address 'xx.xx.xx.xx' using the psql command
To ensure that PostgreSQL is installed correctly and running smoothly, you can create a test database and perform basic operations. Here's an example :
CREATE DATABASE testdb;
\c testdb;
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('John'), ('Alice'), ('Bob');
SELECT * FROM users;
Conclusion
By following these steps, you should have successfully installed PostgreSQL on your CentOS 7 system. PostgreSQL is now ready to use for your projects and applications, providing a reliable and powerful database management solution. Remember to consult the PostgreSQL documentation for more advanced configuration options and features.
Komentar & Pertanyaan