Relational database management systems are a key component of many websites and applications. They provide a structured way to store, organize, and access information.
PostgreSQL, also known as Postgres, is a relational database management system that provides an implementation of Structured Query Language, better known as SQL. It’s used by many popular projects, both large and small, is standards-compliant, and has many advanced features like reliable transactions and concurrency without read locks.
By following this guide, you will install the latest version of PostgreSQL on a CentOS 8 server.
To complete this tutorial, you will need a server running CentOS 8. This server should have a non-root user with administrative privileges and a firewall configured with firewalld
. To set this up, see our Initial Server Setup guide for CentOS 8.
PostgreSQL is available from CentOS 8’s default AppStream software repository, and there are multiple versions which you can install. You can choose between these versions by enabling the appropriate collection of packages and dependencies that align with the version you want to install, with each collection referred to as a module stream.
In DNF, CentOS 8’s default package manager, modules are special collections of RPM packages that together make up a larger application. This is intended to make installing packages and their dependencies more intuitive for users.
List out the available streams for the postgresql
module using the dnf
command:
- dnf module list postgresql
Outputpostgresql 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 PostgreSQL server and client module
You can see in this output that there are three versions of PostgreSQL available from the AppStream repository: 9.6
, 10
, and 12
. The stream that provides Postgres version 10 is the default, as indicated by the [d]
following it. If you want to install that version you could just run sudo dnf install postgresql-server
and move on to the next step. However, even though version 10 is still maintained, this tutorial will install Postgres version 12, the latest release at the time of this writing.
To install PostgreSQL version 12, you must enable that version’s module stream. When you enable a module stream, you override the default stream and make all of the packages related to the enabled stream available on the system. Note that only one stream of any given module can be enabled on a system at the same time.
To enable the module stream for Postgres version 12, run the following command:
- sudo dnf module enable postgresql:12
When prompted, press y
and then ENTER
to confirm that you want to enable the stream:
Output====================================================================
Package Architecture Version Repository Size
====================================================================
Enabling module streams:
postgresql 12
Transaction Summary
====================================================================
Is this ok [y/N]: y
After enabling the version 12 module stream, you can install the postgresql-server
package to install PostgreSQL 12 and all of its dependencies:
- sudo dnf install postgresql-server
When given the prompt, confirm the installation by pressing y
then ENTER
:
Output. . .
Install 4 Packages
Total download size: 16 M
Installed size: 62 M
Is this ok [y/N]: y
Now that the software is installed, you will perform some initialization steps to prepare a new database cluster for PostgreSQL.
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:
- sudo postgresql-setup --initdb
You will see the following output:
Output * Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
Now start the PostgreSQL service using systemctl
:
- sudo systemctl start postgresql
Then, use systemctl
once more to enable the service to start up whenever the server boots:
- sudo systemctl enable postgresql
This will give the following output
OutputCreated symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
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.
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.
Switch over to the postgres account on your server by typing:
- sudo -i -u postgres
You can now access a Postgres prompt immediately by typing:
- psql
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:
- \q
This will bring you back to the postgres account’s Linux command prompt. Now return to your original account with the following:
- exit
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:
- sudo -u postgres psql
This will log you directly into Postgres without the intermediary bash
shell.
Again, you can exit the interactive Postgres session by typing:
- \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.
Currently, you just have the postgres role configured within the database. You can create new roles from the command line with the createrole
command. The --interactive
flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.
If you are logged in as the postgres account, you can create a new user by typing:
- createuser --interactive
If, instead, you prefer to use sudo
for each command without switching from your normal account, type:
- sudo -u postgres createuser --interactive
The script will prompt you with some choices and, based on your responses, execute the necessary Postgres commands to create a user to your specifications. For this tutorial, create a role named sammy and give it superuser privileges by entering y
when prompted:
OutputEnter name of role to add: sammy
Shall the new role be a superuser? (y/n) y
You can get more control by passing some additional flags. Check out the options by looking at the man
page for createuser
:
- man createuser
Your installation of Postgres now has a new role, but you have not yet added any databases. The next section describes this process.
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 sammy, that role will attempt to connect to a database which is also called sammy
by 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:
- createdb sammy
If, instead, you prefer to use sudo
for each command without switching from your normal account, you would type:
- sudo -u postgres createdb sammy
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.
To log in with ident-based authentication, you’ll need a Linux user with the same name as your Postgres role and database.
If you don’t have a matching Linux user available, you can create one with the adduser
command. You will have to do this from your non-root account with sudo
privileges (meaning, not logged in as the postgres user):
- sudo adduser sammy
Once this new account is available, you can either switch over and then connect to the database by first typing:
- sudo -i -u sammy
- psql
Or, you can do this inline:
- sudo -u sammy psql
This command will log you in automatically.
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:
- psql -d postgres
Once logged in, you can check your current connection information by typing:
- \conninfo
This will show the following output:
OutputYou are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".
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.
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)
);
These commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column.
For demonstration purposes, create a simple table like this:
- CREATE TABLE playground (
- equip_id serial PRIMARY KEY,
- type varchar (50) NOT NULL,
- color varchar (25) NOT NULL,
- location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
- install_date date
- );
This command will create a table that inventories playground equipment. It starts with an equipment ID, which is of the serial
type. This data type is an auto-incrementing integer. You’ve also given this column the constraint of PRIMARY KEY
, which means that the values must be unique and not null.
For two of the columns (equip_id
and install_date
), the command does not specify a field length. This is because some column types don’t require a set length because the length is implied by the type.
The next two lines create columns for the equipment type
and color
respectively, each of which cannot be empty. The line after these creates a location
column and a constraint that requires the value to be one of eight possible values. The last line creates a date column that records the date on which you installed the equipment.
You can see your new table by typing:
- \d
This will show the following output:
Output List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+-------
public | playground | table | sammy
public | playground_equip_id_seq | sequence | sammy
(2 rows)
Your playground table is here, but there’s also something called playground_equip_id_seq
that is of the type sequence
. This is a representation of the serial
type that you gave your equip_id
column. This keeps track of the next number in the sequence and is created automatically for columns of this type.
If you want to see just the table without the sequence, you can type:
- \dt
This will yield the following:
Output List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | playground | table | sammy
(1 row)
In this step, you created a sample table. In the next step, you will try out adding, querying, and deleting entries in that table.
Now that you have a table, you can insert some data into it.
As an example, add a slide and a swing by calling the table you want to add to, naming the columns, and then providing data for each column, like this:
- INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
- INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');
You should take care when entering the data to avoid a few common hangups. For one, do not wrap the column names in quotation marks, but the column values that you enter do need quotes.
Another thing to keep in mind is that you do not enter a value for the equip_id
column. This is because it is automatically generated whenever a new row in the table is created.
Retrieve the information you’ve added by typing:
- SELECT * FROM playground;
You will see the following output:
Output equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
1 | slide | blue | south | 2017-04-28
2 | swing | yellow | northwest | 2018-08-16
(2 rows)
Here, you can see that your equip_id
has been filled in successfully and that all of your other data has been organized correctly.
If the slide on the playground breaks and you have to remove it, you can also remove the row from your table by typing:
- DELETE FROM playground WHERE type = 'slide';
Query the table again:
- SELECT * FROM playground;
You will see the following:
Output equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
2 | swing | yellow | northwest | 2018-08-16
(1 row)
Notice that your slide is no longer a part of the table.
Now that you’ve added and deleted entries in your table, you can try adding and deleting columns.
After creating a table, you can modify it to add or remove columns. Add a column to show the last maintenance visit for each piece of equipment by typing:
- ALTER TABLE playground ADD last_maint date;
If you view your table information again, you will see the new column has been added (but no data has been entered):
- SELECT * FROM playground;
You will see the following:
Output equip_id | type | color | location | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
2 | swing | yellow | northwest | 2018-08-16 |
(1 row)
Deleting a column is just as simple. If you find that your work crew uses a separate tool to keep track of maintenance history, you can delete the column by typing:
- ALTER TABLE playground DROP last_maint;
This deletes the last_maint
column and any values found within it, but leaves all the other data intact.
Having now added and deleted columns, you can try updating existing data in the final step.
So far, you’ve learned how to add records to a table and how to delete them, but this tutorial hasn’t yet covered how to modify existing entries.
You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. You can query for the swing
record (this will match every swing in your table) and change its color to red
:
- UPDATE playground SET color = 'red' WHERE type = 'swing';
You can verify that the operation was successful by querying the data again:
- SELECT * FROM playground;
You will see the following:
Output equip_id | type | color | location | install_date
----------+-------+-------+-----------+--------------
2 | swing | red | northwest | 2010-08-16
(1 row)
As you can see, your slide is now registered as being red
.
You are now set up with PostgreSQL on your CentOS 8 server. However, there is still much more to learn with Postgres. Here are some more guides that cover how to use Postgres:
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Was a great tutorial and everything working first try. Can you please do one, or guide me to one, on adding and configuring pgAdmin? I have followed several others and run into errors in the process. Would really like to get it working ASAP. Thanks in advance!