Changes between Initial Version and Version 1 of members/GRIP-UQAM/Postgres

Timestamp:

Aug 22, 2016, 5:40:06 PM (9 years ago)

Author:

Grip Uqam

Comment:

--

members/GRIP-UQAM/Postgres

@@ +1 @@
+= Some PostgreSQL commands =
+* List of DB with parameters
+{{{#!sh
+$ psql postgres --command="\l"
+}}}
+* Create user
+{{{#!sh
+postgres:~$ createuser -D -R -S <UNIX user>
+}}}
+* Create DB
+{{{#!sh
+postgres:~$ createdb -O <postgres user> <database name>
+}}}
+* Backup
+{{{#!sh
+$ pg_dump -Fc --verbose --file=<file name (.data)> <database name>
+}}}
+ * Drupal backup https://www.drupal.org/node/2607038#comment-11204141
+{{{#!sh
+PGPASSWORD="" pg_dumpall -w -U <user> -h /var/run/postgresql -p 5432 -l user -f /.../<user>-2016-07-22-10-17-49.sql
+}}}
+* Restore the data base as a different user eg. to clone a Drupal website (bad idea to clone a drupal website)
+{{{#!sh
+$ pg_restore --verbose --no-owner --no-privileges --dbname=<database name> <file name (.data)>
+}}}
+* Mark cache tables as {{{UNLOGGED}}} Postgres <= 9.4 (Debian 8.0 Jessie)
+{{{#!sh
+...=> \d name
+...
+...=> CREATE UNLOGGED TABLE toto (id serial, ...);
+}}}
+  * http://www.postgresql.org/docs/9.4/static/sql-createtable.html
+  * It is easier to get schema from pgAdmin III, maybe on development server.
+* Mark cache tables as {{{UNLOGGED}}} Postgres >= 9.5
+{{{#!sql
+ALTER TABLE name SET UNLOGGED;
+}}}
+  * http://www.postgresql.org/docs/current/static/sql-altertable.html
+  * //[https://stackoverflow.com/questions/9153157/postgres-hstore-vs-redis-performance-wise Postgres Hstore vs. Redis - performance wise]//
+* {{{-E}}} or {{{--echo-hidden}}} See SQL requests send by {{{psql}}} for commands like {{{\l}}} ({{{\list}}})
+{{{#!sh
+$ psql --echo-hidden --dbname postgres
+}}}
+ * http://www.postgresql.org/docs/9.4/static/app-psql.html
+ * http://www.postgresql.org/message-id/18380.994171549@sss.pgh.pa.us
+ * How do I get a list of databases in a Postgresql database ?
+* [http://www.thegeekstuff.com/2009/04/15-practical-postgresql-database-adminstration-commands 15 Practical PostgreSQL Database Administration Commands]
+* Root Password
+{{{#!sh
+$ sudo -u postgres psql postgres
+ALTER USER postgres WITH PASSWORD '<password>';
+\q
+}}}
+ * https://www.leaseweb.com/labs/2014/04/10-developer-tools-install-ubuntu-14-04/
+* Drop all tables in a database
+ * keywords: drop all tables from postgres database
+ * This can be copyed into psql
+{{{#!sql
+DROP FUNCTION IF EXISTS remove_all();
+
+CREATE FUNCTION remove_all() RETURNS void AS $$
+DECLARE
+    rec RECORD;
+    cmd text;
+BEGIN
+    cmd := '';
+
+    FOR rec IN SELECT
+            'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
+                || quote_ident(c.relname) || ' CASCADE;' AS name
+        FROM
+            pg_catalog.pg_class AS c
+        LEFT JOIN
+            pg_catalog.pg_namespace AS n
+        ON
+            n.oid = c.relnamespace
+        WHERE
+            relkind = 'S' AND
+            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
+            pg_catalog.pg_table_is_visible(c.oid)
+    LOOP
+        cmd := cmd || rec.name;
+    END LOOP;
+
+    FOR rec IN SELECT
+            'DROP TABLE ' || quote_ident(n.nspname) || '.'
+                || quote_ident(c.relname) || ' CASCADE;' AS name
+        FROM
+            pg_catalog.pg_class AS c
+        LEFT JOIN
+            pg_catalog.pg_namespace AS n
+        ON
+            n.oid = c.relnamespace WHERE relkind = 'r' AND
+            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
+            pg_catalog.pg_table_is_visible(c.oid)
+    LOOP
+        cmd := cmd || rec.name;
+    END LOOP;
+
+    FOR rec IN SELECT
+            'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
+                || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
+                || ');' AS name
+        FROM
+            pg_proc
+        INNER JOIN
+            pg_namespace ns
+        ON
+            (pg_proc.pronamespace = ns.oid)
+        WHERE
+            ns.nspname =
+            'public'
+        ORDER BY
+            proname
+    LOOP
+        cmd := cmd || rec.name;
+    END LOOP;
+
+    EXECUTE cmd;
+    RETURN;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT remove_all();
+}}}
+http://stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql
+ * http://stackoverflow.com/questions/20017552/delete-all-table-without-droping-database-in-postgres-in-django-dbshell-in-one-c
+= Postgres for cache tables =
+* nardberjean: dkg: may I ask you a question about postrgres and drupal cache tables?
+* nardberjean: I read that posgres can be configured to be faster on less important data
+* dkg: nardberjean: you can always ask questions here -- even though some of us might not know the answers ;)
+* nardberjean: so I was asking me if that would apply to cache table
+* dkg: yes, i recommend that cache tables be created as in-memory tables if you have enough RAM 
+* nardberjean: well I do not know it I can do that on mf machines
+* nardberjean: as a member
+* nardberjean: I have been told that the less RAM used the better on theses machines
+* dkg: you might be interested in a few different points of documentation at a system level: [https://www.postgresql.org/docs/9.4/static/non-durability.html]
+* dkg: in particular, look at [https://www.postgresql.org/docs/9.4/static/sql-createtable.html] and "unlogged"
+* dkg: "the less RAM used the better" is always generally true but isn't very nuanced -- if you use less RAM by swapping everything to disk all the time to the point where no one can use the disk it seems worse to me.
+* dkg: in my experience, most MF/PL machines are I/O constrained, not RAM constrained.
+* nardberjean: well Non-Durable Settings would be good for I/O, and I could do it by myself, even if I feel the change should be made into Drupal code.
+* nardberjean: for RAM tables, I do not know if it is already configured on mf machines, once I will have that info, I could try to move some small cache table to it...
+* dkg: cool!  please report on support.mayfirst.org once you tried it out -- whether you make successful progress or otherwise, sorting this out is likely to be helpful to everyone on MF/PL. :)
+* nardberjean: dkg: with d8 being very intense on cache, it makes it more an issue
+2016-08-04