jiten
Intern
Intern
  • UID10218
  • Fans0
  • Follows0
  • Posts4
Reads:203Replies:0

[PostgreSQL Development]How to Install PostgreSQL on Alibaba cloud ECS.

Created#
More Posted time:Sep 21, 2020 15:49 PM

Alibaba cloud is one of the top cloud service provider in the world right now. They provide many cloud services in IaaS, Paas, SaaS, etc. In this post I am using one of their Elastic Compute Service (ECS), deployed in Mumbai data center, will install the PostgreSQL on top of that and create a dummy database in it. Lets start then,


I am connecting from puTTY to my ECS, which is Ubuntu Linux 18.04.




Will check if PostgreSQL is already installed or not.


root@iZa2d8h3ecx9fc4kxvm9vjZ:~# cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.2 LTS"
root@iZa2d8h3ecx9fc4kxvm9vjZ:~# apt show postgresql
N: Unable to locate package postgresql
N: Unable to locate package postgresql
E: No packages found
root@iZa2d8h3ecx9fc4kxvm9vjZ:~#

Its not installed in my case, so will add PostgreSQL repository after Importing the GPG repository key first.

root@iZa2d8h3ecx9fc4kxvm9vjZ:~# sudo apt-get install wget ca-certificates
Reading package lists... Done
Building dependency tree Reading state information... Done
wget is already the newest version (1.19.4-1ubuntu2.1).
ca-certificates is already the newest version (20180409).
The following packages were automatically installed and are no longer required: libopts25 sntp
Use 'sudo apt autoremove' to remove them.
0 upgraded,
0 newly installed,
0 to remove and 0 not upgraded.
root@iZa2d8h3ecx9fc4kxvm9vjZ:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - OK
root@iZa2d8h3ecx9fc4kxvm9vjZ:~# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'


Now lets update the packages lists in the repository and install PostgreSQL after that.

root@iZa2d8h3ecx9fc4kxvm9vjZ:~# sudo apt-get update
Get:1 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic InRelease [242 kB]
Get:2 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates InRelease [88.7 kB] .
.
.

.
Get:26 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security/universe i386 Packages [636 kB]

Get:27 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security/universe Translation-en [232 kB]
Fetched 43.7 MB in 9s (5,020 kB/s)
Reading package lists... Done


root@iZa2d8h3ecx9fc4kxvm9vjZ:~# sudo apt-get -y install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree Reading state information... Done
The following packages were automatically installed and are no longer required: libopts25 sntp
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libpq5 libxslt1.1 postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common ssl-cert
Suggested packages:
 postgresql-doc locales-all postgresql-doc-10 libjson-perl openssl-blacklist
The following NEW packages will be installed:
libpq5 libxslt1.1 postgresql postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common postgresql-contrib ssl-cert 0 upgraded, 9 newly installed, 0 to remove and 244 not upgraded.
Need to get 5,180 kB of archives.
After this operation, 20.2 MB of additional disk space will be used.
Get:1 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 libpq5 amd64 10.14-0ubuntu0.18.04.1 [107 kB]
Get:2 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 libxslt1.1 amd64 1.1.29-5ubuntu0.2 [150 kB] .
.
.
.
Get:8 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 postgresql all 10+190ubuntu0.1 [5,884 B]

Get:9 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 postgresql-contrib all 10+190ubuntu0.1 [5,896 B]
Fetched 5,180 kB in 2s (3,020 kB/s) Preconfiguring packages ... .
.
.
.
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.12) ...
Processing triggers for ureadahead (0.100.0-20) ...
root@iZa2d8h3ecx9fc4kxvm9vjZ:~#

After installation just verify the software.

root@iZa2d8h3ecx9fc4kxvm9vjZ:~# apt show postgresql
Package: postgresql
Version: 10+190ubuntu0.1
Priority: optional
Section: database
Source: postgresql-common (190ubuntu0.1)
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Debian PostgreSQL Maintainers <pkg-postgresql-public@lists.alioth.debian.org>
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 63.5 kB Depends: postgresql-10
Suggests: postgresql-doc
Task: postgresql-server
Supported: 5y
Download-Size: 5,884 B
APT-Manual-Installed: yes
APT-Sources: http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 Packages
Description: object-relational SQL database (supported version)
  This metapackage always depends on the currently supported PostgreSQL database server version.
  .
PostgreSQL is a fully featured object-relational database management system.
It supports a large part of the SQL standard and is designed to be extensible by
users in many aspects. Some of the features are: ACID transactions, foreign keys,
 views, sequences, subqueries, triggers, user-defined types and functions, outer joins,
multiversion concurrency control. Graphical user interfaces and bindings for many
programming languages are available as well.


N: There is 1 additional record. Please use the '-a' switch to see it


By default, PostgresSQL creates a user ‘postgres’ with the role ‘postgres’. It also creates a system account with the same name ‘postgres’. You can connect to Postgres server, log in to your system as user postgres and connect the database. I just executed few commands to check the version PostgreSQL.



root@iZa2d8h3ecx9fc4kxvm9vjZ:~# sudo su - postgres
postgres@iZa2d8h3ecx9fc4kxvm9vjZ:~$ id
uid=109(postgres) gid=119(postgres) groups=119(postgres),118(ssl-cert)
postgres@iZa2d8h3ecx9fc4kxvm9vjZ:~$ psql -V
psql (PostgreSQL) 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1)
postgres@iZa2d8h3ecx9fc4kxvm9vjZ:~$ psql
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1))
Type "help" for help.


postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# postgres=# SELECT version();
version

--------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)
postgres-# \q
postgres@iZa2d8h3ecx9fc4kxvm9vjZ:~$

So postgres user is for admin use, so will configure PostgreSQL to make is accessible by normal users. I am creating a new user is new user in OS, and proceed to create  a role in postgres to grant the user to create a DB.

root@iZa2d8h3ecx9fc4kxvm9vjZ:~# adduser jiten
Adding user `jiten' ...
Adding new group `jiten' (1000) ...
Adding new user `jiten' (1000) with group `jiten' ...
Creating home directory `/home/jiten' ...
Copying files from `/etc/skel' ...
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
Changing the user information for jiten
Enter the new value, or press ENTER for the default
Full Name []: jitendra
Room Number []:
Work Phone []:
Home Phone []:
Other []:
Is the information correct? [Y/n] Y
root@iZa2d8h3ecx9fc4kxvm9vjZ:~#
root@iZa2d8h3ecx9fc4kxvm9vjZ:~# sudo su - postgres
postgres@iZa2d8h3ecx9fc4kxvm9vjZ:~$ psql psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1))
Type "help" for help.
postgres=# CREATE ROLE jiten WITH LOGIN CREATEDB ENCRYPTED PASSWORD 'jitenspwd';
CREATE ROLE postgres=# \q
postgres@iZa2d8h3ecx9fc4kxvm9vjZ:~# exit
root@iZa2d8h3ecx9fc4kxvm9vjZ:~# su - jiten
jiten@iZa2d8h3ecx9fc4kxvm9vjZ:~$ createdb j_db

I can check the newly created DB using my admin login


root@iZa2d8h3ecx9fc4kxvm9vjZ:~# sudo su - postgres
postgres@iZa2d8h3ecx9fc4kxvm9vjZ:~$ psql
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1))
Type "help" for help.


postgres-# \list
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------------------------+----------+-------------+----------+--------+-----------------------
j_db | jiten | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres |
postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres
(4 rows)
postgres-# \q


That's all for now, you can also install pgAdmin4 for GUI based administration of PostgreSQL. Maybe will share a post on that soon. Until then,


~thank you for reading
Guest