PostgreSQL performance

Table of Contents Parse connection string Node Check schema identity Node Python Java Monitor Python Empty a table Disable referencing FK to to table to be emptied

Parse connection string

Node

npm install --global pg-connection-string;
node --eval "console.log(require('pg-connection-string').parse(process.argv[1]));" $DATABASE_URL

Check schema identity

Node

Untested: https://github.com/michaelsogos/pg-diff

Python

Get source at https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl

Or upload in Paas

scalingo --region <REGION> --app <APPLICATION> run --file check_postgres.pl bash 

Run

./check_postgres.pl --host=localhost -port=5432 --db=database --dbuser=postgres --action=connection

# should get 
# POSTGRES_CONNECTION OK: DB "database" (host:localhost) version 13.1 | time=0.03s 

./check_postgres.pl --host=localhost --port=5432 --dbname=pix --dbname2=pix_test --dbuser=postgres --action=same_schema

Or use file ~/.pg_service.conf

Refer to https://docs.postgresql.fr/10/libpq-connect.html#LIBPQ-PARAMKEYWORDS

[source]
host=host1
port=port1
user=user1
dbname=db1
password=password1
sslmode=require


[target]
host=host2
port=port2
user=user2
dbname=db2
password=password2
sslmode=require

Then run (do no compare sequence values and permissions from schema public)

./check_postgres.pl --dbservice="source" --action=connection
./check_postgres.pl --dbservice="target" --action=connection
./check_postgres.pl --dbservice="source,target" --action=same_schema --filter="noperm nosequence"

Java

https://www.endpointdev.com/blog/2020/02/compare-postgresql-schema-versions/

Monitor

Python

# download https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl

# check connection
./check_postgres.pl --host=localhost -port=5432 --db=database --dbuser=postgres --action=connection

# should get 
# POSTGRES_CONNECTION OK: DB "database" (host:localhost) version 13.1 | time=0.03s 

# run any action
./check_postgres.pl --host=localhost -port=5432 --db=database --dbuser=postgres --action=wal_files

# sample output
# POSTGRES_WAL_FILES CRITICAL: DB "database" (host:localhost) WAL files found: 23 | time=0.03s files=23;10;15

Empty a table

Disable referencing FK to to table to be emptied

Overview

CREATE TABLE referenced (id INTEGER PRIMARY KEY);
INSERT INTO referenced (id) VALUES(1);
INSERT INTO referenced (id) VALUES(2);

SELECT * FROM referenced;

CREATE TABLE referencing (
	id INTEGER PRIMARY KEY,
	external_id INTEGER,
	CONSTRAINT fk FOREIGN KEY(external_id)
		REFERENCES referenced(id)
);

INSERT INTO referencing (id, external_id) VALUES (1,1);
INSERT INTO referencing (id, external_id) VALUES (2,2);

SELECT * FROM referencing;

TRUNCATE TABLE referenced; // cannot truncate a table referenced in a foreign key constraint
DELETE FROM referenced; // ERROR:  update or delete on table "referenced" violates foreign key constraint "fk" on table "referencing"

\c database postgres
ALTER TABLE referencing DISABLE TRIGGER ALL;

\c database user
DELETE FROM referenced; // ERROR:  update or delete on table "referenced" violates foreign key constraint "fk" on table "referencing"

\c database postgres
ALTER TABLE referencing ENABLE TRIGGER ALL;

\c database user
SELECT * FROM referencing;

Alternative (all tables!)

\c database user
SET session_replication_role = 'replica'; // ERROR:  permission denied to set parameter "session_replication_role"

\c database postgres
SET session_replication_role = 'replica'; // ERROR:  permission denied to set parameter "session_replication_role"

\c database user
DELETE FROM referenced;

\c database postgres
SET session_replication_role = 'origin';

https://stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-available-for-postgresql-server