How to install PostgreSQL in Centos8

List out the available streams for the postgresql module using the dnf command:

[root@MaxwellDBA home]# dnf module list postgresql
Repository extras is listed more than once in the configuration
Invalid configuration value: failovermethod=priority in /etc/yum.repos.d/CentOS-epel.repo; Configuration: OptionBinding with id "failovermethod" does not exist
Last metadata expiration check: 0:37:44 ago on Tue 30 Aug 2022 12:23:12 PM CST.
CentOS-8.5.2111 - AppStream - mirrors.aliyun.com
Name                                Stream                          Profiles                                   Summary                                                    
postgresql                          9.6                             client, server [d]                         PostgreSQL server and client module                        
postgresql                          10 [d]                          client, server [d]                         PostgreSQL server and client module                        
postgresql                          12                              client, server [d]                         PostgreSQL server and client module                        
postgresql                          13                              client, server [d]                         PostgreSQL server and client module                        
 
CentOS Linux 8 - AppStream
Name                                Stream                          Profiles                                   Summary                                                    
postgresql                          9.6                             client, server [d]                         PostgreSQL server and client module                        
postgresql                          10 [d]                          client, server [d]                         PostgreSQL server and client module                        
postgresql                          12                              client, server [d]                         PostgreSQL server and client module                        
postgresql                          13                              client, server [d]                         PostgreSQL server and client module                        
 
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

To enable the module stream for Postgres version 12, run the following command:

[root@MaxwellDBA home]# sudo dnf module enable postgresql:12
Repository extras is listed more than once in the configuration
Invalid configuration value: failovermethod=priority in /etc/yum.repos.d/CentOS-epel.repo; Configuration: OptionBinding with id "failovermethod" does not exist
Last metadata expiration check: 0:41:11 ago on Tue 30 Aug 2022 12:23:12 PM CST.
Dependencies resolved.
==========================================================================================================================================================================
 Package                                  Architecture                            Version                                  Repository                                Size
==========================================================================================================================================================================
Enabling module streams:
 postgresql                                                                       12                                                                                     
 
Transaction Summary
==========================================================================================================================================================================
 
Is this ok [y/N]: y
Complete!
[root@MaxwellDBA home]#

After enabling the version 12 module stream, you can install the postgresql-server package to install PostgreSQL 12 and all of its dependencies:

[root@MaxwellDBA home]# sudo dnf install postgresql-server
Repository extras is listed more than once in the configuration
Invalid configuration value: failovermethod=priority in /etc/yum.repos.d/CentOS-epel.repo; Configuration: OptionBinding with id "failovermethod" does not exist
Last metadata expiration check: 0:43:08 ago on Tue 30 Aug 2022 12:23:12 PM CST.
Dependencies resolved.
==========================================================================================================================================================================
 Package                                  Architecture                  Version                                                    Repository                        Size
==========================================================================================================================================================================
Installing:
 postgresql-server                        x86_64                        12.9-1.module_el8.5.0+1061+5aa9de58                        AppStream                        5.6 M
Installing dependencies:
 libpq                                    x86_64                        13.3-1.el8_4                                               AppStream                        197 k
 postgresql                               x86_64                        12.9-1.module_el8.5.0+1061+5aa9de58                        AppStream                        1.5 M
 
Transaction Summary
==========================================================================================================================================================================
Install  3 Packages
 
Total download size: 7.3 M
Installed size: 28 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): libpq-13.3-1.el8_4.x86_64.rpm                                                                                                      1.1 MB/s | 197 kB     00:00    
(2/3): postgresql-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64.rpm                                                                          4.3 MB/s | 1.5 MB     00:00    
(3/3): postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64.rpm                                                                   6.9 MB/s | 5.6 MB     00:00    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                     9.0 MB/s | 7.3 MB     00:00     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                  1/1 
  Installing       : libpq-13.3-1.el8_4.x86_64                                                                                                                        1/3 
  Installing       : postgresql-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64                                                                                            2/3 
  Running scriptlet: postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64                                                                                     3/3 
  Installing       : postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64                                                                                     3/3 
  Running scriptlet: postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64                                                                                     3/3 
[/usr/lib/tmpfiles.d/postgresql.conf:1] Line references path below legacy directory /var/run/, updating /var/run/postgresql → /run/postgresql; please update the tmpfiles.d/ drop-in file accordingly.
 
  Verifying        : libpq-13.3-1.el8_4.x86_64                                                                                                                        1/3 
  Verifying        : postgresql-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64                                                                                            2/3 
  Verifying        : postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64                                                                                     3/3 
 
Installed:
  libpq-13.3-1.el8_4.x86_64          postgresql-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64          postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64         
 
Complete!
[root@MaxwellDBA home]# 

Step 2 — Creating a New PostgreSQL Database Cluster

You have to create a new PostgreSQL database cluster before you can start creating tables and loading them with data. A database cluster is a collection of databases that are managed by a single server instance. Creating a database cluster consists of creating the directories in which the database data will be placed, generating the shared catalog tables, and creating the template1 and postgres databases.

The template1 database is a template of sorts used to create new databases; everything that is stored in template1, even objects you add yourself, will be placed in new databases when they’re created. The postgres database is a default database designed for use by users, utilities, and third-party applications.

The Postgres package we installed in the previous step comes with a handy script called postgresql-setup which helps with low-level database cluster administration. To create a database cluster, run the script using sudo and with the --initdb option:

[root@MaxwellDBA home]# sudo postgresql-setup --initdb
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@MaxwellDBA home]# 

Now start the PostgreSQL service using systemctl:

[root@MaxwellDBA home]# sudo systemctl start postgresql
[root@MaxwellDBA home]# 

Then, use systemctl once more to enable the service to start up whenever the server boots:

[root@MaxwellDBA home]# sudo systemctl enable postgresql
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
[root@MaxwellDBA home]# 

Now that PostgreSQL is up and running, we will go over using roles to learn how Postgres works and how it is different from similar database management systems you may have used in the past.

Step 3 — Using PostgreSQL Roles and Databases

PostgreSQL uses a concept called roles to handle client authentication and authorization. These are in some ways similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term role.

Upon installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

The installation procedure created a user account called postgres that is associated with the default postgres role. In order to use PostgreSQL, you can log in to that account.

There are a few ways to use this account to access the PostgreSQL prompt.

Switching Over to the postgres Account

Switch over to the postgres account on your server by typing:

[root@MaxwellDBA home]# sudo -i -u postgres

You can now access a Postgres prompt immediately by typing:
 

[postgres@MaxwellDBA ~]$ psql
psql (12.9)
Type "help" for help.

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.

Exit out of the PostgreSQL prompt by typing:

postgres=# \q

This will bring you back to the postgres account’s Linux command prompt. Now return to your original account with the following:

[postgres@MaxwellDBA ~]$ exit
logout
[root@MaxwellDBA home]#

Accessing a Postgres Prompt Without Switching Accounts

You can also run commands with the postgres account directly using sudo.

For instance, in the previous example, you were instructed to access the Postgres prompt by first switching to the postgres user and then running psql to open the Postgres prompt. As an alternative, you could do this in one step by running the single command psql as the postgres user with sudo, like this:

[root@MaxwellDBA home]# sudo -u postgres psql
psql (12.9)
Type "help" for help.
 
postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.9 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
(1 row)
 
postgres=# \q

In this step, you used the postgres account to reach the psql prompt. But many use cases require more than one Postgres role. Read on to learn how to configure new roles.

axwellDBA home]# sudo -i -u postgres
[postgres@MaxwellDBA ~]$ createuser --interactive
Enter name of role to add: maxwell
Shall the new role be a superuser? (y/n) y
[postgres@MaxwellDBA ~]$ 

If, instead, you prefer to use sudo for each command without switching from your normal account, type:

sudo -u postgres createuser --interactive

You can get more control by passing some additional flags. Check out the options by looking at the man page for createuser:

man createuser

Step 5 — Creating a New Database

Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.

This means that if the user you created in the last section is called maxwell, that role will attempt to connect to a database which is also called maxwellby default. You can create such a database with the createdb command.

If you are logged in as the postgres account, you would type something like:

[postgres@MaxwellDBA ~]$ createdb maxwelldb
[postgres@MaxwellDBA ~]$ 

If, instead, you prefer to use sudo for each command without switching from your normal account, you would type:

sudo -u postgres createdb maxwelldb

This flexibility provides multiple paths for creating databases as needed.

Now that you’ve created a new database, you will log in to it with your new role.

[root@MaxwellDBA home]# sudo -i -u postgres
[postgres@MaxwellDBA ~]$ createdb maxwell
[postgres@MaxwellDBA ~]$ exit
logout
[root@MaxwellDBA home]# sudo -i -u maxwell
[maxwell@MaxwellDBA ~]$ psql
psql (12.9)
Type "help" for help.
 
maxwell=# 

Once this new account is available, you can either switch over and then connect to the database by first typing:

[root@MaxwellDBA home]# sudo -i -u maxwell
[maxwell@MaxwellDBA ~]$ psql

If you want your user to connect to a different database, you can do so by including the -d flag and specifying the database, like this:

[maxwell@MaxwellDBA ~]$ psql -d maxwelldb
psql (12.9)
Type "help" for help.
 
maxwelldb=# 

Once logged in, you can check your current connection information by typing: 

[maxwell@MaxwellDBA ~]$ psql -d maxwelldb
psql (12.9)
Type "help" for help.
 
maxwelldb=# \conninfo
You are connected to database "maxwelldb" as user "maxwell" via socket in "/var/run/postgresql" at port "5432".
maxwelldb=# 

This is useful if you are connecting to non-default databases or with non-default users.

Having connected to your database, you can now try out creating and deleting tables.

Step 7 — Creating and Deleting Tables

Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

First, create a table to store some data. As an example, you will make a table that describes some playground equipment.

The basic syntax for this command is as follows:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

maxwelldb=# CREATE TABLE playground (
maxwelldb(#     equip_id serial PRIMARY KEY,
maxwelldb(#     type varchar (50) NOT NULL,
maxwelldb(#     color varchar (25) NOT NULL,
maxwelldb(#     location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
maxwelldb(#     install_date date
maxwelldb(# );
CREATE TABLE
maxwelldb=# \d
                   List of relations
 Schema |          Name           |   Type   |  Owner  
--------+-------------------------+----------+---------
 public | playground              | table    | maxwell
 public | playground_equip_id_seq | sequence | maxwell
(2 rows)
 
maxwelldb=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner  
--------+------------+-------+---------
 public | playground | table | maxwell
(1 row)
 
maxwelldb=# 

Step 8 — Adding, Querying, and Deleting Data in a Table

Step 9 — Adding and Deleting Columns from a Table

Step 10 — Updating Data in a Table

maxwelldb=# INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT 0 1
maxwelldb=# INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');
INSERT 0 1
maxwelldb=# select * from playground;
 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)
 
maxwelldb=# DELETE FROM playground WHERE type = 'slide';
DELETE 1
maxwelldb=# select * from playground;
 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)
 
maxwelldb=# select * from playground;
 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)
 
maxwelldb=# ALTER TABLE playground ADD last_maint date;
ALTER TABLE
maxwelldb=# select * from playground;
 equip_id | type  | color  | location  | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   | 
(1 row)
 
maxwelldb=# ALTER TABLE playground DROP last_maint;
ALTER TABLE
                 ^
maxwelldb=# select * from playground;
 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)
 
maxwelldb=# UPDATE playground SET color = 'red' WHERE type = 'swing';
UPDATE 1
maxwelldb=# select * from playground;
 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2018-08-16
(1 row)
 
maxwelldb=#