Ansible Ansible – simple playbook for installing PostgreSQL on Ubuntu/ DebianBackup and restore PostgreSQL point in time recovery – experiencespg_basebackup – experiencesMove PostgreSQL tablespace / data directory to another diskImport really big export files into PostgreSQL + change tablespace during importpg_dump & pg_restoreExport data into CSV formatCheet sheets Dump and restore postgresql users (roles)psql quick helpUseful regular expressionsCloud Experiences with PostgreSQL Google Cloud SQLConstraints Show all foreign keys for table with DDL constraint definitionDomain – very useful constraint standardizationHierarchy of tables based on foreign keysData types Problems with casts?PostgreSQL Timestamp vs. UNIX timestampDatabase Temporary tablespace – small notesCheck real sizes of columns in tablesPlaying with JSON data in PostgreSQLPostgreSQL and time zonesPostgreSQL 9.3 – find all dependent objects for materialized viewFind database creation dateParallel run of queries / functions / tasks using dblinkFind comments on database objectsFind dependecies for some objectAll pg_catalog objects in PostgreSQL 9.3 with explanationAnalyze data fileShow all sessions / connections on PostgreSQL serverWhere database stores data / PostgreSQL data directoryList all databases with sizeAll objects in databaseDesign patterns Use cases for WINDOW functionsUse cases for GROUP BY aggregate functionsPostgreSQL and high data load applicationParallel run of queries / functions using dblink – example 1Errors, Exceptions error: duplicate key value violates unique constraint pg_type_typname_nsp_indexPostgreSQL: No space left on deviceError “write failed” during SELECT command – on Linux machineUse stacked diagnostics to check error details (+ check of deadlock details)Example application List of values – bank Swift codesExtensions Similarity of two strings – experiencespl/sh language – small hintsmysql_fdwA little bit about pgstattuple extensionBetter query statistics with extension pg_stat_statementsCheck available / installed extensionsShow cstore_fdw foreign table filesTests of cstore_fdw extensionFirst tests of AGG extensionForeign data wrappers file_fdw extension small hintsIndexes Which indexes are really usedJavascript for PG JavaScript in PostgreSQL pl/v8 – tutorial 4 – trigger functionsJavaScript in PostgreSQL pl/v8 – error return_next function calls in context that cannot accept a setJavaScript in PostgreSQL pl/v8 – tutorial 3 – small JavaScript cheat sheetJavaScript in PostgreSQL pl/v8 – tutorial 2 – operations with tables – plv8.executeJavascript in PostgreSQL pl/v8 – tutorial 1 – overviewInstall javascript pl/v8 extension on PostgreSQL on UbuntuLocks Locks on objects with relationshipsLock monitoringExperiencies with table inheritance / partitioning in PostgreSQLUse stacked diagnostics to check error details (+ check of deadlock details)How to simulate deadlock in PostgreSQLProcesses waiting for lock – PostgreSQL 9.3Processes waiting for lock – PostgreSQL 8.4News PostgreSQL 10 – other highlightsPostgreSQL 10 – table partitioning – how to check partitions and manipulate with themPostgreSQL 10 – table partitioning – first impressionsInstall PostgreSQL 10 beta from sources – on Debian 8Parallelism in PostgreSQL 9.6.1 does not work withing CREATE TABLE / CREATE MATERIALIZED VIEW statementPostgreSQL and MySQL on cloud instances – few small hintsInstall pgAdmin3 1.22 on CentOS 7Install PostgreSQL 9.5 on Ubuntu 14.04Optimizer Small PostgreSQL performance tricksParse tree in messages outputPartitioning How to get information about partitions for new native partitioning in PostgreSQLpg-barman / pg_basebackup pg_basebackup – bash script for backup and archiving on Google storagepg_basebackup / pg-barman – restore tar backuppg-barman – check HW config of a server / instanceStreaming replication / pg-barman – archiving WAL logs using scriptpgloader Build latest pgloader from sources (Debian 9)PL/pqSQL GROUP BY CUBE – experiencesFor loop over VALUESHow to find column names of variable type record in PL/pgSQLHow to get DDL for table from PostgreSQLDrop all versions of a functionUsage of arrays in aggregationsSmall PostgreSQL performance tricksSimple query to find object in all databases on your serverList of columns and list of column definitions for dblinkVery simple query decompositionFind dependecies for some objectConvert ARRAY to rowsUse SELECT count in IF commandUse EXISTS in IF conditionCount according to conditionSelect * from VALUES / Using VALUES in SELECTAlias for value in RETURNPostgreSQL + Python How to compare database objects between two databases – program in pythonPostgreSQL on Linux / BSD Script for VACUUM FULL on all tablesSmall useful Linux commands with watchPostgreSQL on Ubuntu without swapVery simple bash script for monitoring waits on databaseUseful linux commands for PostgreSQL administratorLinux commands for simple analysis of PostgreSQL logsSimple patch / update scriptSimple bash script for quick copying of the tables between two servers / databasesInstall PostgreSQL 9.3 on PcBSD 10Check PostgreSQL versionPostgreSQL with ansible Reconfigure PostgreSQL to listen to all addresses and trust local connections using AnsibleInstall PostgreSQL of desired version on Ubuntu using Ansiblepsql psql – ask for value or set a value from command lineReplication Setting logical replication is not entirely straight forward…Streaming replication – increase of max_connections on master can shutdown all your replicasStreaming replication / pg-barman – archiving WAL logs using scriptAdd new disk and tablespace to PostgreSQL master and replicaBash script – clone PostgreSQL replicaStreaming replication – installation notesChanges in streaming replication in PostgreSQL 9.6pglogical on PostgreSQL 9.6 – small hints (on Debian)Security Block some user from connecting into PostgreSQLBash script for emergency stop of PostgreSQLSQL injection in PostgreSQLServer pgbouncer – small hintsOut of shared memory errorHow to easily find row count for tables (and nearly kill server in the process)Server process was terminated by signal 9: KilledReload server configurationFind biggest tables / database objects over all databases based on data file sizeDisplay / find / determine size of data files for tables / objectsFind database creation dateLog all queries into server logHow to check PostgreSQL processes from OSServer statisticsbgwriter statistic in PostgreSQL 8.4System information functionsSessions / Connections How to rename database with active sessionsParallel run of queries / functions / tasks using dblinkKill / end connection / session in PostgreSQLShow current session / process / connection IDShow all sessions / connections on PostgreSQL serverTables Arrays in tables and selectsCheck of missing data filesFind biggest tables / database objects over all databases based on data file sizeExperiencies with table inheritance / partitioning in PostgreSQLSystem column ctid as row identifierTransaction ID wraparoundHow to alter table column which is used in viewsFind newly inserted records / Identify changed tablesWhich indexes are really usedFind data file for tableTransactions Transaction ID wraparound errorParallel run of queries / functions / tasks using dblinkTransaction ID wraparoundAutonomous transaction in PostgreSQLFind newly inserted records / Identify changed tablesAverage time interval between checkpointsSettings for WALTriggers / Event triggers Show source code of trigger functionTriggers – some useful queriesEvent triggers in PostgreSQL 9.3 / 9.4Troubleshooting How to check blocked queriesDefault “postgres” database – how to re-create itHanging PostgreSQL session when called from GO lang or node.js programProblem with “drowsy” sessions in PostgreSQL 9.6 on Debian 8Debug / trace PostgreSQL with perfUsefull programs Check / terminate postgresql processesBash script – dump structures of parent tables one by one and upload them to Google storageBash script – dump functions one by one and upload them to Google storageBash script – dump tables one by one and upload dumps to Google storageSimple plpgsql script to set select only privileges for new user on a databaseBash script – sync table from PostgreSQL to BigqueryAdd indexes to partitionsBash script – synchronize daily partitions between 2 postgresql serversViews How to alter table column which is used in views
