How to Install PostgreSQL on Ubuntu 18.04

How to Install PostgreSQL on Ubuntu 18.04

This tutorial provides instructions on how to install the PostgreSQL database software on an Ubuntu 18.04 server.


Contents

  • 1 Prerequisites
  • 2 Install PostgreSQL on Ubuntu
  • 3 PostgreSQL Roles and Databases
  • 4 Create PostgreSQL Database and Role
  • 5 Open PostgreSQL Prompt with New Role
  • 6 Enabling Remote Access to PostgreSQL server
  • 7 Conclusion
Installing PostgreSQL on Ubuntu 18.04

PostgreSQL or Postgres is a popular relational database management system. PostgreSQL is also an opensource and general-purpose database system which provides an implementation of SQL querying language. It has many advanced features like reliable transactions and concurrency without read locks. In this tutorial, you are going to learn how to install PostgreSQL on Ubuntu 18.04 with basic database administration.


Prerequisites

Before you start to install PostgreSQL on Ubuntu 18.04. You must have the root user account credentials of your system.


Install PostgreSQL on Ubuntu

To install PostgreSQL on Ubuntu run following command.

As Ubuntu’s official repository contains a package of PostgreSQL, we will use here, apt package manager to install Postgres on Ubuntu.

First update apt package manager index typing following command:

sudo apt update

Now run following command to install PostgreSQL with -contrib package which adds additional features and functionalities:

sudo apt install postgresql postgresql-contrib

To check the Postgres version and confirm installation run following psql command:

sudo -u postgres psql -c "SELECT version();"

By using psql utility you can interact with the PostgreSQL database easily.


PostgreSQL Roles and Databases

Both user and group are presented as term role in PostgreSQL for handling database access permissions. Ident, Trust, Password and Peer are different methods for authentication in PostgreSQL. Ident method mainly used on TCP/IP connection while Trust method used to connect without password using given criteria in pg_hba.conf file.

You can log in to PostgreSQL using below command:

sudo su - postgres
psql

To exit from here type following in the terminal:

\q

Create PostgreSQL Database and Role

By using createdb method you can create a database in Postgres and by using createuser method you can create a new role.

To create new database called test_db run following command:

sudo su - postgres -c "createdb test_db"

Now create a new role called test_user run below command:

sudo su - postgres -c "createuser test_user"

Now you should grant permission to the user test_user for the newly created database test_db you should execute a query in PostgreSQL Shell to so run following command:

sudo -u postgres psql

Execute the following query to grant permission:

grant all privileges on database test_db to test_user;

Open PostgreSQL Prompt with New Role

To open the PostgreSQl prompt with new role you should have the same Linux user available as PostgreSQL user and database.

To create new Linux user in ubuntu named test_user if the matching user is not available by running following command:

sudo adduser test_user

After creating matching user account on the Linux system, you can connect to the database running following command:

sudo -u sammy psql

Enabling Remote Access to PostgreSQL server

Use the following steps to enable remote access to the PostgreSQL server.

Edit /etc/postgresql/10/main/postgresql.conf file and place listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION SECTION to do so run following command:

sudo nano /etc/postgresql/10/main/postgresql.conf

Update listen_addresses like given below:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
- Connection Settings -

listen_addresses = ''

Now restart the PostgreSQL service with systemctl command:

sudo systemctl restart postgresql

Now confirm and verify the changes typing following in terminal:

ss -nlt | grep 5432

The output should be:

LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:
LISTEN 0 128 [::]:5432 [::]:*

Finally update pg_hba.conf file to configure server remote connections using below examples:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

The user test_user will be able access all databases from all locations using a md5 password

host all test_user 0.0.0.0/0 md5

The user test_user will be able access only the test_db from all locations using a md5 password

host test_db test_user 0.0.0.0/0 md5

The user test_user will be able access all databases from a trusted location (192.168.43.106) without a password

host all test_user 192.168.1.134 trust

Conclusion

You have successfully learned how to install PostgreSQL on Ubuntu 18.04. If you have any queries please don’t forget to comment below.


Learn More

Learn NoSQL Databases from Scratch - Complete MongoDB Bootcamp 2019

Build a Basic App with Spring Boot and JPA using PostgreSQL

SQL vs NoSQL or MySQL vs MongoDB

The Complete Python & PostgreSQL Developer Course

SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL

The Complete SQL Bootcamp

The Complete Oracle SQL Certification Course

SQL for Newbs: Data Analysis for Beginners

Originally published at https://linux4one.com

How To Connect to a Managed Database on Ubuntu 18.04

How To Connect to a Managed Database on Ubuntu 18.04

In this guide, we will go over how to install client programs for a variety of database management systems (DBMSs), including PostgreSQL, MySQL, and Redis, on an Ubuntu 18.04 server. We’ll also explain how to use these programs to connect to a managed database instance.

Originally published by Mark Drake at https://www.digitalocean.com

Introduction

Managed databases have a number of benefits over self-managed databases, including automated updates, simplified scaling, and high availability. If you’re new to working with managed databases, though, the best way to perform certain tasks — like connecting to the database — may not be self-evident.

In this guide, we will go over how to install client programs for a variety of database management systems (DBMSs), including PostgreSQL, MySQL, and Redis, on an Ubuntu 18.04 server. We’ll also explain how to use these programs to connect to a managed database instance.

Prerequisites

To follow the instructions detailed in this guide, you will need:

  • Access to a server running Ubuntu 18.04. This server should have a non-root user with administrative privileges and a firewall configured with ufw.
  • A managed database instance. This tutorial provides instructions on how to connect to a variety of database management systems, specifically PostgreSQL, MySQL, and Redis. To provision a DigitalOcean Managed Database, review our documentation for the DBMS of your choice:
  • PostgreSQL
  • MySQL
  • Redis

Once you have these in place, jump to whichever section aligns with your DBMS.

Connecting to a Managed PostgreSQL Database

To connect to a managed PostgreSQL database, you can use psql, the standard command line client for Postgres. It’s open-source, maintained by the PostgreSQL Development Group, and is usually included when you download the PostgreSQL server. However, you can install psql by itself by installing the postgresql-client package with APT.

If you’ve not done so recently, update your server’s package index:

sudo apt update

Then run the following command to install psql:

sudo apt install postgresql-client

APT will ask you to confirm that you want to install the package. Do so by pressing ENTER.

Following that, you can connect to your managed Postgres database without any need for further configuration. For example, you might invoke psql with the following flags:

  • -U, the PostgreSQL user you want to connect as
  • -h, the managed database’s hostname or IP address
  • -p, the TCP port on which the managed database is listening for connections
  • -d, the specific database you want to connect to
  • -v, short for “variable,” precedes other connection variables, followed by an equal sign (=) and the variables’ values. For example, if you want to validate the database’s CA certificate when you connect, you would include -v sslmode=require in your command
  • -W, which tells psql to prompt you for the PostgreSQL user’s password. Note that you could precede the psql command with PGPASSWORD=password, but it’s generally considered more secure to not include passwords on the command line

With these flags included, the psql command’s syntax would look like this:

psql -U user -h host -p port -d database -v variable=value -W

Alternatively, if your managed database provider offers a uniform resource identifer (URI) for connecting, you might use the following syntax:

psql postgresql://username:[email protected]:port/database?option_1=value&option_n=value

With that, you’re ready to begin using with your managed PostgreSQL instance. For more information on how to interact with PostgreSQL, see our guide on How to Manage an SQL Database.

Connecting to a Managed MySQL Database

To connect to a managed MySQL database, you can use the official MySQL database client. On Ubuntu, this client is typically installed by downloading the mysql-client package through APT. If you’re using the default Ubuntu repositories, though, this will install version 5.7 of the program.

In order to access a DigitalOcean Managed MySQL database, you will need to install version 8.0 or above. To do so, you must first add the MySQL software repository before installing the package.

Note: If you don’t need to install the latest version of mysql-client, you can just update your server’s package index and install mysql-client without adding the MySQL software repository:

sudo apt update
sudo apt install mysql-client

If you aren’t sure whether you need the latest version of mysql-client, you should consult your cloud provider’s managed databases documentation.

Begin by navigating to the MySQL APT Repository page in your web browser. Find the Download button in the lower-right corner and click through to the next page. This page will prompt you to log in or sign up for an Oracle web account. You can skip that and instead look for the link that says No thanks, just start my download. Right-click the link and select Copy Link Address (this option may be worded differently, depending on your browser).

Now you’re ready to download the file. On your server, move to a directory you can write to:

cd /tmp

Download the file using curl, remembering to paste the address you just copied in place of the highlighted portion of the following command. You also need to pass two command line flags to curl. -O instructs curl to output to a file instead of standard output. The L flag makes curl follow HTTP redirects, which is necessary in this case because the address you copied actually redirects to another location before the file downloads:

curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

The file should now be downloaded in your current directory. List the files to make sure:

ls

You will see the filename listed in the output:

Output
mysql-apt-config_0.8.13-1_all.deb 
. . . 

Now you can add the MySQL APT repository to your system’s repository list. The dpkg command is used to install, remove, and inspect .deb software packages. The following command includes the -i flag, indicating that you’d like to install from the specified file:

sudo dpkg -i mysql-apt-config*

During the installation, you’ll be presented with a configuration screen where you can specify which version of MySQL you’d prefer, along with an option to install repositories for other MySQL-related tools. The defaults will add the repository information for the latest stable version of MySQL and nothing else. This is what we want, so use the down arrow to navigate to the Ok menu option and hit ENTER.

Following that, the package will finish adding the repository. Refresh your apt package cache to make the new software packages available:

sudo apt update

Next, you can clean up your system a bit and delete the file you downloaded, as you won’t need it in the future:

rm mysql-apt-config*

Note: If you ever need to update the configuration of these repositories, run the following command to select your new options:

sudo dpkg-reconfigure mysql-apt-config

After selecting your new options, run the following command to refresh your package cache:

sudo apt update

Now that you’ve added the MySQL repositories, you’re ready to install the actual MySQL client software. Do so with the following apt command:

sudo apt install mysql-client

Once that command finishes, check the software version number to ensure that you have the latest release:

mysql --version


Output
mysql Ver 8.0.17-cluster for Linux on x86_64 (MySQL Community Server - GPL) 

After you’ve installed the mysql-client package, you can access your managed database by running the mysql command with the following flags as arguments:

  • -u, the MySQL user you want to connect as
  • -p, tells mysql to prompt for the user’s password. You could include your password directly in the connection command following the -p flag (without a space, as in -ppassword) but, for security reasons, this is generally not recommended
  • -h, the database’s hostname or IP address
  • -P, the TCP port on which MySQL is listening for connections
  • -D, the specific database you want to connect to

Using these flags, the mysql syntax will look like this:

mysql -u user -p -h host -P port -D database

Alternatively, if you have a connection URI you can use to connect, you would use a syntax like this:

mysql mysql://user:[email protected]:port/database?option_1=value&option_n=value

With that, you’re ready to begin using with your managed MySQL instance. For more information on how to interact with MySQL, see our guide on How to Manage an SQL Database.

A Note Regarding Password Authentication in MySQL 8

In MySQL 8.0 and newer, the default authentication plugin is caching_sha2_password. As of this writing, though, PHP does not support caching_sha2_password. If you plan on using your managed MySQL database with an application that uses PHP, such as WordPress or phpMyAdmin, this may lead to issues when the application attempts to connect to the database.

If you have access to the database’s configuration file, you could add a setting to force it to use a PHP-supported authentication plugin — for example, mysql_native_password — by default:

Example MySQL Configuration File

[mysqld] 
default-authentication-plugin=mysql_native_password 

However, some managed database providers — including DigitalOcean — do not make the database configuration file available to end users. In this case, you could connect to the database and run an ALTER USER command for any existing MySQL users which need to connect to the database, but can’t do so with the caching_sha2_password plugin:

ALTER USER user IDENTIFIED WITH mysql_native_password BY 'password';

Of course, you can set new users to authenticate with mysql_native_password by specifying the plugin in their respective CREATE USER statements:

CREATE USER user IDENTIFIED WITH mysql_native_password BY 'password';

If you’re using a DigitalOcean Managed Database, be aware that if you configure a user to authenticate with a plugin other than caching_sha2_password then you won’t be able to see that user’s password in your Cloud Control Panel. For this reason, you should make sure you note down the passwords of any users that authenticate with mysql_native_password or other plugins in a secure location.

Connecting to a Managed Redis Database

When you install Redis locally, it comes with redis-cli, the Redis command line interface. You can use redis-cli to connect to a remote, managed Redis instance, but it doesn’t natively support TLS/SSL connections. For that reason, it’s recommended that you use an alternative Redis client to enable secure connections to Redis.

For DigitalOcean Managed Redis Databases, we recommend that you install Redli, an open-source, interactive Redis terminal. To do so, navigate to the Releases Page on the Redli GitHub project and locate the Assets table for the latest release. As of this writing, this will be version 0.4.4.

There, find the link for the file ending in linux_amd64.tar.gz. This link points to an archive file known as a tarball that, when extracted, will create a few files on your system. Right-click this link and select Copy link address (this option may differ depending on your web browser).

On your server, move to a directory you can write to:

cd /tmp

Then, paste the link into the following wget command, replacing the highlighted URL. This command will download the file to your server:

wget https://github.com/IBM-Cloud/redli/releases/download/v0.4.4/redli_0.4.4_linux_amd64.tar.gz

Once the file has been downloaded to your server, extract the tarball:

tar xvf redli_0.4.4_linux_amd64.tar.gz

This will create the following files on your server:

Output
LICENSE.txt 
README.md 
redli 

The redli file is the Redli binary file. Move it to the /usr/local/bin directory, the location where Ubuntu looks for executable files:

sudo mv redli /usr/local/bin/ 

At this point, you can clean up your system a bit and remove the tarball:

rm redli 0.4.4_linux_amd64.tar.gz

Now you can use Redli to connect to your managed Redis instance. You could do so by running the redli command followed by these flags:

  • -h, the host to connect to. This can either be a hostname or an IP address
  • -a, the password used to authenticate to the Redis instance
  • -p, the port to connect to

With these flags included, the redli syntax would be as follows. Note that this example also includes the --tls option, which allows you to connect to a managed Redis database over TLS/SSL without the need for a tunnel:

redli --tls -h host -a password -p port

One benefit that Redli has over redis-cli is that it understands the rediss protocol, which is used to designate a URI pointing to a Redis database. This allows you to use a connection string to access your database:

redli --tls -u rediss://user:[email protected]:port

Note that this example includes the -u flag, which specifies that the following argument will be a connection URI.

Following that, you can begin interacting with your managed Redis instance.

Conclusion

As a relatively new development in cloud services, many practices that are well known for self-managed databases aren’t widely or comprehensively documented for databases managed by cloud providers. One of the most fundamental of these practices, accessing the database, may not be immediately clear to those new to working with managed databases. Our goal for this tutorial is that it helps get you started as you begin using a managed database for storing data.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

The Complete Oracle SQL Certification Course

An Introduction to Queries in MySQL

ArangoDB Tutorial - Getting Started with ArangoDB

How To Troubleshoot MySQL Queries?

SQL with MySQL - Complete Tutorial for Beginners

How to import CSV file using MySQL?