Version 2 (modified by 7 years ago) ( diff ) | ,
---|
Some PostgreSQL commands
- 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>
- Drupal backup https://www.drupal.org/node/2607038#comment-11204141
PGPASSWORD="" pg_dumpall -w -U <user> -h /var/run/postgresql -p 5432 -l user -f /.../<user>-2016-07-22-10-17-49.sql
- Drupal backup https://www.drupal.org/node/2607038#comment-11204141
- 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, ...);
- 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.5ALTER TABLE name SET UNLOGGED;
-E
or--echo-hidden
See SQL requests send bypsql
for commands like\l
(\list
)$ 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 ?
- 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
Note:
See TracWiki
for help on using the wiki.