{"id":2215,"date":"2025-06-19T07:09:26","date_gmt":"2025-06-19T07:09:26","guid":{"rendered":"https:\/\/serverhub.com\/kb\/?p=2215"},"modified":"2025-06-19T19:37:34","modified_gmt":"2025-06-19T19:37:34","slug":"introduction-to-postgresql-advanced-open-source-database","status":"publish","type":"post","link":"https:\/\/serverhub.com\/kb\/introduction-to-postgresql-advanced-open-source-database\/","title":{"rendered":"Introduction to PostgreSQL: Advanced Open-source Database"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" width=\"1024\" height=\"597\" src=\"https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Blog-Article-2-Post-1024x597.jpg\" alt=\"\" class=\"wp-image-2236\" srcset=\"https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Blog-Article-2-Post-1024x597.jpg 1024w, https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Blog-Article-2-Post-300x175.jpg 300w, https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Blog-Article-2-Post-768x448.jpg 768w, https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Blog-Article-2-Post.jpg 1200w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>PostgreSQL is a robust, open-source object-relational database management system (ORDBMS), commonly referred to as Postgres. PostgreSQL has been actively developed for more than 30 years, and is well known for its scalability, robustness, and conformance with standards. It is a popular option for developers, data engineers, and businesses worldwide because it offers sophisticated data types, complicated queries, full-text search, and transactional integrity.<\/p>\n\n\n\n<p>Whether you&#8217;re developing a big data-driven platform or a little web application, PostgreSQL provides the adaptability and dependability required to manage challenging workloads with ease.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>When to Use PostgreSQL?<\/strong><\/h2>\n\n\n\n<p>Postgre SQL is a great fit in the following scenarios:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>1. You Need Complex Queries or Large Data Sets<\/strong><\/li>\n\n\n\n<li>Supports advanced joins, window functions, CTEs, and stored procedures.<\/li>\n\n\n\n<li>Handles large-scale datasets efficiently with indexing, partitioning, and parallel processing.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>2. Data Integrity and Reliability Are Critical<\/strong><\/li>\n\n\n\n<li>Fully ACID compliant, ensuring reliable transactions.<\/li>\n\n\n\n<li>Great for banking, finance, e-commerce, or any app where data accuracy is essential.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>3. You&#8217;re Working with JSON or Semi-Structured Data<\/strong><\/li>\n\n\n\n<li>Supports JSON &amp; JSONB (binary JSON), enabling you to store and query semi-structured data.<\/li>\n\n\n\n<li>Acts almost like a hybrid relational + NoSQL database.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>4. You Want an Open-Source, Extensible Database<\/strong><\/li>\n\n\n\n<li>100% free and open source.<\/li>\n\n\n\n<li>You can create custom data types, functions, and extensions (like PostGIS for geospatial data or TimescaleDB for time-series data).<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>5. You Need Full-Text Search<\/strong><\/li>\n\n\n\n<li>Built-in support for full-text indexing and search, useful for content-heavy apps like blogs, CMS platforms, or product catalogs.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>6. You&#8217;re Deploying on Cloud or Need High Availability<\/strong><\/li>\n\n\n\n<li>Integrates well with cloud platforms (AWS RDS, GCP Cloud SQL, Azure).<\/li>\n\n\n\n<li>Supports replication, failover, and point-in-time recovery.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>7. You Want Standards Compliance<\/strong><\/li>\n\n\n\n<li>PostgreSQL adheres closely to the SQL standard, which ensures long-term portability and maintainability.<\/li>\n<\/ul>\n\n\n\n<p><strong>When PostgreSQL Might <em>Not<\/em> Be Ideal to Use:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If you need horizontal scaling with millions of writes per second across nodes, a NoSQL solution like Cassandra may suit you better.<\/li>\n\n\n\n<li>If you want a lightweight embedded database, SQLite might be better.<\/li>\n\n\n\n<li>For small apps where you don\u2019t need advanced features, a simpler database like MySQL might be easier to manage.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>How to Install PostgreSQL on MacBook (macOS)<\/strong><\/h2>\n\n\n\n<p><strong><em>Option 1: Using Homebrew (Recommended)<\/em><\/strong><\/p>\n\n\n\n<p>Homebrew is the easiest and most popular way to install PostgreSQL on macOS. Do the following steps and use the commands in each step:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>1. Install Homebrew (if not already installed):<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">\/bin\/bash -c &#8220;$(curl -fsSL https:\/\/raw.githubusercontent.com\/Homebrew\/install\/HEAD\/install.sh)&#8221;<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>2. Update Homebrew and install PostgreSQL:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">brew update<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">brew install PostgreSQL<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>3. Start PostgreSQL service:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">brew services start postgresql<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>4. Verify the installation:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">psql &#8211;version<\/mark><\/li>\n\n\n\n<li>PostgreSQL should now be running on the default port 5432. You can access the interactive terminal with the following command: <mark class=\"has-inline-color has-accent-color\">psql postgres<\/mark><\/li>\n<\/ul>\n\n\n\n<p><strong><em>Option 2: Using PostgreSQL Official Installer<\/em><\/strong><\/p>\n\n\n\n<p>You can also download a GUI installer from the official PostgreSQL website. Do the following steps:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to: https:\/\/www.postgresql.org\/download\/macosx\/<\/li>\n\n\n\n<li>Download the installer provided by EDB.<\/li>\n\n\n\n<li>Follow the installation wizard (you\u2019ll be able to set a password for the default &#8216;postgres&#8217; user).<\/li>\n\n\n\n<li><strong>Note:<\/strong> This option installs pgAdmin as well, a graphical interface for managing your database.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>How to Install PostgreSQL on Ubuntu?<\/strong><\/h2>\n\n\n\n<p><em><strong>Step-by-Step Guide for Installing PostgreSQL on Ubuntu. Do the steps below and use the commands in each step:<\/strong><\/em><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>1. Update the package list:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">sudo apt update<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>2. Install PostgreSQL and the additional modules:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">sudo apt install postgresql postgresql-contrib<\/mark><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img decoding=\"async\" width=\"588\" height=\"275\" src=\"https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Install-PostgreSQL-1.jpg\" alt=\"\" class=\"wp-image-2227\" style=\"width:745px;height:auto\" srcset=\"https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Install-PostgreSQL-1.jpg 588w, https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Install-PostgreSQL-1-300x140.jpg 300w\" sizes=\"(max-width: 588px) 100vw, 588px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>3. Start the PostgreSQL service:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">sudo systemctl start PostgreSQL<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>4. Enable PostgreSQL to start on boot:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">sudo systemctl enable postgresql<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>5. Verify the installation:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">psql &#8211;version<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>6. Switch to the PostgreSQL default user:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">sudo -i -u postgres<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>7. Access PostgreSQL shell:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">psql<\/mark><\/li>\n\n\n\n<li>Now you&#8217;re inside the PostgreSQL terminal. <strong>Type \\q to exit<\/strong>.<\/li>\n<\/ul>\n\n\n\n<p><strong><em>Optional: Allow Remote Connections<\/em><\/strong><\/p>\n\n\n\n<p><strong>To allow remote connections, do the steps below and use the following commands in each step:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>1. Edit postgresql.conf:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">sudo nano \/etc\/postgresql\/14\/main\/postgresql.conf<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">Set listen_addresses = &#8216;*&#8217;<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>2. Edit pg_hba.conf:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">sudo nano \/etc\/postgresql\/14\/main\/pg_hba.conf<\/mark><\/li>\n\n\n\n<li>Add: <mark class=\"has-inline-color has-accent-color\">host all all 0.0.0.0\/0 md5<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>3. Restart PostgreSQL:<\/strong><\/li>\n\n\n\n<li>sudo systemctl restart PostgreSQL<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>4. Verify the software\u2019s status to ensure installation and activation occurred without errors:<\/strong><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">systemctl status postgresql<\/mark><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img decoding=\"async\" width=\"624\" height=\"211\" src=\"https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Allow-Remote-Connection.jpg\" alt=\"\" class=\"wp-image-2228\" style=\"width:745px;height:auto\" srcset=\"https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Allow-Remote-Connection.jpg 624w, https:\/\/serverhub.com\/kb\/wp-content\/uploads\/2025\/06\/Allow-Remote-Connection-300x101.jpg 300w\" sizes=\"(max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Creating Users and Databases:<\/strong><\/h2>\n\n\n\n<p><strong>For the following procedures, use the commands in each step:<\/strong><\/p>\n\n\n\n<p><strong>Create a PostgreSQL User:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>To create a new user:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">sudo -u postgres createuser &#8211;interactive<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You can also use SQL:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">CREATE USER username WITH PASSWORD &#8216;yourpassword&#8217;;<\/mark><\/li>\n<\/ul>\n\n\n\n<p><strong>Create a PostgreSQL DB:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Once a user is created, create a new database:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">CREATE DATABASE mydatabase OWNER username;<\/mark><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Connect to it with:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">psql -d mydatabase -U username<\/mark><\/li>\n<\/ul>\n\n\n\n<p><strong>Using PostgreSQL Features<\/strong>:<\/p>\n\n\n\n<p>For the following procedures, use the commands in each step:<\/p>\n\n\n\n<p><strong>PostgreSQL COALESCE<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The COALESCE function returns the first non-null value in a list:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">SELECT COALESCE (NULL, NULL, &#8216;default&#8217;, &#8216;another&#8217;) AS result;<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">&#8212; Returns &#8216;default&#8217;<\/mark><\/li>\n<\/ul>\n\n\n\n<p><strong>PostgreSQL FOR UPDATE<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Used to lock rows selected by a query until the transaction is completed:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">BEGIN;<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">SELECT * FROM accounts WHERE id = 1 FOR UPDATE;<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">&#8212; Perform updates here<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">COMMIT;<\/mark><\/li>\n<\/ul>\n\n\n\n<p><strong>PL\/pgSQL Language<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>PostgreSQL supports PL\/pgSQL, a procedural language for writing functions and triggers:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">BEGIN<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">RETURN a + b;<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">END;<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">$$ LANGUAGE plpgsql;<\/mark><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>PostgreSQL with Docker &amp; GIS<\/strong><\/h2>\n\n\n\n<p><strong>Postgres Docker GIS<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>PostgreSQL with PostGIS (a spatial database extender) using Docker:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">docker run &#8211;name postgis -e POSTGRES_PASSWORD=mysecretpassword \\<\/mark><\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">-d -p 5432:5432 postgis\/postgis<\/mark><\/li>\n<\/ul>\n\n\n\n<p><strong>PostgREST on Docker Hub<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>PostgREST is a tool that creates a REST API from any PostgreSQL database:<\/li>\n\n\n\n<li><mark class=\"has-inline-color has-accent-color\">docker pull postgrest\/postgrest<\/mark><\/li>\n<\/ul>\n\n\n\n<p><strong>Note:<\/strong> You can visit Docker Hub site for more info: https:\/\/hub.docker.com\/r\/postgrest\/postgrest<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Conclusion<\/strong>:<\/h2>\n\n\n\n<p>PostgreSQL is an incredibly versatile and feature-rich database system. From installing it on your MacBook or Ubuntu machine to working with advanced SQL features and deploying GIS or RESTful APIs via Docker, PostgreSQL can support a wide range of applications. Whether you&#8217;re a developer or a data professional, PostgreSQL is a tool you can rely on.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>References:<\/strong><\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><a href=\"macOS packages\" title=\"https:\/\/www.postgresql.org\/download\/macosx\/\">PostgreSQL: macOS packages<\/a><\/li>\n\n\n\n<li><a href=\"Linux downloads (Ubuntu)\" title=\"https:\/\/www.postgresql.org\/download\/linux\/ubuntu\/\">PostgreSQL: Linux downloads (Ubuntu)<\/a><\/li>\n\n\n\n<li><a href=\"createuser\" title=\"https:\/\/www.postgresql.org\/docs\/current\/app-createuser.html\">PostgreSQL: Documentation: 17: createuser<\/a><\/li>\n\n\n\n<li><a href=\"CREATE DATABASE\" title=\"https:\/\/www.postgresql.org\/docs\/current\/sql-createdatabase.html\">PostgreSQL: Documentation: 17: CREATE DATABASE<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/postgis.net\/documentation\/getting_started\/install_docker\/\" title=\"\">Installing on Docker | PostGIS<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/current\/plpgsql.html\" title=\"\">PostgreSQL: Documentation: 17: Chapter 41. PL\/pgSQL \u2014 SQL Procedural Language<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/stormatics.tech\/blogs\/ensuring-safe-data-modifications-in-postgresql-with-select-for-update\" title=\"\">Ensuring Safe Data Modifications in PostgreSQL with SELECT FOR UPDATE &#8211; Stormatics<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.geeksforgeeks.org\/postgresql-coalesce\/\" title=\"\">PostgreSQL COALESCE | GeeksforGeeks<\/a><\/li>\n<\/ol>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL is a robust, open-source object-relational database management system (ORDBMS), commonly referred to as Postgres. PostgreSQL has been actively developed for more than 30 years, and is well known for its scalability, robustness, and conformance with standards. It is a popular option for developers, data engineers, and businesses worldwide because it offers sophisticated data types, &#8230; <a title=\"Introduction to PostgreSQL: Advanced Open-source Database\" class=\"read-more\" href=\"https:\/\/serverhub.com\/kb\/introduction-to-postgresql-advanced-open-source-database\/\" aria-label=\"More on Introduction to PostgreSQL: Advanced Open-source Database\">Read more<\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2215","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/posts\/2215","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/comments?post=2215"}],"version-history":[{"count":19,"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/posts\/2215\/revisions"}],"predecessor-version":[{"id":2239,"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/posts\/2215\/revisions\/2239"}],"wp:attachment":[{"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/media?parent=2215"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/categories?post=2215"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/serverhub.com\/kb\/wp-json\/wp\/v2\/tags?post=2215"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}