How to get the existing postgres databases in a list

Question

I am trying to write a python script to automate the task of creating database using the most recent production dump. I am using psychopg2 for the purpose.

But after the creation of the new database I want to delete the previously used database. My idea is that if I could get the names of databases in a list and sort them, I can easily delete the unwanted database.

So, my question is : How can I get the names of the DBs in a list.

Thanks

I've been searching on google. But haven't found a useful post yet. — nish
– nish, Commented Oct 30, 2013 at 9:58
When you update the database, you do you create a new database? Wouldn't it be better to put it into the existing database? — DrColossos
– DrColossos, Commented Oct 30, 2013 at 10:04
@DrColossos : Yup, sorry. I am creating a new database. I cannot make changes to the existing DB as it is being used by the RoR app which I'm using the DB for — nish
– nish, Commented Oct 30, 2013 at 10:06

DrColossos · Accepted Answer · 2013-10-30 10:09:42Z

1

You can list all of your DBs with

SELECT d.datname as "Name",
FROM pg_catalog.pg_database d
ORDER BY 1;

You can filter or order it whatever way you like.

answered Oct 30, 2013 at 10:09

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parthi P · Accepted Answer · 2013-10-30 12:48:57Z

psql -E -U postgres -c "\l"

The output of the above command is like

********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                              List of databases
     Name     |  Owner   | Encoding | Collate | Ctype |   Access privileges   
--------------+----------+----------+---------+-------+-----------------------
 mickey       | postgres | UTF8     | C       | C     | 
 mickeylite   | postgres | UTF8     | C       | C     | 
 postgres     | postgres | UTF8     | C       | C     | 
 template0    | postgres | UTF8     | C       | C     | =c/postgres          +
              |          |          |         |       | postgres=CTc/postgres
 template1    | postgres | UTF8     | C       | C     | =c/postgres          +
              |          |          |         |       | postgres=CTc/postgres
(5 rows)

Collectives™ on Stack Overflow

How to get the existing postgres databases in a list

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related