Differences

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

--- postgresql [2022/05/03 12:10] – Jan Forman
+++ postgresql [2025/08/19 16:32] (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>
+===== DUMP ALL =====
+<code>
+pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql
+pg_dumpall --globals-only
+pg_dumpall --roles-only
+</code>

wiki.janforman.com