Menu Close

Installing PostgreSQL on Alibaba cloud ECS.

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
root@iZa2d8h3ecx9fc4kxvm9vjZ:~# apt show postgresql
N: Unable to locate package postgresql
N: Unable to locate package postgresql
E: No packages found

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 - | sudo apt-key add -
root@iZa2d8h3ecx9fc4kxvm9vjZ:~# sudo sh -c 'echo "deb `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 bionic InRelease [242 kB]
Get:2 bionic-updates InRelease [88.7 kB]
Get:3 bionic-security InRelease [88.7 kB]
Get:4 bionic/main Sources [829 kB]
Get:5 bionic/universe Sources [9,051 kB]
Get:6 bionic/main i386 Packages [1,007 kB]
Get:7 bionic/main amd64 Packages [1,019 kB]
Get:8 bionic/main Translation-en [516 kB]
Get:9 bionic/universe amd64 Packages [8,570 kB]
Get:10 bionic/universe i386 Packages [8,531 kB]
Get:11 bionic/universe Translation-en [4,941 kB]
Get:12 bionic-updates/main Sources [328 kB]
Get:13 bionic-updates/universe Sources [287 kB]
Get:14 bionic-updates/main amd64 Packages [1,072 kB]
Get:15 bionic-updates/main i386 Packages [741 kB]
Get:16 bionic-updates/main Translation-en [356 kB]
Get:17 bionic-updates/universe amd64 Packages [1,107 kB]
Get:18 bionic-updates/universe i386 Packages [1,028 kB]
Get:19 bionic-updates/universe Translation-en [344 kB]
Get:20 bionic-security/universe Sources [173 kB]
Get:21 bionic-security/main Sources [166 kB]
Get:22 bionic-security/main i386 Packages [536 kB]
Get:23 bionic-security/main amd64 Packages [849 kB]
Get:24 bionic-security/main Translation-en [264 kB]
Get:25 bionic-security/universe amd64 Packages [701 kB]
Get:26 bionic-security/universe i386 Packages [636 kB]
Get:27 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 bionic-updates/main amd64 libpq5 amd64 10.14-0ubuntu0.18.04.1 [107 kB]
Get:2 bionic-updates/main amd64 libxslt1.1 amd64 1.1.29-5ubuntu0.2 [150 kB]
Get:3 bionic-updates/main amd64 postgresql-client-common all 190ubuntu0.1 [29.6 kB]
Get:4 bionic-updates/main amd64 postgresql-client-10 amd64 10.14-0ubuntu0.18.04.1 [941 kB]
Get:5 bionic/main amd64 ssl-cert all 1.0.39 [17.0 kB]
Get:6 bionic-updates/main amd64 postgresql-common all 190ubuntu0.1 [157 kB]
Get:7 bionic-updates/main amd64 postgresql-10 amd64 10.14-0ubuntu0.18.04.1 [3,766 kB]
Get:8 bionic-updates/main amd64 postgresql all 10+190ubuntu0.1 [5,884 B]
Get:9 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 ...
Selecting previously unselected package libpq5:amd64.
(Reading database ... 108723 files and directories currently installed.)
Preparing to unpack .../0-libpq5_10.14-0ubuntu0.18.04.1_amd64.deb ...
Unpacking libpq5:amd64 (10.14-0ubuntu0.18.04.1) ...
Selecting previously unselected package libxslt1.1:amd64.
Preparing to unpack .../1-libxslt1.1_1.1.29-5ubuntu0.2_amd64.deb ...
Unpacking libxslt1.1:amd64 (1.1.29-5ubuntu0.2) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../2-postgresql-client-common_190ubuntu0.1_all.deb ...
Unpacking postgresql-client-common (190ubuntu0.1) ...
Selecting previously unselected package postgresql-client-10.
Preparing to unpack .../3-postgresql-client-10_10.14-0ubuntu0.18.04.1_amd64.deb ...
Unpacking postgresql-client-10 (10.14-0ubuntu0.18.04.1) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../4-ssl-cert_1.0.39_all.deb ...
Unpacking ssl-cert (1.0.39) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../5-postgresql-common_190ubuntu0.1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (190ubuntu0.1) ...
Selecting previously unselected package postgresql-10.
Preparing to unpack .../6-postgresql-10_10.14-0ubuntu0.18.04.1_amd64.deb ...
Unpacking postgresql-10 (10.14-0ubuntu0.18.04.1) ...
Selecting previously unselected package postgresql.
Preparing to unpack .../7-postgresql_10+190ubuntu0.1_all.deb ...
Unpacking postgresql (10+190ubuntu0.1) ...
Selecting previously unselected package postgresql-contrib.
Preparing to unpack .../8-postgresql-contrib_10+190ubuntu0.1_all.deb ...
Unpacking postgresql-contrib (10+190ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-20) ...
Setting up ssl-cert (1.0.39) ...
Setting up libxslt1.1:amd64 (1.1.29-5ubuntu0.2) ...
Setting up libpq5:amd64 (10.14-0ubuntu0.18.04.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...
Setting up postgresql-client-common (190ubuntu0.1) ...
Processing triggers for systemd (237-3ubuntu10.12) ...
Setting up postgresql-common (190ubuntu0.1) ...
Adding user postgres to group ssl-cert
Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages…
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/ → /lib/systemd/system/postgresql.service.
Processing triggers for man-db (2.8.3-2ubuntu0.1) …
Setting up postgresql-client-10 (10.14-0ubuntu0.18.04.1) …
update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-10 (10.14-0ubuntu0.18.04.1) …
Creating new PostgreSQL cluster 10/main …
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main –auth-local peer –auth-host md5
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.

The database cluster will be initialized with locale “en_US.UTF-8”.
The default database encoding has accordingly been set to “UTF8”.
The default text search configuration will be set to “english”.

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default timezone … Asia/Shanghai
selecting dynamic shared memory implementation … posix
creating configuration files … ok
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) …

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 <>
Original-Maintainer: Debian PostgreSQL Maintainers <>
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: 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=# SELECT 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

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:~# sudo su - postgres
postgres@iZa2d8h3ecx9fc4kxvm9vjZ:~$ psql
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1))
Type "help" for help.
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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Note: Only commands & code copying allowed.