{"id":540,"date":"2023-01-12T03:49:00","date_gmt":"2023-01-12T03:49:00","guid":{"rendered":"https:\/\/blog.ngocha.biz\/?p=540"},"modified":"2023-01-12T03:49:00","modified_gmt":"2023-01-12T03:49:00","slug":"install-postgresql-on-ubuntu","status":"publish","type":"post","link":"https:\/\/blog.ngocha.biz\/?p=540","title":{"rendered":"How to Install PostgreSQL on Ubuntu [Setup & Configurations]"},"content":{"rendered":"

This guide will teach you how to install PostgreSQL on Ubuntu<\/strong> and change its key configurations.<\/p>\n

PostgreSQL<\/a> is one of the best open source object-relational database systems<\/a>. Whether you are a developer, sysadmin, or DevOps engineer, it is essential to know the basic setup and configurations involved in a PostgreSQL setup.<\/p>\n

As per the latest StackOverflow developer survey, 40.42% of developers use PostgreSQL<\/a> as a database.<\/p>\n

\"\"<\/figure>\n

Install PostgreSQL on Ubuntu<\/h2>\n

It is always better to download the latest version available in the official PostgreSQL Ubuntu repository.<\/p>\n

\nsudo sh -c 'echo \"deb http:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_release -cs)-pgdg main\" > \/etc\/apt\/sources.list.d\/pgdg.list'\n\nwget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add -\n\nsudo apt-get update<\/code><\/pre>\n
Install the latest version of PostgreSQL<\/p>\n
sudo apt-get -y install postgresql<\/code><\/pre>\n
If you want to install a specific version of PostgreSQL, you can search for the available versions from here<\/a> and use the version with the install command as shown below.<\/p>\n
sudo apt-get -y install postgresql-17<\/code><\/pre>\n
Lets validate PostgreSQL by checking the service status.<\/p>\n
sudo systemctl status postgresql<\/code><\/pre>\n
Connect to PostgreSQL & Change Password<\/h2>\n
You can connect to the PostgreSQL server using the psql<\/code> client<\/p>\n
When you install PostgreSQL, the psql<\/code> client also gets installed.<\/p>\n
Execute the following command to connect to the server.<\/p>\n
sudo -u postgres psql<\/code><\/pre>\n
Let’s check the version of PostgreSQL using the following command.<\/p>\n
SELECT version();<\/code><\/pre>\n
Now, we have to change the postgres<\/code> user’s password so that we can allow remote PostgreSQL connection<\/strong> using password authentication.<\/p>\n
Execute the following query to set a password. Replace myPassword with a password of your choice.<\/p>\n
ALTER USER postgres PASSWORD 'myPassword';<\/code><\/pre>\n
PostgreSQL Remote Connection Configuration<\/h2>\n
By default, the PostgreSQL server is accessible only on the loopback interface (127.0.0.1) on port 5432 and through the Unix socket. Therefore, you can only connect to the PostgreSQL server from within the Ubuntu server where PostgreSQL is installed .<\/p>\n
\"PostgreSQL<\/figure>\n
You can verify it by listing all the TCP connections using the ss<\/code> Linux networking command.<\/a><\/p>\n
ss -nlt<\/code><\/pre>\n
\"PostgreSQL<\/figure>\n
To enable remote connection to PostgreSQL, we need to allow it in the postgresql.conf file.
For Ubuntu systems, postgresql.conf <\/code>is located in \/etc\/postgresql\/17\/main\/<\/code> location. 17 is the version number.<\/p>\n
If you are using a different PostgreSQL version, change it accordingly.<\/p>\n
Or you can use the following find command to locate the file.<\/p>\n
sudo find \/ -name \"postgresql.conf\"<\/code><\/pre>\n
Open the file postgresql.conf<\/code> file.<\/p>\n
postgresql.conf<\/code><\/pre>\n
Under CONNECTIONS AND AUTHENTICATION<\/code> section you will find the following commented parameter.<\/p>\n
#listen_addresses = 'localhost' <\/code><\/pre>\n
\"PostgreSQL<\/figure>\n
Replace it with the following and save the file. With this configuration, we are enabling PostgreSQL server connections to accept connections from all IP addresses.<\/p>\n
listen_addresses = '*' <\/code><\/pre>\n
To apply the change, restart the PostgreSQL service.<\/p>\n
sudo systemctl restart postgresql<\/code><\/pre>\n
Now, if you list all the TCP connections, you can see the PostgreSQL service listening on all interfaces, as shown below.<\/p>\n
ss -nlt<\/code><\/pre>\n
\"PostgreSQL<\/figure>\n
We have allowed only connections on all interfaces. However, If you try to connect to PostgreSQL from a remote machine, you will get the following error because you need to enable client connections to all the Databases and users.<\/p>\n
psql: error: connection to server at \"\", port 5432 failed: FATAL:  no pg_hba.conf entry for host \"\", user \"postgres\", database \"postgres\", SSL encryption\nconnection to server at \"\", port 5432 failed: FATAL:  no pg_hba.conf entry for host \"\", user \"postgres\", database \"postgres\", no encryption<\/code><\/pre>\n
To allow client connections to all databases, you need to edit the pg_hba.conf<\/strong> file.<\/p>\n
Open pg_hba.conf<\/strong> file.<\/p>\n
sudo vi \/etc\/postgresql\/17\/main\/pg_hba.conf <\/code><\/pre>\n
Add the following line to the end of the file.<\/p>\n
host    all          all            0.0.0.0\/0  md5<\/code><\/pre>\n
\"PostgreSQL<\/figure>\n
To apply the change, restart the PostgreSQL service.<\/p>\n
sudo systemctl restart postgresql<\/code><\/pre>\n
Test PostgreSQL Remote Connection<\/h2>\n
Now, let’s try connecting PostgreSQL from a remote machine.<\/p>\n
First, install the PostgreSQL client on the system you are trying to connect to the PostgreSQL server.<\/p>\n
For Ubuntu,<\/p>\n
sudo apt-get install postgresql-client<\/code><\/pre>\n
For MAC<\/p>\n
brew install libpq\nbrew link --force libpq<\/code><\/pre>\n
Once the client is installed, execute the following psql command to connect to the PostgreSQL remotely. Replace 192.168.5.5 with your PostgreSQL server IP. When it prompts for a password, enter the password we generated during the initial configuration.<\/p>\n
psql -h 192.168.5.5 -U postgres<\/code><\/pre>\n
\"PostgreSQL<\/figure>\n
Important PostgreSQL Server Configurations<\/h2>\n
The following table contains important PostgreSQL configurations.<\/p>\n
<\/p>\n\n\n\n\n\n\n\n\n\n
Config<\/th>\nDetails<\/th>\n<\/tr>\n<\/thead>\n
PostgreSQL default port<\/td>\n5432<\/td>\n<\/tr>\n
Default user <\/td>\npostgres<\/td>\n<\/tr>\n
Config files location (postgresql.conf & pg_hba.conf<\/code> )<\/td>\n\/etc\/postgresql\/postgresql.conf
\/etc\/postgresql\/pg_hba.conf<\/td>\n<\/tr>\n
Default database<\/td>\npostgres<\/td>\n<\/tr>\n
Default data directory<\/td>\n\/var\/lib\/postgresql\/<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/p>\n
PostgreSQL Server Installation FAQs<\/h2>\n
The following are the frequently asked questions about PostgreSQL Server Installation.<\/p>\n
Does PostgreSQL have a default database?<\/h3>\n
Yes. When you install PostgreSQL, a default database named Postgres gets created. It contains user information, utilities, and third-party applications.<\/p>\n
What is the location of postgresql.conf file?<\/h3>\n
The postgresql.conf<\/code> file is located at the \/etc\/postgresql\/<\/code> location. For example, if you are using PostgreSQL 17, the absolute url of postgresql.conf<\/code> will be \/etc\/postgresql\/17\/main\/postgresql.conf<\/code>.<\/p>\n
What is the location of pg_hba.conf file?<\/h3>\n
The pg_hba.conf<\/code> file is located at the \/etc\/postgresql\/ <\/code>location. For example, if you are using PostgreSQL 17, the absolute url of pg_hba.conf<\/code> would be \/etc\/postgresql\/17\/main\/pg_hba.conf <\/code>.<\/p>\n
Conclusion<\/h2>\n
This guide looked at the steps to install PostgreSQL on a Ubuntu server.<\/p>\n
For the Redhat server, Checkout the PostgreSQL installation on Redhat<\/a><\/p>\n
For the Amazon Linux server, Checkout the PostgreSQL installation on Amazon Linux<\/a><\/p>\n
Also, if you are using Kubernetes<\/a>, check out my guide on setting up PostgreSQL statefulset on Kubernetes<\/a><\/p>\n
As a DevOps engineer, it is essential to know the basic configurations involved in a Database.<\/p>\n
If you are starting your DevOps engineer journey, look at my comprehensive guide to becoming a DevOps engineer<\/a>.<\/p>\n
\n
Ngu\u1ed3n:<\/strong> How to Install PostgreSQL on Ubuntu [Setup & Configurations] \u2014 DevOpsCube<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Source: https:\/\/devopscube.com\/install-postgresql-on-ubuntu\/<\/p>\n","protected":false},"author":1,"featured_media":541,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-540","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-devops"],"_links":{"self":[{"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/posts\/540","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=540"}],"version-history":[{"count":0,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/posts\/540\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/media\/541"}],"wp:attachment":[{"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=540"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=540"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=540"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}