PostgreSQL candies - PostgreSQL Candies

Ansible
- Ansible – simple playbook for installing PostgreSQL on Ubuntu/ Debian
Backup and restore
- PostgreSQL point in time recovery – experiences
- pg_basebackup – experiences
- Move PostgreSQL tablespace / data directory to another disk
- Import really big export files into PostgreSQL + change tablespace during import
- pg_dump & pg_restore
- Export data into CSV format
Cheet sheets
- Dump and restore postgresql users (roles)
- psql quick help
- Useful regular expressions
Cloud
- Experiences with PostgreSQL Google Cloud SQL
Constraints
- Show all foreign keys for table with DDL constraint definition
- Domain – very useful constraint standardization
- Hierarchy of tables based on foreign keys
Data types
- Problems with casts?
- PostgreSQL Timestamp vs. UNIX timestamp
Database
- Event triggers in PostgreSQL 11
- Temporary tablespace – small notes
- Check real sizes of columns in tables
- Playing with JSON data in PostgreSQL
- PostgreSQL and time zones
- PostgreSQL 9.3 – find all dependent objects for materialized view
- Find database creation date
- Parallel run of queries / functions / tasks using dblink
- Find comments on database objects
- Find dependecies for some object
- All pg_catalog objects in PostgreSQL 9.3 with explanation
- Analyze data file
- Show all sessions / connections on PostgreSQL server
- Where database stores data / PostgreSQL data directory
- List all databases with size
- All objects in database
Design patterns
- Use cases for WINDOW functions
- Use cases for GROUP BY aggregate functions
- PostgreSQL and high data load application
- Parallel run of queries / functions using dblink – example 1
Errors, Exceptions
- error: duplicate key value violates unique constraint pg_type_typname_nsp_index
- PostgreSQL: No space left on device
- Error “write failed” during SELECT command – on Linux machine
- Use stacked diagnostics to check error details (+ check of deadlock details)
Example application
- List of values – bank Swift codes
Extensions
- Similarity of two strings – experiences
- pl/sh language – small hints
- mysql_fdw
- A little bit about pgstattuple extension
- Better query statistics with extension pg_stat_statements
- Check available / installed extensions
- Show cstore_fdw foreign table files
- Tests of cstore_fdw extension
- First tests of AGG extension
Foreign data wrappers
- file_fdw extension small hints
Indexes
- Which indexes are really used
Javascript for PG
- JavaScript in PostgreSQL pl/v8 – tutorial 4 – trigger functions
- JavaScript in PostgreSQL pl/v8 – error return_next function calls in context that cannot accept a set
- JavaScript in PostgreSQL pl/v8 – tutorial 3 – small JavaScript cheat sheet
- JavaScript in PostgreSQL pl/v8 – tutorial 2 – operations with tables – plv8.execute
- Javascript in PostgreSQL pl/v8 – tutorial 1 – overview
- Install javascript pl/v8 extension on PostgreSQL on Ubuntu
Locks
- Locks on objects with relationships
- Lock monitoring
- Experiencies with table inheritance / partitioning in PostgreSQL
- Use stacked diagnostics to check error details (+ check of deadlock details)
- How to simulate deadlock in PostgreSQL
- Processes waiting for lock – PostgreSQL 9.3
- Processes waiting for lock – PostgreSQL 8.4
News
- PostgreSQL 10 – other highlights
- PostgreSQL 10 – table partitioning – how to check partitions and manipulate with them
- PostgreSQL 10 – table partitioning – first impressions
- Install PostgreSQL 10 beta from sources – on Debian 8
- Parallelism in PostgreSQL 9.6.1 does not work withing CREATE TABLE / CREATE MATERIALIZED VIEW statement
- PostgreSQL and MySQL on cloud instances – few small hints
- Install pgAdmin3 1.22 on CentOS 7
- Install PostgreSQL 9.5 on Ubuntu 14.04
Optimizer
- Small PostgreSQL performance tricks
- Parse tree in messages output
Partitioning
- How to get information about partitions for new native partitioning in PostgreSQL
pg-barman / pg_basebackup
- pg_basebackup – bash script for backup and archiving on Google storage
- pg_basebackup / pg-barman – restore tar backup
- pg-barman – check HW config of a server / instance
- Streaming replication / pg-barman – archiving WAL logs using script
pgloader
- Build latest pgloader from sources (Debian 9)
PL/pqSQL
- GROUP BY CUBE – experiences
- For loop over VALUES
- How to find column names of variable type record in PL/pgSQL
- How to get DDL for table from PostgreSQL
- Drop all versions of a function
- Usage of arrays in aggregations
- Small PostgreSQL performance tricks
- Simple query to find object in all databases on your server
- List of columns and list of column definitions for dblink
- Very simple query decomposition
- Find dependecies for some object
- Convert ARRAY to rows
- Use SELECT count in IF command
- Use EXISTS in IF condition
- Count according to condition
- Select * from VALUES / Using VALUES in SELECT
- Alias for value in RETURN
PostgreSQL + Python
- How to compare database objects between two databases – program in python
PostgreSQL on Linux / BSD
- Script for VACUUM FULL on all tables
- Small useful Linux commands with watch
- PostgreSQL on Ubuntu without swap
- Very simple bash script for monitoring waits on database
- Useful linux commands for PostgreSQL administrator
- Linux commands for simple analysis of PostgreSQL logs
- Simple patch / update script
- Simple bash script for quick copying of the tables between two servers / databases
- Install PostgreSQL 9.3 on PcBSD 10
- Check PostgreSQL version
PostgreSQL with ansible
- Reconfigure PostgreSQL to listen to all addresses and trust local connections using Ansible
- Install PostgreSQL of desired version on Ubuntu using Ansible
psql
- psql – ask for value or set a value from command line
Replication
- Check published and subscribed tables for PRIMARY KEYs
- Setting logical replication is not entirely straight forward…
- Streaming replication – increase of max_connections on master can shutdown all your replicas
- Streaming replication / pg-barman – archiving WAL logs using script
- Add new disk and tablespace to PostgreSQL master and replica
- Bash script – clone PostgreSQL replica
- Streaming replication – installation notes
- Changes in streaming replication in PostgreSQL 9.6
- pglogical on PostgreSQL 9.6 – small hints (on Debian)
Security
- Block some user from connecting into PostgreSQL
- Bash script for emergency stop of PostgreSQL
- SQL injection in PostgreSQL
Server
- pgbouncer – small hints
- Out of shared memory error
- How to easily find row count for tables (and nearly kill server in the process)
- Server process was terminated by signal 9: Killed
- Reload server configuration
- Find biggest tables / database objects over all databases based on data file size
- Display / find / determine size of data files for tables / objects
- Find database creation date
- Log all queries into server log
- How to check PostgreSQL processes from OS
- Server statistics
- bgwriter statistic in PostgreSQL 8.4
- System information functions
Sessions / Connections
- How to rename database with active sessions
- Parallel run of queries / functions / tasks using dblink
- Kill / end connection / session in PostgreSQL
- Show current session / process / connection ID
- Show all sessions / connections on PostgreSQL server
Tables
- Arrays in tables and selects
- Check of missing data files
- Find biggest tables / database objects over all databases based on data file size
- Experiencies with table inheritance / partitioning in PostgreSQL
- System column ctid as row identifier
- Transaction ID wraparound
- How to alter table column which is used in views
- Find newly inserted records / Identify changed tables
- Which indexes are really used
- Find data file for table
Transactions
- Problems with autovacuum during high load on server
- Transaction ID wraparound error
- Parallel run of queries / functions / tasks using dblink
- Transaction ID wraparound
- Autonomous transaction in PostgreSQL
- Find newly inserted records / Identify changed tables
- Average time interval between checkpoints
- Settings for WAL
Triggers / Event triggers
- Event triggers in PostgreSQL 11
- Show source code of trigger function
- Triggers – some useful queries
- Event triggers in PostgreSQL 9.3 / 9.4
Troubleshooting
- How to check blocked queries
- Default “postgres” database – how to re-create it
- Hanging PostgreSQL session when called from GO lang or node.js program
- Problem with “drowsy” sessions in PostgreSQL 9.6 on Debian 8
- Debug / trace PostgreSQL with perf
Usefull programs
- Repair referenced sequences on tables
- Check / terminate postgresql processes
- Bash script – dump structures of parent tables one by one and upload them to Google storage
- Bash script – dump functions one by one and upload them to Google storage
- Bash script – dump tables one by one and upload dumps to Google storage
- Simple plpgsql script to set select only privileges for new user on a database
- Bash script – sync table from PostgreSQL to Bigquery
- Add indexes to partitions
- Bash script – synchronize daily partitions between 2 postgresql servers
Views
- How to alter table column which is used in views