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:
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
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.