User Tools

Site Tools


postgresql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
postgresql [2022/05/02 16:08]
Jan Forman
postgresql [2022/05/03 12:10] (current)
Jan Forman
Line 1: Line 1:
 +====== Postgresql ======
  
 +===== Duplicate Layer =====
 +<code>CREATE TABLE new_table AS SELECT * FROM old_table</code>
 +
 +<code>effective_io_concurrency = X</code>
 +Can be raised for SSDs
 +
 +
 +<code>
 +for I in `/usr/bin/psql -d template1 -q -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0') ORDER BY datname;"`; do
 +        /usr/bin/pg_dump -F t ${I} | /usr/bin/lzop -1 > ${TEMPDIR}/${DATE}${I}.tar.lzo
 +done
 +</code>
 +
 +<code>
 +max_connections = 300
 +shared_buffers = 80MB
 +</code>
 +
 +===== Create read-only =====
 +
 +<code>
 +GRANT CONNECT ON DATABASE data TO plz;
 +GRANT USAGE ON SCHEMA public TO plz;
 +</code>
 +
 +===== Remove user =====
 +<code>
 +REVOKE ALL ON SCHEMA public FROM myuser;
 +REVOKE ALL ON DATABASE mydb FROM myuser;
 +DROP OWNED BY myuser;
 +DROP USER myuser;
 +</code>
 +
 +**List all sequences**
 +<code>
 +SELECT relname sequence_name FROM pg_class WHERE relkind = 'S';
 +</code>
 +
 +===== Change user pass =====
 +<code>
 +ALTER USER username WITH ENCRYPTED PASSWORD 'password';
 +</code>
postgresql.txt · Last modified: 2022/05/03 12:10 by Jan Forman