From: | Andreas <maps(dot)on(at)gmx(dot)net> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | How to drop automatically some databases? |
Date: | 2015-04-25 12:07:59 |
Message-ID: | 553B839F.2040503@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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? :}
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-04-25 16:25:38 | Re: How to drop automatically some databases? |
Previous Message | danny | 2015-04-24 16:12:38 | Re: server doesn't listen postgresql 9.1 after service change |