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 is composed by 3 steps:
The configuration is done afterwards.
[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!
[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!
[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 of the server is initially done as follows:
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:
PostgreSQL 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
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=#
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: