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:3 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security InRelease [88.7 kB] Get:4 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/main Sources [829 kB] Get:5 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/universe Sources [9,051 kB] Get:6 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/main i386 Packages [1,007 kB] Get:7 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/main amd64 Packages [1,019 kB] Get:8 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/main Translation-en [516 kB] Get:9 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/universe amd64 Packages [8,570 kB] Get:10 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/universe i386 Packages [8,531 kB] Get:11 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/universe Translation-en [4,941 kB] Get:12 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main Sources [328 kB] Get:13 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/universe Sources [287 kB] Get:14 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 Packages [1,072 kB] Get:15 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main i386 Packages [741 kB] Get:16 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main Translation-en [356 kB] Get:17 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/universe amd64 Packages [1,107 kB] Get:18 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/universe i386 Packages [1,028 kB] Get:19 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/universe Translation-en [344 kB] Get:20 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security/universe Sources [173 kB] Get:21 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security/main Sources [166 kB] Get:22 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security/main i386 Packages [536 kB] Get:23 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security/main amd64 Packages [849 kB] Get:24 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security/main Translation-en [264 kB] Get:25 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-security/universe amd64 Packages [701 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:3 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 postgresql-client-common all 190ubuntu0.1 [29.6 kB] Get:4 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 postgresql-client-10 amd64 10.14-0ubuntu0.18.04.1 [941 kB] Get:5 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic/main amd64 ssl-cert all 1.0.39 [17.0 kB] Get:6 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 postgresql-common all 190ubuntu0.1 [157 kB] Get:7 http://ap-south-1.mirrors.cloud.aliyuncs.com/ubuntu bionic-updates/main amd64 postgresql-10 amd64 10.14-0ubuntu0.18.04.1 [3,766 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 ... 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/multi-user.target.wants/postgresql.service → /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) … 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
Pingback:How to install PostgreSQL 13 in Oracle Linux – Digging Data Deliberately 🛢️☁️🚀