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.
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.
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.
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.
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.
Migrating data from ApsaraDB RDS PostgreSQL to an on-premises database by using logical backup files is supported.
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.
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.
Running Your Node.js Application on ECS with Systemd or Forever
2,599 posts | 763 followers
FollowAlibaba Clouder - October 29, 2018
Alibaba Clouder - November 18, 2019
Alibaba Clouder - June 10, 2019
Alibaba Clouder - December 26, 2018
Alibaba Clouder - June 4, 2019
Alibaba Clouder - June 10, 2019
2,599 posts | 763 followers
FollowElastic and secure virtual cloud servers to cater all your cloud hosting needs.
Learn MoreMore Posts by Alibaba Clouder