Installing PostgreSQL on CentOS
Introduction
In
CentOS
the default version of PostgreSQL is rather old.
On CentOS 7 it is version 9.2, which is no longer supported.
This article explains how to install various versions of PostgreSQL
Installation from the repository
Installing from the repository 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!
After installation, check the version of PostgreSQL you have installed
postgres -V
postgres (PostgreSQL) 9.2.24
Installing PostgreSQL 11 on CentOS 7
Run
yum update -y
reboot
You can find the repository you need on the site
yum.postgresql.org
Run
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]:
Choose 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!
Run
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!
You can check the result of the installation by running
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.
Installing the latest version
Initialising the database
Usually the database has to be initialised after installation.
Make sure that you are in your home directory and logged in
as the right user.
In my case this is
sudo su - postgres
cd /var/lib/pgsql
After installing PostgreSQL there are usually only two subdirectories in this directory
ls /var/lib/pgsql
backups data
For PostgreSQL version 9
postgresql-setup initdb
Initializing database ... OK
For more recent versions of PostgreSQL, for example 11
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK
After a successful initialisation a file appears in the directory 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
Now the server can be started with the command
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
server starting
Connecting to PostgreSQL
sudo su - postgres
psql
psql (12.7 (Centos 12.7-0Centos0.20.04.1)) Type "help" for help. postgres=#
Get information about the connection
\conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
Installing pgAdmin
Install the public key
sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
Create the repository configuration file
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'
Install both the desktop and the web version:
sudo apt install pgadmin4
Install only the desktop version
sudo apt install pgadmin4-desktop
Install only the web version
sudo apt install pgadmin4-web
Configure the web server for the web version
sudo /usr/pgadmin4/bin/setup-web.sh
The web version can be opened in a browser
http://127.0.0.1/pgadmin4
Opening the port
5432 is the standard port that PostgreSQL listens on. The full list can be found in the article «List of TCP and UDP ports»
sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent
sucess
Then the firewall has to be restarted
sudo firewall-cmd --reload
sucess
Check
sudo firewall-cmd --list-ports
8443/tcp 3389/tcp 2222/tcp 5432/tcp
Removing PostgreSQL
Remove
sudo yum remove -y postgresql
sudo yum remove -y postgres\*
Remove only postgresql-server but do not remove the dependencies.
On CentOS 7
rpm -e --nodeps postgresql-server
Article author: Andrei Olegovich