Installing and managing a PostgreSQL database

on
postgres_install

Skill - Installing and managing a PostgreSQL database

Table of Contents

In this post we will try to install a PostgreSQL database and explore it's database administration tools.

Why is a database required

  • Database is useful to persist the data/state of an application in the storage for analytics, business logic implementation etc
  • Database makes some common tasks very easy and manageable like data storage with encryption, querying the stored data, creating reports etc
  • Almost all practical software systems will require a database to persist the data

What is a PostgreSQL database

  • PostgreSQL database is a powerful opensource relational database where data can be stored in tables
  • We can achieve very robust data integrity with foreign keys, primary keys, uniqueness constraints, data types, enforcing data size limitations / value ranges, triggers etc

Installing PostgreSQL database

Windows

Linux based systems

  • PostgreSQL is already shipped with many linux distributions like Ubuntu.
  • To check the version of PostgreSQL, run the postgres -V command
  • If you want to install specific versions, the commands for installing are provided in the official postgres website at https://www.postgresql.org/download/

Ubuntu installation commands

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

pgAdmin for database administration

  • The PostgreSQL database administration can be very easily done using the pgAdmin opensource tool
  • It is included with most of the postgreSQL database installations
  • You can easily create/view/update/delete the databases, tables, columns, rows, data, import/export data, run queries etc. using this tool
  • Also it facilitates observability for the database sessions, I/O, transactions, statistics etc via a dashboard screen
    pgAdmin_snap

Access postgreSQL database remotely using the DBeaver tool

DBeaver_demo

  • DBeaver is also a tool like pgAdmin to access postgreSQL or many other types of databases
  • If you manage multiple types of databases you can use DBeaver for managing all databases in one software

Video

The video for this post can be found here

References

Table of Contents

Location: Mumbai, Maharashtra, India

Comments

Post a Comment