Differences

This shows you the differences between two versions of the page.

--- postgresql_installation [2020/01/22 12:45] – [Server Configuration] andonovj
+++ postgresql_installation [2021/12/10 17:47] (current) – [Install the Repositories] andonovj
@@ Line 1: / Line 1: @@
-======Overview======
+=====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:
@@ Line 14: / Line 15: @@
 <sxh bash>
-[root@postegresqlmaster ~]# yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
+[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
@@ Line 122: / Line 123: @@
-====Install the server====
+===Install the server===
 <sxh bash>
 [root@postegresqlmaster ~]# yum install postgresql96-server
@@ Line 195: / Line 196: @@
 Initializing database ... OK
 </Code>
-====Initial connection and Password management====
+Example for 12:
+<Code:bash|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
+</Code>
+===Initial connection and Password management===
 In order to log in, you have to set the password initially as follows:
@@ Line 210: / Line 222: @@
 postgres=#
 </sxh>
+===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:
+<sxh bash>
+[root@postgresqlmaster data]# cat postgresql.conf | grep listen
+listen_addresses = '192.168.0.178'          # what IP address(es) to listen on
+</sxh>
+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:
+<sxh bash>
+[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]#
+</sxh>
+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:
+<sxh bash>
+[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]
+</sxh>
+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.