Postgres
Install
container xenial 16.04 (optional)
lxc image copy images:ubuntu/xenial/amd64 local: --alias xenial lxc launch xenial postgres lxc exec postgres bash
sudo apt install postgresql
eventually open network for remote connections
- /etc/postgresql/9.5/main/postgresql.conf
listen_addresses = '*'
Basic operations
# 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
set search path for database
psql <database> ALTER DATABASE database_name SET search_path TO schema_name,public;
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
ENCRYPTED PASSWORD 'xxx'
LOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION;
… create database and schemas …
grant operations on schema auth to role gianoauth
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;
on database giano set search path on user gianoauth to search first in schema auth
ALTER USER gianoauth SET search_path TO auth;
ALTER ROLE gianoauth IN DATABASE giano SET search_path TO auth;
create superuser <admin> with password
createuser -P -s -e admin
create user and database for sentry
CREATE ROLE sentry WITH PASSWORD 'blabla' LOGIN; CREATE DATABASE sentry ENCODING 'UTF8' OWNER sentry;
developer containers
- pgadmin:
- user: admin@admin
- pass: admin@admin
set static IP on containers
/var/lib/lxd/networks/lxdbr0/dnsmasq.hosts ├── pgmodeler └── postgres
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
pgadmin
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
- /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
systemctl daemon-reload systemctl start pgadmin systemctl enable pgadmin
define admin user in postgresql to use from pgadmin
su - postgres createuser --pwprompt --superuser admin
pgmodeler
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
desktop launcher
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
if in container enable remote connection on host
- /etc/gdm3/custom.conf
[security] DisallowTCP=false [xdmcp] Enable=true
run pgmodeler in container
xhost + lxc exec pgmodeler -- pgmodeler