PostgreSQL Candies
  • Home
  • Back to “freeideas.cz”
Select Page

  • 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
  • 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

Recent Posts

  • Event triggers in PostgreSQL 11
  • Repair referenced sequences on tables
  • Problems with autovacuum during high load on server
  • How to get information about partitions for new native partitioning in PostgreSQL
  • Check / terminate postgresql processes
  • How to check blocked queries
  • 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
  • Transaction ID wraparound error
  • Block some user from connecting into PostgreSQL
  • Setting logical replication is not entirely straight forward…
  • Show source code of trigger function
  • Default “postgres” database – how to re-create it
  • PostgreSQL point in time recovery – experiences
  • Experiences with PostgreSQL Google Cloud SQL
  • Similarity of two strings – experiences
  • Bash script for emergency stop of PostgreSQL
  • Build latest pgloader from sources (Debian 9)
  • Temporary tablespace – small notes

Categories

  • Ansible
  • Backup and restore
  • Cheet sheets
  • Cloud
  • Constraints
  • Data types
  • Database
  • Design patterns
  • Errors, Exceptions
  • Example application
  • Extensions
  • Foreign data wrappers
  • Indexes
  • Javascript for PG
  • Locks
  • News
  • Optimizer
  • Partitioning
  • pg-barman / pg_basebackup
  • pgloader
  • PL/pqSQL
  • PostgreSQL + Python
  • PostgreSQL on Linux / BSD
  • PostgreSQL with ansible
  • psql
  • Replication
  • Security
  • Server
  • Sessions / Connections
  • Tables
  • Transactions
  • Triggers / Event triggers
  • Troubleshooting
  • Usefull programs
  • Views

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • PostgreSQL packages for Debian and Ubuntu
  • Useful SQLs to check contents of PostgreSQL9.4 shared_buffer
  • Install PostgreSQL 10 on Ubuntu
  • Facebook
  • Twitter
  • Google
  • RSS

Designed by Elegant Themes | Powered by WordPress

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept Reject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

Non-necessary

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.