Why Client Portals Matter (More Than You Think)

Its difficult to underestimate the importance of client portals and hopefully the above statistics give you some impression of how important they are. These show number of application coming through eplanning (Scotland in one Quarter) They were obtained through Orkney Council’s website I couldn’t find a direct link from eplanning themselves and although I have access to more complete statistics they haven’t been officially published unlike the above. What you see though is the tremendous amount of automated work that eplanning in particular is managing. Another important example is Self Assessment Taxation – it is likely that the quality and quantity of such systems are going to steadily increase.

Here are some reasons below why it might be a good idea to really concnetrate on them although I think the case for them is now beyond doubt.

If your clients still rely on email threads, ad-hoc file links, and the occasional “just checking in” call, there’s a better way. A client portal centralises communication, documents, timelines, and approvals in one secure place. It’s not just nicer—it’s operationally smarter.

What is a client portal, really?
A client portal is a secure, branded website where clients can log in to see exactly what matters to them: project status, documents, messages, invoices, tasks, and data visualisations. Think of it as your project hub and your client’s single source of truth.

The five biggest wins
1) Clarity beats chaos
One place for everything. Messages, files, versions, decisions, and deadlines live together.
Fewer “can you resend that?” moments. Version control becomes simple and auditable.
Instant context. New stakeholders can self-serve the backstory without pinging you.

2) Faster decisions, fewer blockers
Tasks with due dates keep momentum.
Automated reminders reduce nudges and manual follow-up.
Structured approvals (with timestamps) remove ambiguity about who signed off and when.

3) Trust through transparency
Real-time status shows progress without status-chasing.
Change logs and audit trails make governance straightforward.
Data sharing done right. Dashboards, maps, and reports are available on demand.

4) Security and compliance made practical
End-to-end encryption and access controls beat email attachments every day.
Granular permissions (by project, folder, or role) reduce accidental oversharing.
GDPR-friendly workflows: clear data retention, access requests, and redaction paths.

5) Repeatable processes = scalable business
Templates for onboarding, project plans, and reports create consistency.
Automations (e.g., filing, naming, and notifications) cut admin time.
Analytics surface cycle times, bottlenecks, and utilisation.
How a portal changes the client experience
“What’s happening now?” A live timeline and milestones replace guessing.
“Where’s that file?” Searchable, versioned documents—no more digging in inboxes.
“When will I get an answer?” Defined SLAs, visible queues, and response-time metrics.
“What do I need to do?” Assigned actions with due dates and explanations.
Example use cases (and why they matter)
Planning & development projects: Share drawings, consultation responses, and decision notices with a clear approval trail. Pin a live constraints map so everyone sees the same data layer.
Data & GIS deliverables: Publish interactive maps and dashboards—no emailing huge files. Provide a changelog when datasets update (schema changes, CRS notes, metadata).
Professional services: Scope, proposals, invoices, and contract variations live alongside project work so commercials never drift from delivery.

Features worth prioritising
Secure document vault with versioning, preview, and e-signatures.
Discussions tied to artefacts (comment on the map/report, not in a separate email).
Task & milestone tracking with dependencies and automatic reminders.
Data embeds (maps, dashboards, analytics) that render inside the portal.
Granular permissions (client, partner, internal; read vs. edit).
Search & audit logs that are actually usable.
Branding and custom domains to feel like your service, not a third-party tool.
API & integrations (drive, email, billing, helpdesk, issue trackers).

Metrics to prove ROI

Track these for the first quarter after launch:

Email volume per project (should drop).
Average approval time (should shorten).
On-time milestone rate (should rise).
Client satisfaction (CSAT/NPS) (should improve).
Rework due to version confusion (should fall).
Time to onboard a new stakeholder (should be minutes, not days).
Common objections (and how to answer)
“Clients won’t log in.” They will if the portal is the easiest way to get answers, files, and approvals. Reduce friction with SSO and smart notifications that deep-link to the right place.
“Email works fine.” Email is fine for notes; it’s poor for records, approvals, and version control. Portals turn communication into a system of record.
“It’s another tool to manage.” True—so choose a portal that integrates with your stack and templatise the setup. The first project takes effort; the tenth is nearly automatic.

    Implementation checklist

Pick 1–2 high-leverage projects for a pilot (clear milestones, multiple stakeholders).
Define a standard folder and naming scheme and lock it in as a template.
Decide who sees what (roles, groups, external partners).
Set up automations (e.g., “when a file is approved, notify X and move to Y”).
Create two dashboards: one for clients (outcomes), one for your team (throughput).
Write a 1-page client guide with screenshots and a 10-minute onboarding call.
Measure the before/after using the metrics above.

Conclusion
Client portals should pay for themselves in fewer delays, cleaner records, faster decisions, and happier clients. They replace inbox archaeology with clarity, and they turn your process into a repeatable product. If you want to deliver work with less friction—and prove the value you deliver—build your next project around a portal.

A Practical Framework for Digital Transformation

Digital transformation is often described as a destination — a point at which an organisation becomes “fully digital.” In reality, it’s not a destination at all. It’s a continuous journey of adaptation, integration, and improvement.

Based on experience across different industries, here’s a framework that captures the core strategies for digital transformation and shows how they can be applied in practice.

1. Build a Strong Data Foundation

Transformation begins with data. Moving from spreadsheets, ad hoc files, or legacy silos into industrial-grade databases provides the reliability and scalability organisations need.

Why it matters: Strong databases ensure integrity, security, and a single source of truth.

What to do: Standardise how data is ingested, stored, archived, and protected. Make resilience and compliance a given, not an afterthought.

2. Automate for Efficiency

Repetitive, low-value tasks consume time and create bottlenecks. Automation — through scripts, workflow engines, or robotic process automation — removes these inefficiencies.

Why it matters: Frees up staff for creative and strategic work.

What to do: Target high-volume, rule-based processes first, then refine automation with continuous feedback loops.

3. Integrate and Connect Systems

No organisation runs on a single system. Value comes from connecting multiple programs so that information flows across functions.

Why it matters: Breaks down silos and unlocks richer insights.

What to do: Use APIs, middleware, and modular architectures to allow systems to “talk” to each other without creating rigid dependencies.

4. Empower Domains While Aligning Accountability

Digital tools should serve business domains, not the other way around. Allowing each domain to create unique solutions ensures relevance — but this must be coupled with clear accountability for outcomes and budgets.

Why it matters: Encourages ownership and reduces waste.

What to do: Balance flexibility with central governance so that innovation doesn’t lead to fragmentation.

5. Align Responsibilities Vertically

Too often, digital accountability is spread horizontally across functions — meaning no one truly owns results. Instead, embed end-to-end responsibility within vertical business units.

Why it matters: Each team can own and deliver on its digital performance.

What to do: Define clear KPIs and responsibility matrices for every unit.

6. Standardise Technology Choices

Every new tool, database, or programming language adds complexity. While some diversity is healthy, standardisation reduces costs and makes systems more sustainable.

Why it matters: Minimises technical debt and improves long-term interoperability.

What to do: Regularly review and rationalise platforms, aiming for consistency without stifling innovation.

7. Prioritise User Experience

Digital systems succeed only if people use them. A web-based user interface makes tools accessible across devices, locations, and roles.

Why it matters: Improves usability, inclusivity, and adoption.

What to do: Apply consistent design standards to ensure a unified user experience across systems.

8. Create Customer Portals

Transformation should extend beyond internal systems. Customers increasingly expect self-service, transparency, and direct access to information.

Why it matters: Builds trust and reduces demand on support teams.

What to do: Build customer portals with integrated feedback mechanisms for continuous improvement.

9. Adopt a Continuous Transformation Mindset

Finally, the most important principle: digital transformation never ends.

Why it matters: Technology, markets, and customer expectations constantly evolve.

What to do: Embed agility into culture, train staff in digital literacy, and continuously scan for emerging technologies that could add value.

The last step is always Digital Client Portals when you have all of your ducks in a row you can implement a proper Clent Portal – which could turn your IT estate into a profit centre rather than an additional burden.

Conclusion

Digital transformation isn’t about implementing a few shiny new systems — it’s about reshaping the way an organisation operates, collaborates, and delivers value.

By focusing on foundations (data, infrastructure, integration) → operations (automation, accountability, standardisation) → experience (UX, customer access) → mindset (continuous evolution), organisations can create a sustainable digital framework that evolves with them.

Because in the end, transformation is not a project. It’s a way of working.

Postgres / SQL Server / MariaDB – Table Setup to Store Persistent Default Values

In Postgres and a number of other databases you cannot directly set a columns’ default value to come from another table. Here I talk through how you can setup another table and use a trigger to pull the value from that table and insert it as a default value into a field of your choice if on Insert the value is null.

Postgres

-- Parent table
CREATE TABLE t002defaultvalues (
    gid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse text
);

-- Stock table
CREATE TABLE t001stock (
    gid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse text
);

-- Function to fetch default warehouse
CREATE OR REPLACE FUNCTION set_default_warehouse()
RETURNS trigger AS $$
BEGIN
    IF NEW.warehouse IS NULL THEN
        SELECT warehouse
        INTO NEW.warehouse
        FROM t002defaultvalues
        WHERE gid = 1;  -- adjust if you want another rule
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to apply the function
CREATE TRIGGER trg_t001stock_default_warehouse
BEFORE INSERT ON t001stock
FOR EACH ROW
EXECUTE FUNCTION set_default_warehouse();

SQL Server (T-SQL)
In SQL Server, defaults can’t reference other tables, so an AFTER INSERT trigger updates only the newly inserted rows (via the inserted pseudo-table).

-- Parent table
CREATE TABLE dbo.t002defaultvalues (
    gid BIGINT IDENTITY(1,1) PRIMARY KEY,
    warehouse NVARCHAR(MAX)  -- TEXT is deprecated in SQL Server
);

-- Stock table
CREATE TABLE dbo.t001stock (
    gid BIGINT IDENTITY(1,1) PRIMARY KEY,
    warehouse NVARCHAR(MAX) NULL
);
GO

-- Trigger: fill warehouse from t002defaultvalues when NULL
CREATE TRIGGER dbo.trg_t001stock_default_warehouse
ON dbo.t001stock
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s
    SET s.warehouse = dv.warehouse
    FROM dbo.t001stock AS s
    INNER JOIN inserted AS i
        ON s.gid = i.gid
    CROSS APPLY (
        SELECT TOP (1) warehouse
        FROM dbo.t002defaultvalues
        WHERE gid = 1      -- adjust rule if needed
    ) AS dv
    WHERE s.warehouse IS NULL;
END;
GO

-- Example
INSERT INTO dbo.t002defaultvalues (warehouse) VALUES (N'Main Depot');
INSERT INTO dbo.t001stock (warehouse) VALUES (NULL); -- will be set by trigger

MariaDB
In MariaDB, a BEFORE INSERT trigger can assign NEW.warehouse from a scalar subquery.

-- Parent table
CREATE TABLE t002defaultvalues (
    gid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    warehouse TEXT
);

-- Stock table
CREATE TABLE t001stock (
    gid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    warehouse TEXT NULL
);

DELIMITER $$

-- Trigger: fill warehouse from t002defaultvalues when NULL
CREATE TRIGGER trg_t001stock_default_warehouse
BEFORE INSERT ON t001stock
FOR EACH ROW
BEGIN
    IF NEW.warehouse IS NULL THEN
        SET NEW.warehouse = (
            SELECT warehouse
            FROM t002defaultvalues
            WHERE gid = 1    -- adjust rule if needed
            LIMIT 1
        );
    END IF;
END$$

DELIMITER ;

-- Example
INSERT INTO t002defaultvalues (warehouse) VALUES ('Main Depot');
INSERT INTO t001stock () VALUES (); -- will be set by trigger

Adjust WHERE gid = 1 to whatever selection rule you need (e.g., ORDER BY created_at DESC LIMIT 1, or WHERE active = 1 LIMIT 1).

Vibe Coding – Experimenting with simple tool creation – Combine 2 PDF

I’ve been using chatGPT to pull together simple tools that I have been hosting on my cpanel shared hosting.

If you have been following along you might have noticed others such as the Google Route Planner and the PG Dump command generator. Really great stuff. I have also been using AI to generate thumbnails. I would simply not have had the time to create these tools without AI so this is a big win for me.

This is my latest tool – a simple combine 2 PDF site that I use to merge files into a combined pdf. I still have some ideas on improvements but it immediately started saving me time… I’ve noticed a lot of people creating applications like this now.

Combine2PDF

“Because talent doesn’t scale” – Why IT Projects fail

Over the years I’ve participated in several complex IT projects—many of which succeeded—yet I’ve struggled to understand why some thrived while others failed or stalled, even when I had a front‑row participation seat. I’ve documented my observations and listed factors I believed contributed to success (Link)(Link), but I’ve never distilled them into a cohesive theory.

Recently, I discovered a YouTube vlog by Fredrick Christenson in which he proposed what he believes is the fundamental driver of project success. His insights resonate strongly with me, and I’m inclined to agree.

“Because talent doesn’t scale”

Bookmarked Link below
Why do so many large IT projects fail to meet expectations?

Azure Postgres – Extensions

I was looking around for a list of extensions that can be used with Postgres – didn’t really find a consistent good list.

Just out of curiosity I thought I would look up Azure Postgres documentation which details which ones are available on their platform. Seems quite complete. Importantly it has the foreign data wrapper for Oracle.

Microsoft Azure Documentation – Postgres Extensions

Now I’m not saying you should go out and load too many. I would always be looking to load a minimum number of extensions as I feel you always want to keep complexity to a minimum and extensions are more likely to be prone to dropped support which could be a real long term serious issue.

Azure – Postgres – Restore to Azure Postgres Flexible Server

Have not tested this extensively but I have managed to take a pg_dump file from a version 16 Postgres Local Server and restore it to a Postgres version 16.3 Flexible Server on the Azure platform. I have also briefly tested taking a dump file from azure.

Requirements

  • The computer from which you will restore the database has been IP white listed.
  • Check that you have a higher version of restore than the version of Azure Postgres Database / Overview see version no.
  • Remember to create the database into which you will be placing the dump file
  • host will be endpoint and will be something like myserver.postgres.database.azure.com
  • username will be admin login defined at creation (probably not postgres)
  • Port default is usually 5432
  • These details can be found by going to Settings / Connect –

    • /

    Interestingly the connect screen contains a code generator similar to the one that I created that will assist users in creating their pg_dump and pg_restor commands.



    Postgres Database Dump and Restore

    Please find below link to detailed write up of my investigations of Pg_Dump and Pg_Restore command line tools for postgres.

    Detailed description of using PG_Dump and PG_Restore

    As ever performing database backups is absolutely vital on so many levels. Knowing where your data is, how to back it up and restore it with regular backups of the system of record is your best defence against data breach, data corruption, server failure or server room/building failure.

    Learn backup and learn them well.

    I also created a online tool to help create commands to be fed into pg_dump and pg_restore



    PG_RESTORE – Parameters

    These are the parameters the PG_RESTORE takes there is no strict required order for the parameters. However there are some best practices and dependencies to keep in mind I list these at the end of this post

    Quick Command Generator here

    General Options

    -d, --dbname=DBNAME
    Specifies the database to restore into. If omitted, pg_restore outputs SQL to stdout.

    -h, --host=HOSTNAME
    Specifies the database server hostname or IP. Defaults to a local connection.

    -p, --port=PORT
    Specifies the database server port. Defaults to 5432.

    -U, --username=USERNAME
    Specifies the username for authentication.

    -f, --file=FILENAME
    Specifies the ouput file or directory where the dump or restore should be saved or read from

    -w, --no-password
    Prevents password prompt. Used when authentication does not require a password.

    -W, --password
    Forces a password prompt. Useful when the password is not stored in .pgpass.

    Example Command

    pg_restore -h prod-server-01 -p 5432 -U username -d db003target "c:/dump/2025db003_backup.dump"

    Format Options

    -F, --format=c|d|t|p
    Specifies the archive format:

    c → Custom format (from pg_dump -Fc)

    d → Directory format (from pg_dump -Fd)

    t → Tar format (from pg_dump -Ft)

    p → Plain SQL format (not applicable to pg_restore)

    Restore Control Options

    -a, –data-only
    Restores only data (excludes schema). Useful when schema already exists.

    -c, –clean
    Drops database objects before recreating them. Prevents conflicts with existing objects.

    -C, –create
    Includes a CREATE DATABASE command. The database will be recreated before restoring.

    -I, –index=NAME
    Restores only a specific index. Useful for partial restores.

    -j, –jobs=NUM
    Uses multiple jobs (parallel restore). Speeds up restore for -Fd format dumps.

    -L, –use-list=FILE
    Restores only objects listed in FILE. The list can be generated using pg_restore -l.

    -n, --schema=NAME
    Restores only a specific schema. Useful for restoring part of a dump.

    -P, --function=NAME(args)
    Restores only a specific function.

    -s, --schema-only
    Restores only the schema (no data). Useful for setting up database structure.

    -S, --superuser=USERNAME
    Specifies a superuser for certain restore operations.

    -t, --table=NAME
    Restores only a specific table. Useful for partial restores.

    -T, --trigger=NAME
    Restores only a specific trigger.

    Output Control Options

    -e, --exit-on-error
    Stops on the first error. Useful for debugging issues.

    -v, --verbose
    Provides detailed output. Helps with troubleshooting.

    --no-owner
    Does not restore ownership metadata. Useful when restoring as a different user.

    --no-privileges, --no-acl
    Does not restore GRANT/REVOKE statements.

    --disable-triggers
    Disables triggers on tables during restore. Useful for bulk data loading.

    --if-exists
    Includes IF EXISTS in DROP statements. Prevents errors if objects don’t exist.

    --strict-names
    Requires exact match of table/schema names. Prevents unintended restores.

    Help & Version

    -?
    Displays help information.

    --version
    Shows the version of pg_restore.

    General Rules of IMPLEMENTATION

    These are the parameters the PG_DUMP takes there is no strict required order for the parameters. However there are some best practices and dependencies to keep in mind

    Options Can Be Placed in Any Order

    pg_dump -U postgres -h localhost -d mydb -F c -f backup.dump

    pg_dump -F c -f backup.dump -U postgres -d mydb -h localhost

    Both commands are valid.

    Options That Take Arguments Must Be Followed by Their Values

    ✅ pg_dump -U postgres -h localhost -d mydb -F c -f backup.dump

    ❌ pg_dump -U -h localhost postgres -d mydb -F c -f backup.dump (Invalid because -U needs a value immediately after)

    Multiple Parameters Can Be Combined (if they don’t need arguments)

    ✅ pg_dump -U postgres -v -F c -f backup.dump

    ✅ pg_dump -U postgres -F c -v -f backup.dump

    ❌ pg_dump -F -U postgres c -f backup.dump (Invalid because -F needs an argument immediately after)

    For Readability, It’s Best to Keep Related Options Together

    Authentication options (-U, -h, -p, -W) should be placed together.

    Dump/restore format options (-F, -j) should be grouped logically.

    Filtering options (-n, -t, -T) should be near each other.

    Specific Cases to Watch Out For

    1. pg_restore with -d (Database Restore)

    If using pg_restore -d mydb, you must not use -C (create database) because the restore is running inside an existing database.

    If using -C, omit -d and restore into the default postgres database first: pg_restore -C -d postgres backup.dump

    2. pg_dump Parallel Jobs (-j)

    -j (parallel jobs) only works with directory format (-F d).

    Example: pg_dump -F d -j 4 -f my_backup_dir -U postgres -d mydb

    If -j is used with a non-directory format, pg_dump will fail.

    3. pg_restore and -j (Parallel Restore)

    -j (parallel restore) is only valid for directory format dumps (-F d).

    Example:pg_restore -j 4 -d mydb my_backup_dir

    Using -j with -F c (custom format) does not work unless restoring only selected tables.

    Best Practices

    ✅ Authentication options first (-U, -h, -p)
    ✅ Output control next (-F, -f)
    ✅ Content filtering later (-s, -a, -t, -n)
    ✅ Parallelization at the end (-j)

    PG_DUMP – Parameters

    These are the parameters the PG_DUMP takes there is no strict required order for the parameters. However there are some best practices and dependencies to keep in mind which is at the end.

    Quick Command Generator here

    General Options

    -d, --dbname=DBNAME
    Specifies the database to restore into. If omitted, pg_restore outputs SQL to stdout.

    -h, --host=HOSTNAME
    Specifies the database server hostname or IP. Defaults to a local connection.

    -p, --port=PORT
    Specifies the database server port. Defaults to 5432.

    -U, --username=USERNAME
    Specifies the username for authentication.

    -f, --file=FILENAME
    Specifies the ouput file or directory where the dump or restore should be saved or read from

    -w, --no-password
    Prevents password prompt. Used when authentication does not require a password.

    -W, --password
    Forces a password prompt. Useful when the password is not stored in .pgpass.

    Example below

    pg_dump -h prod-server-01 -p 5432 -U username -d db003 -F c -f "c:/dump/2025db003_backup.dump"

    Format Options

    -F, --format=c|d|t|p
    Specifies the archive format:

    c → Custom format (from pg_dump -Fc)

    d → Directory format (from pg_dump -Fd)

    t → Tar format (from pg_dump -Ft)

    p → Plain SQL format (not applicable to pg_restore)

    Restore Control Options

    -a, --data-only
    Restores only data (excludes schema). Useful when schema already exists.

    -c, --clean
    Drops database objects before recreating them. Prevents conflicts with existing objects.

    -C, --create
    Includes a CREATE DATABASE command. The database will be recreated before restoring.

    -I, --index=NAME
    Restores only a specific index. Useful for partial restores.

    -j, --jobs=NUM
    Uses multiple jobs (parallel restore). Speeds up restore for -Fd format dumps.

    -L, --use-list=FILE
    Restores only objects listed in FILE. The list can be generated using pg_restore -l.

    -n, --schema=NAME
    Restores only a specific schema. Useful for restoring part of a dump.

    -P, --function=NAME(args)
    Restores only a specific function.

    -s, --schema-only
    Restores only the schema (no data). Useful for setting up database structure.

    -S, --superuser=USERNAME
    Specifies a superuser for certain restore operations.

    -t, --table=NAME
    Restores only a specific table. Useful for partial restores.

    -T, --trigger=NAME
    Restores only a specific trigger.

    Output Control Options

    -e, --exit-on-error
    Stops on the first error. Useful for debugging issues.

    -v, --verbose
    Provides detailed output. Helps with troubleshooting.

    --no-owner
    Does not restore ownership metadata. Useful when restoring as a different user.

    --no-privileges, --no-acl
    Does not restore GRANT/REVOKE statements.

    --disable-triggers
    Disables triggers on tables during restore. Useful for bulk data loading.

    --if-exists
    Includes IF EXISTS in DROP statements. Prevents errors if objects don’t exist.

    --strict-names
    Requires exact match of table/schema names. Prevents unintended restores.

    Help & Version

    -?
    Displays help information.

    --version
    Shows the version of pg_restore.

    General Rules of IMPLEMENTATION

    These are the parameters the PG_DUMP takes there is no strict required order for the parameters. However there are some best practices and dependencies to keep in mind

    Options Can Be Placed in Any Order

    pg_dump -U postgres -h localhost -d mydb -F c -f backup.dump

    pg_dump -F c -f backup.dump -U postgres -d mydb -h localhost

    Both commands are valid.

    Options That Take Arguments Must Be Followed by Their Values

    ✅ pg_dump -U postgres -h localhost -d mydb -F c -f backup.dump

    ❌ pg_dump -U -h localhost postgres -d mydb -F c -f backup.dump (Invalid because -U needs a value immediately after)

    Multiple Parameters Can Be Combined (if they don’t need arguments)

    ✅ pg_dump -U postgres -v -F c -f backup.dump

    ✅ pg_dump -U postgres -F c -v -f backup.dump

    ❌ pg_dump -F -U postgres c -f backup.dump (Invalid because -F needs an argument immediately after)

    For Readability, It’s Best to Keep Related Options Together

    Authentication options (-U, -h, -p, -W) should be placed together.

    Dump/restore format options (-F, -j) should be grouped logically.

    Filtering options (-n, -t, -T) should be near each other.

    Specific Cases to Watch Out For

    1. pg_restore with -d (Database Restore)

    If using pg_restore -d mydb, you must not use -C (create database) because the restore is running inside an existing database.

    If using -C, omit -d and restore into the default postgres database first: pg_restore -C -d postgres backup.dump

    2. pg_dump Parallel Jobs (-j)

    -j (parallel jobs) only works with directory format (-F d).

    Example: pg_dump -F d -j 4 -f my_backup_dir -U postgres -d mydb

    If -j is used with a non-directory format, pg_dump will fail.

    3. pg_restore and -j (Parallel Restore)

    -j (parallel restore) is only valid for directory format dumps (-F d).

    Example: pg_restore -j 4 -d mydb my_backup_dir

    Using -j with -F c (custom format) does not work unless restoring only selected tables.

    Best Practices

    ✅ Authentication options first (-U, -h, -p)
    ✅ Output control next (-F, -f)
    ✅ Content filtering later (-s, -a, -t, -n)
    ✅ Parallelization at the end (-j)

    Postgres – Summary Check of Size No of Tables Views and Rows

    It is a good idea to do a general reconcilliation before and after a database dump and restore to peform some sense check as to whether restoration has completed successfully.

    SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

    And here is a further breakdown of numbers in a database

    DO $$
DECLARE
    rec         RECORD;
    rec_count   bigint;
    total_rows  bigint := 0;
    total_items int := 0;
BEGIN
    -- Create a temporary table to store individual counts
    CREATE TEMP TABLE temp_table_counts (
        table_name text,
        row_count  bigint
    ) ON COMMIT DROP;

    -- Loop through each user table and view (exclude system schemas)
    FOR rec IN
        SELECT table_schema, table_name, table_type
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
          AND table_type IN ('BASE TABLE', 'VIEW')
        ORDER BY table_name
    LOOP
        BEGIN
            -- Dynamically count the rows for the current table/view
            EXECUTE format('SELECT count(*) FROM %I.%I', rec.table_schema, rec.table_name)
            INTO rec_count;
        EXCEPTION WHEN OTHERS THEN
            -- In case of error (for example, if the view is not countable), set to NULL
            rec_count := NULL;
        END;

        -- Insert the result into our temporary table.
        INSERT INTO temp_table_counts(table_name, row_count)
        VALUES (rec.table_schema || '.' || rec.table_name, rec_count);

        -- Accumulate totals (if count succeeded)
        IF rec_count IS NOT NULL THEN
            total_rows := total_rows + rec_count;
        END IF;
        total_items := total_items + 1;
    END LOOP;

    -- Return the per-table results
    RAISE NOTICE 'Table Name | Row Count';
    FOR rec IN 
        SELECT table_name, row_count 
        FROM temp_table_counts 
        ORDER BY table_name
    LOOP
        RAISE NOTICE '% | %', rec.table_name, rec.row_count;
    END LOOP;

    -- Return the summary totals
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'Total Rows: %, Total Tables/Views: %', total_rows, total_items;
END $$;

    And on running this is the kind of result it should return

    SQL Server / Postgres – Pattern for Distinct List / Combo Box source

    This primarily refers to variable text fields but in all of the CRUD systems I’ve ever designed every single page has contained fields that use lookup dialogs. Their sheer ubiquity makes their design and operation particularly important significantly impacting an applications feel to users.

    In principle sourcing the values for a lookup appears to be a very simple choice.

  • Hard coded through the user interface (e.g Y/N)
  • Referenced to a separate “lookup” table


    • As you design more systems you start to see further important options

  • Referenced to distinct values sourced from a self referencing query of the table
  • Whether to store values as foreign keys
  • Whether to enforce validation at the database level.


    • Some general rules I follow;

    1)If the application allows users to bulk import data into a table consider using referencing to a distinct values list sourced from a self referencing query of the table.
    2)Almost NEVER alias values / use foreign keys in tables that will receive imported data but often better not to use it even if there is no import facility.
    3)The one exception to rule 2 can be customer account codes – account codes for clients can be incredibly important and often link to a table that specifies say a company name and address – things that may change in time. Users really need to be trained to understand the derivation of foreign keys to use systems or the codes will generally break import routines or break referencing and require correction. Account codes tend to be a weird case of a derived surrogate where the value is obviously derived from the lengthened value – e.g AMZN for Amazon or MSFT for Microsoft in NASDAQ listings. This works for NASDAQ because of its ubiquity and because NASDAQ has educated its users be prepared that you will need to educate your users if you use this pattern. For this reason my general rule is only use foreign key aliases for Account codes.
    4)If you have a field or table that will receive values which may be the subject of a lookup – don’t be too harsh with the field validation in the database data definition and be more permissive with the field variable type and length than you might expect.

    And here is suggested SQL that can be used to identify distinct values in a field.

    SELECT DISTINCT account_code FROM table_name;

    PHP Runner : Javascript Snow Effect at Login

    Something probably better to be implemented prior to Xmas but always good to vary things up and you will have to have a darker background screen at login prior to starting. I have tested this in Edge and Chrome. Minor thing – in google chrome it can be affected by your profile settings. I concluded that one of my google profiles had stricter security settings that prevented the execution of certain scripts or animation effects. Compare the settings in both profiles (e.g., JavaScript enabled/disabled, cookie settings, site data restrictions) to identify any discrepancies and if you have your application installed as an application ensure that you installed it using the working profile. Happy 2025 everyone.

    Code below

    function createSnowflake() {
    const snowflake = document.createElement('div');

    // Randomize position within the middle 95% of the viewport width
    snowflake.style.left = 2.5 + Math.random() * 95 + 'vw'; // Offset for 95% width

    snowflake.style.position = 'absolute';
    snowflake.style.top = '-10%';

    // Base size and variation
    const baseSize = 8; // Base size in pixels
    const sizeVariation = baseSize * 0.2; // 20% of the base size
    const size = baseSize + (Math.random() * 2 * sizeVariation - sizeVariation); // Random size adjustment

    snowflake.style.width = `${size}px`;
    snowflake.style.height = `${size}px`;

    snowflake.style.background = 'white';
    snowflake.style.borderRadius = '50%';
    snowflake.style.opacity = Math.random() * 0.5 + 0.5; // Random initial opacity
    snowflake.style.pointerEvents = 'none';
    snowflake.style.animation = `fall ${Math.random() * 10 + 10}s linear infinite`; // Slower speed

    document.body.appendChild(snowflake);

    // Remove the snowflake after its animation ends
    snowflake.addEventListener('animationend', () => {
        snowflake.remove();
    });
}

// Create snowflakes at regular intervals with fewer instances
setInterval(createSnowflake, 3600); // Further reduced frequency

// CSS for falling animation
const style = document.createElement('style');
style.textContent = `
@keyframes fall {
    0% {
        top: -10%; /* Start slightly above the viewport */
        opacity: 0; /* Fully transparent at the top */
    }
    10% {
        opacity: 1; /* Fade in after falling slightly */
    }
    100% {
        top: 110%; /* Move slightly below the viewport */
        opacity: 0; /* Fully fade out at the bottom */
    }
}
`;
document.head.appendChild(style);

    Gmail : Create an Email List

    Its 2025 and I’ve never created an email list in Gmail despite using it for over a decade. Whats more it just took me half an hour to figure it out. So I thought I would just pull it together because it will probably be a while before I need to use it.

    Synopsis – I’m used to email lists – Gmail doesn’t have this concept and calls them Labels instead.
    Additionally Labels in Gmail are not the same as Labels in Google Contacts – We will be creating and editing a label in Google Contacts which is then referenced in Gmail.

    First things first within your Google Menu go to Contacts – the icon looks as below (Note don’t go into Groups which has a similar icon)

    Here we create the email list we wish to Create…

    Click on the plus symbol next to the Labels item

    You should be presented with the following dialog

    Where it says New lable type in the name of the email list you wish to create..

    Hit save and the label should appear on the left hand side under Labels..

    Now you go to the Contacts on the left hand side and using the search facility look up the email you wish to include in the list here I have Royal Bank of Scotland for anonimity purposes.

    You want to hit the Label button beneath the contact…

    Next hit Apply

    You should see a Working dialog that appears on the screen for a period after which the count next to the newly created label should increase by 1

    e.g.

    You can now go back to Googlemail and this “Label” will be available for selection in the To / CC and Bcc as here.

    Postgres : Adjust default Database to inherit PostGIS and pgRouting on CREATE

    In Postgres when an administrator creates a database in the background Postgres is copying a template database.
    As default the new database is a copy of a database that is created at install called template1. This database is usually hidden and even if you are logged in as the Postgres superuser this database is not listed when you search on the server for all available database.

    Normally an administrator that is limited to dbcreator rights does NOT have rights to install extensions. This is particularly an issue when dealing with geography administrators that need to create databases with PostGIS and pgrouting extensions but you don’t want to elevate these administrators to superusers.

    Superuser administrators can however install these extensions on the template database which means that when an administrator with dbcreator rights logins in and runs the create database command the new database created will inherit the extensions postgis and pgrouting (and any other extensions you add to template1).

    Steps to Alter template1:
    Connect to the PostgreSQL Instance as a Superuser:

    Use a superuser account (e.g., postgres) to connect to the PostgreSQL instance.

    Allow template1 to be modified:

    By default, the template databases are marked as “databases not allowed for connection.” You need to change this setting temporarily.

    ALTER DATABASE template1 IS_TEMPLATE = false;

    Connect to template1:

    Now, connect to the template1 database.

    \c template1

    Install the PostGIS and pgRouting Extensions in template1:

    Once connected to template1, install the extensions:

    CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

    Restore template1 to be a template database:

    After installing the extensions, you need to restore template1 as a template:

    ALTER DATABASE template1 IS_TEMPLATE = true;

    Prevent Modifications to template1 (Optional):

    To prevent accidental changes to template1 in the future, you can revoke connection permissions:

    REVOKE CONNECT ON DATABASE template1 FROM public;

    Future Databases Will Inherit PostGIS and pgRouting:
    Now, whenever a new database is created using the template1 template, it will automatically inherit the PostGIS and pgRouting extensions:

    
CREATE DATABASE new_database_name;

    — This new database will have PostGIS and pgRouting installed.

    Caution:
    Modifying the default template1 means all databases created in the future will have the extensions, which might not be ideal if you don’t want PostGIS or pgRouting in every database. You could alternatively create a custom template and specify it when creating spatial databases.

    Example of creating a custom template:

    CREATE DATABASE template_postgis TEMPLATE template1;
\c template_postgis
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
ALTER DATABASE template_postgis IS_TEMPLATE = true;

    This way, you can create new spatial databases by specifying this custom template:

    CREATE DATABASE spatial_db TEMPLATE template_postgis;

    This approach provides more control over which databases include the extensions.

    Geoserver – CREATE View

    Geoserver has this really great feature whereby admins are able to create views that are held with the configuration of the Geoserver but separate from the database. This allows admins the ability to create views WITHOUT the need to have database admin rights on the server. This is particularly useful where you have an onsite vendor database that you would not otherwise be allowed to write views in but nonetheless you require some way to store spatially enabled views .

    Quick setup guide how to do this.

    Firstly go to layers and hit

    Select the Store that you are interested in

    Next you should see text and just hit it.

    Then there will be a place where you can put the SQL Statement in.

    Postgres : Create Table SQL Syntax Best Practice

    For primary keys it is now the recommendation to use identity during table creation.

    Apparently it explicitly conveys the purpose of the column and aligns with SQL standards thus it is important to use when possible rather than the former serial command. In theory this should be more portable as well.

    Depending on what source you read you may or may not need the following extension to create UUIDs – I needed it but I am told it should be in the main library.

    CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;

    So here is standard code for creating a table with three columns

    CREATE TABLE t001example 
(
pkid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
genuuid uuid DEFAULT gen_random_uuid(),
sitename varchar(500)
);

    And these are example values

    cPanel – Just gets better and better

    Good self service Web Hosting Management software is just a game changer for Dev Ops work.

    Every year I learn a bit more and every year they probably improve it a bit more. I am really very far off the cutting edge so I can’t speak to cutting edge improvements of the whole platform but I can talk to the options that I am continually learning.

  • Being able to quickly create, manage and backup additonal wordpress sites for zero cost
  • Being able to quickly create, manage and backup additional mysql databases for zero cost
  • Being able to host additional CRUD applications for zero cost (with thanks to PHPRunner)
  • Being able to create and manage email addresses easily

    • .

    AND NOW……

    Being able to quickly manage the update PHP versions is just amazing…

    cPanel:

    Log into cPanel

  • Under Software click MultiPHP Manager
  • Make a note of of your current version on the domain
  • Select an updated version of PHP and click Apply
  • Visit your site to confirm that the change has not broken your site.
  • If the site is broken, you can switch back to the previous PHP version

    • .

    Yes its that easy

    Things I want next
    Front end cPanel at InMotion backend SQL Server in Azure
    Front end cPanel at InMotion backend Postgres Server in Azure
    Postgres backends in cPanel

    cPanel rocks
    and so does PHP and PHPRunner
    and AI when it can learn to spell and count!
    (yes the picture annoys me as well)

    Device Name – Quickest ways to Identify on Windows x64

    Computing tries hard to obfuscate the device that is actually running the software that you are using. That is particularly the case with cloud computing and its particularly the case with web enabled software. Are you logging into the software that manages the database? or are you logging into the database itself?

    Irrespective software gets installed on an individual machine and it is often important to know the name of that machine as that software might have its own username and password requirement and you need to know the username/password combination for the particular installation you are trying to access.

    This is often confusing for database servers – increasingly I am trying to remember a broad map of what computers are where and what servers are where so I can at least reference them accurately.

    Two ways to find the DEVICE NAME on a windows machine (there are others)

    In the Windows Search bar type

    ABOUT

    or in the Windows Search bar type

    DEVICE MANAGER

    About will give you this

    Device Manager will give you this

    Note you can get to that About screen through file explorer by right clicking on this PC and selecting Properties – but it is slower than just typing about in the Windows Search box.