Setting up a PostgreSQL Database on Ubuntu

In this guide, we will install and set up a PostgreSQL database on an Ubuntu instance using Alibaba Cloud Elastic Compute Service (ECS).

After you have an instance installed with Ubuntu OS, follow the steps below

1. SSH into your instance using your key pair for Linux user. For windows, you can use SSH client such as putty to connect to your instance.

2. We have to update & upgrade our Ubuntu to latest packages using the commands below

sudo apt-get update && sudo apt-get upgrade

3. Install PostgreSQL by running the command below

sudo apt-get install postgresql

4. To check the version of PostgreSQL installed run psql -V

5. We are going to edit the PostgreSQL configuration file to change the address.

To edit the configuration file(pg_hba.conf), run sudo vim /etc/postgresql/9.5/main/pg_hba.conf.

9.5 is the version of postgreSQL installed. As at the time of writing this article, 9.5 is the default installation for postgreSQL on ubuntu OS.

6. After opening our pg_hba.conf in vim, you can use any editor of your choice. Update the file which read something like this

by default

# TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

to

# TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 md5

This would enable us connect to our database instance.

7. We need to update the postgresql.conf file to enable remote connection from anywhere(IP address).

To edit postgresql.conf file run sudo vim /etc/postgresql/9.5/main/postgresql.conf. Use :set number to enable the line numbers and look for line 59 which should be like this

#listen_addresses = 'localhost'

Update it to this

listen_addresses = '*'

8. To make an effect on the changes made, we need to restart the PostgreSQL service. We can do that by running the command below

sudo /etc/init.d/postgresql restart

9. Now that our PostgreSQL database is ready. we can log into PostgreSQL and create a username and a database.

sudo - su postgres
psql

The psql command gives us access to the Postgres interactive terminal

10. Create a username by running the command below

CREATE USER ubuntu_user SUPERUSER;

11. We have to create a password for the user with the command below

ALTER USER power_user WITH PASSWORD 'password';

12. To create a database with the user created, run

CREATE DATABASE mydatabase WITH OWNER power_user;

13. To exit from psql shell, run q. We can view the content of our database and make changes to it with psql. We can also connect to our database using a database client.

Related Blog Posts

Setting up a PostgreSQL database on an Ubuntu instance

In this guide, we will install and set up a PostgreSQL database on an Ubuntu instance using Alibaba Cloud Elastic Compute Service (ECS).

But before we begin, it is important to know that there are different ways to set up a PostgreSQL database on any cloud provider. For Alibaba Cloud, you can create an instance and set it up manually using Elastic Compute Service (ECS) or by using ApsaraDB RDS.

So before I proceed with the guide, I would point out some few differences between using an ECS or ApsaraDB RDS for your database.

How to Install PostgreSQL for SonarQube on Ubuntu 16.04

In this tutorial, we will be installing and configuring SonarQube on an Alibaba Cloud Elastic Compute Service (ECS) instance with Ubuntu 16.04.

Before proceeding with installation of any kind of package, use the following command to update your Ubuntu system. To execute this command, remember to login from non-root user with sudo privileges.

Related Market Product

Postgres Pro Standard Database 11 (Ubuntu 16)

Postgres Pro Standard Database provides early access to new PostgreSQL features and optimizations.

This image contains Postgres Pro Standard Database 11. Zabbix/Mamonsu monitoring solution is installed and preconfigured. OS fixes for Meltdown and Spectre issues are applied. L1 Terminal Fault vulnerability is eliminated.

Related Documentation

Migrate data to AnalyticDB for PostgreSQL by using the Copy command

You can directly run the \COPY command to import local text file data to AnalyticDB for PostgreSQL. The premise is that the local text file must be formatted, such as using commas (,), colons (:) or special symbols as separators.

Migrate RDS for PostgreSQL data to a local PostgreSQL database

RDS for PostgreSQL supports migration of cloud data to local databases using logical backup files.

Related Products

AnalyticDB for PostgreSQL

ApsaraDB HybridDB for PostgreSQL is an online MPP (Massively Parallel Processing) data warehousing service based on the open source Greenplum Database.

ApsaraDB HybridDB provides online expansion and performance monitoring service to free your team from complicated MPP cluster operations and management (O&M). This enables database administrators, developers and data analysts to focus on upgrading enterprise productivity through SQL development.

ApsaraDB RDS for PostgreSQL

Known as “The world’s most advanced open source database”, PostgreSQL enables OLTP databases that handle enterprise-level SQL statements, supports NoSQL data types such as JSON, XML and hstore, and supports GIS data processing.

Reference

https://www.alibabacloud.com/blog/setting-up-a-postgresql-database-on-ubuntu_594910?spm=a2c41.13018659.0.0

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store