{"id":145163,"date":"2023-09-19T03:33:34","date_gmt":"2023-09-19T00:33:34","guid":{"rendered":"https:\/\/computingforgeeks.com\/?p=145163"},"modified":"2024-02-19T10:11:51","modified_gmt":"2024-02-19T07:11:51","slug":"how-to-install-postgresql-16-on-debian","status":"publish","type":"post","link":"https:\/\/computingforgeeks.com\/how-to-install-postgresql-16-on-debian\/","title":{"rendered":"How To Install PostgreSQL 16 on Debian 12\/11\/10"},"content":{"rendered":"\n<p>Many organizations use PostgreSQL as their primary database server to power all business operations. <a href=\"https:\/\/computingforgeeks.com\/installing-postgresql-database-server-on-ubuntu\/\">PostgreSQL database server<\/a> development has been active for over 35 years. This proofs PostgreSQL is mature, reliable and stable relational database management system. PostgreSQL has a good reputation for its performance, reliability, data integrity, and high level of extensibility. In this article we provide the steps used to install, configure, and use PostgreSQL 16 on Debian 12\/11\/10 Linux system.<\/p>\n\n\n\n<p>At the time of publishing this article, the latest stable release of PostgreSQL available is version <strong>16. <\/strong>This was released to the general public on <strong><em>14th September 2023<\/em><\/strong> as a successor to <a href=\"https:\/\/computingforgeeks.com\/install-postgresql-on-rocky-almalinux-9\/\">PostgreSQL 15<\/a>. Some of the top features available in PostgreSQL 16 are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><em>Privilege admin<\/em><\/strong>: In version 16 there was an overhaul of&nbsp;<a href=\"https:\/\/www.enterprisedb.com\/blog\/postgresql-16-preview-administering-without-superuser\" target=\"_blank\" rel=\"noreferrer noopener\">privilege administration<\/a>. It now allows users to grant privileges in roles only if they possess the&nbsp;<code>ADMIN OPTION<\/code>&nbsp;for those roles.<\/li>\n\n\n\n<li><strong><em>Performance Improvements<\/em><\/strong>: There are enhancements in query execution to  allow for parallel execution of&nbsp;<code>FULL<\/code>&nbsp;and&nbsp;<code>RIGHT<\/code>&nbsp;<code>JOIN<\/code>s, as well as the&nbsp;<code>string_agg<\/code>&nbsp;and&nbsp;<code>array_agg<\/code>&nbsp;aggregate functions.<\/li>\n\n\n\n<li><strong><em>Enhancements in Logical replication<\/em><\/strong>: Logical replication from physical replication standbys, replication without a primary key, initial table synchronization in binary format, e.t.c.<\/li>\n\n\n\n<li><strong><em>Monitoring enhancements<\/em><\/strong>: The <code>pg_stat_io<\/code>&nbsp;view was introduced in PostgreSQL 16 to allow for better insight into I\/O activity of the Postgres system.<\/li>\n\n\n\n<li><strong><em>Better Access Control &amp; Security<\/em><\/strong>: Finer-grained options for access control and enhances other security features.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/current\/release-16.html\" target=\"_blank\" data-type=\"link\" data-id=\"https:\/\/www.postgresql.org\/docs\/current\/release-16.html\" rel=\"noreferrer noopener\">And many more great features<\/a><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Installing PostgreSQL 16 on Debian 12\/11\/10<\/h2>\n\n\n\n<p>Follow the steps in the next sections to install <a href=\"https:\/\/computingforgeeks.com\/install-and-configure-postgresql-on-ubuntu\/\">and configure PostgreSQL 16<\/a> on Debian Linux system.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"0-1-configure-the-postgresql-repository\">1) Update your system<\/h3>\n\n\n\n<p>Let&#8217;s make sure our system is updated.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo apt update &amp;&amp; sudo apt -y full-upgrade<\/code><\/pre>\n\n\n\n<p>If a reboot is required perform it.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91; -f \/var\/run\/reboot-required ] &amp;&amp; sudo reboot -f<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">2) Add PostgreSQL APT repository<\/h3>\n\n\n\n<p>Next we configure PostgreSQL repository on our Debian system. It contains all PostgreSQL 16 packages.<\/p>\n\n\n\n<p>Install dependency packages that we&#8217;ll use.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo apt install vim gnupg2 -y<\/code><\/pre>\n\n\n\n<p>Import repository GPG key.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>curl -fsSL https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc|sudo gpg --dearmor -o \/etc\/apt\/trusted.gpg.d\/postgresql.gpg<\/code><\/pre>\n\n\n\n<p>Run the following commands to configure PostgreSQL repository:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo sh -c 'echo \"deb https:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_release -cs)-pgdg main\" &gt; \/etc\/apt\/sources.list.d\/pgdg.list'<\/code><\/pre>\n\n\n\n<p>Update repository package index.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo apt update<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"1-2-install-postgresql-16-on-ubuntu-220420041804\">3) Install PostgreSQL 16 packages<\/h3>\n\n\n\n<p>Now that the repository is configured, let&#8217;s proceed to install the packages.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo apt install postgresql-16<\/code><\/pre>\n\n\n\n<p>Accept installation prompts.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Reading package lists... Done\nBuilding dependency tree... Done\nReading state information... Done\nThe following additional packages will be installed:\n  libgdbm-compat4 libjson-perl libllvm14 libperl5.36 libpq5 libxslt1.1 libz3-4 perl perl-modules-5.36 postgresql-client-16 postgresql-client-common postgresql-common ssl-cert\nSuggested packages:\n  perl-doc libterm-readline-gnu-perl | libterm-readline-perl-perl make libtap-harness-archive-perl postgresql-doc-16\nRecommended packages:\n  libjson-xs-perl sysstat\nThe following NEW packages will be installed:\n  libgdbm-compat4 libjson-perl libllvm14 libperl5.36 libpq5 libxslt1.1 libz3-4 perl perl-modules-5.36 postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common ssl-cert\n0 upgraded, 14 newly installed, 0 to remove and 0 not upgraded.\nNeed to get 56.8 MB of archives.\nAfter this operation, 252 MB of additional disk space will be used.\nDo you want to continue? &#91;Y\/n] <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-luminous-vivid-orange-color\">y<\/mark><\/code><\/pre>\n\n\n\n<p>After installation start the service and set to come up at system boot.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl start postgresql@16-main.service &amp;&amp; sudo systemctl enable postgresql@16-main.service<\/code><\/pre>\n\n\n\n<p>Confirm status of postgresql service.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-pink-color\">systemctl status postgresql@16-main.service<\/mark>\n<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-light-green-cyan-color\">\u25cf<\/mark> postgresql@16-main.service - PostgreSQL Cluster 16-main\n     Loaded: loaded (\/lib\/systemd\/system\/postgresql@.service; enabled; preset: enabled)\n     Active: <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-green-cyan-color\">active (running) <\/mark>since Thu 2023-11-23 13:00:47 UTC; 12s ago\n   Main PID: 4605 (postgres)\n      Tasks: 6 (limit: 4531)\n     Memory: 18.3M\n        CPU: 298ms\n     CGroup: \/system.slice\/system-postgresql.slice\/postgresql@16-main.service\n             \u251c\u25004605 \/usr\/lib\/postgresql\/16\/bin\/postgres -D \/var\/lib\/postgresql\/16\/main -c config_file=\/etc\/postgresql\/16\/main\/postgresql.conf\n             \u251c\u25004606 \"postgres: 16\/main: checkpointer \"\n             \u251c\u25004607 \"postgres: 16\/main: background writer \"\n             \u251c\u25004609 \"postgres: 16\/main: walwriter \"\n             \u251c\u25004610 \"postgres: 16\/main: autovacuum launcher \"\n             \u2514\u25004611 \"postgres: 16\/main: logical replication launcher \"\n\nNov 23 13:00:45 deb12 systemd&#91;1]: Starting postgresql@16-main.service - PostgreSQL Cluster 16-main...\nNov 23 13:00:47 deb12 systemd&#91;1]: Started postgresql@16-main.service - PostgreSQL Cluster 16-main.<\/code><\/pre>\n\n\n\n<p>You can check installed PostgreSQL version:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-pink-color\">psql --version\n<\/mark>psql (PostgreSQL) 16.1 (Debian 16.1-1.pgdg120+1)<\/code><\/pre>\n\n\n\n<p>Or with PostgreSQL <strong><em>SELECT<\/em><\/strong> statement.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-pink-color\">sudo -u postgres psql -c \"SELECT version();\"<\/mark>\n                                                       version\n---------------------------------------------------------------------------------------------------------------------\n PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\n(1 row)<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"2-3-configure-postgresql-16-on-ubuntu-220420041804\">4). Configure Authentication method<\/h3>\n\n\n\n<p>PostgreSQL has support for several authentication methods. The default method is <strong>ident authentication<\/strong> which associates UNIX user with the Postgres role.<\/p>\n\n\n\n<p>Other authentication methods available are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Password authentication<\/strong> &#8211; Uses a password to connects to PostgreSQL database.<\/li>\n\n\n\n<li><strong>Trust authentication<\/strong> &#8211; Authorize access based the conditions defined in the <strong>pg_hba.conf<\/strong> file.<\/li>\n\n\n\n<li><strong>Peer authentication<\/strong> &#8211; Similar to ident but only supported on local connections.<\/li>\n<\/ul>\n\n\n\n<p>To configure database server for remote connections edit below file.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vim \/etc\/postgresql\/16\/main\/postgresql.conf<\/code><\/pre>\n\n\n\n<p>Locate and modify the the following lines.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#------------------------------------------------------------------------------\n# CONNECTIONS AND AUTHENTICATION\n#------------------------------------------------------------------------------\n\n# - Connection Settings -\n\nlisten_addresses = '<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-green-cyan-color\">*<\/mark>'          # what IP address(es) to listen on;\n                                        # comma-separated list of addresses;\n                                        # defaults to 'localhost'; use '*' for all<\/code><\/pre>\n\n\n\n<p>Allow password authentication using the command below.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo sed -i '\/^host\/s\/ident\/md5\/' \/etc\/postgresql\/16\/main\/pg_hba.conf<\/code><\/pre>\n\n\n\n<p>Next we change identification method from peer to trust with the command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo sed -i '\/^local\/s\/peer\/trust\/' \/etc\/postgresql\/16\/main\/pg_hba.conf<\/code><\/pre>\n\n\n\n<p>Finally, allow PostgreSQL to be accessed remotely  by editing the below lines:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-pink-color\">sudo vim \/etc\/postgresql\/16\/main\/pg_hba.conf<\/mark>\n# IPv4 local connections:\nhost    all             all             127.0.0.1\/32            scram-sha-256\nhost    all             all             0.0.0.0\/0           scram-sha-256\n# IPv6 local connections:\nhost    all             all             ::1\/128                 scram-sha-256\nhost    all             all             0.0.0.0\/0                md5<\/code><\/pre>\n\n\n\n<p>Restart the database server after making the changes.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl restart postgresql<\/code><\/pre>\n\n\n\n<p>If you have UFW firewall active, accept incoming connections.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo ufw allow 5432\/tcp<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"3-4-connect-to-postgresql-16\">5) Connect to PostgreSQL 16 server<\/h3>\n\n\n\n<p>You can connect to the PostgreSQL database server directly:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-pink-color\">sudo -u postgres psql<\/mark>\npsql (16.1 (Debian 16.1-1.pgdg120+1))\nType \"help\" for help.\n\n<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">postgres=#<\/mark><\/code><\/pre>\n\n\n\n<p>For remote connections use <code>psql<\/code> command with the following command options.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>psql 'postgres:\/\/&lt;<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-green-cyan-color\">username<\/mark>&gt;:&lt;<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-luminous-vivid-orange-color\">password<\/mark>&gt;@&lt;<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\">host<\/mark>&gt;:&lt;<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-purple-color\">port<\/mark>&gt;\/&lt;<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-cyan-blue-color\">db<\/mark>&gt;?sslmode=disable'<\/code><\/pre>\n\n\n\n<p>See below example.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>psql 'postgres:\/\/<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-cyan-blue-color\">mydbuser<\/mark>@<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-luminous-vivid-amber-color\">172.20.40.5<\/mark>:<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-cyan-blue-color\">5432<\/mark>\/<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\">mydb<\/mark>?sslmode=disable'<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"4-5-getting-started-with-postgresql-16\">6) Changing superuser password<\/h3>\n\n\n\n<p>Access PostgreSQL shell.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -i -u postgres\npsql<\/code><\/pre>\n\n\n\n<p>Let&#8217;s secure <code>postgres<\/code> user by updating its password.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER USER postgres PASSWORD '<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-cyan-blue-color\">Str0ngDBPassw0rd<\/mark>';<\/code><\/pre>\n\n\n\n<p>Replace the password with your own desired password string and save the file.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>postgres=# <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-pink-color\">\\q<\/mark><\/code><\/pre>\n\n\n\n<p>Now test if the password is working:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-pale-pink-color\">psql -h localhost -U postgres\n<\/mark>Password for user postgres: <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-green-cyan-color\">Enter_set_password<\/mark>\npsql (16.1 (Debian 16.1-1.pgdg120+1))\nSSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)\nType \"help\" for help.\n\npostgres=# <\/code><\/pre>\n\n\n\n<p>Our guide only demonstrated how you can install and configure PostgreSQL 16 on Debian Linux system. For more administrative and usage guides of PostgreSQL visit the <a href=\"https:\/\/www.postgresql.org\/docs\/16\/index.html\" target=\"_blank\" data-type=\"link\" data-id=\"https:\/\/www.postgresql.org\/docs\/16\/index.html\" rel=\"noreferrer noopener\">official documentation pages<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many organizations use PostgreSQL as their primary database server to power all business operations. PostgreSQL database server development has been active for over 35 years. This proofs PostgreSQL is mature, reliable and stable relational database management system. PostgreSQL has a good reputation for its performance, reliability, data integrity, and high level of extensibility. In this &#8230; <a title=\"How To Install PostgreSQL 16 on Debian 12\/11\/10\" class=\"read-more\" href=\"https:\/\/computingforgeeks.com\/how-to-install-postgresql-16-on-debian\/\" aria-label=\"Read more about How To Install PostgreSQL 16 on Debian 12\/11\/10\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":145173,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[461,26,299,50,37631],"tags":[688,38783],"class_list":["post-145163","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-debian","category-how-to","category-linux-tutorials","category-postgresql","tag-postgresql","tag-postgresql-16"],"_links":{"self":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/145163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/comments?post=145163"}],"version-history":[{"count":0,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/145163\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media\/145173"}],"wp:attachment":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media?parent=145163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/categories?post=145163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/tags?post=145163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}