PostgreSQL or Postgres is an open-source general-purpose object-relational database management system with many advanced features that allows you to build fault-tolerant environments or complex applications.
In this guide, we will discuss how to install the PostgreSQL database server on CentOS 8. Before choosing which version to install, make sure that your applications support it.
We will also explore the basics of PostgreSQL database administration.
- You must be registered with Alibaba Cloud.
- You must have added and verified your payment method.
- If you are a new user, after payment method verification you can claim free $450 – $1300 credits. You can register new account and claim your free credits.
- To setup up your ECS for the first time, you can refer to this tutorial or quick-start guide.
- Domain registration is recommended for setting up FQDN hostname of your server. But if you are willing to use IP address directly, you may skip this.
- If you have registered domain with any 3rd party, you may transfer into Alibaba Cloud.
- If you are using the domain name, remember to point your domain name to IP address of your server.
- You should set up your server’s hostname.
- Access to VNC console in your Alibaba Cloud or SSH client installed in your PC.
- Login as root user and create a user with sudo privileges.
Installing PostgreSQL on CentOS 8
At the time of writing this article, there are two versions of PostgreSQL server available for installation from the standard CentOS repositories: version 9.6 and 10.0.
To list the available PostgreSQL module streams, type:
$ dnf module list postgresql
The output shows that the postgresql module is available with two streams. Each stream has two profiles: server and client. Stream 10 with the profile server is the default one:
CentOS-8 – AppStream
Name Stream Profiles Summary
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 9.6 client, server [d] PostgreSQL server and client module
To install the default stream, PostgreSQL server version 10.0 type:
$ sudo dnf install @postgresql:10
To install the PostgreSQL server version 9.6 type:
$ sudo dnf install @postgresql:9.6
You may also want to install the contrib package which provides several additional features for the PostgreSQL database system.
$ sudo dnf install postgresql-contrib
Once the installation is complete, initialize the PostgreSQL database with the following command:
$ sudo postgresql-setup initdb
Output Initializing database ... OK
Start the PostgreSQL service and enable it to start on boot:
$ sudo systemctl enable --now postgresql
Use the psql tool to verify the installation by connecting to the PostgreSQL database server and print its version :
$ sudo -u postgres psql -c "SELECT version();"
Output PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
PostgreSQL Roles and Authentication Methods
PostgreSQL handles the database access permissions using the concept of roles. A role can represent a database user or a group of database users.
PostgreSQL supports multiple authentication methods . The most commonly-used methods are:
- Trust – A role can connect without a password, as long as the conditions defined in the pg_hba.conf are met.
- Password – A role can connect by providing a password. The passwords can be stored as scram-sha-256, md5, and password (clear-text).
- Ident – Only supported on TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
- Peer – Same as Ident, but it is supported on local connections only.
PostgreSQL client authentication is defined in the configuration file named pg_hba.conf. By default, for local connections, PostgreSQL is set to use the peer authentication method.
The postgres user is automatically created when you install the PostgreSQL server. This user is the superuser for the PostgreSQL instance. It is equivalent to the MySQL root user.
To log in to the PostgreSQL server as the postgres user, first switch to the user and then access the PostgreSQL prompt using the psql utility:
$ sudo su - postgres psql
From here, you can interact with the PostgreSQL instance. To exit out of the PostgreSQL shell, type:
You can also access the PostgreSQL prompt without switching users with the sudo command:
$ sudo -u postgres psql
Typically the postgres user is used only from the localhost.
Creating PostgreSQL Role and Database
Only superusers and roles with CREATEROLE privilege can create new roles.
In the following example, we will create a new role named john, a database named johndb, and grant privileges on the database.
- First, connect to the PostgreSQL shell:
$ sudo -u postgres psql
2. Create a new PostgreSQL role using thr following command:
create role john
3. Create a new database:
create database johndb;
4. Grant privileges to the user on the database by running the following query:
grant all privileges on database johndb to john;
Enable remote access to PostgreSQL server
By default, the PostgreSQL server listens only on the local interface 127.0.0.1.
To enable remote access to your PostgreSQL server, open the configuration file:
$ sudo nano /var/lib/pgsql/data/postgresql.conf
Scroll down to the CONNECTIONS AND AUTHENTICATION section and add/edit the following line:
/var/lib/pgsql/data/postgresql.conf #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
Verify the changes with the ss utility:
$ ss -nlt | grep 5432
Output LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
The output above shows that the PostgreSQL server is listening on the default port on all interfaces (0.0.0.0).
The last step is to configure the server to accept remote connections by editing the pg_hba.conf file.
Below are some examples showing different use cases
/var/lib/pgsql/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # The user jane can access all databases from all locations using an md5 password host all jane 0.0.0.0/0 md5 # The user jane can access only the janedb database from all locations using an md5 password host janedb jane 0.0.0.0/0 md5 # The user jane can access all databases from a trusted location (192.168.1.134) without a password host all jane 192.168.1.134 trust
CentOS 8 provides two PostgreSQL versions: 9.6 and 10.0