Partition
Rudy DROP TABLE IF EXISTS sales CASCADE; -- Create parent table with partitioning CREATE TABLE sales ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, sale_date DATE NOT NULL, amount NUMERIC(10,…
postgres
Calendar Basic
-- Use or create database (adjust as needed) -- \c test; DROP TABLE IF EXISTS calendar_basic; CREATE TABLE calendar_basic ( date_id INT NOT NULL PRIMARY KEY, full_date DATE NOT NULL,…
Loop over PostgreSQL Tables
The following script loops over some PostgreSQL tables and grabs row counts
Move PostgreSQL Data Directory and Config Files
The following code is used to move the PostgreSQL data directory to a new directory.
python loops
import psycopg2 import time # Database connection details db_config = { 'dbname': 'users', # Name of the database 'user': 'your_username', # Replace with your PostgreSQL username 'password': 'your_password', # Replace…
Streaming Replication
Streaming1 In PostgreSQL, streaming replication is a process that allows data from a primary server (also called the master) to be replicated in real-time to one or more secondary servers…
Compare perms in PostgreSQL
Compare permissions between two roles in PostgreSQLyea.
PostgreSQL Transactions
The following query lists open PostgreSQL transactions
PostgreSQL Locks
The following script displays PostgreSQL locks The following lists blocking activity
Backup Script in PG
The following script is for backing up a postgres database Backup Via .env file The following script uses a .env instead of a .pgpass file