wiki:members/GRIP-UQAM/Postgres

Some PostgreSQL commands

  • Server version
    $ psql -c 'SELECT version();'
    
  • List of DB with parameters
    $ psql postgres --command="\l"
    
  • Create user
    postgres:~$ createuser -D -R -S <UNIX user>
    
  • Create DB
    postgres:~$ createdb -O <postgres user> <database name>
    
  • Backup
    $ pg_dump -Fc --verbose --file=<file name (.data)> <database name>
    
  • Restore the data base as a different user eg. to clone a Drupal website (bad idea to clone a drupal website)
    $ 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)
    ...=> \d name
    ...
    ...=> CREATE UNLOGGED TABLE toto (id serial, ...);
    
  • Mark cache tables as UNLOGGED Postgres >= 9.5
    ALTER TABLE name SET UNLOGGED;
    
    \dt+ cachetags
    \dt+ cache_*
    select relpersistence, relname from pg_class where relname like 'cache_%';
    
    \db
    SELECT spcname FROM pg_tablespace;
    
  • -E or --echo-hidden See SQL requests send by psql for commands like \l (\list)
    $ psql --echo-hidden --dbname postgres
    
  • 15 Practical PostgreSQL Database Administration Commands
  • Root Password
    $ sudo -u postgres psql postgres
    ALTER USER postgres WITH PASSWORD '<password>';
    \q
    
  • Drop all tables in a database
    • keywords: drop all tables from postgres database
    • This can be copyed into psql
      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

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

Last modified 6 months ago Last modified on Mar 3, 2019, 2:44:29 PM