In this process, 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 process to show you how to begin exploring this forgotten friend.
To Start With: What Do You Need?
To complete this process, 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.
The Process
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:
- Start by logging in your server as root and type:
yum install postgresql postgresql-server - Having installed the database system, we must now enable the database server at boot by typing:
systemctl enable postgresql - When you have finished, initialize the database system as follows:
postgresql-setup initdb - Now complete this process by starting the database server:
systemctl start postgresql - Now set a new initial password for our 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'" - To get rid of the requirement, that the 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 - Next, we have to restart the postgresql service in order to apply our changes:
systemctl restart postgresql - Now you will be able to login to your Postgres server with user postgres without the need to log in the postgres Linux system user first:
psql -U postgres - To exit the shell (postgres=#), type the following command (followed by the Return key):
\q - We will now issue a shell command to create a new database user, by substituting <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> - Now, also on the shell create your first database and assign it to our new user by replacing the <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> - Finally, test if you can access the Postgres server with your new user by printing all the database names:
psql -U <username> -l
How Does It Work?
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 a large array of features to make it an attractive solution for many companies concerned with data integrity.
So what did we learn from this experience?
We began this process 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 process). 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>.
There's more…
Instead of using the createuser or createdb Postgres command-line tools, as we have been showing you in this process, 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 process in this chapter division. 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 process 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.
