=====Overview=====
PostegreSQL is open source database offered under the license of GPL and is supported by the community. The installation of postegresql is rather simple.
=====Installation=====
Installation is composed by 3 steps:
* Installation of the Repositories
* Installation of the Packages
* Installation of the Server
The configuration is done afterwards.
====Install the Repositories====
[root@postegresqlmaster ~]# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Loaded plugins: fastestmirror
Setting up Install Process
pgdg-redhat96-9.6-3.noarch.rpm | 4.7 kB 00:00
Examining /var/tmp/yum-root-c0TACv/pgdg-redhat96-9.6-3.noarch.rpm: pgdg-redhat96-9.6-3.noarch
Marking /var/tmp/yum-root-c0TACv/pgdg-redhat96-9.6-3.noarch.rpm to be installed
base | 3.7 kB 00:00
base/primary_db | 4.7 MB 00:02
extras | 3.4 kB 00:00
extras/primary_db | 30 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 6.4 MB 00:01
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat96.noarch 0:9.6-3 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================================================================================
Installing:
pgdg-redhat96 noarch 9.6-3 /pgdg-redhat96-9.6-3.noarch 2.7 k
Transaction Summary
===================================================================================================================================================================================================================
Install 1 Package(s)
Total size: 2.7 k
Installed size: 2.7 k
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : pgdg-redhat96-9.6-3.noarch 1/1
Verifying : pgdg-redhat96-9.6-3.noarch 1/1
Installed:
pgdg-redhat96.noarch 0:9.6-3
Complete!
====Install the packages====
[root@postegresqlmaster ~]# yum install postgresql96
Loaded plugins: fastestmirror
Setting up Install Process
Determining fastest mirrors
* base: mirrors.coreix.net
* extras: mirrors.coreix.net
* updates: mirrors.coreix.net
pgdg96 | 4.1 kB 00:00
pgdg96/primary_db | 178 kB 00:00
Resolving Dependencies
--> Running transaction check
---> Package postgresql96.x86_64 0:9.6.7-1PGDG.rhel6 will be installed
--> Processing Dependency: postgresql96-libs(x86-64) = 9.6.7-1PGDG.rhel6 for package: postgresql96-9.6.7-1PGDG.rhel6.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql96-9.6.7-1PGDG.rhel6.x86_64
--> Running transaction check
---> Package postgresql96-libs.x86_64 0:9.6.7-1PGDG.rhel6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================================================================================
Installing:
postgresql96 x86_64 9.6.7-1PGDG.rhel6 pgdg96 1.4 M
Installing for dependencies:
postgresql96-libs x86_64 9.6.7-1PGDG.rhel6 pgdg96 286 k
Transaction Summary
===================================================================================================================================================================================================================
Install 2 Package(s)
Total download size: 1.6 M
Installed size: 7.9 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql96-9.6.7-1PGDG.rhel6.x86_64.rpm | 1.4 MB 00:01
(2/2): postgresql96-libs-9.6.7-1PGDG.rhel6.x86_64.rpm | 286 kB 00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 690 kB/s | 1.6 MB 00:02
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : postgresql96-libs-9.6.7-1PGDG.rhel6.x86_64 1/2
Installing : postgresql96-9.6.7-1PGDG.rhel6.x86_64 2/2
Verifying : postgresql96-libs-9.6.7-1PGDG.rhel6.x86_64 1/2
Verifying : postgresql96-9.6.7-1PGDG.rhel6.x86_64 2/2
Installed:
postgresql96.x86_64 0:9.6.7-1PGDG.rhel6
Dependency Installed:
postgresql96-libs.x86_64 0:9.6.7-1PGDG.rhel6
Complete!
===Install the server===
[root@postegresqlmaster ~]# yum install postgresql96-server
Loaded plugins: fastestmirror
Setting up Install Process
Loading mirror speeds from cached hostfile
* base: mirrors.coreix.net
* extras: mirrors.coreix.net
* updates: mirrors.coreix.net
Resolving Dependencies
--> Running transaction check
---> Package postgresql96-server.x86_64 0:9.6.7-1PGDG.rhel6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================================================================================
Installing:
postgresql96-server x86_64 9.6.7-1PGDG.rhel6 pgdg96 4.8 M
Transaction Summary
===================================================================================================================================================================================================================
Install 1 Package(s)
Total download size: 4.8 M
Installed size: 18 M
Is this ok [y/N]: y
Downloading Packages:
postgresql96-server-9.6.7-1PGDG.rhel6.x86_64.rpm | 4.8 MB 00:02
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : postgresql96-server-9.6.7-1PGDG.rhel6.x86_64 1/1
Verifying : postgresql96-server-9.6.7-1PGDG.rhel6.x86_64 1/1
Installed:
postgresql96-server.x86_64 0:9.6.7-1PGDG.rhel6
Complete!
[root@postegresqlmaster ~]#
=====Configuration=====
Configuration of the server is initially done as follows:
====Server Configuration====
For Linux 6:
Complete!
[root@postegresqlmaster ~]# service postgresql-9.6 initdb
Initializing database: [ OK ]
[root@postegresqlmaster ~]# chkconfig postgresql-9.6 on
[root@postegresqlmaster ~]# service postgresql-9.6 start
Starting postgresql-9.6 service: [ OK ]
[root@postegresqlmaster ~]#
You can also start the database as follows, if you don't have a service:
/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start
For >= 7
[root@localhost yum.repos.d]# /usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
Example for 12:
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql12-server
yum install -y postgresql12-contrib
/usr/pgsql-12/bin/postgresql-12-setup initdb
===Initial connection and Password management===
In order to log in, you have to set the password initially as follows:
[root@postegresqlmaster ~]# sudo -u postgres psql postgres
psql (9.6.7)
Type "help" for help.
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=#
postgres=#
===Allow external Connections===
In order to allow, external connections, you show change the listening for PostgreSQL.
By default, PostgreSQL listens only on the loopback IP (e.g. 127.0.0.1) so you better change that to '*' (all) if you want to be accessible to all IPs which are on the server or put specific IP, as follows:
[root@postgresqlmaster data]# cat postgresql.conf | grep listen
listen_addresses = '192.168.0.178' # what IP address(es) to listen on
Like that, PostgreSQL will listen on port 5432 for IP: 192.168.0.178. You can also change the port (as soon as it isn't taken with the following setting:
[root@postgresqlmaster data]# cat postgresql.conf | grep port
port = 6000 # (change requires restart)
# supported by the operating system:
# supported by the operating system:
# %r = remote host and port
[root@postgresqlmaster data]#
That PostgreSQL listens on certain port, doesn't mean it will allow users to the database. The user connection settings are specified in the pg_hba.conf file in the PGDATA directory (by default).
In order to allow a connection you have to add an entry there, per specified syntax:
[root@postgresqlmaster data]# cat pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file. A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
Where the fields stands for:
- Source can be either: Host (for external connections) or Local (for local connections)
- The database to which you wish to connect, can be: "all" or particular database
- User which will be user or (+ = role)
- Address which the connection will be coming (omitted for local and IP for host type)
- Method of the authentication: md5(old), trust, ident, AES and others
- OPTIONS - additional options like LDAP (Pam) and so on.