Install Postgresql 10 database on Centos 7

Installation and configuration of Postgresql 10 on Centos 7

Installation

Centos Linux 7 by default installs Postgresql version 9. These are summary of steps in order to install Postgresql version 10, the following steps can be taken.

1. Yum manages the installation of software, and utilises a list of repositories of there to locate its repository. It is highly advised to update existing software with yum before proceeding with software installations. Add the additional repository site must be added for Postgresql 10 from postgresql.org
2. Install the version of Postgresql database client software that is needed to open the database.
3. Install the version of Postgresql database server software. This will hold the database files and controlling software.
4. Initialise Postgresql database files. This will create the username "postgres" where it is configured to run all the client commands.
5. Start Postgresql database server.
6. Test the server by requesting info.
7. Configure Centos 7 to automatically start at boot up.

At a terminal, execute the commands

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum install postgresql10
# yum install postgresql10-server
# /usr/pgsql-10/bin/postgresql-10-setup initdb
# systemctl start postgresql-10
# /usr/pgsql-10/bin/postgres -V
# systemctl enable postgresql-10

Using the client

For those who wish to use the postgres clients, here are commands to get started.

Login as user "postgres" and use the Postgresql client.

# sudo -u postgres psql

Once in psql, here are several commands to browse the database;

Display server version (the Uppercase is a matter of good practice)
SELECT version();

List database schema
\l

Switch database schema
\c database_name

Describe a table
\d table_name

Get the last command
\g

Show history of commands
\s

Help with a command
\h command_name

Exit Postgresql client
\q

Remote access and firewall

In development databases, many users may require to access the database from remote computers. On the default Centos 7, with firewall running this require some configuration. The steps involved;

1. Add httpd and postgresql service rule to the firewall. The httpd is an additional web service I am demonstrating here.
2. Restart firewall
3. Configure postgresql to listen from incoming networks, or all networks. Replace the line listen_addresses = 'localhost'
4. Allow user authentication from incoming networks, or all networks. Replace the line with 127.0.0.1/32
5. Restart postgresql

At a terminal, execute the commands

# sudo firewall-cmd --add-port=8080/tcp --permanent

# sudo firewall-cmd --add-service=postgresql --permanent

# sudo firewall-cmd --reload

# sudo vi /var/lib/pgsql/10/data/postgresql.conf

listen_addresses = '*'

# sudo vi /var/lib/pgsql/10/data/pg_hba.conf

host    all             all             0.0.0.0/0            md5

# sudo systemctl restart postgresql-10