 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 |
|
