postgresql.md

Tools

• cli for databases
• binaries to download

chmod for exec files

chmod +x %pgsql%/bin/*

replace 'text link' with files

%pgsql%/lib/*.so

create cluster

./initdb -U postgres -A password -E utf8 -W -D /dev/shm/pgsql-data/data

The command line parameters of the initdb command are described in following:

• -U postgres means that the superuser account of your database is called ‘postgres’.
• -A password means that password authentication is used.
• -E utf8 means that the default encoding will be UTF-8.
• -W means that you will enter the superuser password manually.
• -D /dev/shm/pgsql-data/data specifies the data directory of your PostgreSQL installation.

Issue:

/initdb: /lib64/libc.so.6: version `GLIBC_2.12' not found (required by /dev/shm/pgsql/bin/../lib/libldap_r-2.4.so.2)

solution:

version of your glibc is older than compiled code - decrease version of postgres

must work:

./postgres -V

start DB

./pg_ctl -D "/dev/shm/pgsql-data/data" -l "/dev/shm/pgsql-log/pgsql.log" start

stop DB

./pg_ctl -D "/dev/shm/pgsql-data/data" -l "/dev/shm/pgsql-log/pgsql.log" stop

change access from external addresses

find /dev/shm/pgsql-data/data -name "postgresql.conf"

listen_addresses = '*'

find /dev/shm/pgsql-data/data -name "pg_hba.conf"

host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5

connect to db

cli installation

pip install -U pgcli
sudo apt install pgcli

postgresql cli connection

PG_HOST=localhost
PG_PORT=5432
PG_USER=postgres-prod
PG_PASS=my_secure_pass
PG_DB=data-production

psql -h $PG_HOST -p $PG_PORT -U $PG_USER $PG_DB

save results to file

# pgcli save query result
# \o [filename]                        | Send all query results to file.

\copy (select sku from testaccount01_variant) to 'db-2.sku' csv header;
COPY tablename TO '/tmp/output.csv' DELIMITER ',' CSV HEADER;

jdbc url

url:
    jdbc:postgresql:database
    jdbc:postgresql://host/database
    jdbc:postgresql://host:port/database

jdbc driver

<dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.1-901-1.jdbc4</version>
</dependency>

DB requests

install client

sudo apt install postgresql-client-common
sudo apt-get install postgresql-client-12

list of all databases, ad-hoc

psql --username postgres --list

execute query, ad-hoc check connection

psql -w -U user_name -d database_name -c "SELECT 1"

execute prepared sql file

psql -w -U user_name -d database_name -a -f /path/to/file.sql
psql -h ${DB_VARIANT_HOST} -p ${DB_VARIANT_PORT} -U ${DB_VARIANT_USERNAME} -f query.sql

execute sql file without password

# option 1 - via pgpass file
echo "${DB_VARIANT_HOST}:${DB_VARIANT_PORT}:${DB_VARIANT_DATABASE}:${DB_VARIANT_USERNAME}:${DB_VARIANT_PASSWORD}" > ~/.pgpass
chmod 0600 ~/.pgpass

# option 2 - via export variable - has precedence over pgpass file
unset PGPASSWORD
export PGPASSWORD=$DB_VARIANT_PASSWORD
echo $PGPASSWORD

psql -h ${DB_VARIANT_HOST} -p ${DB_VARIANT_PORT} -d ${DB_VARIANT_DATABASE} -U ${DB_VARIANT_USERNAME} -f clean-airflow.sql

connect to db

# connect
psql -U workflowmonitoring -d workflowmonitoringdb
# exit
\q

command inside client

-- help
\h
\?

-- 
-- list of all databases
\l
-- list of all tables
-- CREATE TABLE testaccount01_variant( id INTEGER PRIMARY KEY, variant_key VARCHAR(64));
SELECT table_name FROM information_schema.tables WHERE table_schema='public';
-- list of all tables
\dt
-- list of all views
\dv
-- describe object
\d my_table_name
-- save output of query to file
\o
-- execute external file
\i 
-- execute command line
\!

get version

SHOW server_version;
SELECT version();

internal tables

select * from pg_tables;
SELECT schemaname, relname FROM pg_stat_user_tables;  

create database create user create role

CREATE USER $DB_VARIANT_USERNAME WITH PASSWORD '$DB_VARIANT_PASSWORD'
GRANT $DB_VARIANT_USERNAME TO main_database;
CREATE DATABASE $DB_VARIANT_DATABASE OWNER $DB_VARIANT_USERNAME;
-- 
CREATE USER qa_read_only_xxxxx WITH PASSWORD 'xxxxxxx';
-- grant read only access 
GRANT connect ON database w5a823c88301e9 TO qa_read_only_xxxxx;
GRANT select on all tables in schema public to qa_read_only_xxxxx;

Common operations

-- create schema
CREATE SCHEMA IF NOT EXISTS airflow_02;
DROP SCHEMA IF EXISTS airflow_02;
-- print all schemas
select s.nspname as table_schema,
       s.oid as schema_id,  
       u.usename as owner
from pg_catalog.pg_namespace s
join pg_catalog.pg_user u on u.usesysid = s.nspowner
order by table_schema;

# row number
select ROW_NUMBER () OVER (ORDER BY sku), sku from w60be740099999931852ba405_variant group by sku;