From: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
To: 'depesz@depesz.com' <depesz@depesz.com>
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: AW: Truncate data from whole cluster
Date: Wed, 20 Dec 2023 11:39:40 +0000
Message-ID: <e087df8b0c194a5fa64c625a9cf9cdee@med.uni-muenchen.de> (raw)
In-Reply-To: <ZYLArRMPsNu3ZooU@depesz.com>
References: <CAJk5Atah42SXmNFDJmP22XtTeov+aKfSSkzCAAqXYzMe0En-8A@mail.gmail.com>
<ZYLArRMPsNu3ZooU@depesz.com>
Hi all,
if you ask Bard LLM do do this you get:
#!/bin/bash
# List all databases in the PostgreSQL instance databases=$(psql -t -c "SELECT datname FROM pg_database;")
# Truncate table data for each database
for database in $databases; do
echo "Processing database '$database'..."
psql -d $database -c "SET FOREIGN_KEY_CHECKS=0;"
tables=$(psql -d $database -t -c "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public';")
for table in $tables; do
echo "Truncating table '$table' in database '$database'..."
psql -d $database -c "TRUNCATE TABLE $table;"
done
psql -d $database -c "SET FOREIGN_KEY_CHECKS=1;"
done
This may be a good script to start.
You will definetly test this first on a test installation!
Best,
Anton
-----Ursprüngliche Nachricht-----
Von: depesz@depesz.com <depesz@depesz.com>
Gesendet: Mittwoch, 20. Dezember 2023 11:24
An: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Betreff: Re: Truncate data from whole cluster
On Wed, Dec 20, 2023 at 02:35:52PM +0530, Rajesh Kumar wrote:
> Hi all,
>
> I am using postgres 15.2. one leader and one replica. I have multiple
> dbs and multiple tables. I want to keep definitions and only want to
> delete 'data only' from all dbs and tables.
>
> Any command to do that instead of truncating all tables one by one?
No such command exists. For starters, no command in SQL can do stuff in other databases than the one you are connected to.
You can write a simple one-liner that will do it, in your shell.
shouldn't be really complex - get list of dbs, and in each db connect with psql and truncate all tables. basically: `psql | xargs psql`
depesz
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Anton.Dischner@med.uni-muenchen.de, depesz@depesz.com, rajeshkumar.dba09@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: AW: Truncate data from whole cluster
In-Reply-To: <e087df8b0c194a5fa64c625a9cf9cdee@med.uni-muenchen.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox