{"id":1677,"date":"2024-02-13T15:21:22","date_gmt":"2024-02-13T12:21:22","guid":{"rendered":"http:\/\/pgdataeraold.local\/?p=1677"},"modified":"2024-02-13T15:21:22","modified_gmt":"2024-02-13T12:21:22","slug":"postgresql-installation-and-settings","status":"publish","type":"post","link":"https:\/\/pgdataera.com\/en\/postgresql-installation-and-settings\/","title":{"rendered":"Postgresql Installation and Settings"},"content":{"rendered":"\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># The gnupg package is installed to import the keys of the\nrepository.\napt-get install -y gnupg\n# A pgdg.list file is created to enter repository information.\nsudo sh -c \u2018echo \u201cdeb http:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_\nrelease -cs)-pgdg main\u201d &gt; \/etc\/apt\/sources.list.d\/pgdg.list\u2019\n# Import the signature of the repository.\nwget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/\nACCC4CF8.asc | sudo apt-key add -\n# The package list is updated.\nsudo apt-get update\n# To install the latest PostgreSQL version\nsudo apt-get -y install postgresql<\/pre>\n\n\n\n<p>The gnupg package is needed to avoid getting errors about security keys during<br>installation. In the first step, we install this package.<br>In the second step, we create the pgdg.list file in the repositor directory and<br>provide postgresql link.<br>Then we enter the security key, update the package list and install.<\/p>\n\n\n\n<p><strong>Note: <\/strong>If you want to install a different version, a version such as \u2018postgresql-15\u2019<br>must be specified.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"postgresql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">postgres@srv1:~$ sudo su - postgres\npostgres@srv1:~$ psql\npsql (14.5 (Debian 14.5-1.pgdg100+1))\nType \u201chelp\u201d for help.\npostgres=# \\l\nList of databases\nName      | Owner    | Encoding | Collate     | Ctype       |\n-----------+----------+----------+-------------+-------------+\npostgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |\ntemplate0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |\ntemplate1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |<\/pre>\n\n\n\n<p><\/p>\n\n\n\n<p>The point to note here is that in versions before postgresql 13, it was necessary<br>to create the database (cluster -this concept was explained in the architecture<br>module-) with initdb after installation.<\/p>\n\n\n\n<p>Example<br><code data-enlighter-language=\"generic\" class=\"EnlighterJSRAW\">initdb -D \/var\/lib\/postgresql\/9.6\/data -E unicode<\/code><\/p>\n\n\n\n<p>In this article, we saw the installation of the Postgresql database on Debian-based<br>Linux.<br>Redhat\/Centos\/Oracle Linux systems are installed with yum instead of apt. Apart from<br>these, it can be installed by compiling from source code. In the installation from the<br>source code, we will have to do all the operations one by one.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Creating the Postgres user.<\/li>\n\n\n\n<li>Preparation of directories.<\/li>\n\n\n\n<li>Granting of rights.<\/li>\n\n\n\n<li>Downloading and compiling source codes.<\/li>\n\n\n\n<li>Initialization of the cluster.<\/li>\n\n\n\n<li>Writing scripts and creating services, etc. <\/li>\n<\/ul>\n\n\n\n<p>All these processes are done manually.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">Giving a Postgres User a Password<\/h2>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"postgresql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># Updating a password to the postgres user in the operating\nsystem.\n# With root user\npasswd postgres\n# Password does not appear on the screen while entering, it\nprompts us to enter the same password second time.\n# Changing the password of the postgres user in the database\n# Login to the operating system with the postgres user.\npsql\nalter user postgres password \u2018password\u2019;<\/pre>\n\n\n\n<p><\/p>\n\n\n\n<p>When connecting to the server with SSH (ssh username@192.168.134.71), the<br>user in the operating system is logged in. An SSH connection is an operating systemlevel<br>connection (VNC, RDP, etc.).<br>After connecting to the server with SSH, you can connect to the database with psql.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">Connecting to Postgresql<\/h2>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"postgresql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># We set the IP \/ IPs that the Postgresql daemon will listen\nto.\n# Postgresql DB server\u2019s IP is 192.168.134.71\npsql\nalter system set listen_addresses=\u2019192.168.134.71\u2019;\n# Listens only to IP address provided.\n# If your server has more than 1 ethernet and\/or IP address and\nall IPs are requested to be listened.\nalter system set listen_addresses=\u2019*\u2019 ;\n# 2. In the pg_hba.conf file, we need to add the IPs to connect\nto the database. (It can be added to the end of the page.)\nvim \/etc\/postgresql\/13\/main\/pg_hba.conf\nhost all all 192.168.134.0\/24 md5\n# Restart Postgresql for the settings to take effect.\nsudo systemctl restart postgresql.service<\/pre>\n\n\n\n<p><\/p>\n\n\n\n<p>After these settings, from your host (desktop\/laptop) machine you can connect with a<br>postgres client like pgadmin, psql etc.<br>Connection via terminal:<br><code data-enlighter-language=\"postgresql\" class=\"EnlighterJSRAW\">psql -h 192.168.134.71 -d db_ismi -U postgres -W<\/code><\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">pgAdmin<\/h2>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"839\" src=\"http:\/\/pgdataeraold.local\/wp-content\/uploads\/2023\/11\/image-55-1024x839.png\" alt=\"\" class=\"wp-image-1465\" style=\"aspect-ratio:1.2205005959475566;width:596px;height:auto\" srcset=\"https:\/\/pgdataera.com\/wp-content\/uploads\/2023\/11\/image-55-1024x839.png 1024w, https:\/\/pgdataera.com\/wp-content\/uploads\/2023\/11\/image-55-300x246.png 300w, https:\/\/pgdataera.com\/wp-content\/uploads\/2023\/11\/image-55.png 1980w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure><\/div>\n\n\n<p>Enter the name of the database to be connected in the Maintenance database box. If<br>you want to connect with super user and access all databases, \u201cpostgres\u201d can be used.<br>Since this is a short guide, not much detail has been provided.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">Installation and Management of Extensions<\/h2>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"postgresql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># To install the latest package\napt install postgresql-contrib\n# If a different version is required, it can be queried with teh following\nparameters.\napt list |grep postgresql-contrib\n# Query\npsql\nSELECT * FROM pg_available_extensions;\n# List of installed extensions\n\\dx\n# Activate\ncreate extension adminpack ;\n# Delete\ndrop extension adminpack ;<\/pre>\n\n\n\n<p><\/p>\n\n\n\n<p>Extensions were previously called add-on and contrib. Now they are called extension n<br>general.<br>The functionality of postgrsql can be increased with extensions. A feature that does not<br>exist in the default can be developed as open source and included in these extensions.<br>Currently, there are 44 extensions by default.<br>The adminpack extension provides additional features for remote database<br>management. For example, management of remote log files (with superuser).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The gnupg package is needed to avoid getting errors about security keys duringinstallation. In the first step, we install this package.In the second step, we create the pgdg.list file in the repositor directory andprovide postgresql link.Then we enter the security key, update the package list and install. Note: If you want to install a different <a href=\"https:\/\/pgdataera.com\/en\/postgresql-installation-and-settings\/\" class=\"more-link\">&#8230;<span class=\"screen-reader-text\">  Postgresql Installation and Settings<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[310],"tags":[],"class_list":["post-1677","post","type-post","status-publish","format-standard","hentry","category-postgresql-installation-and-settings"],"_links":{"self":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts\/1677","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/comments?post=1677"}],"version-history":[{"count":0,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts\/1677\/revisions"}],"wp:attachment":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/media?parent=1677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/categories?post=1677"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/tags?post=1677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}