Oracle linux install postgres

How to install PostgreSQL 13 on CentOS 8 / RHEL 8 / Oracle Linux 8?

PostgreSQL is one of the most popular database management systems in the world. Its robustness, high availability, and ease of installation make it perhaps the most advanced in the world. Despite easy installation, not always many users do. So, in this post, I will show you how to install PostgreSQL 13 on CentOS 8 / RHEL 8 / Oracle Linux 8. In addition, we’ll show you the most basic configurations. At the end of the post, you will have a PostgreSQL installation ready to start working.

Install PostgreSQL 13 on CentOS 8 / RHEL 8 / Oracle Linux 8

Both RHEL 8 and its derivatives include PostgreSQL 10 in their repositories. However, we already have available version 13. And it is recommended to install it because thanks to it, we can enjoy interesting news and improvements in the performance of the application. Important, if we are going to store large quantities of records.

So first, open a terminal session. Or if you are using a server connect to it using ssh.

Then log in as the root user.

The best way to install PostgreSQL 13 on CentOS 8, RHEL 8 and Oracle Linux 8 is to add the official PostgreSQL refill. Not only is it easy, but also safe and the possibility to be always up to date.

To do this, run the following command:

:~# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Then, disable the PostgreSQL module on CentOS 8.

:~# dnf module disable postgresql

Now, install PostgreSQL 13 on CentOS 8.

:~# dnf install postgresql13-server postgresql13

After that, let us configure it.

Configure PostgreSQL on CentOS / RHEL 8 / Oracle Linux 8

Initialize PostgreSQL database and start the service

After installing it, the first thing to do is to initialize the database. To achieve this, it is necessary to execute the following command:

:~# /usr/pgsql-13/bin/postgresql-13-setup initdb Initializing database . OK

With this, we will have already started the initial database. At this point, the PostgreSQL service is not enable, so you can’t use it yet. Then you have to do it.

:~# systemctl enable postgresql-13

Then, if you want PostgreSQL to start along with the system run this command:

:~# systemctl start postgresql-13

To check that everything is going well, check the status of the service:

:~# systemctl status postgresql-13

As you can see, everything’s fine.

Change the password to the “postgres” user and allow the remote connections

During the installation of PostgreSQL, a new user called postgres is created. The problem is that you do not define a password, so it makes you vulnerable. To avoid problems, it is convenient to create a password.

To do this, just use the Unix passwd command.

There you will have to enter the password twice. If both are effective, the final change will be made.

Now, we have to allow access to remote connections. The client must be installed on each of the systems that will access PostgreSQL. It will depend on each operating system.

Then, a good security measure is to specify the hosts that can access it. Of course, if the data will be served on the Internet, then it is necessary to allow all the accesses.

:~# nano /var/lib/pgsql/13/data/postgresql.conf

then searches for the listen_addresses line and places the hosts that can access it. If you allow any access, type ‘*’.

listen_addresses = '[host/IP_adress]' or listen_addresses = '*'

:~# systemctl restart postgresql-13

Finally, you have to open the port 5432 on the firewall to allow the connections.

:~# firewall-cmd --add-port=5432/tcp --permanent success :~# firewall-cmd --reload success

Now, you can access to the PostgreSQL shell.

Now, you can start to work.

Conclusion

PostgreSQL is one of the best there is for databases. Thanks to its community spirit, it is possible to find a lot of documentation about it. On the other hand, installing version 13 on CentOS 8, RHEL 8 and Oracle Linux 8 is quite simple as you have seen in this post.

Please share this post with your friends and join our Telegram channel.

2 COMMENTS

Источник

13 Мар 2019 21:03:26 | 0 comments

Установка и первоначальная настройка PostgreSQL 11 на Oracle Linux 7.6

В октябре 2017 года я писал статью про установку PostgreSQL 10 на Debian 9, с тех пор много воды утекло, вышла 11-я версия PostgreSQL и я решил написать краткую статью про его установку на Oracle Linux 7.6

По сравнению с Debian в установке на Oracle Linux есть несколько нюансов и в статье я это опишу.

Исходные данные: Oracle Linux 7.6
Задача: Установить PostgreSQL 11.x

Устанавливаем PostgreSQL 11.x:

yum -y install postgresql11-server

После установки PostgreSQL 11.x не создает кластер баз данных, это нужно сделать вручную.
Важно знать, что в Oracle Linux каталог с данными и файлами настройки будет /var/lib/pgsql/11/data

Initializing database . OK

Я задал через PGSETUP_INITDB_OPTIONS свои настройки, у Вас их может не быть (можете использовать настройки по умолчанию), не нужно слепо повторять мой пример, читайте systemctl enable postgresql-11.service

systemctl start postgresql-11.service

systemctl status postgresql-11.service

● postgresql-11.service - PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled) Active: active (running) since Wed 2019-03-13 23:01:43 +05; 10s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 3895 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir $ (code=exited, status=0/SUCCESS) Main PID: 3902 (postmaster) CGroup: /system.slice/postgresql-11.service ├─3902 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/ ├─3903 postgres: logger ├─3905 postgres: checkpointer ├─3906 postgres: background writer ├─3907 postgres: walwriter ├─3908 postgres: autovacuum launcher ├─3909 postgres: stats collector └─3910 postgres: logical replication launcher Mar 13 23:01:38 oracle76 systemd[1]: Starting PostgreSQL 11 database server. Mar 13 23:01:43 oracle76 postmaster[3902]: 2019-03-13 23:01:43.797 +05 [3902] LOG: listening on IPv6 address "::1%1", port 5432 Mar 13 23:01:43 oracle76 postmaster[3902]: 2019-03-13 23:01:43.798 +05 [3902] LOG: listening on IPv4 address "127.0.0.1", port 5432 Mar 13 23:01:43 oracle76 postmaster[3902]: 2019-03-13 23:01:43.802 +05 [3902] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" Mar 13 23:01:43 oracle76 postmaster[3902]: 2019-03-13 23:01:43.814 +05 [3902] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" Mar 13 23:01:43 oracle76 postmaster[3902]: 2019-03-13 23:01:43.836 +05 [3902] LOG: redirecting log output to logging collector process Mar 13 23:01:43 oracle76 postmaster[3902]: 2019-03-13 23:01:43.836 +05 [3902] HINT: Future log output will appear in directory "log". Mar 13 23:01:43 oracle76 systemd[1]: Started PostgreSQL 11 database server.

netstat -ltupn | grep -E 'post(gres|master)' tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 3902/postmaster tcp6 0 0 ::1:5432 . * LISTEN 3902/postmaster

Первым делом меняем пароль пользователя postgres:

su - postgres psql postgres=# \password postgres postgres=# \q

По умолчанию PostgreSQL принимает соединения только с локальных служб, т.к. слушает интерфейс localhost и это абсолютно правильно с точки зрения безопасности, но если Вы планируете подключения к серверу извне или из локальной сети, то Вам потребуется поменять параметр listen_addresses

Для PostgreSQL 11 открываем основной файл настроек /var/lib/pgsql/11/data/postgresql.conf и раскомментируем строку

listen_addresses = 'localhost'

listen_addresses = 'localhost,192.168.35.10'

таким образом мы укажем PostgreSQL слушать сетевые соединения на интерфейсе localhost и на нашем внутреннем интерфейсе локальной сети с IP адресом 192.168.35.10

Теперь разрешим подключение из локальной сети с любых хостов и к любым БД, для этого файле /var/lib/pgsql/11/data/pg_hba.conf после строки:

# IPv4 local connections: host all all 127.0.0.1/32 ident

host all all 192.168.35.0/24 md5

,где 192.168.35.0/24 — это наша локальная подсеть.

Далее выходим из под пользователя postgres и рестартуем PostgreSQL:

systemctl restart postgresql-11.service

netstat -ltupn | grep -E 'post(gres|master)' tcp 0 0 192.168.35.10:5432 0.0.0.0:* LISTEN 7829/postmaster tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 7829/postmaster tcp6 0 0 ::1:5432 . * LISTEN 7829/postmaster

По умолчанию в Oracle Linux включен firewalld и он запрещает все входящие соединения кроме служб ssh и dhcpv6-client, если мы решили открыть доступ на порт 5432 из локальной сети, то нужно настроить firewalld.

Проверим список разрешенных служб:

firewall-cmd --zone=public --list-services

Теперь добавим службу postgresql:

firewall-cmd --zone=public --permanent --add-service=postgresql firewall-cmd --reload

Проверим правила через iptables:

iptables -L IN_public_allow -n Chain IN_public_allow (1 references) target prot opt source destination ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22 ctstate NEW ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:5432 ctstate NEW

Более подробно о настройке firewalld можно узнать про установку PostgreSQL 10 на Debian 9 + использовать онлайн конфигураторы связаться со мной разными доступными способами.

Источник