7,612
edits
Changes
Jump to navigation
Jump to search
→Find the sizes of databases on our postgres server
[[category:McNair Admin]]
See also:
*[[Working with PostgreSQL]]
*[[Haas PhD Server Configuration]]
Note that the server is currently now on 128.32.252204.201 203 (but will shortly be having been movedto the new colo)
==Mount Bear==
If the user doesn't have an account on the box, then you'll need to add a database for them (use DBname of 'firstname_data' as a default):
/usr/local/pgsql/bin/createdb -O username DBname
==Size, Backup & Restore==
===Find the sizes of databases on our postgres server===
*Connect to the dbase server (poss. with an admin account)
*psql postgres
*run the following query:
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;
To see the disk space use on all drives use:
df -h
===Backing up a dbase===
pg_dump dbase > dbase.dump
or
pg_dump -Fc dbase > dbase_fc.dump (which uses compression)
Note: Use top or ps -aux to see memory/CPU usage.
We can also manually compress/decompress using:
gzip filename
gzip -d filename.gz
When done, we can drop the database:
dropdb dbase
===Restoring a dbase===
pg_restore -d newdb dbase.dump
[[admin_classification::IT Build| ]]
