= Some PostgreSQL commands = * List of DB with parameters {{{#!sh $ psql postgres --command="\l" }}} * Create user {{{#!sh postgres:~$ createuser -D -R -S }}} * Create DB {{{#!sh postgres:~$ createdb -O }}} * Backup {{{#!sh $ pg_dump -Fc --verbose --file= }}} * Drupal backup https://www.drupal.org/node/2607038#comment-11204141 {{{#!sh PGPASSWORD="" pg_dumpall -w -U -h /var/run/postgresql -p 5432 -l user -f /.../-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= }}} * 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 ''; \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