Introduction
PostgreSQL is an open source object-relational database system. It is a mature database system known for reliability, data integrity, and correctness.
CentOS 7 includes PostgreSQL 9.2 in its default repositories. This tutorial will demonstrate the steps involved in getting the current PostgreSQL version, 9.4 as of this writing, up and running on CentOS 7.
Requirements
- CentOS 7
- PostgreSQL 9.4
- Internet Access
Add the Repository
A set of PostreSQL repositories are maintained at this site:
http://yum.postgresql.org/repopackages.php#pg94
Since our target distribution is CentOS 7, we’ll be downloading and installing this specific rpm:
http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
This can be done from a shell (as the root user):
sudo yum -y install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
Successful installation will result in the appearance of a new repository:
/etc/yum.repos.d/pgdg-94-centos.repo
Installation
We’ll now proceed to install:
sudo yum -y groupinstall "PostgreSQL Database Server 9.4 PGDG"
Congratulations! PostgreSQL 9.4 is now installed.
Post Install Configuration
Initialize PostgreSQL:
sudo /usr/pgsql-9.4/bin/postgresql94-setup initdb
Start the PostgreSQL service:
sudo systemctl start postgresql-9.4.service
Set the PostgreSQL service to start on boot:
sudo systemctl enable postgresql-9.4.service
Verification and Examples
PostgreSQL utilizes local system user accounts for access control. Lets go ahead and verify we can connect to our local PostgreSQL instance.
First we will switch to the “postgres” user that was added when we installed PostgreSQL:
sudo su postgres -
Now we can connect to the database server running on localhost:
psql
To see the available commands in the psql shell, run:
help
or:
\h
Let’s take a look at our currently configured user roles:
\du
Verify the version of PostgreSQL installed:
SELECT version();
which should result in something like this:
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)
Now we will go ahead and exit:
\q
You can see a list of databases by running:
psql -l
Configuration Changes
The configuration files for PostgreSQL on CentOS 7 are located in:
/var/lib/pgsql/9.4/data/
These files contains helpful comments regarding the configuration options available.
By default, PostgreSQL is operating through a socket on the localhost. In that configuration, the installation is secured against remote threats. If you do not need to access the database from a remote host, you can leave the default configuration. However, we often need to access a database from a remote host.
Configuring access is handled by editing a couple files. First we will tell PostgreSQL to start listening on our network interfaces. This is done by making a change in /var/lib/pgsql/9.4/data/postgresql.conf. Find this section:
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
and change it by uncommenting the listen_addresses line and changing localhost to *. Like this:
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
We will leave the port set to its default value of “5432”. Make sure and save the change. Now we will edit /var/lib/pgsql/9.4/data/pg_hba.conf and tell PostgreSQL that we want to accept connections from a specific IP address or range. Find this section in the file:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
and add a new host line with a specific IP address or range:
host all all <Client IP address>/32 md5
Now we can restart PostgreSQL:
# sudo systemctl restart postgresql-9.4.service
and verify that we are now listening on port 5432:
# ss -l -n |grep 5432
u_str LISTEN 0 128 /var/run/postgresql/.s.PGSQL.5432 7728992 * 0
u_str LISTEN 0 128 /tmp/.s.PGSQL.5432 7728994 * 0
tcp LISTEN 0 128 *:5432 *:*
tcp LISTEN 0 128 :::5432 :::*
We will also add a local firewall rule to allow the incoming connection on port 5432. For example:
sudo firewall-cmd --permanent --zone=trusted --add-source=<Client IP address>/32
sudo firewall-cmd --permanent --zone=trusted --add-port=5432/tcp
sudo firewall-cmd --reload
We are almost ready to go at this point, the only remaining issue is that the default postgres user has its password disabled by default. We can set one using psql like this:
sudo su postgres -
bash-4.2$ psql
psql (9.4.4)
Type "help" for help.
postgres=# \password
Enter new password:
Enter it again:
postgres=# \q
exit
Now we can connect from a remote system using a command-line client, or a GUI such as “pgAdmin3”. Connecting from another system using psql looks like this:
$ psql -h <Server IP Address> -p 5432 -U postgres -W
Password for user postgres:
psql (9.4.4)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
postgres=# \q
This is just a starting point for working with PostgreSQL