CentOS 8: Install and Use PostgreSQL

This guide demonstrates how to install and use PostgreSQL, a popular open-source object-relational database management system (RDBMS). PostgreSQL enhances the original Structured Query Language (SQL) specification with many new features but still emphasizes compliance. PostgreSQL transactions are atomic, consistent, isolated, and durable which means the application is ACID-compliant. PostgreSQL ranks as one of the most widely-used database systems and is available for CentOS 8 and most other operating systems.

Before You Begin

Familiarize yourself with our Getting Started with Linode guide and complete the steps for setting your Linode’s hostname and timezone.
This guide will use sudo wherever possible. Complete the sections of our How to Secure Your Server guide to create a standard user account, harden SSH access and remove unnecessary network services. Do not follow the Configure a Firewall section yet as this guide includes firewall rules specifically for an OpenVPN server.
Update your system:
```
 yum update
```

Note
This guide is written for a non-root user. Commands that require elevated privileges are prefixed with sudo. If you’re not familiar with the sudo command, see the Linux Users and Groups guide.

Advantages and Disadvantages of PostgreSQL

Advantages of PostgreSQL:

PostgreSQL delivers many advanced utilities, including materialized views, triggers, and stored procedures.
It is stable and capable of handling a very high workload, such as data warehouses and high-traffic web applications.
PostgreSQL can integrate with clients from different languages, and you can extend it with custom data types and functions.

Disadvantages of PostgreSQL:

PostgreSQL does not always match other database systems in terms of performance due to its focus on compatibility.
Some open-source applications do not support PostgreSQL.
In some cases, PostgreSQL can consume a large amount of disc space. For these situations, we recommend hosting PostgreSQL on a High Memory Linode.

A Summary of the PostgreSQL Installation and Configuration Process

Installing PostgreSQL and performing basic configuration tasks consists of the following high-level steps. Each step is described in detail in the following sections:

Install PostgreSQL.
Secure PostgreSQL and Access the PostgreSQL Shell.
Install the PostgreSQL Administration Package.

Install PostgreSQL

Install the Latest Version of PostgreSQL From the CentOS Packages

If you do not require the absolute latest version of PostgreSQL, you can easily install it using the CentOS package installation program yum. This procedure installs the version of PostgreSQL that is included with the CentOS packages (currently version 10.15).

Use yum to update and upgrade the existing CentOS packages.
```
 sudo yum update
```
Install PostgreSQL and all dependencies, as well the postgresql-contrib component, which provides a set of useful extensions.
```
 sudo yum install postgresql-server postgresql-contrib
```
CentOS does not automatically initialize or enable PostgreSQL. You must first run the following command:
```
 sudo postgresql-setup --initdb
```
Launch PostgreSQL with systemctl.
```
 systemctl start postgresql.service
```
(Optional) You can configure PostgreSQL to automatically launch upon system boot-up with the systemctl enable directive.
```
 sudo systemctl enable postgresql.service
```

Confirm PostgreSQL is running by verifying its status in systemctl.

 systemctl status postgresql.service

CentOS returns the status of the PostgreSQL service which should display as active (running).

postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2021-02-02 14:04:08 UTC; 41s ago

Install PostgreSQL From the PostgreSQL Yum Repository

If you want to choose a more specific version of PostgreSQL, you can install it from the PostgreSQL yum repository. The instructions below show you how to install the most recent stable version of PostgreSQL. At the time of writing this guide, the version of PostgreSQL is 13.1. You can also choose to install an earlier release of PostgreSQL.

Update and upgrade the existing packages.
```
 sudo yum update
```

Install the PostgreSQL repository.

 dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Disable the built-in CentOS module.

 sudo dnf -qy module disable postgresql

Use the new repository to install PostgreSQL along with the optional postgresql-contrib component, which many users find convenient. The following command installs PostgreSQL version 13.
```
 sudo dnf install -y postgresql13-server postgresql13-contrib
```
PostgreSQL is not automatically initialized or enabled on CentOS platforms. To initialize the database, run the following command:
```
 sudo postgresql-13-setup initdb
```
Start PostgreSQL with systemctl.
```
 systemctl start postgresql-13
```
(Optional) If you want PostgreSQL to automatically launch upon system boot-up, register it with systemctl.
```
 systemctl enable postgresql-13
```

Verify if the PostgreSQL service is active with systemctl.

 sudo systemctl status postgresql-13

This returns a summary of the application’s status. PostgreSQL should be listed as active.

postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2021-02-02 15:04:45 UTC; 1min 9s ago
Docs: <https://www.postgresql.org/docs/13/static/>

Note
If you require a specific minor release of PostgreSQL, you must obtain the source code from the PostgreSQL Downloads Page.
Locate the version and file you want, download and transfer it to your host, and extract the files with tar.
Follow the build instructions at Installation Procedure to complete the installation.
This process additionally provides you more control over the installation process.

Secure PostgreSQL and Access the PostgreSQL Shell

You should enhance the security of your PostgreSQL installation before proceeding further. During the installation, PostgreSQL automatically creates a default user account named postgres and grants this user full superadmin privileges. Therefore, it is crucial to apply Linux and database passwords to the account.

Create a strong password for the postgres Linux account and store it in a secure place.
```
 sudo passwd postgres
```
Switch over to this account with the su command.
```
 sudo su - postgres
```
Change the password that is required when the PostgreSQL user (postgres) connects over a network.
```
 psql -c "ALTER USER postgres WITH PASSWORD 'new password'"
```
The -d option allows you to restrict the password to a specific database.
```
 psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword'";
```
Note
This password does not apply when the postgres user logs in via the localhost. This ensures the account has administrative access to PostgreSQL for maintenance jobs and scripting access. Unless you override this setting, PostgreSQL always allows you to log in locally as postgres without any password.

Verify you can communicate with PostgreSQL and you are running the version you expect. The following command queries the PostgreSQL database for the current version.

 sudo -u postgres psql -c "SELECT version();"

PostgreSQL returns information about the release, platform, and architecture.

version

--------------------------------------------------------------------------------------------------------
 PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)

Confirm you can access PostgreSQL by logging into the administrative postgres database.
```
 psql postgres
```
PostgreSQL displays the application version and provides a prompt.
```
psql (13.1)
Type "help" for help.
postgres=#
```
By default, local system users do not require a password to access PostgreSQL. PostgreSQL refers to this as peer authentication. This means it determines the system name of the user and validates it against the database privileges. We recommend you edit the pg_hba.conf file to force local users to provide a valid password. You can determine the location of this file by running the following command within the psql shell.
```
 SHOW hba_file ;
```
PostgreSQL returns the location of the file.
```
hba_file

------------------------------------
/var/lib/pgsql/13/data/pg_hba.conf
(1 row)
```
Exit PostgreSQL using the \q meta-command to return to the Linux shell.
```
 \q
```
Note
PostgreSQL commands starting with a backslash are called meta-commands. These pre-processed commands are helpful for administration and scripting purposes. See the PostgreSQL PSQL Documentation page for more information.

Edit the pg_hba.conf file to require passwords from local users. Locate the line local under Unix domain socket connections only and change the METHOD attribute from peer to md5.

However, we recommend you add a rule to exempt the default postgres user from the local password requirement. This allows for easier non-interactive access to PostgreSQL for maintenance tasks and scripting. Add a new line for the postgres user right above the rule for general local access. The entire section should now look like this.

File: /var/lib/pgsql/13/data/pg_hba.conf

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
...

# Database administrative login by Unix domain socket

local   all             postgres                                peer

# "local" is for Unix domain socket connections only

local   all             all                                     md5
...
    

Caution
If you do not define this exception for the postgres user, you could potentially lock yourself out of the database. We recommend making a backup copy of this file before editing it and taking note of its location.

You must restart PostgreSQL to apply the new access rule.
```
 sudo systemctl restart postgresql-13
```

Note
The postgres Linux user and the postgres database user are two different roles. The Linux account is used to access PostgreSQL, while the postgres PostgreSQL role is allowed to perform administrative tasks inside the database.

Install the PostgreSQL Administration Package

PostgreSQL’s Adminpack module adds several management and administration tools. You can find out more about the module at the PostgreSQL documentation site. The following process installs Adminpack.

Log in to the administrative postgres database in PostgreSQL.
```
 psql postgres
```
Create the extension.
```
 CREATE EXTENSION adminpack;
```

Verify the module is correctly installed with the dx meta-command.

\dx

Adminpack is now listed as one of the modules.

List of installed extensions
   Name    | Version |   Schema   |               Description
-----------+---------+------------+-----------------------------------------
 adminpack | 2.1     | pg_catalog | administrative functions for PostgreSQL
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language

Using PostgreSQL

Like most common RDBMS systems, PostgreSQL uses fairly standard SQL commands. If you are familiar with SQL, the following sections should serve as a quick review. If not, the PostgreSQL documentation offers a good starter tutorial.

Work With PostgreSQL Databases

You must create a database before you can define any tables or add any table rows. A database cluster collects all the databases that the single PostgreSQL server manages, while a database contains one or more tables. The PostgreSQL documentation discusses databases in far greater detail.

As the postgres Linux user, create a test database from the Linux shell using the createdb command.
```
 createdb testdatabase
```
Note
You can assign database ownership to a specific PostgreSQL user when you create it with the -O option, for example, createdb testdatabase -O testuser.
Connect directly to the new database.
```
 psql testdatabase
```
The PostgreSQL prompt now displays the name of the new database.
```
testdatabase=#
```

The \l meta-command lists all of the databases in the cluster.

\l

testdatabasenow appears in the list.

List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 testdatabase | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

While you are logged into PostgreSQL, you can always determine the current database and your PostgreSQL role with the conninfo meta-command.
```
 \conninfo
```
Conninfo returns details about your PostgreSQL connection.
```
You are connected to database "testdatabase" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
```
You can always switch to a different database without logging out of PostgreSQL via the \c meta-command.
```
 \c postgres
```
You can delete a database with the dropdb command, but first ensure you no longer need it. Execute the dropdb command from the Linux shell as the postgres user.
```
 dropdb testdatabase
```

Caution
The dropdb command permanently deletes all of the tables and data inside the database and cannot be undone.

Work With PostgreSQL Tables

A database table refers to an ordered sequence of named columns. Each column is defined together with its data type, for example,int. You can find more details about tables and table creation in the PostgreSQL documentation.

Follow these steps to create a table within the testdatabase database.

Connect to the database.
```
 psql testdatabase
```
Add a new table called customers with the CREATE TABLE SQL command. Provide a list of columns, along with a data type for each column as parameters to the command.
```
 CREATE TABLE customers (customer_id int, first_name varchar(80), last_name varchar(80));
```
List all of the tables in the database using the \dt meta-command.
```
 \dt
```
PostgreSQL returns a table-formatted list showing all of the tables in the database, along with some other information.
```
List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | postgres
(1 row)
```

You can itemize all of the columns in the customers table, including all of the data types, with the \d+ meta-command.

 \d+ customers

Table "public.customers"
   Column    |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
-------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 customer_id | integer               |           |          |         | plain    |              |
 first_name  | character varying(80) |           |          |         | extended |              |
 last_name   | character varying(80) |           |          |         | extended |              |

The DROP TABLE command can be used to delete an existing table.
```
 DROP TABLE customers;
```
Caution
The DROP TABLE operation deletes all of the data in the table. It cannot be undone.

Work With PostgreSQL Columns

It is a good practice to finalize your database schema and table layout before adding any data. However, if you need to add or drop columns, later on, PostgreSQL provides functions to allow you to do so.

To add a column to a pre-existing table, use the ALTER TABLE command. This field is set to empty in all existing rows.
```
 ALTER TABLE customers ADD branch_id int;
```
You can use the ALTER TABLE command along with the DROP keyword to delete a column. PostgreSQL removes this column from all rows and removes the associated data.
```
 ALTER TABLE customers DROP first_name;
```

You can use the d+ meta-command to display the new table structure after your changes.

 \d+ customers

PostgreSQL returns a list of all of the columns that are currently part of the table.

Table "public.customers"
   Column    |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
-------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
customer_id | integer               |           |          |         | plain    |              |
last_name   | character varying(80) |           |          |         | extended |              |
branch_id   | integer               |           |          |         | plain    |              |

Add and Read Data With PostgreSQL

PostgreSQL tables store the data entries as a series of rows. A row can be thought of as a single record within the table with values for each of the columns. Depending on the table’s definition, some columns might not require a value. The INSERT command adds data to a PostgreSQL table on a row-by-row basis. Data can later be retrieved with the SELECT command. For more information about how to add and read data from a table, see the PostgreSQL Documentation.

To add entries into a table, first connect to the database.
```
 psql testdatabase
```
Add two rows to the customers table with the INSERT command. Provide the table name, along with a list of the values to insert, separated by commas. The order of the values and the order of the table columns must match exactly.
```
 INSERT INTO customers VALUES (1, 'John', 'Client');
 INSERT INTO customers VALUES (2, 'Jane', 'Purchaser');
```
Use the SELECT command to retrieve some or all of the contents of a table. In its simplest form, SELECT returns all of the data from a table.
```
 SELECT * FROM customers;
```
PostgreSQL returns a table showing all the columns from all the rows in the table.
```
customer_id | first_name | last_name
-------------+------------+-----------
           1 | John       | Client
           2 | Jane       | Purchaser
(2 rows)
```
Note
The * symbol represents a wild card. In this case, it indicates all columns.
You can restrict a query to return only a subset of the columns by listing the specific columns you want. Only those columns (from all rows) are retrieved.
```
 SELECT last_name FROM customers;
```
PostgreSQL returns a list of the customer last names.
```
last_name

-----------
 Client
 Purchaser
(2 rows)
```
You can use the WHERE keyword along with a search condition to conditionally select rows. PostgreSQL treats the search condition as a Boolean expression and returns each row in which the expression evaluates to true. The following example looks for a particular value in the last_name column. However, PostgreSQL supports more complicated expressions involving multiple comparisons and set operations.
```
 SELECT * FROM customers WHERE last_name = 'Client';
```
PostgreSQL returns the only row matching the search criteria.
```
customer_id | first_name | last_name
-------------+------------+-----------
           1 | John       | Client
(1 row)
```
You can remove rows from a PostgreSQL table with the DELETE command. Conditionally select rows to delete with the WHERE keyword which works the same way it does in the SELECT command. If you use the * symbol, all rows are deleted.
```
 DELETE FROM customers WHERE last_name = 'Purchaser';
```
You can edit rows using the UPDATE command. Specify one or more columns to edit along with the new values. In almost every case, you should include a conditional WHERE clause to designate the rows where the change should be applied.
```
 UPDATE customers SET last_name= 'Buyer' WHERE customer_id = '1';
```
If you select the entire customers table, you can see the updated values. In this case, only one row remains.
```
 SELECT * FROM customers;
```
```
customer_id | first_name | last_name
-------------+------------+-----------
           1 | John       | Buyer
(1 row)
```
Note
A join is a complex query that combines information from two or more tables. To execute a join, indicate a column from each table where the values must match. Every time a match occurs, the join operation returns the pair of relevant rows (one from each table). For instance, if branch_id is the same in a row in the branches table and another in the employees table, both rows are returned. Depending on how your tables are designed, matches might be one-to-one, many-to-one, or many-to-many. PostgreSQL processes the matching rows into one entity and retrieves the requested columns.
You can learn more about Joins on the PostgreSQL site.

Create PostgreSQL Roles (Users) and Groups

If you are the only person using an instance of PostgreSQL, you probably only need the default postgres account. In all other cases, we recommend you create a separate account for each user.

This section explains how to create new PostgreSQL roles (users) and place them into groups.

From the Linux shell as the postgres user, add a new PostgreSQL role with createuser command.
```
 createuser testuser --pwprompt
```
PostgreSQL prompts you for a password for testuser. Choose a high-security password and enter it twice.

Enter the PostgreSQL shell and execute the \du meta-command to verify the role has been created.

\du

PostgreSQL returns an overview of all the roles in the database. Notice that the testuser does not have any attributes (or privileges) yet.

Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  |                                                            | {}

Access the database as testuser in the following manner.
```
 psql testdatabase -U testuser
```
PostgreSQL displays a slightly different prompt this time.
```
testdatabase=>
```
You can add privileges to user accounts with the GRANT command. To grant testuser full access to the customers table, access the database as the postgres user and enter the following command:
```
 GRANT ALL ON customers TO testuser;
```

You can add or remove user permissions with the ALTER ROLE command. The following command allows the postgres user to create databases.

 ALTER ROLE testuser CREATEDB;

The \du command shows the access level for all users.

List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
testuser  | Create DB                                                  | {}

You can create groups in order to grant access to many users at one time. From the Linux shell as the postgres user, run the following command:
```
 createuser testgroup --no-login
```
Log in to the database as the postgres user and assign testuser to the new group.
```
 GRANT testgroup TO testuser;
```

Use the \du meta-command to list all the groups and roles along with the membership details.

\du

List of roles
Role name |                         Attributes                         |  Member of
-----------+------------------------------------------------------------+-------------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
testgroup | Cannot login                                               | {}
testuser  | Create DB                                                  | {testgroup}

You can delete a role or group by executing the dropuser command from the Linux shell.
```
 dropuser testgroup
```

Access PostgreSQL Remotely

There is a security risk in opening up PostgreSQL to listen for remote connections. For more secure remote access to PostgreSQL, use a Graphical User Interface such as pgAdmin.

The following Linode guides provide more information on pgAdmin:

Learn More About PostgreSQL

PostgreSQL can be a complicated application with many options. Our guide only covers the basics, so we recommend you spend more time learning about PostgreSQL.

The PostgreSQL documentation is complete and comprehensive and includes an introductory tutorial that is great for beginners.
PostgreSQL also has an active user base and community. You can find out more about these user groups on the PostgreSQL site.

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

Featured

Why Linode

Get to Know Us Better

Featured

Compute

Storage

Databases

Services

Networking

Developer Tools

Featured

Solutions

Featured

Pricing

Free eBook

Community

Engage With Us

Search Results

No Results

Filters

How to Install and Use PostgreSQL on CentOS 8

Before You Begin

Advantages and Disadvantages of PostgreSQL

A Summary of the PostgreSQL Installation and Configuration Process

Install PostgreSQL

Install the Latest Version of PostgreSQL From the CentOS Packages

Install PostgreSQL From the PostgreSQL Yum Repository

Secure PostgreSQL and Access the PostgreSQL Shell

Install the PostgreSQL Administration Package

Using PostgreSQL

Work With PostgreSQL Databases

Work With PostgreSQL Tables

Work With PostgreSQL Columns

Add and Read Data With PostgreSQL

Create PostgreSQL Roles (Users) and Groups

Access PostgreSQL Remotely

Learn More About PostgreSQL

More Information

Your Feedback Is Important