PostgreSQL is a powerful, free, and open-source relational database management system known for its reliability, robustness, extensibility, and high performance. It is mainly used for data storage or data warehouse for different applications.

Use cases for PostgreSQL include: 

  • Online Transactions Processing for both startups and large enterprises.
  • Used by government in Geographical Information Systems (GIS) as it has “PostGIS” extension that processes geometric data in different formats
  • Also used in Mobile and Web for storage
  • Used in linking other data sources like NoSQL types by use of foreign data wrappers
  • Used to store scientific data that involves terabytes of data

For PostgreSQL to perform highly on the above use cases, it has features that make it appropriate. They include;

  • Free, easy to get started and use
  • Table inheritance
  • Case sensitive
  • Locking mechanism
  • Asynchronous replication
  • Supports Multi-version concurrency control (MVCC)
  • User defined data types
  • Compatible with Data Integrity like Primary Keys
  • Compatible with various Programming Languages like JSON
  • Supported in various Operating systems

In this guide, I will show you how to;

  • Install PostgreSQL on Kubuntu | KDE Neon
  • Basic configurations

Install PostgreSQL on Kubuntu | KDE Neon

First, we update the package index by using

### Kubuntu ###
sudo apt update && sudo apt upgrade -y

### KDE Neon ###
sudo apt update && sudo pkcon update -y

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

Then import the repository signing key;

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

The Output is as below.

Then update the package lists and install PostgreSQL 

sudo apt update
sudo apt -y install postgresql

If you want a specific version add the version. Example postgresql-13 instead of postgresql alone. For now, I installed the latest version 14.

Reading package lists... Done
Building dependency tree       
Reading state information... Done
Starting pkgProblemResolver with broken count: 0
Starting 2 pkgProblemResolver with broken count: 0
Done
The following packages were automatically installed and are no longer required:
  libfwupdplugin1 linux-headers-5.11.0-40-generic linux-headers-5.13.0-27-generic linux-headers-5.4.0-90
  linux-headers-5.4.0-90-generic linux-hwe-5.11-headers-5.11.0-40 linux-hwe-5.13-headers-5.13.0-27
  linux-image-5.11.0-40-generic linux-image-5.13.0-27-generic linux-image-5.4.0-90-generic
  linux-image-5.4.0-96-generic linux-modules-5.11.0-40-generic linux-modules-5.13.0-27-generic
  linux-modules-5.4.0-90-generic linux-modules-5.4.0-96-generic linux-modules-extra-5.11.0-40-generic
  linux-modules-extra-5.13.0-27-generic linux-modules-extra-5.4.0-90-generic linux-modules-extra-5.4.0-96-generic
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm9 libpq5 libtypes-serialiser-perl pgdg-keyring
  postgresql-14 postgresql-client-14 postgresql-client-common postgresql-common sysstat
Suggested packages:
  postgresql-doc postgresql-doc-14 isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm9 libpq5 libtypes-serialiser-perl pgdg-keyring postgresql
  postgresql-14 postgresql-client-14 postgresql-client-common postgresql-common sysstat

To check for the version of postgres installed use,

sudo -u postgres psql

This will log you into the postgres account.

psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

postgres=#

Then type SELECT version(); to get the version running.

                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

The version will be displayed. Press q to exit.

Connect to PostgreSQL

With the installation a default user account postgres is created. To switch to this account and login, use;

sudo -i -u postgres psql

To check the connection information, use,

\conninfo

It will show you the database you are connected to and the user

To exit out of the PostgreSQL prompt, use

\q

The output is as follows

psql (14.2 (Ubuntu 14.2-1.pgdg20.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=# \q

Create a New Role

To create a new role using the postgres account type

createuser --interactive

If you prefer using the sudo command you will type

sudo -u postgres createuser --interactive

It will prompt you with questions like the name of the new role as shown below. Execute the script as you would prefer

Set Superuser Password

By default, the postgres account does not have a password. To set the password for postgres account, Login to the account using

sudo -u postgres psql

Then use the following command to change the password. You can put the password of your choice apart from the one I have used.

ALTER USER postgres PASSWORD 'myPassword';

Change the password to what you want to set as. then use \q to exit the account.

psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

postgres=# ALTER USER postgres PASSWORD 'myPassword';
ALTER ROLE

Creating a Database

By default, once a role is created Postgres will create a database with the same name of the role once you log in as that user. But to create a database without login in, use the following command while still logged in as the postgres account. It is advised to use a name similar to the account.

createdb ann

If again you are not logged in as the postgres account use the sudo command as follows

sudo -u postgres createdb ann

Whichever way you use, the database will be created. As you can see from below, I used the sudo command to create the database and if I was to try and create the same database with the same name, I get an error that the database exists.

Connect to Postgres Prompt as the New User

sudo -u ann psql

Then check the info using

\conninfo

The output is as below

[email protected]:~$ sudo -u ann psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

ann=# \conninfo
You are connected to database "ann" as user "ann" via socket in "/var/run/postgresql" at port "5432".

Change Default Port

First, find the config file by using the following command

sudo -u postgres psql
SHOW config_file;

The config file path has the version so take note of that.

psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

postgres=# SHOW config_file;
               config_file               
-----------------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)

Open the config file using any editor. I will use nano

sudo nano /etc/postgresql/*/main/postgresql.conf

Chane the ‘listen-addresses’ to ‘*’ to allow every IP address to be connected to the database server.

We shall edit the pg_hba.conf file to allow users to be connected to the database

sudo nano /etc/postgresql/*/main/pg_hba.conf

Then append the following data.

# TYPE  DATABASE	USER	ADDRESS   	METHOD
host    all     	all     0.0.0.0/0       md5
host    all             all     :/0             md5

Save your work and exit the file.

Restart for changes to take effect.

sudo service postgresql restart

Change Default PostgreSQL Data Directory

Let us check the data directory first

sudo -u postgres psql
SHOW data_directory;

This will display the current data directory

       data_directory        
-----------------------------
 /var/lib/postgresql/14/main
(1 row)

Type \q to exit the account.

Copy the data using the following command to the new directory.

sudo rsync -av /var/lib/postgresql /mnt/vol_new/

It will copy instantly

Then change the directory from the conf. file

sudo nano /etc/postgresql/14/main/postgresql.conf

Change the data_directory to the new one depending on the directory you chose. Mine is /mnt/vol_new/.

Afterward, restart the Postgres service.

sudo systemctl restart postgresql

Then verify the new directory

sudo -u postgres psql
SHOW data_directory;

Sample output;

psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

postgres=# SHOW data_directory;
         data_directory          
---------------------------------
 /mnt/vol_new/postgresql/14/main
(1 row)

Conclusion

In this guide, you have learned how to install PostgreSQL on Kubuntu|KDE Neon systems. You have also learned how to create a role and log in using the new role. We have set the Password for the user. We have allowed all IP addresses to connect to the server. We have also changed the default data directory.

Similar guides:

RELATED ARTICLESMORE FROM AUTHOR

LEAVE A REPLY

Please enter your comment!
Please enter your name here