Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQ8aT-00GlAz-Ia for pgsql-general@arkaria.postgresql.org; Wed, 17 Jan 2024 16:16:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rQ8aS-00EMFt-OZ for pgsql-general@arkaria.postgresql.org; Wed, 17 Jan 2024 16:16:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQ8aS-00EMF3-4T for pgsql-general@lists.postgresql.org; Wed, 17 Jan 2024 16:16:20 +0000 Received: from antelope.elm.relay.mailchannels.net ([23.83.212.4]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQ8aO-002Jmx-PO for pgsql-general@lists.postgresql.org; Wed, 17 Jan 2024 16:16:19 +0000 X-Sender-Id: dreamhost|x-authsender|pyromania@guungle.com Received: from relay.mailchannels.net (localhost [127.0.0.1]) by relay.mailchannels.net (Postfix) with ESMTP id 2FA197A3E4D for ; Wed, 17 Jan 2024 16:16:14 +0000 (UTC) Received: from pdx1-sub0-mail-a290.dreamhost.com (unknown [127.0.0.6]) (Authenticated sender: dreamhost) by relay.mailchannels.net (Postfix) with ESMTPA id 8B72D7A3B8A for ; Wed, 17 Jan 2024 16:16:13 +0000 (UTC) ARC-Seal: i=1; s=arc-2022; d=mailchannels.net; t=1705508173; a=rsa-sha256; cv=none; b=Xo7DeLvvwJgaSwWwpNFR+W/CsWAKR2hwuCFn5kCI93bAFHyTudB7Cmum4O3k+zLLjeoGfY AnYDNrq9DCsWn8GxqP2zKl3PvCcl7zYAh1FoeoxwPLX9Q6klGpEuDC2TaTJVcI3yMorm5v EdfgHyo3zvUZ4MgCRufcXSectNHwJoh3I4ym6ed5HxTSmFMrOaMnvPSB2fQSYwTipOS2mC HmiUSv68BFBAokzCQvHFsmDZ67g2lSIf+QHTMlQfKpxt/O1KRoC1uVHj143orvp7njV/M2 1YMmV7qAczIqjyEpdq99O6HfdQBTtXC0YzRP2mhbV2KVcL3F48DaAxVAn3WlGA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=mailchannels.net; s=arc-2022; t=1705508173; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding:dkim-signature; bh=ApYZyfn0oZric128IL0aUJAS0cN+aO0wV0UCYMjBNRU=; b=NOBR/gJ/r8Et2sug051+NFgulnZ1oKU1bVV5jeg3oIMPuOUNIPi5mJ8taK16RvJANW4MgX 5hLOpUWY4oW/fJRc/VVu5VCWvoeqPdQq6iPA6l3KnTOBWzZXf8V4YvWMjjg8U+VmnRHROU 0tHVYJPrOxNu9J8DjhZXSdWlkucq4mxcowPLi/r+jNITVxIoKXPwz0KmZSPqBdBhNefYF3 1swmRTNbaaI6Q35d9lPwy+Yk4F8v+OcGRp5eHbPghM+rc88kCksTVXWlLchDjkO+KgIKjs RUhEUD/PHrCsdPXVKPLJw3NWaxplalwwRd9Rs+l1SOHhDiW+suORC9KfvUd6uQ== ARC-Authentication-Results: i=1; rspamd-88587c4b9-ztm8c; auth=pass smtp.auth=dreamhost smtp.mailfrom=pyromania@guungle.com X-Sender-Id: dreamhost|x-authsender|pyromania@guungle.com X-MC-Relay: Neutral X-MailChannels-SenderId: dreamhost|x-authsender|pyromania@guungle.com X-MailChannels-Auth-Id: dreamhost X-Zesty-Stretch: 124ddd4f1262b0d1_1705508173981_1761318066 X-MC-Loop-Signature: 1705508173981:2491861823 X-MC-Ingress-Time: 1705508173981 Received: from pdx1-sub0-mail-a290.dreamhost.com (pop.dreamhost.com [64.90.62.162]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384) by 100.99.140.183 (trex/6.9.2); Wed, 17 Jan 2024 16:16:13 +0000 Received: from webmail.dreamhost.com (ip-66-33-200-4.dreamhost.com [66.33.200.4]) (Authenticated sender: pyromania@guungle.com) by pdx1-sub0-mail-a290.dreamhost.com (Postfix) with ESMTPA id 4TFWFT5XjDz5j for ; Wed, 17 Jan 2024 08:15:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=guungle.com; s=dreamhost; t=1705508141; bh=ApYZyfn0oZric128IL0aUJAS0cN+aO0wV0UCYMjBNRU=; h=Date:From:To:Subject:Content-Type:Content-Transfer-Encoding; b=qdw83bqpM+5DQMPwIwQLWBbbtSxaMAmcBm9LG/wvhC/Sk5SnNyxsvPHQqPbUGuAJb kp42XxeyYxXBqcE3L/OnrFu3Yg31dh2hBCc0m1zAqo5sVzCaXqrha7wL+8IJ/fjh7b w1UbQouji44Wffz3HDJ3LUlbcBT8ko11pEtcpbpuCBJ8MB9Pp8wcPnk+Wvtm+nEVZP hbKEGJk9MJ4QYZKphZc8QzrbVmAQDrJY8yh4JqvOZRSfqB8+dQp73LDS1UdQRDf7Gq Xr4Lw0YN5AOBxxeP31YcdFqxz5UgLXYLIGUReQWkoysXEKDxShRwnW3UrggXW+taDZ /mawYzV2jxfbw== MIME-Version: 1.0 Date: Wed, 17 Jan 2024 10:15:41 -0600 From: Pyrote To: pgsql-general@lists.postgresql.org Subject: Re: Initiate backup from routine? User-Agent: Roundcube Webmail/1.4.3 Message-ID: X-Sender: pyromania@guungle.com Content-Type: text/plain; charset=US-ASCII; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wednesday, January 17, 2024, Troels Arvin wrote: > Is it possible to call pg_dump (or equivalent action) through a > procedure/function? You could create a new schema and table that holds a flag column or a simple queue. Then setup a script/app on a 5 minute cron that checks the status of the flag or queue to see if it needs to start a backup. The script/app could update the table to show the user that the backup has started and when its finished. The user account on the DB server that runs the code would have their shell set to /bin/false so they cannot login. The DB account used by the code would have the minimum privileges to perform the backup. Or if it needs to be more real-time, the app could use LISTEN/NOTIFY to trigger the backup process. Then use cron on some interval to make sure the app is always running. Then you just need the procedure to update the flag/queue or send a NOTIFY message.