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 [2017/11/30 02:19] asok |
deployment:postgresql [2018/02/28 19:09] (current) asok |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | =====PostgreSQL - Dumping Data ===== | + | =====PostgreSQL - Dumping Data, Stopping Queries and Maintenance===== |
| ====Windows==== | ====Windows==== | ||
| 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> | ||
| - | -- Show Running Queries | ||
| - | SELECT pid, age(query_start, clock_timestamp()), usename, query | + | -- 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 | ||
| + | |||
| + | SELECT pid, age(query_start, clock_timestamp()), usename, state ,query | ||
| FROM pg_stat_activity | FROM pg_stat_activity | ||
| - | WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' | + | |
| ORDER BY query_start desc; | ORDER BY query_start desc; | ||
| 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 | ||
| Line 93: | Line 113: | ||
| </code> | </code> | ||
| + | ====Useful Commands==== | ||
| + | <code> | ||
| + | $ netstat -nlt | ||
| + | Proto Recv-Q Send-Q Local Address Foreign Address State | ||
| + | tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 127.0.0.1:11211 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 127.0.0.1:3737 0.0.0.0:* LISTEN | ||
| + | tcp6 0 0 :::22 :::* LISTEN | ||
| + | </code> | ||
| + | After setting listen_address = '*' in postgresql.conf | ||
| + | |||
| + | <code> | ||
| + | $ netstat -nlt | ||
| + | Proto Recv-Q Send-Q Local Address Foreign Address State | ||
| + | tcp 0 0 127.0.0.1:11211 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN | ||
| + | tcp 0 0 127.0.0.1:2812 0.0.0.0:* LISTEN | ||
| + | tcp6 0 0 ::1:11211 :::* LISTEN | ||
| + | tcp6 0 0 :::22 :::* LISTEN | ||
| + | tcp6 0 0 :::5432 :::* LISTEN | ||
| + | tcp6 0 0 ::1:25 :::* LISTEN | ||
| + | |||
| + | |||
| + | </code> | ||
/srv/www/htdocs/wiki/data/attic/deployment/postgresql.1512008384.txt.gz · Last modified: 2017/11/30 02:19 by asok
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
