PostgreSQL and pgAdmin Installation
This blog post describes the steps to install PostgreSQL and pgAdmin. We will install PostgreSQL version 17 running on a RedHat Enterprise Linux 8.10 VM and pgAdmin version 4 running on Windows 10 and on RHEL 8. The installation is very simple and straightforward.
Table of Contents
Preparation
We need a Linux VM and a Windows 10 VM as described in the links. Download the pgAdmin installer file pgadmin4-8.14-x64.exe should from here.
Installation and setup of the PostgreSQL 17 server
We create the commands to install PostgreSQL 17 on RHEL 8.10 on this page. Here they are:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql17-server
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable postgresql-17
sudo systemctl start postgresql-17
echo 'postgres:changeme'|chpasswd
usermod -a -G vboxsf postgres
echo 'MANPATH=/usr/pgsql-17/share/man:$MANPATH' >> ~/.bash_profile
echo 'export MANPATH' >> ~/.bash_profile
echo "alias plog='tail -n 100 -F \`ls -tr /var/lib/pgsql/17/data/log/*log|tail -1\`'" >> ~/.bash_profile
echo "alias pcd='cd /var/lib/pgsql/17/data'" >> ~/.bash_profile
echo "alias sq=\"read -p 'Enter SQL statement: ' a; su - postgres -c \\\"psql -c '\\\$a'\\\"\"" >> ~/.bash_profile
source ~/.bash_profile
echo 'MANPATH=/usr/pgsql-17/share/man:$MANPATH' >> ~postgres/.bash_profile
echo 'export MANPATH' >> ~postgres/.bash_profile
echo 'PATH=/usr/pgsql-17/bin:$PATH' >> ~postgres/.bash_profile
echo 'export PATH' >> ~postgres/.bash_profile
echo "alias plog='tail -n 100 -F \`ls -tr /var/lib/pgsql/17/data/log/*log|tail -1\`'" >> ~postgres/.bash_profile
echo "alias pcd='cd /var/lib/pgsql/17/data'" >> ~postgres/.bash_profile
We run these commands and get the following output:
[root@lin2 ~]# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Updating Subscription Management repositories.
Red Hat Enterprise Linux 8 for x86_64 - AppStream (RPMs) 6.8 MB/s | 69 MB 00:10
Last metadata expiration check: 0:00:08 ago on Mon 20 Jan 2025 05:36:22 PM CET.
pgdg-redhat-repo-latest.noarch.rpm 24 kB/s | 15 kB 00:00
Dependencies resolved.
===================================================================================================================================================================
Package Architecture Version Repository Size
===================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-45PGDG @commandline 15 k
Transaction Summary
===================================================================================================================================================================
Install 1 Package
Total size: 15 k
Installed size: 17 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : pgdg-redhat-repo-42.0-45PGDG.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-45PGDG.noarch 1/1
Installed products updated.
Installed:
pgdg-redhat-repo-42.0-45PGDG.noarch
Complete!
[root@lin2 ~]# sudo dnf -qy module disable postgresql
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
[root@lin2 ~]# sudo dnf install -y postgresql17-server
Updating Subscription Management repositories.
Last metadata expiration check: 0:00:22 ago on Mon 20 Jan 2025 05:36:59 PM CET.
Dependencies resolved.
===================================================================================================================================================================
Package Architecture Version Repository Size
===================================================================================================================================================================
Installing:
postgresql17-server x86_64 17.2-1PGDG.rhel8 pgdg17 7.0 M
Installing dependencies:
postgresql17 x86_64 17.2-1PGDG.rhel8 pgdg17 2.1 M
postgresql17-libs x86_64 17.2-1PGDG.rhel8 pgdg17 334 k
Transaction Summary
===================================================================================================================================================================
Install 3 Packages
Total download size: 9.4 M
Installed size: 41 M
Downloading Packages:
(1/3): postgresql17-libs-17.2-1PGDG.rhel8.x86_64.rpm 1.0 MB/s | 334 kB 00:00
(2/3): postgresql17-17.2-1PGDG.rhel8.x86_64.rpm 4.2 MB/s | 2.1 MB 00:00
(3/3): postgresql17-server-17.2-1PGDG.rhel8.x86_64.rpm 5.2 MB/s | 7.0 MB 00:01
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 7.0 MB/s | 9.4 MB 00:01
PostgreSQL 17 for RHEL / Rocky / AlmaLinux 8 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql17-libs-17.2-1PGDG.rhel8.x86_64 1/3
Running scriptlet: postgresql17-libs-17.2-1PGDG.rhel8.x86_64 1/3
Installing : postgresql17-17.2-1PGDG.rhel8.x86_64 2/3
Running scriptlet: postgresql17-17.2-1PGDG.rhel8.x86_64 2/3
Running scriptlet: postgresql17-server-17.2-1PGDG.rhel8.x86_64 3/3
Installing : postgresql17-server-17.2-1PGDG.rhel8.x86_64 3/3
Running scriptlet: postgresql17-server-17.2-1PGDG.rhel8.x86_64 3/3
Verifying : postgresql17-17.2-1PGDG.rhel8.x86_64 1/3
Verifying : postgresql17-libs-17.2-1PGDG.rhel8.x86_64 2/3
Verifying : postgresql17-server-17.2-1PGDG.rhel8.x86_64 3/3
Installed products updated.
Installed:
postgresql17-17.2-1PGDG.rhel8.x86_64 postgresql17-libs-17.2-1PGDG.rhel8.x86_64 postgresql17-server-17.2-1PGDG.rhel8.x86_64
Complete!
[root@lin2 ~]# sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
Initializing database ... OK
[root@lin2 ~]# sudo systemctl enable postgresql-17
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-17.service → /usr/lib/systemd/system/postgresql-17.service.
[root@lin2 ~]# sudo systemctl start postgresql-17
[root@lin2 ~]# echo 'MANPATH=/usr/pgsql-17/share/man:$MANPATH' >> ~/.bash_profile
[root@lin2 ~]# echo 'export MANPATH' >> ~/.bash_profile
[root@lin2 ~]# echo 'MANPATH=/usr/pgsql-17/share/man:$MANPATH' >> ~postgres/.bash_profile
[root@lin2 ~]# echo 'export MANPATH' >> ~postgres/.bash_profile
The database server installation is now finished. A system service (postgresql-17) has been created by the installation of the rpm package postgresql17-server and a PostgreSQL cluster (aka instance) has been created by running initdb.
Configure the database server to allow connections from other hosts
To be able to connect to the database server from another system (e.g. from pgAdmin 4) we need to perform the following steps.
First we need to configure the database to listen on all configured IP addresses (not only on localhost):
# in postgresql.conf whe change the listening endpoint from localhost to all interfaces (*)
sed -i s/^"#listen_addresses = 'localhost'"/"listen_addresses = '*'"/g /var/lib/pgsql/17/data/postgresql.conf
systemctl restart postgresql-17
Then we need to allow connections from other hosts:
cat << EOF >> /var/lib/pgsql/17/data/pg_hba.conf
# Allow connections from all other IPv4 hosts
host all all 0.0.0.0/0 scram-sha-256
EOF
su - postgres -c 'pg_ctl reload'
Finally we give the postgres database user a password (changeme):
su - postgres -c "psql -c \"alter user postgres password 'changeme'\";"
Starting and stopping of the database server
We can start the database server with:
systemctl start postgresql-17
and we can stop the server with:
systemctl stop postgresql-17
Installation and setup of pgAdmin 4 on Windows
To install the pgAdmin tool we just start the installer and click next…
Once that is done, we can add the new database server by right clicking on Servers > Register > Server…
In the General tab we give the connection a name and on the Connection tab we specify the Host name and password (changeme) for the postgres user.
After that the database server is added to pgAdmin and we can use the tool to administer the database.
Installation and setup of pgAdmin 4 on Linux
This will install the packages needed for the Desktop Mode and the Web Mode of pgAdmin 4:
dnf -y install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
dnf -y install pgadmin4
# to only install the desktop version:
# dnf -y install pgadmin4-desktop
# to only install the web version:
# dnf -y install pgadmin4-web
We start the Desktop Mode of pgAdmin4 by running this command in a non root user X terminal:
/usr/pgadmin4/bin/pgadmin4
We need to configure the Web Mode before we are able to use it:
export PGADMIN_SETUP_EMAIL=admin@admin.de
export PGADMIN_SETUP_PASSWORD=changeme
/usr/pgadmin4/bin/setup-web.sh --yes
Sample Output
[root@lin1 /]# /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: admin@admin.de
Password:
Retype password:
pgAdmin 4 - Application Initialisation
======================================
Creating storage and log directories...
Configuring SELinux...
The Apache web server is not running. We can enable and start the web server for you to finish pgAdmin 4 installation. Continue (y/n)? y
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
Apache successfully enabled.
Apache successfully started.
You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
[root@lin1 /]#You can now access pgAdmin 4 Web Mode by connecting with a browser to http://lin1.fritz.box/pgadmin4 and using admin@admin.de as the username and changeme as the password.
This concludes the PostgreSQL and pgAdmin Installation. Have fun!
Deinstallation
Deinstallation of the PostgreSQL server
We can remove the PostgreSQL server software with these commands:
systemctl stop postgresql-17.service
dnf -y remove postgresql17-server pgdg-redhat-repo
userdel postgres
cd && rm -rf /var/lib/pgsql/ /var/tmp/arch /var/tmp/arch2 /var/tmp/bb /var/tmp/ib*
dnf -qy module enable postgresql
Deinstallation of pgAdmin 4 on Windows
We can remove pgAdmin 4 as usual with “Apps & features” Window or by running
"C:\Program Files\pgAdmin 4\unins000.exe" /SILENT
rem if you would to remove config data of pgAdmin (e.g. connection information/passwords) too, these folders need to be removed:
rmdir /S /Q "%APPDATA%\pgAdmin"
rmdir /S /Q "%APPDATA%\pgadmin4"
in a command prompt with Administrator privileges.
Deinstallation of pgAdmin 4 on Linux
These commands remove pgAdmin 4 from the Linux server:
dnf -y remove pgadmin4 pgadmin4-desktop pgadmin4-web pgadmin4-server pgadmin4-redhat-repo
Leave a Reply