pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: Truncate data from whole cluster
3+ messages / 3 participants
[nested] [flat]
* AW: Truncate data from whole cluster
@ 2023-12-20 11:39 Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
2023-12-20 11:50 ` Re: AW: Truncate data from whole cluster hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dischner, Anton @ 2023-12-20 11:39 UTC (permalink / raw)
To: 'depesz@depesz.com' <depesz@depesz.com>; Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: AW: Truncate data from whole cluster
2023-12-20 11:39 AW: Truncate data from whole cluster Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
@ 2023-12-20 11:50 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-12-20 15:09 ` Re: AW: Truncate data from whole cluster Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: hubert depesz lubaczewski @ 2023-12-20 11:50 UTC (permalink / raw)
To: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>; +Cc: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Dec 20, 2023 at 11:39:40AM +0000, Dischner, Anton wrote:
> 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!
No, it's not.
it's full of errors, and hallucinations.
couple of things that took me < 1 minute to notice:
1. will fail for database names that contain spaces in them
2. setting variable like psql -d ... -c "set .." - is pointless, the
value will be active only for this session, but this session has just
eneded
3. there is no "FOREIGN_KEY_CHECKS" setting in pg
4. will fail for table names with spaces
5. will ignore tables in non-public schemas
6. will fail for table names with UpperCaseLetters
7. for db with N tables (in public), it will call 101 psql connections
to this database. if your db has many small tables it will be
unreasonably slow
depesz, cursing whatever "magic ai tool" people try to use as base for
their scripts
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: AW: Truncate data from whole cluster
2023-12-20 11:39 AW: Truncate data from whole cluster Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
2023-12-20 11:50 ` Re: AW: Truncate data from whole cluster hubert depesz lubaczewski <depesz@depesz.com>
@ 2023-12-20 15:09 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rajesh Kumar @ 2023-12-20 15:09 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thank you all. I found function already that has to executed connecting to
each db one by one. I thought to have one command that takes care of
everything..Anyways...thank you
On Wed, 20 Dec 2023, 17:20 hubert depesz lubaczewski, <depesz@depesz.com>
wrote:
> On Wed, Dec 20, 2023 at 11:39:40AM +0000, Dischner, Anton wrote:
> > 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!
>
> No, it's not.
> it's full of errors, and hallucinations.
>
> couple of things that took me < 1 minute to notice:
>
> 1. will fail for database names that contain spaces in them
> 2. setting variable like psql -d ... -c "set .." - is pointless, the
> value will be active only for this session, but this session has just
> eneded
> 3. there is no "FOREIGN_KEY_CHECKS" setting in pg
> 4. will fail for table names with spaces
> 5. will ignore tables in non-public schemas
> 6. will fail for table names with UpperCaseLetters
> 7. for db with N tables (in public), it will call 101 psql connections
> to this database. if your db has many small tables it will be
> unreasonably slow
>
> depesz, cursing whatever "magic ai tool" people try to use as base for
> their scripts
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-12-20 15:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-20 11:39 AW: Truncate data from whole cluster Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
2023-12-20 11:50 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-12-20 15:09 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
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