Setting up a PostgreSQL database on an Ubuntu instance

  1. You have an instance which you can SSH into
  2. You are free to install any software of your choice but you are responsible for the license validity.
  3. You have full control over the configuration of the database, including any performance tweaking you want to do.
  4. You own the responsibility of the DB’s uptime and health as it becomes an app running in the ECS.
  5. You have to do full system administration yourself, including OS maintenance, security, patches, etc.
  1. You don’t need to worry about the Database health or uptime, as everything is already been taken care of by Alibaba Cloud
  2. You can’t SSH into ApsaraDB RDS DB instance but you can connect to the RDS instance via any internet enabled system or application provided that the security group has been enabled
  3. You don’t need to bother about any license validity for the database.
  4. Automatic backup is also taken care of.

Prerequisites:

Procedure

  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
  1. Install PostgreSQL by running the command below
  • sudo apt-get install postgresql
  1. To check the version of PostgreSQL installed run psql -V
  2. We are going to edit the PostgreSQL configuration file to change the address.
  3. To edit the configuration file(pg_hba.conf), run sudo vim /etc/postgresql/9.5/main/pg_hba.conf.
  4. 9.5 is the version of postgreSQL installed. As at the time of writing this article,
  5. 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
  7. 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
  1. 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
  1. This would enable us connect to our database instance.
  2. We need to update the postgresql.conf file to enable remote connection from anywhere(IP address).
  3. 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'
  1. Update it to this
  • listen_addresses = '*'
  1. 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
  1. Now that our PostgreSQL database is ready. we can log into PostgreSQL and create a username and a database.
  • sudo - su postgres psql
  1. The psql command gives us access to the Postgres interactive terminal
  2. Create a username by running the command below
  • CREATE USER ubuntu_user SUPERUSER;
  1. We have to create a password for the user with the command below
  • ALTER USER power_user WITH PASSWORD 'password';
  1. To create a database with the user created, run
  • CREATE DATABASE mydatabase WITH OWNER power_user;
  1. 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.

Connect to database via database client

  1. Click on new favourite to add your connection parameters
  1. Fill in the field box
  1. The field parameters in the screenshot above are explained below
  2. The nickname field can be anything
  3. The Host field contains the public IP address of our ECS instance
  4. The User field contains the username we created earlier which is ubuntu_user
  5. The password field is for the password we created for the ubuntu_user which is password
  6. The database field is for the database we created earlier which is mydatabase.

Conclusion

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Full re-write in 10 days with tachyons and functional CSS: A case study (Part 3)

Python Dictionaries — in and out

The Key Concept of RxJS Error Handling

The best way to organize your iPhone Apps

Beginner’s guide to Flutter with DDD

Undo Git Reset Hard

Playing with Terraform & AWS — Part I

Create High Availability Architecture Using AWS Command Line

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Setup hot standby PostgreSQL

PostgreSQL in a docker container

Use docker-compose to run PostgreSQL

Standalone within Docker container