deployment:postgresql
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
deployment:postgresql [2018/02/27 20:22] asok [Useful Commands] |
deployment:postgresql [2018/02/28 19:09] (current) asok |
||
|---|---|---|---|
| Line 33: | Line 33: | ||
| ====Linux==== | ====Linux==== | ||
| + | ===Local=== | ||
| <file> | <file> | ||
| su - postgres | su - postgres | ||
| Line 41: | Line 42: | ||
| exit | exit | ||
| + | </file> | ||
| + | ===Remote=== | ||
| + | <file> | ||
| + | pg_dump -Fc dbname --host=hosturl > filename | ||
| + | |||
| + | pg_restore -d dbname --host=hosturl filename | ||
| </file> | </file> | ||
| Line 46: | Line 53: | ||
| <code> | <code> | ||
| + | |||
| + | -- Recreate Schema | ||
| + | |||
| + | DROP SCHEMA public CASCADE; | ||
| + | CREATE SCHEMA public; | ||
| + | GRANT ALL ON SCHEMA public TO postgres; | ||
| + | GRANT ALL ON SCHEMA public TO public; | ||
| + | |||
| -- Show Running Queries -- Look for active ones | -- Show Running Queries -- Look for active ones | ||
| Line 67: | Line 82: | ||
| -- all databases and their sizes | -- all databases and their sizes | ||
| select * from pg_user; | select * from pg_user; | ||
| + | |||
| + | -- number of records | ||
| + | SELECT schemaname,relname,n_live_tup | ||
| + | FROM pg_stat_user_tables | ||
| + | ORDER BY n_live_tup DESC; | ||
| -- all tables and their size, with/without indexes | -- all tables and their size, with/without indexes | ||
/srv/www/htdocs/wiki/data/attic/deployment/postgresql.1519762926.txt.gz · Last modified: 2018/02/27 20:22 by asok
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
