From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp1 ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms11 with LMTPS id JQImHFTLkF95VAAA0tVLHw (envelope-from ) for ; Wed, 21 Oct 2020 23:59:16 +0000 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp1 with LMTPS id aAx5F1TLkF+eRwAAbx9fmQ (envelope-from ) for ; Wed, 21 Oct 2020 23:59:16 +0000 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id E1AB2940105 for ; Wed, 21 Oct 2020 23:59:14 +0000 (UTC) Received: from localhost ([::1]:34932 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kVO0e-00070f-Te for larch@yhetil.org; Wed, 21 Oct 2020 19:59:12 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:49174) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kVO0H-00070K-6Q for emacs-orgmode@gnu.org; Wed, 21 Oct 2020 19:58:49 -0400 Received: from mail-pj1-x102b.google.com ([2607:f8b0:4864:20::102b]:50218) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1kVO0B-0003TA-NA for emacs-orgmode@gnu.org; Wed, 21 Oct 2020 19:58:46 -0400 Received: by mail-pj1-x102b.google.com with SMTP id p21so8746pju.0 for ; Wed, 21 Oct 2020 16:58:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=references:user-agent:from:to:subject:in-reply-to:message-id:date :mime-version; bh=2P+OMOw+1QH+b/HKCjEob3xI5186g5wE2wg+Jvph8DQ=; b=rLG8jBpFBOCgrZA0QaEv2FWkfZM89pV/uzjvThl+k9ZLWRsr7rwznZqd+l2yNSakKw eyIijsMOZBKD23/xs0zNi6edOz/qehYO13UCrgyjyWRX5uTvSWPc9OgtQbD9L1Xheq4j Lwi9UQ5e/zIl4YscNPkt8/wNzH0EKsrR8Bdq1zocTiu6HMp77fnGMs0Wb/N10vlBlQ6C 3p6j4UPsGTdkA8kzzSYRHBK0rAGKBNJIaMqV/2DHZDJHCGXaycyXQGhAeCHHNmiGDFpB EctLP106VYtISGNkjYexl8JqGQL5qM2bTtig8oANHC6vKCZHM/9qR+EwrkupQ3mOx+IU feSw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:references:user-agent:from:to:subject :in-reply-to:message-id:date:mime-version; bh=2P+OMOw+1QH+b/HKCjEob3xI5186g5wE2wg+Jvph8DQ=; b=SNjcCGZgaT5LXjH/pOzqqu4Gy00ggexdJVGYQxIK0Tnl2Anfxh0PDqWiXjKbT0OyI5 1IHy/Vhah0djEuNK/7vzOKOWEtxHW8Wf862HGvZCeLD7ESE9TkeymrG5TryEbJT1J7jm B9FstqUsiTW+J4VkFTiGZ8epNxCcuxVdwW5dgQ28NjY4+vJNhaVOgwUrXrl8Fbgu2GGe +JxrKYTwWxVUZ5lY2yPGIfhVgYkEgUXGZ9VkV4j3Lnjd3+1f3JKkBvfTC1EytRU200r3 oLjjS0gY3/s6kVbXXOEcqVUOca+t1ZhsB2Fe0nETKEITa3+vOus38aLuBe1Ed6Q6DNQv lqiA== X-Gm-Message-State: AOAM531HgEMoys6VTbqRakKFpV5+DwzGUjD/SKe79Ox7tkIHFlru3fm7 B/ioVCZcmQN+kLvSt9UzLFv5uyNuuNpceg== X-Google-Smtp-Source: ABdhPJw3d6XgsPmYQPU3AS9KfvZjzVwc7iNHAG9AhVPEQVdvPW7xwmRnWbSTy4mD/FedumCvnFQ0NQ== X-Received: by 2002:a17:90a:ab18:: with SMTP id m24mr71622pjq.182.1603324718455; Wed, 21 Oct 2020 16:58:38 -0700 (PDT) Received: from tim-desktop (106-69-85-59.dyn.iinet.net.au. [106.69.85.59]) by smtp.gmail.com with ESMTPSA id u65sm3414692pfc.11.2020.10.21.16.58.36 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 21 Oct 2020 16:58:37 -0700 (PDT) References: User-agent: mu4e 1.5.6; emacs 27.1 From: Tim Cross To: emacs-orgmode@gnu.org Subject: Re: Combining ob-sql, docker-tramp, and tramp ssh? In-reply-to: Message-ID: <87r1pri11y.fsf@gmail.com> Date: Thu, 22 Oct 2020 10:58:33 +1100 MIME-Version: 1.0 Content-Type: text/plain Received-SPF: pass client-ip=2607:f8b0:4864:20::102b; envelope-from=theophilusx@gmail.com; helo=mail-pj1-x102b.google.com X-detected-operating-system: by eggs.gnu.org: No matching host in p0f cache. That's all we know. X-Spam_score_int: -20 X-Spam_score: -2.1 X-Spam_bar: -- X-Spam_report: (-2.1 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001, SPF_HELO_NONE=0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: "Emacs-orgmode" X-Scanner: scn0 Authentication-Results: aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20161025 header.b=rLG8jBpF; dmarc=pass (policy=none) header.from=gmail.com; spf=pass (aspmx1.migadu.com: domain of emacs-orgmode-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=emacs-orgmode-bounces@gnu.org X-Spam-Score: -0.71 X-TUID: dYV1zYqgUJkY I have done something very similar, but with a few differences which might help. In my case, I didn't use tramp, but instead used ssh tunnels that I defined in my ssh config file e.g. Host wdb-dev HostName dev-example.com User fred LocalForward localhost:3330 localhost:5432 Host wdb-prod HostName prod-example.com User fred LocalForward localhost:3331 localhost:5432 This allows me to run psql using the -p switch to set the port e.g. 3330 or 3331 with same user name and database name. I then used sql's connection alist to define connection names for the different databases. You could probably use a .dir-locals.el file to do the same thing on a per directory/project basis. I would then open a terminal for each connection I needed and do ssh wdb-dev or ssh wdb-prod to setup the tunneling. The downside of this setup is that if you lose the ssh connection, you lose connection to the host and will need to restart it for things to work (where with the tramp setup it will make the connection when needed). The advantage is that I could use other tools, like psql or dbbeaver/pgadmin/whatever at the same time. If you want things to work in such a way that your org sql blocks all use the same database name, you can just adjust the ssh config to use the same port number, but then you can only have one connection active at a time. However, this means you can setup things to connect to your dev system, refine and test all your sql blocks and once confident they are working, just change the ssh connection to point to the prod system and re-run. Jay Zawrotny writes: > I've been using ob-sql to document & prune our dev db and it's been > amazing. > > https://media.discordapp.net/attachments/428916969861808130/768589313964507166/image0.gif > > The only rough edge has been that I have to expose the SQL ports to my > host, forward it over ssh (I'm using a remote machine to offload docker > resources), then install a version of psql required to connect. Lastly I > then need to symlink psql to point to the targeted version of psql I need. > > This means I can only match one setup at a time. A preferred route would be > to use the :dir property to eval over tramp like: > > #+begin_src sql :dir "/sshx:user@devbox|docker:user@vm:/src" > SELECT count(id) from accounts; > #+end_src > > If I use Python it works, but something specific about this combo is not > working. Any suggestions to fix this, personal config, docs, or debugging > steps one could point me to? -- Tim Cross