PostgreSQL cheatsheet

tags: postgresql, cheatsheet

Connect to the database as DB administrator

su -l postgres -c psql

Create user

CREATE USER xyu WITH PASSWORD 'str0ngp0ssw0rd';

Change user’s password

ALTER USER xyu WITH PASSWORD 'cykableat';

Create database

CREATE DATABASE nextcloud WITH OWNER xyu;

vi ~/.pgpass

Add indexes

Add index on field:

ALTER TABLE stat ADD INDEX (hostname);

ALTER TABLE stat ADD INDEX (created);

Add index on two fields:

ALTER TABLE stat ADD INDEX `hostname_created_index`(`hostname`, `created`);

Review indexes

SHOW INDEX FROM `stat`;

Delete indexes

DROP INDEX  hostname ON stat;

Log queries

Add to /etc/postgresql/VERSION/main/postgresql.conf /etc/postgresql/postgresql.conf

log_statement = 'all'

References

https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html