Latest

Wednesday, 4 March 2020

Install PostgreSQL and pgAdmin on Ubuntu Server 18

Install PostgreSQL and pgAdmin on Ubuntu Server 18

PostgreSQL (or Postgres) is a RDBMS (relational database management system) that emphasizes on extensibility and technical standards compliance. PostgreSQL is free and open source and distributed under PostgreSQL (permissive) License.

pgAdmin 4 is web-based interface for database administration of PostgreSQL servers. pgAdmin is the most popular and feature enrich web interface for PostgreSQL. pgAdmin is also free and open source.

In this article, you will learn how to install PostgreSQL and pgAdmin 4 on Ubuntu Server 18.04 LTS.

Have a look at our previous article, if you intend to install PostgreSQL and pgAdmin on CentOS 7.

This article focuses on installation of PostgreSQL and pgAdmin on Ubuntu Server. However, if you want to learn about database administration of PostgreSQL database servers, we highly recommend you to read PostgreSQL 11 Administration Cookbook by Packt Publishing. It contains over 175 recipes for database administrators to manage enterprise databases.

 Install PostgreSQL and pgAdmin on Ubuntu Server 18

This Article Provides:

 

Environment Specification:

We are using a minimal Ubuntu Server 18.04 virtual machine with following specification.

  • CPU - 3.4 Ghz (2 cores)
  • Memory - 2 GB
  • Storage - 20 GB
  • Operating System - Ubuntu Server 18.04 LTS
  • Hostname – postgresql-01.centlinux.com
  • IP Address - 192.168.116.218 /24

 

Upgrading Ubuntu Server Packages:

Connect with postgresql-01.centlinux.com as a privileged user by using a ssh tool.

Check the apt repository and obtain the list of packages that can be upgraded on this Ubuntu Server.

$ sudo apt update
...
1 package can be upgraded. Run 'apt list --upgradable' to see it.

Upgrade all available packages as follows.

$ sudo apt upgrade -y

 

Installing PostgreSQL APT Repository on Ubuntu Server:

Download and import GPG Key of PostgreSQL apt repository.

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK

Add PostgreSQL apt repository by creating a repository source file.

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

 

Installing PostgreSQL on Ubuntu Server 18.04 LTS:

We are installing PostgreSQL and relevant packages using apt command.

$ sudo apt -y install postgresql postgresql-contrib
[sudo] password for ahmer:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following package was automatically installed and is no longer required:
  libdumbnet1
Use 'sudo apt autoremove' to remove it.
The following additional packages will be installed:
  libpq5 libsensors4 postgresql-10 postgresql-client-10
  postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
  lm-sensors postgresql-doc locales-all postgresql-doc-10 libjson-perl
  openssl-blacklist isag
The following NEW packages will be installed:
  libpq5 libsensors4 postgresql postgresql-10 postgresql-client-10
  postgresql-client-common postgresql-common postgresql-contrib ssl-cert
  sysstat
0 upgraded, 10 newly installed, 0 to remove and 0 not upgraded.
Need to get 28.8 kB/5,344 kB of archives.
After this operation, 21.1 MB of additional disk space will be used.
...
Setting up ssl-cert (1.0.39) ...
Setting up libpq5:amd64 (10.12-0ubuntu0.18.04.1) ...
Setting up postgresql-client-common (190ubuntu0.1) ...
Setting up postgresql-common (190ubuntu0.1) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service â /lib/systemd/system/postgresql.service.
Setting up libsensors4:amd64 (1:3.4.0-4) ...
Setting up postgresql-client-10 (10.12-0ubuntu0.18.04.1) ...
update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up sysstat (11.6.1-1ubuntu0.1) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service â /lib/systemd/system/sysstat.service.
Setting up postgresql-10 (10.12-0ubuntu0.18.04.1) ...
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5
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".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (10+190ubuntu0.1) ...
Setting up postgresql-contrib (10+190ubuntu0.1) ...
Processing triggers for systemd (237-3ubuntu10.39) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...

PostgreSQL installation process creates a default OS user postgres to own PostgreSQL files and processes.

Switch to postgres user.

$ sudo su - postgres

Connect to PostgreSQL by using psql command and set the password for postgres (database user).

$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# ALTER USER postgres PASSWORD '123';
ALTER ROLE
postgres=# \q
postgres@ubuntu-server:~$ exit
logout

 

Installing pgAdmin 4 on Ubuntu Server 18.04 LTS:

PGDG repository also provides pgAdmin and relevant packages. Therefore, we can easily install it by using an apt command.

But first, we have to update the packages on our Ubuntu Server.

$ sudo apt update
...
7 packages can be upgraded. Run 'apt list --upgradable' to see them.

Upgrade available packages.

$ sudo apt upgrade -y
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
The following package was automatically installed and is no longer required:
  libdumbnet1
Use 'sudo apt autoremove' to remove it.
The following NEW packages will be installed:
  libllvm6.0 pgdg-keyring postgresql-12 postgresql-client-12
The following packages will be upgraded:
  libpq5 postgresql postgresql-10 postgresql-client-10
  postgresql-client-common postgresql-common postgresql-contrib
7 upgraded, 4 newly installed, 0 to remove and 0 not upgraded.
Need to get 37.5 MB of archives.
After this operation, 126 MB of additional disk space will be used.
...
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5
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".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/12/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 12 main start

Ver Cluster Port Status Owner    Data directory              Log file
12  main    5433 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql-contrib (12+213.pgdg18.04+1) ...
Setting up postgresql (12+213.pgdg18.04+1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...
Processing triggers for systemd (237-3ubuntu10.39) ...

Install pgAdmin 4 and relevant packages using apt command.

$ sudo apt install -y pgadmin4 pgadmin4-apache2

The pgAdmin 4 installation process will launch a TUI (Terminal User Interface) based configuration screen.

01-postgresql-pgadmin-ubuntu-configure-email

Enter an email address to create a user account for pgAdmin web interface. Press Ok button.

02-postgresql-pgadmin-ubuntu-configure-password

Set a password for pgAdmin web interface user and press Ok button.

The pgAdmin installation will be continue as follows.

...
apache2_invoke: Enable configuration pgadmin4
NOTE: Configuring authentication for SERVER mode.

pgAdmin 4 - Application Initialisation
======================================

Setting up libpango-1.0-0:amd64 (1.40.14-1ubuntu0.1) ...
...
Processing triggers for libc-bin (2.27-3ubuntu1) ...

pgAdmin 4 has been installed on Ubuntu Server 18.04 LTS.

pgAdmin web application runs at default http service port. Therefore, to access pgAdmin 4 we need to allow http service port in Ubuntu firewall.

$ sudo ufw allow http
Rules updated
Rules updated (v6)

To access pgAdmin 4 web interface, open URL http://postgresql-01.centlinux.com/pgadmin4 in a browser.

03-postgresql-pgadmin-ubuntu-login-page

Login using email/password that we have created during pgAdmin 4 installation.

04-postgresql-pgadmin-ubuntu-dashboard

 

Adding a PostgreSQL Server in pgAdmin 4:

At the pgAdmin 4 dashboard, click on Add New Server to add a PostgreSQL database server in our pgAdmin 4 web interface.

05-postgresql-pgadmin-ubuntu-add-server

06-postgresql-pgadmin-ubuntu-add-server

Click on Save to add local postgreSQL server.

07-postgresql-pgadmin-ubuntu-server-stats

We have successfully installed PostgreSQL and pgAdmin 4 on Ubuntu Server 18.04 LTS.

If you find this article useful? Don't you mind support us by Buy Me A Coffee


No comments:

Post a Comment

© 2022 CentLinux. All Rights Reserved.