| 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] |
