PostgreSQL aka Postgres is an open-source object-oriented relational database management system (RDBMS). This post guide you to install PostgreSQL database on Ubuntu 20.04 LTS with pgAdmin4. pgAdmin4 is a web-based administration tool for PostgreSQL. Also we will explore the basic PostgreSQL administration including setting up a new user and database.
. Ubuntu 20.04 LTS Server Installation Guide with Screenshots
Prerequisites
. Running Ubuntu 20.04 LTS instance
. sudo or root privileges.
. Internet access to download packages
Installing PostgreSQL
Step 1: PostgreSQL packages are available on default software repositories. Use the following command to list currently available versions. At the time of writing this post, PostgreSQL 12 version is available on Ubuntu repositories.
Update your system (Click on picture to enlarge it).
$ sudo apt update && sudo apt upgrade
List available PostgreSQL on Ubuntu Repositories (Click on picture to enlarge it).
$ sudo apt list postgresql*
Step 2: Run the following command to install PostgreSQL (Click on picture to enlarge it).
$ sudo apt install postgresql postgresql-contrib
Step 3: We have installed postgresql-contrib package along with postgresql which provides additional features for PostgreSQL database.
postgresql.service is started automatically once the installation is complete. Run the following commands to start|stop|enable|status postgresql.service (Click on picture to enlarge it).
$ systemctl status postgresql.service
$ systemctl status postgresql@12-main.service
$ systemctl start postgresql.service
$ systemctl enable postgresql.service
$ systemctl stop postgresql.service
$ systemctl is-enabled postgresql
Step 4: Open PostgreSQL Server’s listening TCP Port 5432 to any incoming TCP traffic if you have UFW firewall exists.
$ sudo ufw allow from any to any port 5432 proto tcp
Step 5: PostgreSQL is up and running on Ubuntu 20.04 LTS, it creates a user called “postgres” during installation that has postgresql console access. Let’s switch user (su) and log into PostgreSQL console with following command.
Login with user postgres
$ sudo -i -u postgres
Access the PostgreSQL console with command below.
$ psql
List all available database
postgres=# list
Connect to a database
postgres=# c postgres
Quit from PostgreSQL prompt
postgres=# q
Check current connection information like user, database and port number (Click on picture to enlarge it).
postgres=# conninfo
$ sudo apt update && sudo apt upgrade
Use the following command to download and install PostgreSQL Client from terminal after updating the packages above.
$ sudo apt install postgresql-client
We can connect to PostgreSQL server remotely using psql command with hostname or IP Address also provide the correct credentials to the server. Use the following command to connect to the server.
Syntax:
$ psql -h <postgre-server> -U <postgre-user>
Step 7: Set password for user postgres (OS USER) and postgres (PostgreSQL database administrator)
Set password for postgres (OS user password)
$ sudo passwd postgres
Set password for postgres (PostgreSQL database administrator), run the following command from psql prompt (Click on picture to enlarge it).
$ postgres=#password
or
$ postgres=#password postgres
Install pgAdmin4 on Ubuntu 20.04 LTS Linux
pgAadmin4 is a free software project released under the PostgreSQL/Artistic licence. It is a web-based administration tool that simplifies the creation, maintenance and use of database objects which runs on Linux, Unix, Mac OS X and Windows. Follow the steps below to install pgAdmin4 on Ubuntu 20.04 LTS.
Prerequisites:
Prior installation of pgAdmin4, please do the following changes in the “postgresql.conf” and “pg_hba.conf” files accordingly.
Step 1: Configure Remote Connection
PostgreSQL only accepts connections from Localhost. Edit “postgresql.conf” file to enable remote connections. Uncomment “listen_addresses” and specific your IP Address from where you are access PostgreSQL database. In this guide we have added ‘*’ (From Any IP ) for demo.
$ sudo vi /etc/postgresql/12/main/postgresql.conf
Step 2: Next, add the following line to /etc/postgresql/12/main/pg_hba.conf configuration file, which will allow incoming connections to all databases and users. The “md5” option specifies that the users much authenticate with a password. Following command will add below line at the end of “/etc/postgresql/12/main/pg_hba.conf” configuration file.
$ sudo bash -c “echo host all all 0.0.0.0/0 md5 >> /etc/postgresql/12/main/pg_hba.conf”
$ sudo systemctl restart postgresql.service
. Import the pgAdmin4 key by running following command:
$ curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
. Following command to add an external repository to install pgAdmin4:
$ 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’
Step 4: After adding pgAdmin4 repository, use the following command to install pgAdmin4 on Ubuntu 20.04 LTS (Click on picture to enlarge it).
$ sudo apt install pgadmin4-web
Step 5: Once the installation is complete, configure pgAdmin4 by setting the initial pgAdmin user and Apache Web Server with the following command. Enter the email address and password to use for the initial pgAdmin user account. (Click on picture to enlarge it).
$ sudo /usr/pgadmin4/bin/setup-web.sh
Step 6: Access pgAdmin4 Web-interface
Open web browser and use the IP Address/HostName to open pgAdmin4 Web-interface as shows below. Login using Email Address and Password set in earlier step (Click on picture to enlarge it).
http://192.168.56.119/pgadmin4/ [In your case IP Address would be different]
Step 7: After login on pgAdmin dashboard, click on “Add New Server” to add PostgreSQL database server to manage with pgAdmin.
Under “General” tab, provide Name and Description.
Under “Connection” tab, provide PostgreSQL DB Hostname/address, DB User and Password and click on “Save” button.
Step 8: Once the credentials are ok, pgAdmin will connect with PostgreSQL Database Server, pgAdmin will show the database details in the left sidebar.
That’s it! In this guide, we have seen PostgreSQL Installation on Ubuntu 20.04 LTS. Please share it with others and use comment box below to raise a query if any. Also follow us on tecluesdotcom Facebook Official Page.