From: Andreas <maps.on@gmx.net>
To: pgsql-admin@postgresql.org
Subject: How to drop automatically some databases?
Date: Sat, 25 Apr 2015 14:07:59 +0200
Message-ID: <553B839F.2040503@gmx.net> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
I've got a developer PC (Windows) that hosts copies of a production
database so I can tinker with it without causing damage to the
production database.
Every night a script fetches a dump of the production database.
Then it renames the test-db on my PC, creates a new test-db and imports
the dump.
The current version is named "db_test".
The older copies are named "db_test_yymmdd_hhmm". The name is extended
with the timestamp of the import.
Now I'd like to keep just 3 older copies in case I want to look up
structural moifications I've made the day before.
Older copies should get dropped.
This would show the names to drop.
select datname
from (
select datname
from pg_database
where datname ilike 'db_test_%'
order by datname
) as x
EXCEPT
select datname
from (
select datname
from pg_database
where datname ilike 'db_test_%'
order by datname desc
limit 3
) as y;
I tried to run this
drop database ( ... the select above ... )
in the DB "postgres" but it didn't work.
After a couple of seconds I get a syntax error.
What now? :}
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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: maps.on@gmx.net
Subject: Re: How to drop automatically some databases?
In-Reply-To: <553B839F.2040503@gmx.net>
* 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