How to drop automatically some databases?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin




[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux