postgres Archives - 77 Interactive http://77interactive.com/?tag=postgres Rudy's Code snippets Wed, 18 Mar 2026 17:47:24 +0000 en-US hourly 1 https://wordpress.org/?v=6.9.4 http://77interactive.com/wp-content/uploads/2023/05/cropped-77-32x32.png postgres Archives - 77 Interactive http://77interactive.com/?tag=postgres 32 32 pgbench http://77interactive.com/?p=517 Wed, 18 Mar 2026 16:16:17 +0000 http://77interactive.com/?p=517 The following are scripts I use for pgbench. The scripts assume:

The post pgbench appeared first on 77 Interactive.

]]> The following are scripts I use for pgbench. The scripts assume:

  1. A database named example
  2. The tools are located in the folder /usr/pgsql-17/bin/
  3. A .pgpass file so we don’t have to worry about credentials
# Create the example database and run pgbench tests
/usr/pgsql-17/bin/pgbench -i -s 50 example

# Run pgbench with different configurations
# Basic test with 10 clients, 2 threads, 20000 transactions
/usr/pgsql-17/bin/pgbench -c 10 -n -j 2 -t 20000 -P 30 example

# Test with 40 clients, 2 threads, 50000 transactions
/usr/pgsql-17/bin/pgbench -c 40 -n -j 2 -t 50000 -P 30 example

# Clean up the pgbench tables
/usr/pgsql-17/bin/pgbench -i -I d

The post pgbench appeared first on 77 Interactive.

]]> pgbouncer http://77interactive.com/?p=513 Tue, 03 Mar 2026 05:15:05 +0000 http://77interactive.com/?p=513 Download pgbouncer user Get Auth Min pgbouncer.ini Max pgbouncer

The post pgbouncer appeared first on 77 Interactive.

]]> Download 
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo percona-release setup ppg-18
sudo apt install percona-pgbouncer -y

pgbouncer user

-- This file sets up the database role and function for PgBouncer authentication.
CREATE ROLE pgbouncer_auth WITH LOGIN PASSWORD 'REPLACE_ME_LONG_RANDOM5432';

-- Execute the following so the pgbouncer_auth role can access the get_auth function and schema.
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer_auth;

Get Auth

-- This file sets up the database role and function for PgBouncer authentication.
CREATE SCHEMA IF NOT EXISTS pgbouncer;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer_auth;

-- Create the get_auth function that PgBouncer will call to retrieve user credentials.
CREATE OR REPLACE FUNCTION pgbouncer.get_auth(p_usename text)
RETURNS TABLE(username text, password text)
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT rolname::text, rolpassword::text
  FROM pg_authid
  WHERE rolname = p_usename;
$$;

-- Execute the following so the pgbouncer_auth role can access the get_auth function and schema.
REVOKE ALL ON FUNCTION pgbouncer.get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(text) TO pgbouncer_auth;

Min pgbouncer.ini

[databases]
aws_dbz_001 = host=172.31.11.248 port=5432 dbname=example auth_user=pgbouncer_auth
aws_dbz_001_ro = host=172.31.9.92 port=5432 dbname=example auth_user=pgbouncer_auth
aws_dbz_sales = host=172.31.11.248 port=5432 dbname=sales auth_user=pgbouncer_auth
aws_dbz_sales_ro = host=172.31.9.92 port=5432 dbname=sales auth_user=pgbouncer_auth


[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

auth_type = scram-sha-256
auth_user = pgbouncer_auth
auth_query = SELECT username, password FROM pgbouncer.get_auth($1)
auth_file  = /etc/pgbouncer/userlist.txt 

; Use session pooling for long-running reports
; Use transaction pooling for short transactions (e.g. BI tools)
pool_mode = transaction

max_client_conn = 200

; Cap true DB concurrency for reporting
default_pool_size = 25
reserve_pool_size = 10
reserve_pool_timeout = 5

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

Max pgbouncer

[databases]
aurora_reader = host=<aurora-reader-endpoint> port=5432 dbname=<db> auth_user=pgbouncer_auth

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Use session pooling for long-running reports
pool_mode = session

max_client_conn = 2000

; Cap true DB concurrency for reporting
default_pool_size = 25
reserve_pool_size = 10
reserve_pool_timeout = 5

; Don’t kill long queries
query_timeout = 0

; Let users queue (tune to expectations)
query_wait_timeout = 1800   ; 30 minutes

; Keep BI tool connections stable
client_idle_timeout = 3600  ; 60 minutes

; Backend hygiene
server_idle_timeout = 600
server_lifetime = 3600      ; recycle backend conns hourly
server_connect_timeout = 15
server_login_retry = 15

; Logging
log_connections = 1
log_disconnections = 1
stats_period = 60

The post pgbouncer appeared first on 77 Interactive.

]]> pg_cron and more http://77interactive.com/?p=507 Wed, 11 Feb 2026 05:54:03 +0000 http://77interactive.com/?p=507 0) What you’ll end up with AWS confirms: 1) Create & attach a custom DB parameter group (required for pg_cron) In the AWS Console Parameters to set A) shared_preload_libraries AWS explicitly requires adding pg_cron to shared_preload_libraries. B) cron.database_name (choose where pg_cron stores its metadata) This makes pg_cron’s metadata tables live in sales, which is usually […]

The post pg_cron and more appeared first on 77 Interactive.

]]> 0) What you’ll end up with
  • pg_cron enabled at the instance level (via shared_preload_libraries) and installed in a database
  • pg_partman installed in sales (schema partman)
  • A pg_cron job that periodically runs pg_partman maintenance (partition creation/retention)

AWS confirms:

  • pg_cron requires adding pg_cron to shared_preload_libraries and restarting.
  • pg_partman is supported on RDS and is enabled per database via CREATE EXTENSION ….

1) Create & attach a custom DB parameter group (required for pg_cron)

In the AWS Console

  1. RDS → Parameter groups → Create parameter group
    • Parameter group family: postgres15
    • Type: DB Parameter Group
    • Name: e.g. pg15-cron-partman
  2. Open your new parameter group and edit:

Parameters to set

A) shared_preload_libraries

  • Add pg_cron to the list.
  • Keep what’s already there (commonly pg_stat_statements).
  • Example final value:
    • pg_stat_statements,pg_cron

AWS explicitly requires adding pg_cron to shared_preload_libraries.

B) cron.database_name (choose where pg_cron stores its metadata)

  • Default is typically the postgres database, and AWS notes the scheduler is set there by default.
  • Since you want to operate in sales, I recommend setting:
    • cron.database_name = sales

This makes pg_cron’s metadata tables live in sales, which is usually what you want for a single-app DB.

Note: changing these static params requires a DB reboot to take effect.

Attach the parameter group to the DB instance

  1. RDS → Databases → your instance → Modify
  2. Set DB parameter group = pg15-cron-partman
  3. Apply (immediate is fine, but it won’t take effect until reboot)
  4. Reboot the DB instance

2) Install pg_cron in the correct database

Because you set cron.database_name = sales, connect to sales as your master user (commonly postgres) and run:

-- Connect to sales
CREATE EXTENSION pg_cron;

AWS documents that creating the extension requires an account with rds_superuser privileges (your master user typically has what you need).

Quick verification

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_cron';

SELECT * FROM cron.job LIMIT 5;

3) Grant pg_cron usage to an application role (optional but recommended)

If you want a non-master role to schedule jobs, AWS recommends granting access to the cron schema.

Example (adjust role name):

-- As master user / rds_superuser
GRANT USAGE ON SCHEMA cron TO sales_app;

-- Optional: allow viewing job run history (least privilege is your call)
GRANT SELECT ON ALL TABLES IN SCHEMA cron TO sales_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA cron GRANT SELECT ON TABLES TO sales_app;

Important: the job will still fail if that role doesn’t have permissions on the objects it tries to touch.

4) Install pg_partman in the sales database

AWS’s RDS guide recommends creating a partman schema and installing the extension there.

-- Connect to sales
CREATE SCHEMA IF NOT EXISTS partman;

CREATE EXTENSION pg_partman WITH SCHEMA partman;

If you hit a permissions error, AWS notes you need rds_superuser or grant it to the role performing the install.

Verification

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_partman';

-- Display the age of each table in transactions along with its total size
select
 c.oid,
 age(c.relfrozenxid) as age_in_transactions,
 , pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
from  pg_class as c
join pg_namespace as n on c.relnamespace = n.oid
where c.relkind in ('r','t','m')
  and n.nspname not in ('pg_catalog', 'information_schema')
  and c.relname not like 'pg_%_toast'
order by age_in_transactions desc

-- Display the age of the database in transactions along with the autovacuum freeze max age setting
select datname,
       age(datfrozenxid) as age_in_transactions,
       current_setting('autovacuum_freeze_max_age')::int as autovacuum_freeze_max_age
from pg_database
order by age_in_transactions desc;

with max_age as (
    select 2000000000 as max_old_xid,
    setting as autovacuum_freeze_max_age
    from pg_settings
    where name = 'autovacuum_freeze_max_age' )
, per_database_stats as (
    select datname,
        age(datfrozenxid) as oldest_current_xid,
        datfrozenxid,
        max_old_xid::int,
        autovacuum_freeze_max_age::int
    from pg_database
    join max_age on (true)
    where datallowconn )
select max(oldest_current_xid) as oldest_current_xid,
       max(round(100.0*(oldest_current_xid/max_old_xid)::numeric, 2)) as percent_toward_wraparound,
       max(round(100.0*(oldest_current_xid/autovacuum_freeze_max_age)::numeric, 2)) as percent_toward_autovacuum_freeze_max_age
from per_database_stats;

The post pg_cron and more appeared first on 77 Interactive.

]]> Partition 2 http://77interactive.com/?p=505 Wed, 11 Feb 2026 05:46:22 +0000 http://77interactive.com/?p=505 The post Partition 2 appeared first on 77 Interactive.

]]> CREATE TABLE sales.orders_new3 ( created_at timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, order_id uuid NOT NULL DEFAULT gen_random_uuid(), customer_id bigint NOT NULL, amount numeric(12,2) NOT NULL, PRIMARY KEY (created_at, order_id) ) PARTITION BY RANGE (created_at); -- Helpful for point lookups by order_id CREATE INDEX ON sales.orders_new3 (order_id); 
# Create past and future partitions
DO $$
DECLARE
  start_ts timestamp(3) := '2023-01-01 00:00:00';
  -- create through next Sunday + 2 weeks for breathing room
  end_ts   timestamp(3) := (
              date_trunc('day', now())  -- today at midnight (local server time, but it's timestamp)
              - (extract(dow from now())::int) * interval '1 day'  -- go back to Sunday 00:00
              + interval '3 weeks'  -- this Sunday + 3 weeks (i.e., two weeks ahead from next boundary)
            )::timestamp(3);

  cur      timestamp(3);
  nxt      timestamp(3);
  partname text;
BEGIN
  cur := start_ts;

  WHILE cur < end_ts LOOP
    nxt := (cur + interval '1 week')::timestamp(3);

    -- partitions named like orders_new3_p20230101, orders_new_p20230108, ...
    partname := format('orders_new3_p%s', to_char(cur, 'YYYYMMDD'));

    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS sales.%I
         PARTITION OF sales.orders_new3
         FOR VALUES FROM (%L) TO (%L);',
      partname, cur, nxt
    );

    cur := nxt;
  END LOOP;
END $$;
-- Add the job to add new partitions
SELECT partman.create_parent(
  p_parent_table := 'sales.orders_new3',
  p_control      := 'created_at',
  p_type         := 'range',
  p_interval     := '1 week'
);

-- Verify the partitions
SELECT
  child.relname AS partition,
  pg_get_expr(child.relpartbound, child.oid) AS bound
FROM pg_inherits i
JOIN pg_class parent ON parent.oid = i.inhparent
JOIN pg_class child  ON child.oid  = i.inhrelid
WHERE parent.oid = 'sales.orders_new3'::regclass
ORDER BY child.relname;

The post Partition 2 appeared first on 77 Interactive.

]]> Partition http://77interactive.com/?p=502 Fri, 06 Feb 2026 16:56:48 +0000 http://77interactive.com/?p=502 The post Partition appeared first on 77 Interactive.

]]> 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, 2) NOT NULL, customer TEXT NOT NULL, PRIMARY KEY (id, sale_date) ) PARTITION BY RANGE (sale_date); -- create some indexes -- Create default partition Create table sales_default partition of sales default; -- January 2024 partition create table sales_2024_01 partition of sales for values from ('2024-01-01') to ('2024-02-01'); create table sales_2024_02 partition of sales for values from ('2024-02-01') to ('2024-03-01'); create table sales_2024_03 partition of sales for values from ('2024-03-01') to ('2024-04-01'); create table sales_2024_04 partition of sales for values from ('2024-04-01') to ('2024-05-01'); create table sales_2024_05 partition of sales for values from ('2024-05-01') to ('2024-06-01'); create table sales_2024_06 partition of sales for values from ('2024-06-01') to ('2024-07-01'); create table sales_2024_07 partition of sales for values from ('2024-07-01') to ('2024-08-01'); create table sales_2024_08 partition of sales for values from ('2024-08-01') to ('2024-09-01'); create table sales_2024_09 partition of sales for values from ('2024-09-01') to ('2024-10-01'); create table sales_2024_10 partition of sales for values from ('2024-10-01') to ('2024-11-01'); create table sales_2024_11 partition of sales for values from ('2024-11-01') to ('2024-12-01'); create table sales_2024_12 partition of sales for values from ('2024-12-01') to ('2025-01-01'); -- January 2023 partition create table sales_2023_01 partition of sales for values from ('2023-01-01') to ('2023-02-01'); create table sales_2023_02 partition of sales for values from ('2023-02-01') to ('2023-03-01'); create table sales_2023_03 partition of sales for values from ('2023-03-01') to ('2023-04-01'); create table sales_2023_04 partition of sales for values from ('2023-04-01') to ('2023-05-01'); create table sales_2023_05 partition of sales for values from ('2023-05-01') to ('2023-06-01'); create table sales_2023_06 partition of sales for values from ('2023-06-01') to ('2023-07-01'); create table sales_2023_07 partition of sales for values from ('2023-07-01') to ('2023-08-01'); create table sales_2023_08 partition of sales for values from ('2023-08-01') to ('2023-09-01'); create table sales_2023_09 partition of sales for values from ('2023-09-01') to ('2023-10-01'); create table sales_2023_10 partition of sales for values from ('2023-10-01') to ('2023-11-01'); create table sales_2023_11 partition of sales for values from ('2023-11-01') to ('2023-12-01'); create table sales_2023_12 partition of sales for values from ('2023-12-01') to ('2024-01-01'); -- January 2025 partition create table sales_2025_01 partition of sales for values from ('2025-01-01') to ('2025-02-01'); create table sales_2025_02 partition of sales for values from ('2025-02-01') to ('2025-03-01'); create table sales_2025_03 partition of sales for values from ('2025-03-01') to ('2025-04-01'); create table sales_2025_04 partition of sales for values from ('2025-04-01') to ('2025-05-01'); create table sales_2025_05 partition of sales for values from ('2025-05-01') to ('2025-06-01'); create table sales_2025_06 partition of sales for values from ('2025-06-01') to ('2025-07-01'); create table sales_2025_07 partition of sales for values from ('2025-07-01') to ('2025-08-01'); create table sales_2025_08 partition of sales for values from ('2025-08-01') to ('2025-09-01'); create table sales_2025_09 partition of sales for values from ('2025-09-01') to ('2025-10-01'); create table sales_2025_10 partition of sales for values from ('2025-10-01') to ('2025-11-01'); create table sales_2025_11 partition of sales for values from ('2025-11-01') to ('2025-12-01'); create table sales_2025_12 partition of sales for values from ('2025-12-01') to ('2026-01-01'); -- 2026 partitions CREATE TABLE sales_2026_01 PARTITION OF sales FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'); CREATE TABLE sales_2026_02 PARTITION OF sales FOR VALUES FROM ('2026-02-01') TO ('2026-03-01'); CREATE TABLE sales_2026_03 PARTITION OF sales FOR VALUES FROM ('2026-03-01') TO ('2026-04-01'); CREATE TABLE sales_2026_04 PARTITION OF sales FOR VALUES FROM ('2026-04-01') TO ('2026-05-01'); CREATE TABLE sales_2026_05 PARTITION OF sales FOR VALUES FROM ('2026-05-01') TO ('2026-06-01'); CREATE TABLE sales_2026_06 PARTITION OF sales FOR VALUES FROM ('2026-06-01') TO ('2026-07-01'); CREATE TABLE sales_2026_07 PARTITION OF sales FOR VALUES FROM ('2026-07-01') TO ('2026-08-01'); CREATE TABLE sales_2026_08 PARTITION OF sales FOR VALUES FROM ('2026-08-01') TO ('2026-09-01'); CREATE TABLE sales_2026_09 PARTITION OF sales FOR VALUES FROM ('2026-09-01') TO ('2026-10-01'); CREATE TABLE sales_2026_10 PARTITION OF sales FOR VALUES FROM ('2026-10-01') TO ('2026-11-01'); CREATE TABLE sales_2026_11 PARTITION OF sales FOR VALUES FROM ('2026-11-01') TO ('2026-12-01'); CREATE TABLE sales_2026_12 PARTITION OF sales FOR VALUES FROM ('2026-12-01') TO ('2027-01-01'); -- 2027 partitions CREATE TABLE sales_2027_01 PARTITION OF sales FOR VALUES FROM ('2027-01-01') TO ('2027-02-01'); CREATE TABLE sales_2027_02 PARTITION OF sales FOR VALUES FROM ('2027-02-01') TO ('2027-03-01'); CREATE TABLE sales_2027_03 PARTITION OF sales FOR VALUES FROM ('2027-03-01') TO ('2027-04-01'); CREATE TABLE sales_2027_04 PARTITION OF sales FOR VALUES FROM ('2027-04-01') TO ('2027-05-01'); CREATE TABLE sales_2027_05 PARTITION OF sales FOR VALUES FROM ('2027-05-01') TO ('2027-06-01'); CREATE TABLE sales_2027_06 PARTITION OF sales FOR VALUES FROM ('2027-06-01') TO ('2027-07-01'); CREATE TABLE sales_2027_07 PARTITION OF sales FOR VALUES FROM ('2027-07-01') TO ('2027-08-01'); CREATE TABLE sales_2027_08 PARTITION OF sales FOR VALUES FROM ('2027-08-01') TO ('2027-09-01'); CREATE TABLE sales_2027_09 PARTITION OF sales FOR VALUES FROM ('2027-09-01') TO ('2027-10-01'); CREATE TABLE sales_2027_10 PARTITION OF sales FOR VALUES FROM ('2027-10-01') TO ('2027-11-01'); CREATE TABLE sales_2027_11 PARTITION OF sales FOR VALUES FROM ('2027-11-01') TO ('2027-12-01'); CREATE TABLE sales_2027_12 PARTITION OF sales FOR VALUES FROM ('2027-12-01') TO ('2028-01-01'); 
insert into sales(sale_date, amount, customer)
select

        date '2023-01-01' + (random() * 364)::int
    ,
    round((2.99 + random() * (1870.00 - 2.99))::numeric, 2),
    (array[
        'Beauty','Clothing','Health','Industrial','Kids','Garden','Toys','Tools','Computers','Electronics',
        'Jewelry','Books','Movies','Grocery','Automotive','Outdoors','Music','Home','Sports','Games','Baby','Shoes'
    ])[floor(random()*22 + 1)]
from generate_series(1,50000);

CREATE TABLE sales_archive (LIKE sales INCLUDING ALL) 
PARTITION BY RANGE (sale_date);

ALTER TABLE sales DETACH PARTITION sales_2023_01;

ALTER TABLE sales_archive ATTACH PARTITION sales_2023_01 
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

The post Partition appeared first on 77 Interactive.

]]> Calendar Basic http://77interactive.com/?p=499 Mon, 02 Feb 2026 05:17:07 +0000 http://77interactive.com/?p=499 The post Calendar Basic appeared first on 77 Interactive.

]]> -- 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, date_name VARCHAR(10) NOT NULL, year_month INT, year_week INT, yqmd VARCHAR(10) NOT NULL, day_of_week INT NOT NULL, day_of_week_name VARCHAR(10) NOT NULL, day_of_month INT NOT NULL, month_name VARCHAR(10) NOT NULL, month_abbr VARCHAR(3) NOT NULL, day_of_year INT NOT NULL, is_week_day BOOLEAN NOT NULL, is_weekend_day BOOLEAN NOT NULL, is_holiday BOOLEAN NOT NULL, week_of_year INT NOT NULL, month_of_year INT NOT NULL, is_last_day_of_month BOOLEAN NOT NULL, calendar_quarter INT NOT NULL, calendar_semester INT NOT NULL, calendar_year INT NOT NULL, fiscal_month_of_year INT NOT NULL, fiscal_quarter INT NOT NULL, fiscal_semester INT NOT NULL, fiscal_year INT NOT NULL ); DO $$ DECLARE process_date DATE; BEGIN process_date := '2000-01-01'::DATE; WHILE process_date <= '2030-12-31'::DATE LOOP INSERT INTO calendar_basic ( date_id, full_date, date_name, year_month, year_week, yqmd, day_of_week, day_of_week_name, day_of_month, month_name, month_abbr, day_of_year, is_week_day, is_weekend_day, is_holiday, week_of_year, month_of_year, is_last_day_of_month, calendar_quarter, calendar_semester, calendar_year, fiscal_month_of_year, fiscal_quarter, fiscal_semester, fiscal_year ) SELECT TO_CHAR(process_date, 'YYYYMMDD')::INT AS date_id, process_date AS full_date, TO_CHAR(process_date, 'YYYY-MM-DD') AS date_name, TO_CHAR(process_date, 'YYYYMM')::INT AS year_month, (TO_CHAR(process_date, 'YYYY') || LPAD(EXTRACT(WEEK FROM process_date)::TEXT, 2, '0'))::INT AS year_week, (TO_CHAR(process_date, 'YYYY') || '0' || EXTRACT(QUARTER FROM process_date)::TEXT || LPAD(EXTRACT(MONTH FROM process_date)::TEXT, 2, '0') || LPAD(EXTRACT(DAY FROM process_date)::TEXT, 2, '0'))::VARCHAR(10) AS yqmd, EXTRACT(ISODOW FROM process_date)::INT AS day_of_week, TO_CHAR(process_date, 'Day')::VARCHAR(10) AS day_of_week_name, EXTRACT(DAY FROM process_date)::INT AS day_of_month, TO_CHAR(process_date, 'Month')::VARCHAR(10) AS month_name, TO_CHAR(process_date, 'Mon')::VARCHAR(3) AS month_abbr, EXTRACT(DOY FROM process_date)::INT AS day_of_year, CASE WHEN EXTRACT(ISODOW FROM process_date) NOT IN (6, 7) THEN TRUE ELSE FALSE END AS is_week_day, CASE WHEN EXTRACT(ISODOW FROM process_date) IN (6, 7) THEN TRUE ELSE FALSE END AS is_weekend_day, FALSE AS is_holiday, EXTRACT(WEEK FROM process_date)::INT AS week_of_year, EXTRACT(MONTH FROM process_date)::INT AS month_of_year, CASE WHEN EXTRACT(MONTH FROM process_date) <> EXTRACT(MONTH FROM process_date + INTERVAL '1 day') THEN TRUE ELSE FALSE END AS is_last_day_of_month, EXTRACT(QUARTER FROM process_date)::INT AS calendar_quarter, CASE WHEN EXTRACT(MONTH FROM process_date) < 7 THEN 1 ELSE 2 END AS calendar_semester, EXTRACT(YEAR FROM process_date)::INT AS calendar_year, EXTRACT(MONTH FROM process_date)::INT AS fiscal_month_of_year, EXTRACT(QUARTER FROM process_date)::INT AS fiscal_quarter, CASE WHEN EXTRACT(MONTH FROM process_date) < 7 THEN 1 ELSE 2 END AS fiscal_semester, EXTRACT(YEAR FROM process_date)::INT AS fiscal_year; process_date := process_date + INTERVAL '1 day'; END LOOP; END $$; -- Test query SELECT * FROM calendar_basic WHERE date_id >= 20260101 ORDER BY date_id LIMIT 1;

The post Calendar Basic appeared first on 77 Interactive.

]]> Loop over PostgreSQL Tables http://77interactive.com/?p=482 Thu, 21 Aug 2025 17:31:44 +0000 http://77interactive.com/?p=482 The following script loops over some PostgreSQL tables and grabs row counts

The post Loop over PostgreSQL Tables appeared first on 77 Interactive.

]]> The following script loops over some PostgreSQL tables and grabs row counts

-- 1) Where results will go
CREATE TEMP TABLE tmp_table_stats (
  schema_name      text,
  table_name       text,
  row_count        bigint,
  lastupdated_max  timestamptz,
  count_method     text  -- 'exact' (this script) or 'estimate' (alternative below)
);

-- 2) Gather stats for all non-system tables (exact counts)
DO $$
DECLARE
  r           RECORD;
  colname     text;
  cnt         bigint;
  max_ts      timestamptz;
BEGIN
  FOR r IN
    SELECT
      n.nspname AS schema_name,
      c.relname AS table_name,
      c.oid     AS relid,
      quote_ident(n.nspname) AS qi_schema,
      quote_ident(c.relname) AS qi_table
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'r'                    -- ordinary tables
      AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast')
    ORDER BY 1, 2
  LOOP
    -- exact row count
    EXECUTE format('SELECT count(*) FROM %s.%s', r.qi_schema, r.qi_table)
      INTO cnt;

    -- find a column named lastUpdated / lastupdated (quoted vs unquoted)
    SELECT a.attname
      INTO colname
    FROM pg_attribute a
    WHERE a.attrelid = r.relid
      AND NOT a.attisdropped
      AND lower(a.attname) = 'lastupdated'
    LIMIT 1;

    IF colname IS NOT NULL THEN
      EXECUTE format('SELECT max(%s) FROM %s.%s',
                     quote_ident(colname), r.qi_schema, r.qi_table)
        INTO max_ts;
    ELSE
      max_ts := NULL;
    END IF;

    INSERT INTO tmp_table_stats(schema_name, table_name, row_count, lastupdated_max, count_method)
    VALUES (r.schema_name, r.table_name, cnt, max_ts, 'exact');
  END LOOP;
END
$$;

The post Loop over PostgreSQL Tables appeared first on 77 Interactive.

]]> Move PostgreSQL Data Directory and Config Files http://77interactive.com/?p=454 Thu, 03 Jul 2025 18:49:31 +0000 http://77interactive.com/?p=454 The following code is used to move the PostgreSQL data directory to a new directory.

The post Move PostgreSQL Data Directory and Config Files appeared first on 77 Interactive.

]]> The following code is used to move the PostgreSQL data directory to a new directory.

# Move the data directory for PostgreSQL 15
# This script moves the data directory for PostgreSQL 15 to a new location.

# Stop PostgreSQL 15 service
sudo systemctl stop postgresql-15

# Create the new data directory for PostgreSQL 15
sudo mkdir -p /var/lib/postgresql/15/main
sudo mkdir -p /etc/postgresql/15/main

# Copy the data directory from PostgreSQL 15 to PostgreSQL 15
sudo rsync -av --progress /var/lib/pgsql/15/data/ /var/lib/postgresql/15/main/

# Move Configuration files to the new location
sudo cp /var/lib/pgsql/15/data/postgresql.conf /etc/postgresql/15/main/
sudo cp /var/lib/pgsql/15/data/pg_hba.conf /etc/postgresql/15/main/
sudo cp /var/lib/pgsql/15/data/pg_ident.conf /etc/postgresql/15/main/

# Change ownership of the new data directory
sudo chown -R postgres:postgres /var/lib/postgresql/15/main
sudo chown -R postgres:postgres /etc/postgresql/15/main

# Edit the PostgreSQL 15 configuration file
# Replace the data_directory and hba_file paths in the configuration file
# with the new paths to the moved data directory and configuration files.
sudo nano /etc/postgresql/15/main/postgresql.conf

# data_directory = '/var/lib/postgresql/15/main'          # use data in another directory
#                                                         # (change requires restart)
# hba_file = '/etc/postgresql/15/main/pg_hba.conf'        # host-based authentication file
#                                                         # (change requires restart)   
# ident_file = '/etc/postgresql/15/main/pg_ident.conf'    # ident configuration file
#                                                         # (change requires restart)
# # If external_pid_file is not explicitly set, no extra PID file is written.
# external_pid_file = '/var/run/postgresql/15-main.pid'   # write an extra PID file
#                                                         # (change requires restart)

# Edit the systemd service file for PostgreSQL 15
sudo systemctl edit postgresql-15.service
# If the service file does not exist, create it:
sudo nano /etc/systemd/system/postgresql-15.service

# Add the following content to the service file:
# [Service]
# Environment=PGDATA=/var/lib/postgresql/15/main
# ExecStart=
# ExecStart=/usr/pgsql-15/bin/postgres -D ${PGDATA} -c config_file=/etc/postgresql/15/main/postgresql.conf

# Use the correct path to the PostgreSQL 15 binary in the ExecStart line.
# ExecStart=/usr/pgsql-17/bin/postgres -D ${PGDATA} -c config_file=/etc/postgresql/15/main/postgresql.conf


# Reload the systemd daemon to apply the changes
sudo systemctl daemon-reload

# Start PostgreSQL 15 service
sudo systemctl start postgresql-15


# Compare directory sizes
sudo du -sh /var/lib/pgsql/15/data
sudo du -sh /var/lib/postgresql/15/main

# Remove the old data directory if everything is working fine
sudo rm -rf /var/lib/pgsql/15

The post Move PostgreSQL Data Directory and Config Files appeared first on 77 Interactive.

]]> python loops http://77interactive.com/?p=392 Fri, 06 Sep 2024 13:53:35 +0000 http://77interactive.com/?p=392 The post python loops appeared first on 77 Interactive.

]]> 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 with your PostgreSQL password 'host': 'localhost', # Replace with your host if needed 'port': '5432' # PostgreSQL default port } # Function to connect to the PostgreSQL database def connect_to_db(config): try: conn = psycopg2.connect(**config) print("Connection established.") return conn except Exception as error: print(f"Error connecting to the database: {error}") return None # Function to perform the updates def perform_updates(conn): try: cursor = conn.cursor() for i in range(10): print(f"Iteration {i+1}/10") # Step a.i: Append 'ccc' to plain_text append_query = """ UPDATE dbo.paragraph SET plain_text = plain_text || 'ccc' WHERE id IS NOT NULL; """ cursor.execute(append_query) conn.commit() # Commit the change print("Appended 'ccc' to plain_text") # Wait for 5 seconds time.sleep(5) # Step c.i: Remove the last 3 characters from plain_text remove_query = """ UPDATE dbo.paragraph SET plain_text = substring(plain_text FROM 1 FOR length(plain_text) - 3) WHERE id IS NOT NULL; """ cursor.execute(remove_query) conn.commit() # Commit the change print("Removed last 3 characters from plain_text") except Exception as error: print(f"Error performing updates: {error}") conn.rollback() # Rollback in case of an error finally: cursor.close() # Main execution if __name__ == "__main__": # Connect to the database connection = connect_to_db(db_config) if connection is not None: # Perform the updates in a loop perform_updates(connection) # Close the connection connection.close() print("Connection closed.")

The post python loops appeared first on 77 Interactive.

]]> Streaming Replication http://77interactive.com/?p=387 Wed, 28 Aug 2024 16:20:44 +0000 http://77interactive.com/?p=387 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 (also called standby servers). This replication ensures that secondary servers are nearly up-to-date with the primary server, providing high availability and disaster recovery. Here’s how […]

The post Streaming Replication appeared first on 77 Interactive.

]]> 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 (also called standby servers). This replication ensures that secondary servers are nearly up-to-date with the primary server, providing high availability and disaster recovery.

Here’s how it works, step-by-step:

Key Components:

  • Primary Server (Master): The main PostgreSQL server where data is actively written.
  • Secondary Server (Standby): The replica server(s) that maintain a copy of the primary server’s data.
  • WAL (Write-Ahead Log) Files: PostgreSQL uses WAL to log every change made to the database. These logs are central to the replication process.

How Streaming Replication Works:

  1. Write-Ahead Logging (WAL):
    • Every time a change (like an INSERT, UPDATE, or DELETE) is made to the primary server’s database, it’s first written to a WAL file.
    • WAL ensures data consistency by logging the changes before they are applied to the actual database files.
  2. Sending WAL to Secondary Servers:
    • The primary server continuously streams these WAL files to the secondary servers.
    • A WAL sender process on the primary server transmits the WAL entries to the secondary servers.
    • The secondary server, running a WAL receiver process, receives the stream of WAL data in near real-time.
  3. Replaying WAL on Secondary Servers:
    • The secondary servers apply (or “replay”) the changes from the WAL files to their own copies of the database, thus staying in sync with the primary.
    • This ensures that the secondary server has the same data as the primary server, except for the small delay caused by network transmission and processing time.
  4. Synchronous vs. Asynchronous Replication:
    • Asynchronous Replication: WAL data is sent to the secondary server after it’s written to the WAL on the primary server, but the primary server does not wait for confirmation from the secondary before acknowledging transactions to the client. This results in low latency but can lead to minor data loss if the primary fails.
    • Synchronous Replication: The primary server waits for acknowledgment from the secondary server before confirming a transaction. This ensures no data loss at the expense of higher latency.
  5. Failover:
    • In the event of a primary server failure, a secondary server can be promoted to the primary role. This process is called failover.
    • After the failover, the new primary server can begin accepting write transactions, and other secondary servers can start replicating from this new primary.
  6. Hot Standby:
    • PostgreSQL’s streaming replication also supports hot standby, which means the secondary servers can be used for read-only queries while still replicating from the primary server. This helps distribute the read workload and ensures better availability.

Flow of Streaming Replication:

  1. Primary server writes all changes to WAL files.
  2. WAL sender on the primary transmits WAL data to secondary servers.
  3. WAL receiver on secondary servers receives the WAL data and replays it to keep the database in sync.
  4. Secondary servers are continuously updated, with the ability to be promoted in case of primary failure.

Summary:

  • WAL files record all changes made to the primary server’s database.
  • The primary server sends WAL files to secondary servers in real time.
  • The secondary servers apply the WAL data to keep their copy of the database up-to-date.
  • The replication can be either synchronous (no data loss, higher latency) or asynchronous (lower latency, potential for minimal data loss).

Streaming replication ensures that the secondary servers are ready to take over in case of failure, providing a robust high-availability solution.

Streaming 2

How Streaming Replication Works in PostgreSQL

Streaming replication in PostgreSQL is a powerful feature that enables the creation of a highly available system capable of continuing operation even in the event of a failure. It involves the real-time transfer of updated information from a primary server to one or more standby servers, keeping the databases in sync across these servers. This mechanism leverages the Write-Ahead Log (WAL), a transaction log that records changes made to the database, to facilitate replication.

Key Components:

  • Primary Server: The main database server where transactions occur and changes are initially recorded in the WAL.
  • Standby Servers: Secondary servers that receive the WAL records from the primary server and apply them to replicate the database. They can serve read-only queries and take over as the primary server in case of a failure.
  • WAL Files: The Write-Ahead Log files contain all changes made to the database. These files are crucial for both crash recovery and replication.

Process Overview:

  1. WAL Generation: Whenever a change is made to the database on the primary server, PostgreSQL writes these changes to the WAL before they are committed to the database. This ensures data integrity and durability.
  1. WAL Shipping: The WAL records are sent from the primary server to the standby servers in real-time. This process is managed by the WAL sender on the primary server and the WAL receiver on the standby server.
  1. Applying WAL Records: Upon receiving the WAL records, the standby servers apply these changes to their own databases, thus maintaining consistency with the primary server.

Configuration and Setup:

  • Primary Server Configuration: Requires adjustments to postgresql.conf and pg_hba.conf. Key settings include enabling WAL archiving (archive_mode = on), specifying the maximum number of concurrent connections (max_connections) and WAL senders (max_wal_senders), and configuring the WAL level (wal_level = replica).
  • Standby Server Configuration: Also involves modifications to postgresql.conf, including enabling hot standby mode (hot_standby = on), setting the connection information to the primary server (primary_conninfo), and defining the restore command for retrieving WAL archives if needed.

Benefits and Considerations:

  • High Availability: Streaming replication provides a robust solution for achieving high availability by allowing standby servers to take over seamlessly in case the primary server fails.
  • Load Balancing: Standby servers can handle read-only queries, distributing the load and improving query performance.
  • Disaster Recovery: By replicating data to geographically distant locations, streaming replication supports disaster recovery strategies.

Synchronous vs. Asynchronous Replication:

  • Synchronous Replication: Ensures that transactions are committed on the primary server only after they have been replicated to the standby servers, providing strong consistency but potentially impacting performance.
  • Asynchronous Replication: Transactions are committed on the primary server without waiting for confirmation from the standby servers, offering better performance at the risk of potential data loss in case of a failure.

In summary, streaming replication in PostgreSQL leverages WAL files to replicate changes from a primary server to one or more standby servers, enhancing system availability, supporting load balancing, and facilitating disaster recovery. Proper configuration of both primary and standby servers is essential for effective replication.

Streaming 3

Streaming replication in PostgreSQL is a mechanism that allows a primary PostgreSQL server to continuously replicate its data to one or more standby servers in real time. This ensures high availability and redundancy by keeping the standby servers synchronized with the primary.

Key Components:

  • Primary Server: The primary server is the main source of data. It processes transactions and writes changes to the Write-Ahead Log (WAL).
  • Secondary Server: Also known as a standby server, it receives WAL records from the primary server and applies them to its own database, keeping it in sync.
  • Write-Ahead Log (WAL): A journal file that records changes made to the database. When a transaction commits, PostgreSQL writes a record to the WAL before acknowledging the transaction as successful.

How It Works:

  1. WAL Generation: The primary server generates WAL records as transactions are committed.
  2. WAL Streaming: The primary server streams the WAL records to the secondary server(s) over a network connection.
  3. WAL Application: The secondary server receives the WAL records and applies them to its own database, ensuring that the data on the secondary server remains consistent with the primary.

Benefits of Streaming Replication:

  • High Availability: If the primary server fails, a standby server can take over its role, ensuring minimal downtime.
  • Disaster Recovery: Streaming replication provides a mechanism for recovering data in case of a disaster.
  • Read-Only Load Balancing: Read-only queries can be directed to standby servers to reduce load on the primary server.
  • Asynchronous or Synchronous Replication: PostgreSQL supports both asynchronous and synchronous replication modes. In asynchronous mode, the primary server acknowledges a transaction as successful as soon as the WAL record is written to disk. In synchronous mode, the primary server waits for the WAL record to be applied on a standby server before acknowledging the transaction.

Streaming replication is a powerful feature of PostgreSQL that provides high availability, disaster recovery, and performance benefits. It is essential for mission-critical applications that require a high level of data reliability and availability.

The post Streaming Replication appeared first on 77 Interactive.

]]>