User Tools

Site Tools


postgresql

Postgresql

Duplicate Layer

CREATE TABLE new_table AS SELECT * FROM old_table
effective_io_concurrency = X

Can be raised for SSDs

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
max_connections = 300
shared_buffers = 80MB

Create read-only

GRANT CONNECT ON DATABASE data TO plz;
GRANT USAGE ON SCHEMA public TO plz;

Remove user

REVOKE ALL ON SCHEMA public FROM myuser;
REVOKE ALL ON DATABASE mydb FROM myuser;
DROP OWNED BY myuser;
DROP USER myuser;

List all sequences

SELECT relname sequence_name FROM pg_class WHERE relkind = 'S';

Change user pass

ALTER USER username WITH ENCRYPTED PASSWORD 'password';
postgresql.txt · Last modified: 2022/05/03 12:10 by Jan Forman