CODEWITHSUNDEEP
postgresql
membersound
membersound

Reputation: 86727

How to find database name of \data\base postgres folders?

I have a large folder of 70 GB in my postgres installation under:

D:\Program Files\PostgreSQL\9.5\data\base\130205

Question: how could I find out which database is based on that folder? I have like 10 databases running on the same server, and most of them having a tablespace on a different drive.

But probably I'm missing a mapping somewhere, maybe a large index or kind of. How can I find out the "causing" database of these amounts of data?

Upvotes: 3

Views: 14698

Answers (4)

Foad Najafi
Foad Najafi

Reputation: 1

you can use this syntax. it takes the ID and the name of the database from the pg_databse.

$ select pg_database.datname,pg_database.oid from pg_database;

Upvotes: 0

membersound
membersound

Reputation: 86727

Thanks to the hint of @a_horse, the following statement shows the oid and table names:

SELECT oid,* from pg_database

Upvotes: 6

redneb
redneb

Reputation: 23850

You can use the following query to find the size of your largest databases (taken from here): 

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246393

Just run oid2name as PostgreSQL operating system user.

Upvotes: 2

Related Questions