Changes between Version 157 and Version 158 of members/GRIP-UQAM


Ignore:
Timestamp:
Aug 22, 2016, 1:40:32 PM (5 years ago)
Author:
Grip Uqam
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • members/GRIP-UQAM

    v157 v158  
    135135* https://support.mayfirst.org/wiki/bulk-mail-relay
    136136* https://support.mayfirst.org/wiki/email-deliverability
    137 == Some PostgreSQL commands ==
    138 * List of DB with parameters
    139 {{{#!sh
    140 $ psql postgres --command="\l"
    141 }}}
    142 * Create user
    143 {{{#!sh
    144 postgres:~$ createuser -D -R -S <UNIX user>
    145 }}}
    146 * Create DB
    147 {{{#!sh
    148 postgres:~$ createdb -O <postgres user> <database name>
    149 }}}
    150 * Backup
    151 {{{#!sh
    152 $ pg_dump -Fc --verbose --file=<file name (.data)> <database name>
    153 }}}
    154  * Drupal backup https://www.drupal.org/node/2607038#comment-11204141
    155 {{{#!sh
    156 PGPASSWORD="" pg_dumpall -w -U <user> -h /var/run/postgresql -p 5432 -l user -f /.../<user>-2016-07-22-10-17-49.sql
    157 }}}
    158 * Restore the data base as a different user eg. to clone a Drupal website (bad idea to clone a drupal website)
    159 {{{#!sh
    160 $ pg_restore --verbose --no-owner --no-privileges --dbname=<database name> <file name (.data)>
    161 }}}
    162 * Mark cache tables as {{{UNLOGGED}}} Postgres <= 9.4 (Debian 8.0 Jessie)
    163 {{{#!sh
    164 ...=> \d name
    165 ...
    166 ...=> CREATE UNLOGGED TABLE toto (id serial, ...);
    167 }}}
    168   * http://www.postgresql.org/docs/9.4/static/sql-createtable.html
    169   * It is easier to get schema from pgAdmin III, maybe on development server.
    170 * Mark cache tables as {{{UNLOGGED}}} Postgres >= 9.5
    171 {{{#!sql
    172 ALTER TABLE name SET UNLOGGED;
    173 }}}
    174   * http://www.postgresql.org/docs/current/static/sql-altertable.html
    175   * //[https://stackoverflow.com/questions/9153157/postgres-hstore-vs-redis-performance-wise Postgres Hstore vs. Redis - performance wise]//
    176 * {{{-E}}} or {{{--echo-hidden}}} See SQL requests send by {{{psql}}} for commands like {{{\l}}} ({{{\list}}})
    177 {{{#!sh
    178 $ psql --echo-hidden --dbname postgres
    179 }}}
    180  * http://www.postgresql.org/docs/9.4/static/app-psql.html
    181  * http://www.postgresql.org/message-id/18380.994171549@sss.pgh.pa.us
    182  * How do I get a list of databases in a Postgresql database ?
    183 * [http://www.thegeekstuff.com/2009/04/15-practical-postgresql-database-adminstration-commands 15 Practical PostgreSQL Database Administration Commands]
    184 * Root Password
    185 {{{#!sh
    186 $ sudo -u postgres psql postgres
    187 ALTER USER postgres WITH PASSWORD '<password>';
    188 \q
    189 }}}
    190  * https://www.leaseweb.com/labs/2014/04/10-developer-tools-install-ubuntu-14-04/
    191 * Drop all tables in a database
    192  * keywords: drop all tables from postgres database
    193  * This can be copyed into psql
    194 {{{#!sql
    195 DROP FUNCTION IF EXISTS remove_all();
    196 
    197 CREATE FUNCTION remove_all() RETURNS void AS $$
    198 DECLARE
    199     rec RECORD;
    200     cmd text;
    201 BEGIN
    202     cmd := '';
    203 
    204     FOR rec IN SELECT
    205             'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
    206                 || quote_ident(c.relname) || ' CASCADE;' AS name
    207         FROM
    208             pg_catalog.pg_class AS c
    209         LEFT JOIN
    210             pg_catalog.pg_namespace AS n
    211         ON
    212             n.oid = c.relnamespace
    213         WHERE
    214             relkind = 'S' AND
    215             n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
    216             pg_catalog.pg_table_is_visible(c.oid)
    217     LOOP
    218         cmd := cmd || rec.name;
    219     END LOOP;
    220 
    221     FOR rec IN SELECT
    222             'DROP TABLE ' || quote_ident(n.nspname) || '.'
    223                 || quote_ident(c.relname) || ' CASCADE;' AS name
    224         FROM
    225             pg_catalog.pg_class AS c
    226         LEFT JOIN
    227             pg_catalog.pg_namespace AS n
    228         ON
    229             n.oid = c.relnamespace WHERE relkind = 'r' AND
    230             n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
    231             pg_catalog.pg_table_is_visible(c.oid)
    232     LOOP
    233         cmd := cmd || rec.name;
    234     END LOOP;
    235 
    236     FOR rec IN SELECT
    237             'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
    238                 || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
    239                 || ');' AS name
    240         FROM
    241             pg_proc
    242         INNER JOIN
    243             pg_namespace ns
    244         ON
    245             (pg_proc.pronamespace = ns.oid)
    246         WHERE
    247             ns.nspname =
    248             'public'
    249         ORDER BY
    250             proname
    251     LOOP
    252         cmd := cmd || rec.name;
    253     END LOOP;
    254 
    255     EXECUTE cmd;
    256     RETURN;
    257 END;
    258 $$ LANGUAGE plpgsql;
    259 
    260 SELECT remove_all();
    261 }}}
    262 http://stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql
    263  * http://stackoverflow.com/questions/20017552/delete-all-table-without-droping-database-in-postgres-in-django-dbshell-in-one-c
    264 === Postgres for cache tables ===
    265 * nardberjean: dkg: may I ask you a question about postrgres and drupal cache tables?
    266 * nardberjean: I read that posgres can be configured to be faster on less important data
    267 * dkg: nardberjean: you can always ask questions here -- even though some of us might not know the answers ;)
    268 * nardberjean: so I was asking me if that would apply to cache table
    269 * dkg: yes, i recommend that cache tables be created as in-memory tables if you have enough RAM
    270 * nardberjean: well I do not know it I can do that on mf machines
    271 * nardberjean: as a member
    272 * nardberjean: I have been told that the less RAM used the better on theses machines
    273 * 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]
    274 * dkg: in particular, look at [https://www.postgresql.org/docs/9.4/static/sql-createtable.html] and "unlogged"
    275 * 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.
    276 * dkg: in my experience, most MF/PL machines are I/O constrained, not RAM constrained.
    277 * 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.
    278 * 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...
    279 * 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. :)
    280 * nardberjean: dkg: with d8 being very intense on cache, it makes it more an issue
    281 2016-08-04
     137== Postgres ==
     138[Postgres]
    282139== MongoDB ==
    283140* #11321