{"id":1424,"date":"2024-07-09T05:37:21","date_gmt":"2024-07-09T05:37:21","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1424"},"modified":"2024-07-09T05:37:22","modified_gmt":"2024-07-09T05:37:22","slug":"running-postgres-and-pgadmin-through-docker-on-a-dodgy-linux-vm","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1424","title":{"rendered":"Running Postgres and pgadmin through Docker on a Dodgy Linux VM"},"content":{"rendered":"\n<p>In the organisation that I work in; I have a good old Windows 10 ThinkPad that has been my work horse for many years.&nbsp;<\/p>\n\n\n\n<p>It does the job and works happily with our on Prem apps and I can do most of my role\u2019s development on it.<\/p>\n\n\n\n<p>There are areas where the work horse cannot help me out.\u00a0 For instance, I needed to develop a JavaScript function on a Postgres database to split a field into different elements.\u00a0 I do not have access to be able to develop on the target database; so, I turned to Docker to run a containerised version of Postgres and pgadmin to have a safe area to play in.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The dreaded Linux VM<\/h2>\n\n\n\n<p>The \u201ccool\u201d developers have access to Macs to run their DevOps tools on.&nbsp;<\/p>\n\n\n\n<p>I have a Linux VM, running Ubuntu 20.04 on.<\/p>\n\n\n\n<p>It loads slow, it runs slow and the support VM application hogs a significant amount of the memory available, leaving little left for me.&nbsp;<\/p>\n\n\n\n<p>But does allow me to run Docker containers.<\/p>\n\n\n\n<p>The first container I created; broke the VM.\u00a0 The VM support team speculated that a port for Postgres or pgadmin broke the organisation\u2019s VM ports.\u00a0 They rebuilt my VM and I tried again.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">docker-compose.yml<\/h2>\n\n\n\n<p>This is my docker-compose.yml file for Postgres and pgadmin<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: yaml; title: ; notranslate\" title=\"\">\nversion: &#039;3.3&#039;\n\nservices:\n  db:\n    #image: postgres\n    image: clkao\/postgres-plv8\n    container_name: local_pgdb\n    restart: always\n    ports:\n      - &quot;9432:5432&quot;\n    environment:\n      - POSTGRES_PASSWORD=verystrongpassword\n      - POSTGRES_USER=jonny \n      - POSTGRES_DB=work\n\n    volumes:\n      #- ~\/apps\/postgres:\/var\/lib\/postgresql\/data\n      - ~\/apps\/postgres-plv8:\/var\/lib\/postgresql\/data\n    \n  pgadmin:\n    image: dpage\/pgadmin4\n    container_name: pgadmin4_container\n    restart: always\n    ports:\n      - &quot;9888:80&quot;\n      - &quot;9443:443&quot;\n   \n    environment:\n       PGADMIN_DEFAULT_EMAIL: jonny@craftcookcode.com\n       PGADMIN_DEFAULT_PASSWORD: verystrongpassword\n       \n       # Fix for IPv6-disabled systems https:\/\/stackoverflow.com\/questions\/68766411\/pgadmin-4-in-docker-failed-with-gunicorn-server-start-error\n       PGADMIN_LISTEN_ADDRESS: 0.0.0.0\n\n    volumes:\n      - ~\/apps\/pg_admin\/pgadmin-data:\/var\/lib\/pgadmin\n\nvolumes:\n  local_pgdata:\n<\/pre><\/div>\n\n\n<p>There a couple of changes from the boiler plate docker-compose.yml files on the internet:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The ports are mapped to non-standard ports.  This is to avoid any potential problems with ports conflicting with the VM software<\/li>\n\n\n\n<li>I had to change the volumes to my home drive due to security settings on my VM <\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Errors, Problems and Issues (Oh my)<\/h2>\n\n\n\n<p>When initially running the docker-compose; I got the following error and pgadmin wouldn&#8217;t start.  <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\npgadmin4_container | ERROR  : Failed to create the directory \/var\/lib\/pgadmin\/sessions:\npgadmin4_container |            &#x5B;Errno 13] Permission denied: &#039;\/var\/lib\/pgadmin\/sessions&#039;\npgadmin4_container | HINT   : Create the directory \/var\/lib\/pgadmin\/sessions, ensure it is writeable by\npgadmin4_container |          &#039;pgadmin&#039;, and try again, or, create a config_local.py file\npgadmin4_container |          and override the SESSION_DB_PATH setting per\npgadmin4_container |          https:\/\/www.pgadmin.org\/docs\/pgadmin4\/8.9\/config_py.html\n\n<\/pre><\/div>\n\n\n<p>This issue was resolved from an article from a <a href=\"https:\/\/stackoverflow.com\/questions\/64781245\/permission-denied-var-lib-pgadmin-sessions-in-docker\" target=\"_blank\" rel=\"noopener\" title=\"\">Stack Overflow<\/a> thread by changing the ownership of the pg_admin trigger to 5050<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\nsudo chown -R 5050:5050 ~\/apps\/pg_admin\n<\/pre><\/div>\n\n\n<p>The next error I had was a &#8220;Can&#8217;t connect to (&#8216;::&#8217;, 80)&#8221; error in pgadmin<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\npgadmin4_container | &#x5B;2024-07-09 05:25:58 +0000] &#x5B;1] &#x5B;INFO] Starting gunicorn 22.0.0\npgadmin4_container | &#x5B;2024-07-09 05:25:58 +0000] &#x5B;1] &#x5B;ERROR] Retrying in 1 second.\npgadmin4_container | &#x5B;2024-07-09 05:25:59 +0000] &#x5B;1] &#x5B;ERROR] Retrying in 1 second.\npgadmin4_container | &#x5B;2024-07-09 05:26:00 +0000] &#x5B;1] &#x5B;ERROR] Retrying in 1 second.\npgadmin4_container | &#x5B;2024-07-09 05:26:01 +0000] &#x5B;1] &#x5B;ERROR] Retrying in 1 second.\npgadmin4_container | &#x5B;2024-07-09 05:26:02 +0000] &#x5B;1] &#x5B;ERROR] Retrying in 1 second.\npgadmin4_container | &#x5B;2024-07-09 05:26:03 +0000] &#x5B;1] &#x5B;ERROR] Can&#039;t connect to (&#039;::&#039;, 80)\n\n<\/pre><\/div>\n\n\n<p>Again Google and Stack Overflow came to the rescue.  The issue was potentially caused if IPv6 is disabled on the VM.  I added in the the following line to the yml file:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: yaml; title: ; notranslate\" title=\"\">\nPGADMIN_LISTEN_ADDRESS: 0.0.0.0\n<\/pre><\/div>\n\n\n<p>This resolved the issue and now pgadmin could start up.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Inside pgadmin<\/h3>\n\n\n\n<p>When I got inside pgadmin; for the life of me I couldn&#8217;t connect to the Postgres database.  <\/p>\n\n\n\n<p>I could see that the Postres container was running with no errors.  I could see the remapped ports.  I could connect to Postgres with psql.  Why couldn&#8217;t I connect to the Postgres in pgadmin?<\/p>\n\n\n\n<p>I was frustrated and tired after a long day of work and had walked away from the computer.<\/p>\n\n\n\n<p>When I got back after a walk around the block and a cup of tea &#8211; I could now see the problem and the solution:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1024\" height=\"439\" src=\"https:\/\/craftcookcode.com\/wp-content\/uploads\/2024\/07\/code_pgadmin-1024x439.png\" alt=\"\" class=\"wp-image-1427\" srcset=\"https:\/\/craftcookcode.com\/wp-content\/uploads\/2024\/07\/code_pgadmin-1024x439.png 1024w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2024\/07\/code_pgadmin-300x129.png 300w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2024\/07\/code_pgadmin-768x329.png 768w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2024\/07\/code_pgadmin-1536x659.png 1536w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2024\/07\/code_pgadmin.png 1760w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Initially I was using &#8220;Hostname&#8221; as 127.0.0.1 and port as 9432.  Because that&#8217;s where my mind went to how to connect to the Postgres database running locally.  <\/p>\n\n\n\n<p>But because pgadmin is trying to access Postgres from within the docker network; it will be looking for port <strong><em>5432 <\/em><\/strong>instead of <strong><em>9432<\/em><\/strong> and the container name <strong><em>local_pgdb <\/em><\/strong>instead of <strong><em>127.0.0.1<\/em><\/strong><\/p>\n\n\n\n<p>If I am running from outside the docker; I would use localhost and port 9432.  For instance I imported some data to develop off:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\npsql --host localhost --port 9432 --username jonny -d work -f ~\/some_postgres_data.dmp\n<\/pre><\/div>\n\n\n<p>Once I entered the right details; pgadmin works fine connecting and I could develop the Postgres function in a safe area.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the organisation that I work in; I have a good old Windows 10 ThinkPad that has been my work horse for many years.&nbsp; It does&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":1429,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13,64],"tags":[65,66,56],"class_list":["post-1424","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-code","category-postgres","tag-docker","tag-pgadmin","tag-postgres"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1424","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1424"}],"version-history":[{"count":7,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1424\/revisions"}],"predecessor-version":[{"id":1433,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1424\/revisions\/1433"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1429"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}