In this article, you will learned how to install PostgreSQL on Ubuntu Server 18. #centlinux #ubuntu #postgres
Table of Contents
What is PostgreSQL?
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.
What is pgAdmin4?
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.
Have a look at our previous article, if you intend to install PostgreSQL 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 (PAID LINK) by Packt Publishing. It contains over 175 recipes for database administrators to manage enterprise databases.
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
Update Ubuntu 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 updated 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
Install PostgreSQL APT Repository:
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'
Install PostgreSQL on Ubuntu:
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
Install pgAdmin4 on Ubuntu:
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.
Enter an email address to create a user account for pgAdmin web interface. Press Ok button.
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.
Login using email/password that we have created during pgAdmin 4 installation.
Add 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.
Click on Save to add local postgreSQL server.
We have successfully installed PostgreSQL and pgAdmin 4 on Ubuntu Server 18.04 LTS.
Conclusion – Install PostgreSQL on Ubuntu Server:
In this article, you have learned how to install PostgreSQL on Ubuntu Server.
