Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
| tips:db:postgres [2017/11/21 13:43] – [pgmodeler] scipio | tips:db:postgres [2019/03/13 09:55] (current) – [Basic operations] scipio | ||
|---|---|---|---|
| Line 21: | Line 21: | ||
| ===== Basic operations ===== | ===== Basic operations ===== | ||
| - | create role (user) | ||
| < | < | ||
| - | -- User: gianoauth | + | # list databases |
| - | DROP USER gianoauth; | + | \l |
| + | # connect to database | ||
| + | \c giano | ||
| + | |||
| + | # list schema | ||
| + | \dn | ||
| + | |||
| + | # list tables in schema | ||
| + | \dt auth.* | ||
| + | |||
| + | # describe table | ||
| + | \d+ auth.users | ||
| + | |||
| + | # list of installed extensions | ||
| + | \dx | ||
| + | |||
| + | # enable expanded display | ||
| + | \x | ||
| + | </ | ||
| + | |||
| + | set search path for database | ||
| + | < | ||
| + | psql < | ||
| + | ALTER DATABASE database_name SET search_path TO schema_name, | ||
| + | </ | ||
| + | |||
| + | |||
| + | drop role gianoauth | ||
| + | < | ||
| + | REASSIGN OWNED BY gianoauth TO postgres; | ||
| + | DROP OWNED BY gianoauth; | ||
| + | DROP USER IF EXISTS gianoauth; | ||
| + | </ | ||
| + | |||
| + | create role gianoauth | ||
| + | < | ||
| CREATE USER gianoauth WITH | CREATE USER gianoauth WITH | ||
| + | ENCRYPTED PASSWORD ' | ||
| LOGIN | LOGIN | ||
| NOSUPERUSER | NOSUPERUSER | ||
| Line 33: | Line 68: | ||
| NOCREATEROLE | NOCREATEROLE | ||
| NOREPLICATION; | NOREPLICATION; | ||
| + | </ | ||
| - | ALTER USER gianoauth SET search_path TO gianoauth; | + | ... create database and schemas ... |
| - | ALTER ROLE gianoauth IN DATABASE giano SET search_path TO gianoauth; | + | |
| - | GRANT ALL PRIVILEGES ON SCHEMA | + | grant operations on schema auth to role gianoauth |
| - | GRANT ALL PRIVILEGES | + | < |
| - | GRANT ALL PRIVILEGES | + | GRANT ALL PRIVILEGES ON SCHEMA |
| + | ALTER DEFAULT | ||
| + | ALTER DEFAULT | ||
| + | </ | ||
| + | |||
| + | on database giano set search path on user gianoauth to search first in schema auth | ||
| + | < | ||
| + | ALTER USER gianoauth SET search_path | ||
| + | ALTER ROLE gianoauth | ||
| + | </ | ||
| + | |||
| + | create superuser < | ||
| + | < | ||
| + | createuser -P -s -e admin | ||
| + | </ | ||
| + | |||
| + | |||
| + | create user and database for sentry | ||
| + | < | ||
| + | CREATE ROLE sentry WITH PASSWORD ' | ||
| + | CREATE DATABASE sentry ENCODING ' | ||
| </ | </ | ||
| ==== developer containers ===== | ==== developer containers ===== | ||
| Line 69: | Line 124: | ||
| apt install wget python3-pip | apt install wget python3-pip | ||
| pip3 install -U pip | pip3 install -U pip | ||
| - | cd /tmp | + | pip install |
| - | wget https:// | + | |
| - | pip install ./pgadmin4-2.0-py2.py3-none-any.whl | + | |
| # define ip binding | # define ip binding | ||
| Line 110: | Line 163: | ||
| < | < | ||
| - | PG_VERSION=0.9.1-alpha | + | PG_VERSION=0.9.1-beta1 |
| # for building, can remove after install | # for building, can remove after install | ||
| - | sudo apt install make g++ qt5-qmake libxml2-dev libpq-dev pkg-config libqt5svg5-dev | + | sudo apt install |
| # runtime dependencies | # runtime dependencies | ||
| - | sudo apt install qt5-default libqt5svg5 postgresql-server-dev-all | + | sudo apt install |
| cd / | cd / | ||
| - | sudo apt install wget | + | sudo apt install |
| wget https:// | wget https:// | ||
| tar xvzf v${PG_VERSION}.tar.gz | tar xvzf v${PG_VERSION}.tar.gz | ||
| - | cd pgmodeler-0.9.1-alpha/ | + | cd pgmodeler-${PG_VERSION}/ |
| qmake pgmodeler.pro | qmake pgmodeler.pro | ||
| make -j 4 | make -j 4 | ||