Установка PostgreSQL в CentOS

Введение

В CentOS по умолчанию будет стоять довольно старая версия PostgreSQL.

В CentOS 7 это версия 9.2 которая уже не поддерживается.

Из этой статьи вы узнаете об установке различных версий PostgreSQL

Установка из репозитория

Установка из репозитория CentOS

sudo yum install postgresql-server postgresql-contrib

Loaded plugins: fastestmirror, langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. Loading mirror speeds from cached hostfile * base: ftp.funet.fi * epel: mirrors.xtom.ee * extras: ftp.funet.fi * updates: ftp.funet.fi Resolving Dependencies --> Running transaction check ---> Package postgresql-contrib.x86_64 0:9.2.24-6.el7_9 will be installed --> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-6.el7_9 for package: postgresql-contrib-9.2.24-6.el7_9.x86_64 --> Processing Dependency: postgresql(x86-64) = 9.2.24-6.el7_9 for package: postgresql-contrib-9.2.24-6.el7_9.x86_64 --> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-contrib-9.2.24-6.el7_9.x86_64 --> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql-contrib-9.2.24-6.el7_9.x86_64 ---> Package postgresql-server.x86_64 0:9.2.24-6.el7_9 will be installed --> Running transaction check ---> Package postgresql.x86_64 0:9.2.24-6.el7_9 will be installed ---> Package postgresql-libs.x86_64 0:9.2.24-6.el7_9 will be installed ---> Package uuid.x86_64 0:1.6.2-26.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================= Package Arch Version Repository Size ============================================================================================================= Installing: postgresql-contrib x86_64 9.2.24-6.el7_9 updates 552 k postgresql-server x86_64 9.2.24-6.el7_9 updates 3.8 M Installing for dependencies: postgresql x86_64 9.2.24-6.el7_9 updates 3.0 M postgresql-libs x86_64 9.2.24-6.el7_9 updates 235 k uuid x86_64 1.6.2-26.el7 base 55 k Transaction Summary ============================================================================================================= Install 2 Packages (+3 Dependent packages) Total download size: 7.7 M Installed size: 35 M Is this ok [y/d/N]:

y

Downloading packages: (1/5): postgresql-contrib-9.2.24-6.el7_9.x86_64.rpm | 552 kB 00:00:00 (2/5): postgresql-libs-9.2.24-6.el7_9.x86_64.rpm | 235 kB 00:00:00 (3/5): uuid-1.6.2-26.el7.x86_64.rpm | 55 kB 00:00:00 (4/5): postgresql-9.2.24-6.el7_9.x86_64.rpm | 3.0 MB 00:00:00 (5/5): postgresql-server-9.2.24-6.el7_9.x86_64.rpm | 3.8 MB 00:00:01 ------------------------------------------------------------------------------------------------------------- Total 5.2 MB/s | 7.7 MB 00:00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : postgresql-libs-9.2.24-6.el7_9.x86_64 1/5 Installing : postgresql-9.2.24-6.el7_9.x86_64 2/5 Installing : uuid-1.6.2-26.el7.x86_64 3/5 Installing : postgresql-contrib-9.2.24-6.el7_9.x86_64 4/5 Installing : postgresql-server-9.2.24-6.el7_9.x86_64 5/5 Verifying : postgresql-libs-9.2.24-6.el7_9.x86_64 1/5 Verifying : postgresql-server-9.2.24-6.el7_9.x86_64 2/5 Verifying : postgresql-contrib-9.2.24-6.el7_9.x86_64 3/5 Verifying : uuid-1.6.2-26.el7.x86_64 4/5 Verifying : postgresql-9.2.24-6.el7_9.x86_64 5/5 Installed: postgresql-contrib.x86_64 0:9.2.24-6.el7_9 postgresql-server.x86_64 0:9.2.24-6.el7_9 Dependency Installed: postgresql.x86_64 0:9.2.24-6.el7_9 postgresql-libs.x86_64 0:9.2.24-6.el7_9 uuid.x86_64 0:1.6.2-26.el7 Complete!

После установки проверьте версию установленного PostgreSQL

postgres -V

postgres (PostgreSQL) 9.2.24

Установка PostgreSQL 11 в CentOS 7

Выполните

yum update -y
reboot

Нужный репозиторий можно найти на сайте yum.postgresql.org

Выполните

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

↓ Пропустить вывод ↓

Loaded plugins: fastestmirror, langpacks pgdg-redhat-repo-latest.noarch.rpm | 7.5 kB 00:00 Examining /var/tmp/yum-root-nr5e4V/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-18.noarch Marking /var/tmp/yum-root-nr5e4V/pgdg-redhat-repo-latest.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgdg-redhat-repo.noarch 0:42.0-18 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: pgdg-redhat-repo noarch 42.0-18 /pgdg-redhat-repo-latest.noarch 11 k Transaction Summary ================================================================================ Install 1 Package Total size: 11 k Installed size: 11 k Is this ok [y/d/N]:

Выберите y

Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : pgdg-redhat-repo-42.0-18.noarch 1/1 Verifying : pgdg-redhat-repo-42.0-18.noarch 1/1 Installed: pgdg-redhat-repo.noarch 0:42.0-18 Complete!

Выполните

sudo yum -y install postgresql11-server postgresql11

↓ Пропустить вывод ↓

Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: ftp.funet.fi * epel: mirrors.glesys.net * extras: ftp.funet.fi * updates: ftp.funet.fi pgdg-common/7/x86_64/signature | 198 B 00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-18.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG pgdg-common/7/x86_64/signature | 2.9 kB 00:00 !!! pgdg10/7/x86_64/signature | 198 B 00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-18.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG pgdg10/7/x86_64/signature | 3.6 kB 00:00 !!! pgdg11/7/x86_64/signature | 198 B 00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-18.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG pgdg11/7/x86_64/signature | 3.6 kB 00:00 !!! pgdg12/7/x86_64/signature | 198 B 00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-18.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG pgdg12/7/x86_64/signature | 3.6 kB 00:00 !!! pgdg13/7/x86_64/signature | 198 B 00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-18.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG pgdg13/7/x86_64/signature | 3.6 kB 00:00 !!! pgdg96/7/x86_64/signature | 198 B 00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-18.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG pgdg96/7/x86_64/signature | 3.6 kB 00:00 !!! (1/11): pgdg11/7/x86_64/group_gz | 245 B 00:01 (2/11): pgdg10/7/x86_64/primary_db | 336 kB 00:01 (3/11): pgdg12/7/x86_64/group_gz | 245 B 00:00 (4/11): pgdg12/7/x86_64/primary_db | 215 kB 00:00 (5/11): pgdg13/7/x86_64/group_gz | 246 B 00:00 (6/11): pgdg-common/7/x86_64/primary_db | 133 kB 00:01 (7/11): pgdg96/7/x86_64/group_gz | 249 B 00:00 (8/11): pgdg10/7/x86_64/group_gz | 245 B 00:01 (9/11): pgdg13/7/x86_64/primary_db | 125 kB 00:00 (10/11): pgdg11/7/x86_64/primary_db | 358 kB 00:01 (11/11): pgdg96/7/x86_64/primary_db | 326 kB 00:00 Resolving Dependencies --> Running transaction check ---> Package postgresql11.x86_64 0:11.13-1PGDG.rhel7 will be installed --> Processing Dependency: postgresql11-libs(x86-64) = 11.13-1PGDG.rhel7 for package: postgresql11-11.13-1PGDG.rhel7.x86_64 ---> Package postgresql11-server.x86_64 0:11.13-1PGDG.rhel7 will be installed --> Running transaction check ---> Package postgresql11-libs.x86_64 0:11.13-1PGDG.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: postgresql11 x86_64 11.13-1PGDG.rhel7 pgdg11 1.7 M postgresql11-server x86_64 11.13-1PGDG.rhel7 pgdg11 4.8 M Installing for dependencies: postgresql11-libs x86_64 11.13-1PGDG.rhel7 pgdg11 365 k Transaction Summary ================================================================================ Install 2 Packages (+1 Dependent package) Total download size: 6.8 M Installed size: 29 M Downloading packages: warning: /var/cache/yum/x86_64/7/pgdg11/packages/postgresql11-libs-11.13-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Public key for postgresql11-libs-11.13-1PGDG.rhel7.x86_64.rpm is not installed (1/3): postgresql11-libs-11.13-1PGDG.rhel7.x86_64.rpm | 365 kB 00:00 (2/3): postgresql11-server-11.13-1PGDG.rhel7.x86_64.rpm | 4.8 MB 00:01 (3/3): postgresql11-11.13-1PGDG.rhel7.x86_64.rpm | 1.7 MB 00:03 -------------------------------------------------------------------------------- Total 2.1 MB/s | 6.8 MB 00:03 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-18.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 : postgresql11-libs-11.13-1PGDG.rhel7.x86_64 1/3 Installing : postgresql11-11.13-1PGDG.rhel7.x86_64 2/3 failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink failed to link /usr/bin/pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup: /usr/bin/pg_basebackup exists and it is not a symlink failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink Installing : postgresql11-server-11.13-1PGDG.rhel7.x86_64 3/3 Verifying : postgresql11-11.13-1PGDG.rhel7.x86_64 1/3 Verifying : postgresql11-libs-11.13-1PGDG.rhel7.x86_64 2/3 Verifying : postgresql11-server-11.13-1PGDG.rhel7.x86_64 3/3 Installed: postgresql11.x86_64 0:11.13-1PGDG.rhel7 postgresql11-server.x86_64 0:11.13-1PGDG.rhel7 Dependency Installed: postgresql11-libs.x86_64 0:11.13-1PGDG.rhel7 Complete!

Проверить результат установки можно выполнив

rpm -qi postgresql11-server

↓ Пропустить вывод ↓

Name : postgresql11-server Version : 11.13 Release : 1PGDG.rhel7 Architecture: x86_64 Install Date: Fri 13 Aug 2021 11:04:05 AM EEST Group : Unspecified Size : 19789221 License : PostgreSQL Signature : DSA/SHA1, Thu 12 Aug 2021 10:56:26 AM EEST, Key ID 1f16c2e1442ef0f8 Source RPM : postgresql11-11.13-1PGDG.rhel7.src.rpm Build Date : Thu 12 Aug 2021 10:42:27 AM EEST Build Host : koji-centos7-x86-64-pgbuild Relocations : (not relocatable) Vendor : PostgreSQL Global Development Group URL : https://www.postgresql.org/ Summary : The programs needed to create and run a PostgreSQL server Description : PostgreSQL is an advanced Object-Relational database management system (DBMS). The postgresql11-server package contains the programs needed to create and run a PostgreSQL server, which will in turn allow you to create and maintain PostgreSQL databases.

Установка новейшей версии

Инициализация БД

Обычно после установки нужно инициализировать базу данных.

Убедитесь, что вы находитесь в домашней директории и зашли под нужным пользователем.

В моём случае это

sudo su - postgres
cd /var/lib/pgsql

После установки PostgreSQL в этой директории обычно всего две поддиректории

ls /var/lib/pgsql

backups data

Для PostgreSQL версии 9

postgresql-setup initdb

Initializing database ... OK

Для более современных версий PostgreSQL, например 11

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

Initializing database ... OK

После успешной инициализации в директории появится файл initdb.log

ls /var/lib/pgsql

backups data initdb.log

cat initdb.log

The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". fixing permissions on existing directory /var/lib/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /var/lib/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating collations ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok loading PL/pgSQL server-side language ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok Success. You can now start the database server using: /usr/bin/postgres -D /var/lib/pgsql/data or /usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

Теперь сервер можно запустить командой

/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

server starting

Подключение к PostgreSQL

sudo su - postgres

psql

psql (12.7 (Centos 12.7-0Centos0.20.04.1)) Type "help" for help. postgres=#

Получить данные о подключении

\conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Установка pgAdmin

Установить публичный ключ

sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

Создать файл конфигурации репозитория

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Установить и десктопную и веб версии:

sudo apt install pgadmin4

Установить только десктопную версию

sudo apt install pgadmin4-desktop

Установить только веб версию

sudo apt install pgadmin4-web

Настроить веб сервер для веб версию

sudo /usr/pgadmin4/bin/setup-web.sh

Веб версию можно открыть в браузере

http://127.0.0.1/pgadmin4

Открыть порт

5432 - это стандартный порт, на котором слушает PostgreSQL. Полный список можно изучить в статье «Список TCP и UDP портов»

sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent

sucess

Затем нужно перезапустить firewall

sudo firewall-cmd --reload

sucess

Проверка

sudo firewall-cmd --list-ports

8443/tcp 3389/tcp 2222/tcp 5432/tcp

Удаление PostgreSQL

Удалить

sudo yum remove -y postgresql
sudo yum remove -y postgres\*

Удалить только postgresql-server но не удалять зависимости.

В CentOS 7

rpm -e --nodeps postgresql-server