postgresql snippets

SQL for generating a report for each month of the year using PostgreSQL's crosstab

Tagged crosstab, pivot, postgresql, year  Languages sql

This is the report we want to generate:

┌──────────────────────────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬─────┬─────┐
│           key            │  jan  │  feb  │  mar  │  apr  │  may  │  jun  │  jul  │  aug  │  sep  │  oct  │ nov │ dec │
├──────────────────────────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────┼─────┤
│ Christian                │ 4209  │ 3627  │ 3686  │ 3109  │ 3605  │ 3506  │ 2892  │ 3380  │ 3262  │ 1821  │ ¤   │ ¤   │
│ Barney                   │ 17188 │ 17139 │ 16622 │ 14096 │ 17302 │ 17063 │ 13372 │ 16277 │ 16672 │ 9263  │ ¤   │ ¤   │
│ Donald                   │ 16078 │ 14627 │ 16518 │ 14241 │ 16397 │ 16655 │ 15739 │ 17639 │ 16178 │ 9588  │ ¤   │ ¤   │
│ Duck                     │ 9369  │ 9099  │ 10640 │ 9184  │ 10489 │ 10332 │ 9711  │ 11108 │ 10405 │ 6338  │ ¤   │ ¤   │
│ Jebus                    │ 17774 │ 16433 │ 18502 │ 15877 │ 17918 │ 17411 │ 15900 │ 18175 │ 17149 │ 10141 │ ¤   │ ¤   │
└──────────────────────────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴─────┴─────┘

You need the crosstab function which can be found in the tablefunc extension:

CREATE EXTENSION tablefunc;

Now generate the report using the crosstab function:

SELECT * 
FROM CROSSTAB(
  'SELECT key, month, SUM(value) FROM people_statistics WHERE month >= ''2017-01-01'' GROUP BY key, month ORDER BY key',
  'SELECT (DATE ''2017-01-01'' + (INTERVAL ''1'' month * generate_series(0,11)))::date')
AS
  ct_result (key bigint, jan bigint, feb bigint, mar bigint, apr bigint, may bigint, jun bigint, jul bigint, aug bigint, sep bigint, oct bigint, nov bigint, dec bigint);

We used the crosstab function that accepts two SQL queries as arguments. The first argument generates the rows for the query:

  • Column 1 is the key or identifier for the data, e.g., person name (Christian)
  • Column 2 contains the categories that will be used to pivot the data, e.g., year and month (2017-01)
  • Column 3 is the value that will be displayed, e.g., number of people (12)

The second argument generates the categories which in this example are the months of the year.

PostgreSQL 10 With Streaming Replication and PITR

Tagged pitr, postgresql, replication, standby, streaming  Languages bash, sql

Notes on how to configure streaming replication and PITR with PostgreSQL 10: https://www.postgresql.org/docs/current/static/warm-standby.html

Goal

               ┌──────────────────┐                
               │                  │                
               │ Web application  │                
             ┌─│                  │───┐            
             │ │                  │   │            
             │ └──────────────────┘   │            
          Reads                    Writes          
             │                        │            
             │                        │            
             │                        │            
             ▼                        ▼            
   ┌──────────────────┐     ┌──────────────────┐   
   │                  │     │                  │   
   │      Slave       │     │      Master      │   
┌─▶│                  │     │                  │──┐
│  │                  │     │                  │  │
│  └──────────────────┘     └──────────────────┘  │
│                                     │           │
└──────────────Streaming replication──┼───────────┘
                                      │            
                                      │            
                               WAL archiving       
                                      │            
              ┌──────────────────┐    │            
              │                  │    │            
              │  Network drive   │    │            
              │                  │◀───┘            
              │                  │                 
              └──────────────────┘                 
  • Master and slave (warm standby)

    • Master database backed up to slave server via streaming replication
    • WAL files copied from master to a network drive
  • Point-in-time-recovery (PITR) which is useful, for example, if someone deletes a table by mistake
  • Recovery possible even if both master and slave are lost
  • Daily and weekly backups available on a network drive
  • WAL files available on a network drive
  • Network drive backed up

Steps

  1. Install postgres 10 on master and slave
  2. Configure the master

    • wal_level=replica, archive_mode=on, archive_command, wal_keep_segments (optional with replication slots), etc in postgresql.conf
    • archive_command should copy WAL files to a shared network drive for additional redundancy, or the slave
    • create a replication slot for each slave, so that WAL files are stored long enough for slaves to receive them:
SELECT * FROM pg_create_physical_replication_slot('pg-slave-1');
  1. Configure the slave

    • hot_standby=on, etc in postgresql.conf (keep slave configuration as identical as possible to master)
    • primary_slot_name = ‘pg-slave-1’, standby_mode=on, restore_command, primaryconn_info, trigger_file in recovery.conf
    • restore_command should use the WAL files on the network drive that are copied there from the master
  2. Start primary server, stop the slave

Make sure, e.g., monit does not start the slave immediately again.

  1. Copy the master database to the slave with pg_basebackup

Make sure the slave’s data directory is empty:

psql -c '\db'
sudo /etc/init.d/postgresql stop
sudo rm -rf /var/lib/postgresql/10/main
sudo mkdir /var/lib/postgresql/10/main
sudo pg_basebackup -h 10.0.0.1 -D /var/lib/postgresql/10/main -P -U replicator -X stream -W
sudo chown -R postgres:postgres /var/lib/postgresql/10/main
sudo chmod -R 0700 /var/lib/postgresql/10/main
  1. Start slave
sudo service postgresql start
  1. Set up daily backups

Configure daily backups of PostgreSQL data to a network drive.

  1. Backup the backup

Configure daily and weekly backups of network drive.

  1. Check replication status

On master:

select pg_current_wal_lsn();

On slave:

select pg_last_wal_replay_lsn();

Both values should match.

Scenarios

  • Master server is killed

Promote slave to master with touch /tmp/promote-slave-to-master.trigger

  • Master server is killed, slave replication has fallen behind master

Restore slave from the WAL files located on the network drive. Or use a daily backup plus the WAL files if replication has fallen behind too much.

  • Master server and slave are killed

Restore the database from a daily backup and WAL files located on the network drive.

  • Master server, slave, and network drive are killed

Restore the database from a daily backup and the WAL files located on another network drive.

  • “drop database xxx” was run by mistake

Restore the database with PITR. For example, set recovery_target_time = ‘2017-06-06 06:06:06’ in recovery.conf.

  • Additional slaves are needed

Configure the new slave. Remember to create a new replication slot.

  • Slave is removed permanently

Delete the replication slot or the WAL archives will keep accumulating until the master’s disk is full.

Troubleshooting

  • Replication not working

Is recovery.conf in the data directory? Was it there before the server was started?

References

How to find all tables referring to another table in Postgres

Tagged column, postgresql  Languages sql

This example lists all tables that contain a column named ‘organization_id’:

SELECT c.relname AS table,
       a.attname AS column,
       n.nspname AS schema
FROM   pg_class AS c
       INNER JOIN pg_attribute AS a
               ON a.attrelid = c.oid
       JOIN pg_namespace n
         ON n.oid = c.relnamespace
WHERE  a.attname = 'organization_id'
       AND c.relkind = 'r'
       AND c.relname NOT LIKE 'pg_%'
ORDER  BY c.relname,
          a.attname  

"ERROR: permission denied for schema public" WTF

Tagged grant, postgresql, wtf  Languages sql

If you get this error:

ERROR:  permission denied for schema history

And, you’re running a query such as, for example, this one:

SELECT 1 FROM ONLY "history"."dummytafvel" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x;

And, your user has been granted access to the schema:

GRANT ALL ON history TO myself_and_i;

Then, you might have been hit over the head by the “permission denied for schema even as superuser” feature in PostgreSQL:

That’s a foreign key checking query.FK checks are done as the owner of the target table, not as the user who did the original query. So your problem is that the owner of the table lacks permissions to access the other table (or more specifically, the schema it’s in).

The solution is to grant access to the owner of the foreign keys to the schema in question.

References

https://sharingtechknowledge.blogspot.com/2012/03/postgresql-foreign-key-checking.html

How to use Rails' form_for with a PostgreSQL array

Tagged array, form_for, string, postgresql  Languages ruby

Using Rails’ form_for with a PostgreSQL array, for example, user roles can be done as follows:

Migration:

class CreateProjectMembers < ActiveRecord::Migration[6.0]
  def change
    create_table :project_members, id: :uuid do |t|
      ...
      t.string :roles, array: true, default: []

Model:

class ProjectMember < ApplicationRecord
  validate :validate_roles
  ROLES = %w[administrator collaborator].freeze

  def validate_roles
    return if roles.is_a?(Array) && roles.all? { |d| ROLES.include?(d) }

    errors.add(:roles, :invalid)
  end
end

View (slim-lang):

= form @project_member do |f|
  .form-group
    = f.label :roles, 'Roles'
    = f.check_box :roles, { label: 'Administrator', multiple: true }, 'administrator', nil
    = f.check_box :roles, { label: 'Collaborator', multiple: true }, 'collaborator', nil

Controller:

class ProjectMembersController < ApplicationController
  ...
  def update
    if @project_member.update(project_member_params)
    ...
  end

  def project_member_params
    params.require(:project_member).permit(roles: [])
  end

Note that if you have a “has_many through” relationship, instead of a PostgreSQL array, you can simply use:

= f.collection_check_boxes(:locale_ids, Locale.all, :id, :language) do |b|
  b.check_box

Reference: https://apidock.com/rails/v6.0.0/ActionView/Helpers/FormOptionsHelper/collection_check_boxes

For loops with LATERAL JOIN in PostgreSQL

Tagged postgresql, quarter, previous, for, loop  Languages sql
SELECT
  *
-- initial data that we want to loop through
FROM (
  SELECT
    *
  FROM
    financials
  WHERE
    quarter = '2020-06-30'
-- for each row run this query
) current_quarter LEFT JOIN LATERAL (
  SELECT
    revenue AS previous_revenue
  FROM
    financials
  WHERE
    company_id = current_quarter.company_id AND 
    -- calculate previous_quarter by subtracting one day
    quarter = to_char(date_trunc('quarter', current_quarter.quarter)::date - 1, 'yyyy-mm-dd')::date
) previous_quarter ON true;

Handling invalid CSV quoting with PostgreSQL's "COPY FROM"

Tagged copy from, postgresql, quote  Languages sql

PostgreSQL “COPY FROM” not importing all CSV rows and not reporting any errors?

This is an example of how to use a character that does not exist in the CSV file as a quote character to fix and handle invalid quoting issues:

CREATE TABLE xxx (
  id SERIAL,
  name VARCHAR,
  value VARCHAR,
  PRIMARY KEY (id)
);
COPY xxx(name, value)
FROM 'xxx.txt'
DELIMITER E'\t'
-- Use a character that does not exist in the CSV file as quote character to handle quoting
QUOTE E'\x01'
ENCODING 'utf8'
CSV HEADER;

Best of luck…

How to use PostgreSQL's "COPY FROM STDIN"

Tagged postgresql, copy from  Languages bash

This is an example of how to use PostgreSQL’s “COPY FROM STDIN”:

psql xxx_development -c "COPY table1(column1, column2) FROM STDIN DELIMITER E'\t' QUOTE E'\x01' ENCODING 'ISO-8859-1' CSV HEADER;" < sub.txt

NOTE: The following example where I put the “COPY FROM STDIN” in a file does not work:

# The COPY FROM STDIN SQL statement is in a file named copy.sql. This does not work:
psql xxx_development | cat copy.sql