Show pageOld revisionsBacklinksAdd to bookExport to PDFBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ====== Postgres ====== ===== Install ===== container xenial 16.04 (optional) <code> lxc image copy images:ubuntu/xenial/amd64 local: --alias xenial lxc launch xenial postgres lxc exec postgres bash </code> <code> sudo apt install postgresql </code> eventually open network for remote connections <file ini /etc/postgresql/9.5/main/postgresql.conf> listen_addresses = '*' </file> ===== Basic operations ===== <code> # list databases \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 </code> set search path for database <code> psql <database> ALTER DATABASE database_name SET search_path TO schema_name,public; </code> drop role gianoauth <code> REASSIGN OWNED BY gianoauth TO postgres; DROP OWNED BY gianoauth; DROP USER IF EXISTS gianoauth; </code> create role gianoauth <code> CREATE USER gianoauth WITH ENCRYPTED PASSWORD 'xxx' LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; </code> ... create database and schemas ... grant operations on schema auth to role gianoauth <code> GRANT ALL PRIVILEGES ON SCHEMA auth TO gianoauth; ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA auth GRANT SELECT ON TABLES TO gianoauth; ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA auth GRANT SELECT, USAGE ON SEQUENCES TO gianoauth; </code> on database giano set search path on user gianoauth to search first in schema auth <code> ALTER USER gianoauth SET search_path TO auth; ALTER ROLE gianoauth IN DATABASE giano SET search_path TO auth; </code> create superuser <admin> with password <code> createuser -P -s -e admin </code> create user and database for sentry <code> CREATE ROLE sentry WITH PASSWORD 'blabla' LOGIN; CREATE DATABASE sentry ENCODING 'UTF8' OWNER sentry; </code> ==== developer containers ===== * pgadmin: * user: admin@admin * pass: admin@admin set static IP on containers <code> /var/lib/lxd/networks/lxdbr0/dnsmasq.hosts ├── pgmodeler └── postgres </code> <code> cat > /var/lib/lxd/networks/lxdbr0/dnsmasq.hosts/pgmodeler <<EOF 00:16:3e:9d:ad:d2,10.8.120.205,pgmodeler EOF cat > /var/lib/lxd/networks/lxdbr0/dnsmasq.hosts/postgres <<EOF 00:16:3e:ce:41:53,10.8.120.228,postgres EOF </code> ==== pgadmin ==== <code> apt install wget python3-pip pip3 install -U pip pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.2/pip/pgadmin4-4.2-py2.py3-none-any.whl # define ip binding CONFIG=/usr/local/lib/python3.5/dist-packages/pgadmin4/config.py sed -i -e "s/DEFAULT_SERVER\s*=.*/DEFAULT_SERVER = '0.0.0.0'/" $CONFIG # define pgadmin admin user python3 /usr/local/lib/python3.5/dist-packages/pgadmin4/pgAdmin4.py </code> <file ini /etc/systemd/system/pgadmin.service> [Unit] Description=pgadmin Service After=network.target [Service] User=root ExecStart=/usr/bin/python3 /usr/local/lib/python3.5/dist-packages/pgadmin4/pgAdmin4.py [Install] WantedBy=multi-user.target </file> <code> systemctl daemon-reload systemctl start pgadmin systemctl enable pgadmin </code> define admin user in postgresql to use from pgadmin <code> su - postgres createuser --pwprompt --superuser admin </code> ==== pgmodeler ==== * [[https://github.com/ksylvan/docker-pgmodeler/blob/master/Dockerfile|reference]] <code> PG_VERSION=0.9.1-beta1 # for building, can remove after install sudo apt install -y make g++ qt5-qmake libxml2-dev libpq-dev pkg-config libqt5svg5-dev # runtime dependencies sudo apt install -y qt5-default libqt5svg5 postgresql-server-dev-all cd /usr/local/src sudo apt install -y wget wget https://github.com/pgmodeler/pgmodeler/archive/v${PG_VERSION}.tar.gz tar xvzf v${PG_VERSION}.tar.gz cd pgmodeler-${PG_VERSION}/ qmake pgmodeler.pro make -j 4 make install sudo mkdir /usr/local/lib/pgmodeler/plugins cd .. rm *.tar.gz apt clean </code> desktop launcher <code> mkdir -p ~/.local/share/applications/ cat > ~/.local/share/applications/pgmodeler.desktop <<EOF #!/usr/bin/env xdg-open [Desktop Entry] Type=Application Terminal=false Exec=/usr/local/bin/pgmodeler Name=pgmodeler EOF </code> if in container enable remote connection on host <file ini /etc/gdm3/custom.conf> [security] DisallowTCP=false [xdmcp] Enable=true </file> run pgmodeler in container <code> xhost + lxc exec pgmodeler -- pgmodeler </code> tips/db/postgres.txt Last modified: 2019/03/13 09:55by scipio