In this recipe, we will not only learn how to install the PostgreSQL DBMS on our server, but we will also discover how to add a new user and create our first database. PostgreSQL is considered to be the most advanced open source database system in the world. It is known for being a solid, reliable, and well-engineered system that is fully capable of supporting high-transaction and mission-critical applications. PostgreSQL is a descendant of the Ingres database. It is community-driven and maintained by a large collection of contributors from all over the world. It may not be as flexible or as pervasive as MariaDB, but because PostgreSQL is a very secure database system that excels in data integrity, it is the purpose of this recipe to show you how to begin exploring this forgotten friend.
To complete this recipe, you will require a working installation of the CentOS 7 operating system with root privileges, a console-based text editor of your choice, and a connection to the Internet in order to facilitate the download of additional packages. It is expected that your server will be using a static IP address.
PostgreSQL (also known as Postgres) is an object-relational database management system. It supports a large part of the SQL standard and it can be extended by the server administrator in many ways. However, in order to begin, we must start by installing the necessary packages:
yum install postgresql postgresql-server
systemctl enable postgresql
postgresql-setup initdb
systemctl start postgresql
postgres
administrator of your choice. As the default postgres
user is currently using peer authentication, we need to execute any Postgres-related command with user postgres
:su - postgres -c "psql --command 'password postgres'"
postgres
user has to be logged in on a system user basis before he can execute Postgres-related commands such as psql
, and to allow login with database user accounts in general, we need to change the authentication method for localhost
from peer
to md5
in the Postgres client authentication configuration file. You can do this manually or use the sed
tool as shown next, after you have made a backup of the file first:cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.BAK sed -i 's/^(local.*)peer$/1md5/g' /var/lib/pgsql/data/pg_hba.conf
postgresql
service in order to apply our changes:systemctl restart postgresql
postgres
without the need to login the postgres
Linux system user first:psql -U postgres
postgres=#)
, type the following command (followed by the Return key):q
<username>
with a relevant user name to fit your own needs (type in a new password for the user when prompted, repeat it, and afterwards enter the password for the administrator user postgres
to apply these settings):createuser -U postgres -P <username>
<database-name>
and <username>
values with something more appropriate to your needs (enter the password for the postgres
user):createdb -U postgres <database-name> -O <username>
psql -U <username> -l
PostgreSQL is an Object-Relational Database Management System and it is available to all CentOS servers. Postgres may not be as common as MariaDB, but its architecture and large array of features do make it an attractive solution for many companies concerned with data integrity.
So what did we learn from this experience?
We began this recipe by installing the necessary server and client rpm
packages using yum
. Having done this, we then proceeded to make the Postgres system available at boot before initializing the database system using the postgresql-setup initdb
command. We completed this process by starting the database service. In the next stage, we were then required to set the password for the Postgres administrator user to harden the system. By default, the postgresql
package creates a new Linux system user called postgres
(which is also used as an administrative Postgres user account to access our Postgres DBMS), and by using su - postgres - c
we were able to execute the psql
commands as the postgres
user, which is mandatory upon installation (this is called peer authentication).
Having set the admin password, to have more like a MariaDB shell-type of login procedure where every database user (including the administrator postgres
user) can log in using the database psql
client's user -U
parameter, we changed this peer
authentication to md5
database password-based authentication for the localhost in the pg_hba.conf
file (see the next recipe). After restarting the service, we then used Postgres's createuser
and createdb
command line tools to create a new Postgres user and connect it to a new database (we needed to provide the postgres
user with the -U
parameter because only he has the privileges for it). Finally, we showed you how to make a test connection to the database with your new user using the -l
flag (which lists all the available databases). Also, you can use the -d
parameter to connect to a specific database using the syntax: psql -d <database-name> -U <username>
.
Instead of using the createuser
or createdb
Postgres command-line tools, as we have been showing you in this recipe, to create your databases and users, you can also do the same using the Postgres shell. In fact, those command-line tools are actually just wrappers around the Postgres shell commands, and there is no effective difference between the two. psql
is the primary command-line client tool for entering SQL queries or other commands on a Postgres server, similar to the MariaDB shell shown to you in another recipe in this chapter. Here, we will launch psql
with a template called template1
, the boilerplate (or default template) that is used to start building databases. After login (psql -U postgres template1
), and typing in the administrator password you should be presented with the interactive Postgres prompt (template1=#
). Now to create a new user in the psql
shell, type:
CREATE USER <username> WITH PASSWORD '<password>';
To create a database, type:
CREATE DATABASE <database-name>;
The option to grant all privileges on the recently created database to the new user is:
GRANT ALL ON DATABASE <database-name> to <username>;
To exit the interactive shell, use: q
followed by pressing the Return key.
Having completed this recipe you could say that you not only know how to install PostgreSQL, but this process has served to highlight some simple architectural differences between this database system and MariaDB.