Install PostgreSQL 14 and pgAdmin 4 on CentOS 7

Install database postgreSQL 14 dan pgAdmin 4 pada linux centos 7

  • Update package repository dan install repo epel pada centos 7
yum update -y && yum install epel-release nano wget -y
  • Install PostgreSQL yum repository
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  • Disable old version PostgreSQL yum repositories
yum-config-manager --disable pgdg10 pgdg94 pgdg95 pgdg96
  • Build cache yum repository
[root@PostgreSQL-centos admin]# yum makecache fast
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/x86_64/metalink                                                                             |  50 kB  00:00:00
 * base: mirror.idroot.cloud
 * epel: epel.mirror.angkasa.id
 * extras: mirror.biznetgio.com
 * updates: mirror.gi.co.id
base                                                                                             | 3.6 kB  00:00:00
extras                                                                                           | 2.9 kB  00:00:00
pgdg-common/7/x86_64/signature                                                                   |  198 B  00:00:00
pgdg-common/7/x86_64/signature                                                                   | 2.9 kB  00:00:00 !!!
pgdg11/7/x86_64/signature                                                                        |  198 B  00:00:00
pgdg11/7/x86_64/signature                                                                        | 3.6 kB  00:00:00 !!!
pgdg12/7/x86_64/signature                                                                        |  198 B  00:00:00
pgdg12/7/x86_64/signature                                                                        | 3.6 kB  00:00:00 !!!
pgdg13/7/x86_64/signature                                                                        |  198 B  00:00:00
pgdg13/7/x86_64/signature                                                                        | 3.6 kB  00:00:00 !!!
pgdg14/7/x86_64/signature                                                                        |  198 B  00:00:00
pgdg14/7/x86_64/signature                                                                        | 3.6 kB  00:00:00 !!!
updates                                                                                          | 2.9 kB  00:00:00
Metadata Cache Created
  • Install PostgreSQL 14
yum install -y postgresql14 postgresql14-server
  • Initialize PostgreSQL14 database instance
[root@PostgreSQL-centos admin]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK
  • Enable and start PostgreSQL14 service
systemctl enable postgresql-14.service
systemctl start postgresql-14.service
  • Connect sebagai user postgres dan set admin password
[root@PostgreSQL-centos admin]# su - postgres
-bash-4.2$ psql
psql (14.0)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'P@ssw0rd!';
ALTER ROLE
postgres=# \q
-bash-4.2$ exit
logout

PostgreSQL Remote Access


  • Konfigurasi PostgreSQL14 Remote Access
nano /var/lib/pgsql/14/data/postgresql.conf
  • Jika ingin database postgreSQL14 diaskes dari jaringan cari baris berikut listen_addresses = ‘localhost’ , ganti localhost dengan *
  • postgreSQL menggunakan port 5432/tcp
  • Allow network clients untuk akses PostgreSQL14 service pada file pg_hba.conf , tambahkan baris berikut pada bagian paling bawah, ganti ip address sesuaikan
nano /var/lib/pgsql/14/data/pg_hba.conf
# Add client to accesss postgreSQL
host    all             all             103.157.27.0/24         md5
  • Restart PostgreSQL14 service
systemctl restart postgresql-14.service

Install pgAdmin 4


  • Install pgAdmin 4 yum repository
sudo yum install -y https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
  • Cek repository pgAdmin4
[root@PostgreSQL-centos admin]# cat /etc/yum.repos.d/pgadmin4.repo
[pgAdmin4]
name=pgadmin4
baseurl=https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/redhat/rhel-$releasever-$basearch
enabled=1
repo_gpgcheck=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/PGADMIN_PKG_KEY
  • Install pgAdmin4 web mode (untuk server only)
sudo yum install pgadmin4-web -y
  • Start and enable httpd service
systemctl start httpd && sudo systemctl enable httpd
  • Konfigurasi user dan password pgAdmin4, masukan email dan password untuk akses login ke pgAdmin4
[root@PostgreSQL-centos admin]# sudo /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based platform...
Creating configuration database...
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: [email protected]
Password:
Retype password:
pgAdmin 4 - Application Initialisation
======================================

Creating storage and log directories...
Configuring SELinux...
The Apache web server is running and must be restarted for the pgAdmin 4 installation to complete. Continue (y/n)? y
Apache successfully restarted. You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
  • Tampilan login dan dashboard pgAdmin4. Akses pgAdmin dengan url: http://ip_address_pgadmin4/pgadmin4
  • jika tidak bisa login lakukan ini kembali: sudo /usr/pgadmin4/bin/setup-web.sh
  • Menambahkan server postgreSQL14

herdiana3389

A system administrator with skills in system administration, virtualization, linux, windows, networking, cloud computing, container, etc.