Install PostgreSQL 12 on CentOS 8

This post will cover installing PostgreSQL 12 on CentOS 8.

We’ll be using a bash script below, which is commented so you can see the steps.

We’ll install PostgreSQL using the PostgreSQL repository, configure the pg_hba.conf file to secure the instance, and update the postgresql.conf file to allow remote connections and enable SSL.

We will also create a self-signed SSL certificate for the cluster.

As root, save the above script as and make it executable.

Run the script:

Upon completion, the postgres and SSL password will be displayed as below:

The random passwords will also be saved as auth.txt in the root directory.

Tes your installation with su – postgres and then enter the password to start psql:

Looking at the files under /var/lib/pgsql/data, our pg_hba.conf looks like below:

Our postgresql.conf has also been updated both to allow connections as well as enable SSL:




PostgreSQL Auto Increment

This post will demonstrate how to auto increment on a column in PostgreSQL.

In our example we will create a table, Managers.

Our table will have three columns: mgr_id, mgr_name, and mgr_email.

For each insertion of a new Manager entry, we want to auto increment our primary key, mgr_id, by 1.

Step 1: Create a Sequence


Step 2. Create Table and Set the Column Default


We now create the Manager table.

For mgr_id, set as PRIMARY KEY and set the DEFAULT to NEXTVAL(‘mgr_id_seq’) as shown below.

This will increment our sequence by 1 each time a new Manager entry is inserted.

Step 3. Insert Data.


Step 4. Select on Table to View Sequence.


As we an see from above, the increment begins at 1 and increments by 1 by default.


Note: Auto Increment in PhpPgAdmin


Unlike PhpMyAdmin, PhpPgAdmin does not have a handy drop-down to auto increment a column when you create a table.

You can simply use the PhpPgAdmin SQL Editor to create the sequence and table as shown in steps 1 to 3 above.

Alternatively, you can create your sequence using the Sequence node in PhpPgAdmin as shown below:


With your sequence created, you can then set the DEFAULT value for the mgr_id column to nextval(‘mgr_id_seq’) in the table creation GUI as shown below:



More on Sequences in PostgreSQL: