Differences

This shows you the differences between two versions of the page.

--- deployment:postgresql [2015/03/22 07:13]
asok [Linux]
+++ deployment:postgresql [2018/02/28 19:09] (current)
asok
@@ Line 1: / Line 1: @@
-=====PostgreSQL - Dumping Data =====
+=====PostgreSQL - Dumping Data, Stopping Queries and Maintenance=====
 ====Windows====
@@ Line 33: / Line 33: @@
 ====Linux====
+===Local===
 <file>
    su - postgres
@@ Line 42: / Line 43: @@
 </file>
+===Remote===
+<file>
+ pg_dump -Fc dbname --host=hosturl > filename
+ pg_restore -d dbname --host=hosturl filename
+</file>
+====Important Maintenance Statements====
+<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
+SELECT pid, age(query_start, clock_timestamp()), usename, state ,query
+FROM pg_stat_activity
+ORDER BY query_start desc;
+-- kill running query
+SELECT pg_cancel_backend(procpid);
+-- kill idle query
+SELECT pg_terminate_backend(procpid);
+-- vacuum command
+VACUUM (VERBOSE, ANALYZE);
+-- all database users
+select * from pg_stat_activity where current_query not like '<%';
+-- all databases and their sizes
+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
+select datname, pg_size_pretty(pg_database_size(datname))
+from pg_database
+order by pg_database_size(datname) desc;
+-- cache hit rates (should not be less than 0.99)
+SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
+FROM pg_statio_user_tables;
+-- table index usage rates (should not be less than 0.99)
+SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
+FROM pg_stat_user_tables
+ORDER BY n_live_tup DESC;
+-- how many indexes are in cache
+SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
+FROM pg_statio_user_indexes;
+-- Dump database on remote host to file
+$ pg_dump -U username -h hostname databasename > dump.sql
+-- Import dump into existing database
+$ psql -d newdb -f dump.sql
+</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>

DreamApps Wiki

User Tools

Site Tools

Differences

Page Tools