Features of PostgreSQL
PostgreSQL has many advanced features as compared to other database management systems. These include:
- Table inheritance
- Nested transactions
- Multi-version concurrency control.
- Sophisticated locking mechanism
- Foreign key referential integrity
- Asynchronous replication.
- Views, rules and subquery
The current versions of PostgreSQL support the following additional features:
- Native Microsoft Windows server version
- Table spaces
- Point-in-time recovery
Installing PostgreSQL 12 on Oracle Linux
The following steps guide on how to install PostgreSQL on Oracle Linux 8.
Step 1: Update system packages
It is always good to ensure that your system is up to date when carrying out installations.
sudo dnf update
Step 2: Add PostgreSQL repository
Use the below command to add the official PostgreSQL on Oracle Linux 8
sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Step 3: Install PostgreSQL on Oracle 12 on Oracle Linux 8
We need to disable any other existing PostgreSQL if there is then install PostgreSQL 12. Run the below commands:
sudo dnf module disable postgresql sudo dnf --repo pgdg12 install postgresql12-server postgresql12
When installation is complete, you should see an output as below:
Step 4: Initialize and start Database Service
We first need to initialize database before starting. Use the following command:
$ sudo /usr/pgsql-12/bin/postgresql-12-setup initdb Initializing database --- OK
Now we can start and enable database service.
sudo systemctl enable --now postgresql-12
Confirm status if postgresql is running using the below command.
sudo systemctl status postgresql-12
The output should be as below if running:
If running Firewall service and remote clients should connect to your database server, allow PostgreSQL service.
sudo firewall-cmd --permanent --add-service=postgresql sudo firewall-cmd --reload
Step 5: Set PostgreSQL admin user
To set PostgreSQL admin user, run:
$ sudo su – postgres $ psql -c "alter user postgres with password 'MyStrongPassword'" ALTER ROLE
Step 6: Enable Remote Access
Edit Postgresql configuration file to set listen address. You can set to your system IP address or use ‘*’ for any.
sudo vim /var/lib/pgsql/12/data/postgresql.conf
Add listen address as shown
listen_addresses = ‘192.168.100.178’
Also allow remote connection by editing the shown the below file
sudo vim /var/lib/pgsql/12/data/pg_hba.conf
Add the following content
# Accept from anywhere host all all 0.0.0.0/0 md5 # Accept from specific addresses host all all 192.168.100.0/24 md5
Restart database service after the changes
sudo systemctl restart postgresql-12
Test remote connection by using the shown command:
$ psql -U <dbuser> -h <serverip> -p 5432 <dbname>
This has been a step by step guide on how to install PostgreSQL on Oracle Linux 8.
Recommended Video courses for Learning PostgreSQL Database:
- SQL and PostgreSQL: The Complete Developer’s Guide
- The Complete Python/PostgreSQL Course 2.0
- SQL & PostgreSQL for Beginners: Become an SQL Expert
- Learn SQL Using PostgreSQL: From Zero to Hero
- PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours
I hope it has been using. Check below for more of your daily Linux guides: